Login Authentication for Standard Accounts

We have a couple of vendor applications that create standard accounts.
These accounts have the same format as our domain accounts. For
example, my Windows account would be <domain>\<user_name>. The SQL
standard account would be <user_name>.
What we would like to do is change the login process to validate
against active directory either prior to or instead of checking the
SQL password. Everything else could remain the same.
This would be on SQL 2000 or SQL 2005. Any suggestions or other
insight would be appreciated.RogerT (roger.tompkins@.gmail.com) writes:
> We have a couple of vendor applications that create standard accounts.
> These accounts have the same format as our domain accounts. For
> example, my Windows account would be <domain>\<user_name>. The SQL
> standard account would be <user_name>.
> What we would like to do is change the login process to validate
> against active directory either prior to or instead of checking the
> SQL password. Everything else could remain the same.
> This would be on SQL 2000 or SQL 2005. Any suggestions or other
> insight would be appreciated.
I'm not really sure what you mean here. SQL Server provides to
means of authentication: SQL authentication and Windows authentication.
It sounds from your description that the vendor accounts are for
SQL authentication. But you cannot change a login from being an SQL
login, to be a Windows login. These two types of logins are competely
unconnected.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Logging uses of SELECT ?

Scenario:

-I'm brand new to SQL Server 2000

-I have a vendor supplied application running on SQLServer 2k.

-I need to log which of my users has seen which data (from a
particular table). It's a healthcare privacy thing.

-The application does not do this and the vendor has no interest in
implementing it, though it seems pretty easy if one has the app code.

So I don't control the application or its code but I need to catch
who's seen what. Note that I don't have to catch it all. In other
words, I don't need to catch every instance, only whether user X has
seen patient Y's data at all. So I'm talking about a fairly small data
set. The end result I'm heading toward is a case in which a patient
demands to know who's seen his health records. I go to a db table and
say "select username from <viewlogtable> where patient_id = XXX".

How do I do that? It'd be easy if I could it were possible to write a
select trigger, or if I had some hooks into the app, but as it is I am
stumped. This is trickier than logging updates and inserts...

Thanks,
JohnThere is no such thing as a SELECT trigger. You may be able to satisfy
the requirement by analyzing the SQL log. There are some third party
products that can be of use. Do a google on "Lumigent" for literature
on their product.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"John" <jgoodlea@.fhcrc.org> wrote in message
news:80ff7fc9.0312151444.41c57adc@.posting.google.c om...
> Scenario:
> -I'm brand new to SQL Server 2000
> -I have a vendor supplied application running on SQLServer 2k.
> -I need to log which of my users has seen which data (from a
> particular table). It's a healthcare privacy thing.
> -The application does not do this and the vendor has no interest in
> implementing it, though it seems pretty easy if one has the app code.
> So I don't control the application or its code but I need to catch
> who's seen what. Note that I don't have to catch it all. In other
> words, I don't need to catch every instance, only whether user X has
> seen patient Y's data at all. So I'm talking about a fairly small data
> set. The end result I'm heading toward is a case in which a patient
> demands to know who's seen his health records. I go to a db table and
> say "select username from <viewlogtable> where patient_id = XXX".

Unfortunately I can't see any way of doing that. There's no such thing as a
select trigger.

As another poster said, Lumigent may do this, but I doubt it since I don't
believe Selects are logged at all. (wouldn't make much sense.)

Another option is to run the SQL Profiler and setup a profile to look for
JUST selects, etc. This solution actually might work fairly well
short-term. And even long-term if you really tighten the profile down to
record ONLY what you need.

> How do I do that? It'd be easy if I could it were possible to write a
> select trigger, or if I had some hooks into the app, but as it is I am
> stumped. This is trickier than logging updates and inserts...

Really what the vendor should be doing is all access through stored procs.
Then the stored proc could handle security and handle logging.

> Thanks,
> John|||You would have to ensure that the users had to view that data by executing a
stored procedure, not with a direct SELECT statement.
I'm not sure how much control you have over the application, or if you can
modify it, but this is certainly a way of 'logging' SELECT statements
made against a table or set of tables. If you can, in fact, edit the
application then you can edit it to execute a procedure that simply inserts
into the log and selects as this <simplified>:

DECLARE PROCEDURE ViewPatientRecords
(
@.PatientID int
,@.UserID int
)
AS

INSERT INTO ViewLogTable(Patient_ID, Viewer_ID, View_Date)
VALUES(@.Patient, @.UserID, GETDATE())

SELECT *
FROM Patient_Record
WHERE Patient_ID = @.PatientID

"John" <jgoodlea@.fhcrc.org> wrote in message
news:80ff7fc9.0312151444.41c57adc@.posting.google.c om...
> Scenario:
> -I'm brand new to SQL Server 2000
> -I have a vendor supplied application running on SQLServer 2k.
> -I need to log which of my users has seen which data (from a
> particular table). It's a healthcare privacy thing.
> -The application does not do this and the vendor has no interest in
> implementing it, though it seems pretty easy if one has the app code.
> So I don't control the application or its code but I need to catch
> who's seen what. Note that I don't have to catch it all. In other
> words, I don't need to catch every instance, only whether user X has
> seen patient Y's data at all. So I'm talking about a fairly small data
> set. The end result I'm heading toward is a case in which a patient
> demands to know who's seen his health records. I go to a db table and
> say "select username from <viewlogtable> where patient_id = XXX".
> How do I do that? It'd be easy if I could it were possible to write a
> select trigger, or if I had some hooks into the app, but as it is I am
> stumped. This is trickier than logging updates and inserts...
> Thanks,
> John