Connecting a Thread to a User

Question

Connecting a Thread to a User

Tom Carnahan

SSCommitted

Points: 1788
More actions
May 22, 2007 at 6:04 am

#173718

I inherited a MS-Access/SQL-Server application that was poorly designed. Much to my chagrine, the designer used ODBC for connecting to the backend and coded the SA user and password in the connection strings so everyone logging in has the SA user.
It would be very helpful to be able to identify which users made which changes to the data.
Question: is there anyway to identify the user other than by SQL Server logon ID?
My first thought was to force the FE to send the person's Windows Logon or computer name on first connection, then associate the logon ID with the thread/s it generates, however, that is the part I don't know how to do.
By the way, the application is under CMMI configuration and it will be like pulling teeth to get it changed. My customer probably will not pay to switch over to SQL-Server logins.
Thank you ahead of time for any ideas!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

Can you change the front end to use the Windows login through a trusted connection rather than sa? All you should have too do is relink the tables in access using trusted connection or change the connection string to use trusted connection if you are using connection strings instead of DSN. Of course you have to give access to all the users in SQL Server.

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 2

I would like to use trusted connections, but eventually, they want to blow away the MS-Access front-end and go to a web front-end. The CM board would not take kindly to my going that direction. My boss is not happy with the idea of maintaining passwords.

I do have the blessing of the powers to be to gradually migrate the front-end processes into stored procedures or user defined processes. I could require the front end to pass the person's identity on each call.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

A web front end can use trusted connections as well by setting the permissions in IIS and on the web directory structure to require a logon. That logon is passed to SQL in the trusted connection.

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 4

If I understand correctly, if I went to Windows Authentication with my current MS-Access front-end, I could:

- eliminate having to have a DSN

- not have to pass the SA UserID and PW in my ODBC table and Pass Thru query connection strings.

But then, I would still have to set each person up as a user in the system, right? How would I enter them in Users? Would I use the Windows (user friendly) name in the user wizard (e.g. Jones, John Paul)? or would I enter the NT Logon ID in as a user (e.g. JJones)?

Right now, the User-Admin person enters the userid and NT login into a table in the back-end. When they connect, the first thing that the front-end does is to see if there is a matching NT-Login ID in the table. If there is not, it kicks them out.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

You would still need the DSN for access.

The way we do it is create a domain group for app users and the group gets access to the database. That way you only need to move people in and out of the group instead of changing the security on sql server constantly. When you have a web front end you can use that same domain group to give access to the web site as well.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

It's less than perfect, but you could use the host_name() to get the accessing computer name rather than the user name. If you have regular desktop users, you can usually make the assumption that the user of the computer that the change came from is the user that made the change.