The Right Stuff

I often see SQL Server 2000 installations where the server is left running under the SYSTEM account. This is not a good idea because if SQL Server gets compromised, the attacker will have full control over the machine. Even if the server is running under an account with Administrator or Power User rights similar risks arise. If the account in question is a domain account, the attacker will have access to other systems of your organization as well.

I suspect the root cause for these often-seen SYSTEM-level instances lies in the fact that the SQL Server 2000 setup program suggests running the SQL Server services under the SYSTEM account.

There are actually ways to run SQL Server 2000 under a Limited User Account without having to sacrifice functionality. The administrative tools of Windows and SQL Server will help you to accomplish better security for your server. The settings made below may be applied earlier during the setup program. However, I'll describe how to change them after the installation has finished.

1. Create a new local or domain account to be used just for SQL Server.
   Open MMC or AD User Management to create a new user. Use the default naming convention deployed in your organization. This will it make more difficult to hackers that are trying to identify the account (I'll use smeier/Susanne Meier as an example). Set the password to never expire and revoke the right to change the password. Use a strong password containing numbers and special characters.
   
2. Remove default group membership.
   Newly created accounts are members of the User group by default. Remove this membership, it won't be needed. This also prevents interactive logons using this account.
   
3. Tell SQL Server to use the newly created account.
   Open the Enterprise Manager, select the SQL Server instance you want to secure. Right-click, select Properties and go to the Security tab. On the bottom you'll see the account settings. Enter the name of the newly created account and the password.
   
4. Let Enterprise Manager modify the rights for the service account.
   After clicking the OK button Enterprise Manager (SQLEM) will grant the rights required to run the SQL Server service to your SQL Server account. The SQL services will be restarted during this process.
5. Set the NTFS rights on your database files.
   The SQL Server account needs change rights on the *.mdf and *.ldf files of your databases. SQLEM will modify the rights of the <SQL Server Path>\MSSQL\Data directory automatically, which is the default path for new databases. For all databases outside this folder you will have to modify the rights manually using the File Properties Dialog.
   
6. Restart SQL services.
   This step is only needed if you had to set NTFS rights manually in step 5.
7. Verify that all databases are working.
   Use SQLEM and/or the Windows Event Log to check that all databases could be loaded successfully. If errors occur, it's likely that NTFS rights are set incorrectly, i.e. the datebase files are inaccessible.

That's it. Your SQL Server instance is now running with limited rights. In case an attacker is able to gain access to the server he will be constrained in his abominable deeds. A side note: This makes sense on development machines too. Deploying security not only on production servers is key to best practice development.

Happy SQL'ing!