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.

Windows server security has been a hot topic for many years now.  Password complexity, periodic password changes, restrictive NTFS rights are all great steps toward a secure Windows installation.   Unfortunately many system administrators leave a big hole in their SQL sever installations by letting the SQL services run under the local system account.   This account has local admin privileges and therefore any process executed by the SQL server or SQL agent are running with those privileges.

Enter the “xp_cmdshell” extended stored procedure.   This procedure will allow you to execute commands as if you were typing them in a command window under the windows account that SQL sever is configured to run.   If that account is the local system account, the process has full access to the user database, file system and registry.  

In a situation where a username and password is compromised in SQL (this can be common on accounts with password that don’t change) a hacker can basic tools to further compromise your system.   A common hacking attempt would be to execute the extended stored procedure to first create an account, then another command to give them local administrative rights to the local server.  It can do this if SQL is running as a local administrator equivalent.  Now they have complete access to the system with a “valid” account.  They can now install new software, turn on RDP, fetch backup files, replace files…  it is pretty much endless path of destruction.  In addition, by being a local administrator, they also now have DBO access to all the databases in your SQL installation making things worse.

Now that I have  spread a lot of doom and gloom about a single SQL command, let me tell you the best way to prevent it.    If you aren’t using it…   Disable it.    Log into Query Analyzer (SQL 2000) or Management Studio (SQL 2005) as SA or SA equivalent and run the following command:

-- Allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO

-- Update the currently configured value for advanced options.

RECONFIGURE

GO

-- Disable the feature.

EXEC sp_configure 'xp_cmdshell', 0

GO

-- Update the currently configured value for this feature.

RECONFIGURE

GO

I am sure that some readers will say that this is a handy command.   I agree it is, but use it wisely and grant permission to it sparingly or change the windows account that the SQL services are running.    Look for more of this in the future.

Chances are that if you have just done a fresh install of SQL 2005 you will get an error when you try to create your first SQL 2005 maintenance plan.   This error tells you that the “‘Agent XPs component is turned off as part of the security configuration for this server”  and refers you to your system administrator or the SQL Books Online.

In order to enable the “Agent XPs” component. Start up SQL Management Studio and click the “New Query” button on the tool bar.   Execute the following script in the query window (on the right hand side of the screen) . 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Agent XPs', 1;

GO

RECONFIGURE

GO