April 14, 2008 at 4:04 pm
Hi,
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 specify access control rules for SQL Server from within Active Directory? For example, say I want a certain group to have only read only access to databases. Can I specify these rules using Active Directory Group policies?
Thanks!
April 16, 2008 at 10:29 pm
No. SQL Server security is handled by SQL Server. The only thing a GPO can influence is the password policy of the machine SQL Server is installed on. And it only applies to SQL Server if you connect to SQL Server and tell it so (so essentially still handled by SQL Server).
Not sure if the new DMF stuff in SQL Server 2008 can support this, haven't read up on it yet. But I suspect not.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
April 18, 2008 at 4:22 am
There is no way to influence security within SQL Server via GPOs (apart from password policy). This is true also in SQL Server 2008.
IMO it would be great if a vendor built an interface between SQL Server 2008 policies and the GPO tools, so that SQL Security could be configured via GPOs.
The concept of controlling DBMS and OS security from a single toolset has been around on other OS / DBMS configurations for many years, and is standard practice in those environments. I certainly hear from security people they are unhappy the SQL Server security cannot be controled externally to SQL Server.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 21, 2008 at 8:59 pm
Well, since you can create a login based on a Windows group you can map that login to a user in your DBs, so you could implement a security model based solely on AD groups if you wanted to. But you'd lose the ability to differentiate between individual Windows users, rather each group would be a single user on the DB, which is probably not going to work very well.
Alternatively you could create some sort of syncing mechanism using a mixture of ADSI/LDAP (for interrogating AD group memberships), Database Roles (to add your users to) and SMO (to change the DB role memberships for your users based on their AD group memberships). A little hacky however...
Regards,
Jacob
April 23, 2008 at 12:03 pm
as a former exchange admin, tight AD integration is the last thing you want
with sql you can detach and attach databases on any server and restore to any server under any name. with exchange it's integrated into AD you don't have this flexibility because it creates objects in AD
April 26, 2008 at 8:05 am
We just implementing this in our SQL....that we have AD Groups and people are assigned into this group. This is great saves having tom, dick and jerry in the sql logins but just have the ADUSERS group.
The question now comes that dba does not have control of AD - so who knows that mary just got added and is mary really allowed access.
So i was thinking of writing a report that would ready AD get the GROUP, and all the users then i can check periodically that x person got added and is this correct.
Not sure how else you could tighten up in SQL.
Be nice to do ADGROUP NT in SQL then add the users to a role but then thats double the work and defeats object of using ADGROUP.
April 29, 2008 at 3:57 am
Security practitioners think it is good there is separation of duties between the people administering a service and the people granting authority to use the service. Equally, they see it as bad practice that the same staff both administer and grant authority to a service. This may not be how all DBAs view their job, but it IS how the security and audit folks view the job.
Therefore the fact that DBAs do not know who has been added to a Windows group that has acess to a database is compliant with security best practice.
Your organisation should have appropriate controls in place to ensure only people who should be put in the group actually are in the group.
The DBS's job is to ensure the group can access the data they need, and are not able to access data they don't need.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 29, 2008 at 7:33 am
Totally agree - thats why its really important to ensure security per Groups. We now have one group per each database then even down to role level within the database and you can only see a little bit.
April 30, 2008 at 7:53 am
TRACEY (4/26/2008)
We just implementing this in our SQL....that we have AD Groups and people are assigned into this group. This is great saves having tom, dick and jerry in the sql logins but just have the ADUSERS group.The question now comes that dba does not have control of AD - so who knows that mary just got added and is mary really allowed access.
So i was thinking of writing a report that would ready AD get the GROUP, and all the users then i can check periodically that x person got added and is this correct.
Not sure how else you could tighten up in SQL.
Be nice to do ADGROUP NT in SQL then add the users to a role but then thats double the work and defeats object of using ADGROUP.
if you have windows 2003 domain controllers you can install Powershell and run a script every few hours to check if there was a change in membership
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply