SQL Security - Running SQL without local user rights

In a previous posting, I mentioned the risks of the “xp_cmdshell” extended stored procedure and how to disable the command.    It is a good step towards a more secure SQL installation but more can be done.   In that same article, I mention that many SQL installations run the SQL services using the local system credentials.    It is recommend that you follow these steps and run the services as a user with as little rights as possible on the SQL host machine.  Note: I am going to assume this server is part of active directory, although the process is pretty close to the same for a stand alone server. 

First, you need to create a user for the SQL services.   Follow these steps:

1. Create a user called “sqluser” and give it an appropriately complex password. 
2. Create a global security group called “sqluser_group” .  
3. Open the properties of “sqluser” and go to the “member of” tab.
4. Add the sqluser to the “sqluser_group”.  
5. Making sure the “sqluser_group” is still selected click the “Set Primary Group” button.
6. Highlight the “domain users” group and click the remove button   

We now have  a user that can only authenticate to the domain and has no domain right (with the exception of rights granted to everyone).   

Next is to tell SQL to run as the newly created account.   The steps to do this are as follows:

SQL 2005

1. Open SQL Server Configuration Manager and connect to the instance using an account with administrative privileges.
2. Select SQL Server 2005 Services
3. Double click the SQL Server instance you want to reconfigure.
4. On the “Log On” tab, select “This account” and fill in the Account name and password fields.
5. Click “OK” to finalize the change

SQL 2000

1. Open SQL Server Enterprise Manager
2. Connect to the server or instance you are wanting to reconfigure.
3. Right click on the server and select properties.
4. Navigate to the “Security” tab.
5. Select “This account” and fill in the account and password field. 
6. Click OK.   When prompted, enter the password again to verify it.

Both versions require you to restart the services in order for the changes to take effect.   I also caution you to not make these changes in the services control panel.   While that may seem faster, it will not work.   SQL explicitly grants NTFS and registry permissions to the account when you follow this process.  It only grants the rights it needs to run.

If you have SQL data or write SQL backup files in any directory other than the default directories, you will need to explicitly grant NTFS change permissions to the user account.

Following these steps will get you one step closer to a more secure SQL installation.