SQL Server 2005 & Active Directory
Hello All,
My question is regarding access control for SQL Server via Active Directory Group policies.
Let's say you are using Windows Authentication to allow users to log into SQL Server and active directory is the method of authentication. Is there a way to specifiy access control rules for SQL Server from within Active Directory? For example, say I want a certain group to only read database tables and execute stored procedures but not be able to directly write data into the database. Can I specify these rules using Active Directory Group policies?
Thanks!
# 1 Re: SQL Server 2005 & Active Directory
I was able to find the answer to the question above. I am posting it here in case anyone else needs to know:
1. There are two stages of authentication inside SQL - one at the SQL Server level and the other at the database level.
2. A valid login is required to connect to SQL Server. This is where Windows Authentication and Active Directory comes in. If you're valid windows user then that login can be used to log into SQL Server, provided that the login has been granted access to the SQL Server. This can be controlled via Active Directory. We have two options here:
1. Grant individual users access to the SQL Server
2. Create Groups in Active Directory, add users to those group, and then grant the groups access to SQL Server.
This is just my hypothesis but when using the second option, it seems to me that since the entity that is logging into the SQL Server is actually a group and not an individual user, we won't be able to find out which user modified a given record but only the group that modified it. For example, the SQL command 'current_user' would return DOMAIN\Developers instead of DOMAIN\Nizar.noorani if I were logged in and I belogned to the Developers group. But we need to verify if this is indeed the case.
3. In order to access a particular database, you must be a user of that database. The way this works is that you create a database user for a given database and associate that user with a SQL Server login. You can now give this user different permissions on that database. So a given SQL Server login will have a separate user account for each database that he/she has access to.
This would be a pain if we'd have to do this for every one of our users. However it would become much easier if we are using Windows Groups to log into SQL Server since then we only have to create user accounts for those groups in our databases.
If we want to make these really easy (from a admin prespective) we can add the builtin user 'guest' to a database. What this means is that any VALID SQL Server login can access the database. The user would inherit the permissions granted to the user 'guest' for that database.
Here a couple websites that talk more about this:
http://vyaskn.tripod.com/sql_server_security_best_practices.htm
http://www.developer.com/tech/article.php/10923_721441_1
