1. Make sure the latest NT and SQL Server Service Packs are applied. At the time of this writing, this means

Windows NT 4.0 - Service Pack 6a

SQL Server 6.5 - Service Pack 5a

SQL Server 7.0 - Service Pack 3

SQL Server 2000 - Service Pack 1

2. Evaluate and choose a network protocol library that allows for maximum security but doesn’t break functionality. Multi-protocol is the obvious choice, but sometimes can’t be used in heterogeneous environments.

3. Secure the “sa” and “probe” accounts with strong passwords. Assign a strong password and lock away the password in a secure location. Note: The probe account is used for performance analysis and distributed transactions. Assigning a password to this account can break functionality when used in standard security mode.

4. Use a relatively low-privilege user account for SQL Server rather than LocalSystem or Administrator. This account should olny have minimal rights (note that Run as a Service Right will be required) and should help contain (but not stop) an attack to the server in case of compromise. Note: This step should be done very early on in development so that the necessary authorizations issues can be addressed before deployment.

5. Make sure all SQL Server data and system files are installed on NTFS partitions and the appropraite ACLs are applied. If someone should gain access to the OS, make sure that the necessary permissions are in place to prevent a catastrophe.  Of course, if the person is a system administrator and SQL Server is running as LocalSystem then its probably futile anyway.

6. Drop master..Xp_cmdshell if you can do without it. If using SQL 6.5, at least use the SQLExecutieCmdExec account option in “Server Options” to limit the rights when non-“sa” users use xp_cmdshell. 

In any isql window (or Query Analyzer):

use master
sp_dropextendedproc 'xp_cmdshell'

OR right click the procedure in Enterprise Manager and click DROP (or DELETE)
The procedure is located in the master database under Extended Stored Procedures

For the SQLExecutiveCmdExec check the following KB article first:

http://support.microsoft.com/support/kb/articles/Q159/2/21.asp

If you don't need xp_cmdshell the PLEASE drop it.  Sleep better.  Just remember that a system administrator can always add it back if need be.  This is good and bad - an intruder may find it missing an simply add it back.  Consider removing the dll (xplog70.dll) as well but test first since the same dll is used for several procedures. To find other procedures using the same dll try:

First get the dll.

select o.name,c.text from dbo.syscomments c, dbo.sysobjects o where c.id = o.id and o.name = 'xp_cmdshell' 

Second, find the other extended stored procs using that same dll.

select o.name,c.text from dbo.syscomments c, dbo.sysobjects o where c.id = o.id and c.text = 'xplog70.dll' 

Use can use this method with any other procedures you wish to drop in the following steps.

7. Drop OLE automation stored procedures if you can do without them (warning - some Enterprise Manager features may be lost when these stored procedures are dropped) . These include:

• Sp_OACreate

• Sp_OADestroy

• Sp_OAGetErrorInfo

• Sp_OAGetProperty

• Sp_OAMethod

• Sp_OASetProperty

• Sp_OAStop

If you decide to drop procedures then script it so that you can add them back en-masse if you need full functionality in the future.  Remember, what we're doing here is locking down a production app - your development platform should be done on machines elsewhere.

8. Drop registry access procedures that you don’t need. (Same warnings as above) These include:

• Xp_regaddmultistring

• Xp_regdeletekey

• Xp_regdeletevalue

• Xp_regenumvalues

•  Xp_regread

• Xp_regremovemultistring

• Xp_regwrite

9. Delete other system stored procedures that you believe could pose a threat. There are quite a few of them, and this could take some time. Be careful not to do this on a production server first. Test on a development machine so you don’t break any functionality.  Below is a list of the ones we recommend you assess:

12. Disable SQL Mail capability unless absolutely necessary. Leaving it open gives a potential attacker another means of delivering potential trojans, viruses, or simply launching a particularly nasty denial of service attack.

13. Check master..Sp_helpstartup for trojan procedures. Make sure no one has placed a backdoor here. Use Sp_unmakestartup to remove any rogue procedures.

xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',N'AuditLevel', REG_DWORD,3

findstr /C:"Login Failed" \mssql7\log\*.*'

and redirect to the output to a text file or email so you can monitor failed login attempts. This also provides a good way for administrators to document attacks. There are also many third-party tools for analyzing NT event logs. Note: You may need to change the path for the log files based on your installation and SQL Server version.

20. Set alerts to log failed object access and logins. Go to “Manage SQL Server Messages” in Enterprise Manager and search for any messages relating to permission denial (start by searching for “Login Failed” or “denied”). Make sure any messages you’re interested in are logged to the event log. Next, set up an alert on that message or severity level 14 to send an email or page to an operator who can quickly react to the issue.

21. Make sure roles at the server and database levels are only assigned to the users who need them. While the SQL Server 7 security model has many enhancements, it also adds the extra layer of permissions that we must monitor to make sure no one has been given more access than they need or that they’ve already circumvented security to elevate themselves. 

Use master
Select name,
      Password
from syslogins
where password is null
order by name

Use master
Select sysobjects.name
From sysobjects, sysprotects
Where sysprotects.uid = 0
AND xtype IN ('X','P')
AND sysobjects.id = sysprotects.id
Order by name

HKEY_CURRENT_USER\SOFTWARE\Microsoft\
MSSQLServer\SQLEW\Registered Server\
SQL 6.5