March 6, 2006 at 4:26 am
I got a request from my seniors to tighten security on my SQL servers. They do not want me to use windows authentication because any network administrator can then give himself access to the group that has access to the database that he is not allowed to see. I also need to block out certain developers (VB6) from certain databases. We use a DLL to supply the connection string that is needed for the ADO controls. The other connections to the databases are done via DLL’s and there we can easily protect what the developer can see. The problem is with the ADO control. So, we decided that if we wrap the ADO control, expose a database name and hide the connection string, we have the problem solved. Of course, that is not so easy. So this question is two fold:
5ilverFox
Consulting DBA / Developer
South Africa
March 7, 2006 at 1:16 pm
Integrated Security is the way to go. Your 'seniors' are slightly off base (partially incorrect in their assumptions) about the way things work. Yes they are somewhat correct, if you just use integrated security and do not 'lock down' SQL Server. But you can tighten up security and utilize the power of AD and keep the network admins from giving themselves 'sa' authority. There are a few KB articles on it and it's actually a MS 'best practice' as well.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 7, 2006 at 10:46 pm
If you give a group access to a database, what prevents an AD Administrator from adding himself to the group, look up what he/she wants to see, and removing himself from the group?
5ilverFox
Consulting DBA / Developer
South Africa
March 8, 2006 at 9:41 am
Let me begin with a quote - "Locks only keep honest people out".
Under normal circumstances I would use an AD group for the SQL Server Adminstrators. But in a "super-paranoid" environment, I'd add only the SQL Adminstrator's AD login accounts individually. This would keep even the most notorious AD Administrator out of the SQL Server. However, in more moderate security environments a group for SQL Server Administrators is usually the right course. You really do not have to worry too much about an AD Administrator wanting to "muck" with your SQL Server for a few reasons:
- Most do not even know how to pronounce SQL
- They usually have way too much to do already
- There are logs that audit all AD activities
Now one final point about the "super-paranoid" environment described. If those in power are so worried about access to the SQL Servers by AD Administrators, one could ask them about their concerns about the AD Administyrators having access to the 'entire' Windows Network. By 'entire' Windows Network, I mean everyone's confidential and personal information whenever the AD Administrator(s) choose to see it. Eventually 'trust' has to come into the picture ... think about it and remember - "Locks only keep honest people out".
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 9, 2006 at 2:16 am
OK, lets be more specific. Maybe I will get a better understanding which direction to go. I work in South Africa and because of the brain drain; we have lots of multi skilled personnel (people that was forced to take on more than one job at a time). I know of at least 2 AD administrators in our company with SQL skills (albeit limited). If you have access to the HRD database, you do not need to be a rocket scientist to query the Salaries table. The seniors do not want to advertise salaries (hey, they pay mine, so I do what they want). The only people allowed to access the HRD database is the HR department and 2 IT personnel. Now, if we create an HRD group on AD and give that group rights to the HRD database, nothing prevents any AD administrator from adding himself to the group to query the HRD database. The AD logs will not help much, since they (the AD administrators) control it.
5ilverFox
Consulting DBA / Developer
South Africa
March 9, 2006 at 10:40 am
The solution, as Mr. Komacsar suggested, would be don't assign Windows Groups to access a specific database. Instead, assign individual Windows logins with access to SQL-Server and then assign that user to a Role that has access to the specific database.
You only allow individuals logins to access SQL-Server and then you have control over the databases they have access to.
Steve
March 9, 2006 at 11:45 am
This ought to fill the bill ... or at least get you off to a good start !
---
--- Notes:
--- 1) make sure you know the 'sa' account password and it is a 'strong' one
--- 2) make sure that the AD Admins do not know it !
--- 3) revoke sysadmin from all SQL users except 'sa'
--- (use sp_dropsrvrolemember @loginame='sql_login', @rolename='sysadim')
--- 4) revoke sysadmin from all Windows Users/Groups except 'Builtin\Administrators'
--- (use sp_dropsrvrolemember @loginame=[YourDomain\SQL_Admin_UserId_1], @rolename='sysadim')
---
exec sp_denylogin @loginame=[Builtin\Administrators] --> this stops any Administrator, including Domain Admins
exec sp_grantlogin @loginame=[YourDomain\SQL_Admin_UserId_1]
exec sp_grantlogin @loginame=[YourDomain\SQL_Admin_UserId_2]
exec sp_addsrvrolemember @loginame=[YourDomain\SQL_Admin_UserId_1], @rolename='sysadim'
exec sp_addsrvrolemember @loginame=[YourDomain\SQL_Admin_UserId_2], @rolename='sysadim'
---
--- 1) now create a domain user account for the SQL Server Service and SQL Agent Service (e.g. sv-sqlserver & sv-sqlqagent)
--- 2) now grant the following right 'deny interactive login' to the new accounts sv-sqlserver & sv-sqlqagent
--- 3) now you have a choice:
---
--- a) make the new accounts sv-sqlserver & sv-sqlqagent members of LocalAdministrators group on the databse server and grant the following rights:
--- Lock Pages In Memory
--- Log on as a Batch Job
--- Log on as a Service
--- Replace a Process Level Token
--- b) deny interactive logon and grant the following additional rights:
--- Act as Part of the Operating System
--- Bypass Traverse Checking
--- Lock Pages In Memory
--- Log on as a Batch Job
--- Log on as a Service
--- Replace a Process Level Token
---
--- Notes:
--- 1) make sure the password for each account is 'strong' and at least 14 characters in length
--- 2) make sure that only the SQL Admins know the password and none of the AD Admins
---
exec sp_grantlogin @loginame=[YourDomain\sv-sqlserver]
exec sp_grantlogin @loginame=[YourDomain\sv-sqlqagent]
exec sp_addsrvrolemember @loginame=[YourDomain\sv-sqlserver], @rolename='sysadim'
exec sp_addsrvrolemember @loginame=[YourDomain\sv-sqlqagent], @rolename='sysadim'
---
--- 1) Change the service account for SQL Server from LocalSystem to YourDomain\sv-sqlserver
--- 2) Change the service account for SQL Agent from LocalSystem to YourDomain\sv-sqlagent
--- 3) Stop SQL Server Service (the SQL Agent stops as a dependent service)
--- 4) Start the SQL Server service
--- 5) Start the SQL Agent Service
---
At this point your SQL Server in inaccessable by any AD Admins.
Now you are free to set up your HRD users and such in any way that you see fit.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 9, 2006 at 2:21 pm
Please do this ONLY after you grant your NT account sysadmin rights.
exec sp_denylogin @loginame=[Builtin\Administrators] --> this stops any Administrator, including Domain Admins
Tim S
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply