September 25, 2008 at 2:39 pm
In our applications I have traditionally added utilities to add users to the database and defined roles to control access to stored procedures. We are looking at tying our applications to the AD to validate users. The paradigm that we are analyzing would be for the user to be validated against the AD at the application level and then to be able to use the user name for Windows Authentication into SQL Server.
This is where I am not clear. If I use Windows Authentication to log in SQL Server how is it that users are assigned roles which define procedure permissions? My thinking was that by using AD validation I would be able to eliminate the creation of user logins in SQL Server where the SQL Server logins are simply duplicates of the AD use definitions.
I am to new to AD validation so I am sure there is something that I do not understand about this paradigm. Any help in this area would be helpful.
September 25, 2008 at 8:15 pm
Add the AD groups as logins, not the users, and assign permissions to the group.
September 26, 2008 at 8:25 am
We connect AD groups to roles within a database and assign priveleges to the role. It works really well. We just don't get into managing individual logins.
The major shortcoming to this approach is in auditing. You won't know which user performed which action since the applications use a single login. So you either have to trash the approach or get the applications to pass in a user name with each transaction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2008 at 3:31 pm
I appreciate the feedback it is very helpful. But I need a bit of clarification. You stated that you add the groups as logins. Does this infer that when a user is validated against the AD his groups are retrieved. Then a group specific to a database is used as a login to SQL Server.
If the group is used, does SQL Server view this as Windows Authentication and validate the group againt the AD?
Thank you again for the help.
September 26, 2008 at 3:50 pm
If the application is running on the user's machine, and if the application attempt to connect to SQL server using a trusted connection, then the application will be trying to connect to SQL server using the login that it is running under. This should be the user's "Windows" login.
Isn't this what you're after?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 28, 2008 at 4:24 am
The individual users are validated through AD and the group they belong to. So we only have to give access to the group. It is a trusted windows connection.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 28, 2008 at 7:29 am
The groups will cause the user to be validated against AD with the trusted connection. This is what you're after, though it might seem a bit funny. When the user connect, the server will check to see if they have a Windows login. They won't, so it will check for groups in their list of AD groups to see if there's a match for SQL Server Logins.
September 29, 2008 at 5:42 pm
Think I understand the mechanics of a trusted login. The application can validate the user credentials against the AD and get the group. The group is used for the login and SQL Server has a login with the group name.
I would conclude from this that a trusted login is a trade off then that this trusted login approach simplifies the configuration of SQL Server, but mitigates the effectiveness of auditing. It would not appear that there is any mechanism by which trusted login could be used and still audit by specific user.
September 30, 2008 at 6:35 am
Perhaps the "group" discussion is something of a red herring. I do not use AD Groups at all (because the group membership is controlled by the IT staff, and is neither visible to nor controlled by me.) Instead every user is given their own server login permission (using user names like ournetwork\userID, where user ID matches the ID they use to login to their desktop. They are all set to use Windows Authentication, so they don't have to provide yet another password when they hit the server (but, as I understand it, their network membership is verified by the AD server.)
Beyond that, all access to individual DBs, queries, procedures, etc. is based on defined roles, as you are doing now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply