Attempting to Remove builtin\administrators
-
Hello all,
We are running SQL server 2000 enterprise edition with SP3 on Windows 2000 server, with log shipping to standby servers.
We are trying to remove the builtin\administrators to get more secure.
When I did this in the development environment we did the following:
1. Made the SQL server and the SQL agent start with the local system account.
2. Removed the builtin\administrators login.
The problem was SQL agent would not run. So we ended up switching the Agent to run with a domain account.
SQL agent is working now.
My questions are:
1. Can I leave the SQL server start up with local account and SQL agent start with a domain account without any problem?
2. If I go back to starting SQL Server and SQL Agent with the domain account, does the account starting the SQL server need to have SA rights in SQL server, or can I use any thing less. I do not want the domain acconts being used as a trap door to making changes to my SQL server.
3. Any other suggestions you have for me?
Regards.
Peri Narayanan.
-
First since the Agnet accesses the network, local system is a bad choice.
Second, local system is a bad choice because it's an adminsitrator on the local machine. Setup a domain user with no rights other than what is needed and use that as your SQL Server service account user. Use Enterprise Manager to assign this and get the right permissions.
Third, Agent doesn't need to be sa in general, but it might need certain rights for Log Shipping. not sure here.
-
1. You can, but as Steve indicated, you shouldn't. The local System account is actually more powerful than a normal administrator. If a service is running under that, it can perform any action that the machine account can do. This has implications in Active Directory, for instance, with Kerberos delegation. As a result, running SQL Server under local System is a bad idea.
2. Yes and this goes for SQL Server Agent's service account, too. See Books Online's topic Installing SQL Server >> Overview of Installing SQL Server >> Setting up Windows Service Accounts for the requirements.
3. Use domain user accounts. Give them the minimum privileges possible. When you use them, assign them very, very difficult passwords to remember. Write these passwords down. Configure your SQL Server and SQL Server Agent service to use these accounts. Then take the password you wrote down, seal it, and put it in a secure location. If possible, make at least two copies and store one of those off-site. This will ensure no one else is using those domain accounts to backdoor your SQL Server.
K. Brian Kelley
@kbriankelley -
The concern about 'other' using the accounts to login is a moot one if you 'revoke' 'interactive login' from with Active Directory. The only real issu that I've heard of is that if you are using Full Text Search there may be issues. In any event, here's our site 'standard' lockdown (step by step):
---
--- 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."
-
Just a small point from an AD administrator... The local security policy can easily be overwritten with group policy. Therefore, if you're looking to keep your admins out by saying no interactive login in the local security policy, as a domain admin (or with the appropriate delegated rights on the OU), I can override your setting without much trouble.
Also, if an AD admin REALLY wanted to get in as you and not reveal it by changing your password, he could grab a backup of an AD DC, and using rainbow tables or old fashioned cracking carve up your password on a system without your knowledge.
In other words, yes, you should try and restrict out AD administrators when it comes to that sort of data. It'll keep 80% out with the minimal 20% effort. However, you still, at the end of the day, have to be able to trust your AD administrators. Then again, that same rule applies to DBAs, too.
K. Brian Kelley
@kbriankelley -
Good valid points Brian ... but one has to remember that:
"Locks only keep honest people out!"
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Rudy,
--- 4) revoke sysadmin from all Windows Users/Groups except 'Builtin\Administrators'
Them, AD Administrators like to group into their own group and use scripts to add this group to local Administrators group, which maps to SQL Server as Builtin\Administrators.
Also, them (us) , all administrators can access the file system and get backup files. Or get backup files from backup tapes. So I would really advise to password-protect backups when working on the system security.
Regards,Yelena Varsha
-
Password protected backups in SQL Server 2000 only prevent you from restoring the data. You can open up said backups with a text editor and get the data anyway. Also, there are means of subverting the backup passwords, which is why additional security in the form of checksums was added to SQL Server 2005. An option would be to use something like EFS on the backup files, but even that's not full proof because the recovery agent is by default the local administrator account on the server, something you would expect the admins to have access to.
K. Brian Kelley
@kbriankelley -
Agree with Brian.
I did want to add myself that the backups are very readable in the text editor but was afraid to sound too pessimistic.
Regards,Yelena Varsha
-
to reiterate:
"Locks only keep honest people out!"
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Locks don't only keep honest people out. They keep out dishonest people who can't figure out how to bypass the lock.
That's a good reason to have them, if nothing else. K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply