SQL Database Access

[SimpleMan78]

I've used SQL authentication and windows authentication in my connection strings to SQL server just fine. I've got a login form that the user has to put their username/password in and it verifies to AD to ensure that is who it is and then logs in. Currently it is set to windows authentication.

I don't want to use SQL authentication because when the user changes their password from Windows the connection will still work.

My question is, what if a user tries to log into my application from a pc who's currently logged in user does not have access to the sql database? My guess is the AD verification will pass but when it tries to create a connection to the SQL server it will fail because the logged in windows user doesn't have access.

Is there a way to some how create a connection string to a SQL server that has the AD information in it as well so that it can authenticate using that? ...instead of trusted?

Thanks.

[Reverend Jim]

Typically if you are using AD to authenticate then the user does not need to supply a username/password. The usual practice is to create a group in AD then add the appropriate users to that group. You then add the AD group to SQL server with appropriate access rights and manage access to the database through AD rather than SQL Server.

You said that the user supplies a username/password to login to your application. That implies that you are not using AD (the user's domain logon userid/password) to authenticate. That being the case, the user should be able to login to your appliciation from any PC, no matter who is currently logged on. So your connection string would use standard security rather than integrated security.

Howoever, I presume you need to authenticate access to your app as well as access to the underlying database. Why not set up a SQL account for that app for database access and let the user use a username/password for access to the app? When I had to set up access rights for processes I would create accounts such as EMSDATA_RO and EMSDATA_RW (RO for read only and RW for read/write).

[adam_k]

I've never used it, but I bet that if you use the "Run as a different user" option you can get authenticated with an AD account other than the one currently logged in.
To find the option hold Ctrl+Shift and right click the shortcut/exe

[SimpleMan78]

Thanks for the replies.

Jim, I've got an app that we are going to go live with next week...only 4 users at the moment. I do have an AD group setup and they are part of that group. When the user logs in, I've got some code that verifies the login information against AD...just to make sure it is who they say it is. If AD authentication passes, i then make sure they are setup in a user table for that username and then perform some data transactions. You sparked and idea for me to use. I think I will create a generic SQL username/password that the application will use for the data methods.

User will be authenticated by the AD check and then I will use a sql account that will aquire my data.

Thanks.