Use Windows Only authentication mode for security if possible
By using integrated security, you can greatly simplify administration by relying on the OS security and saving yourself from maintaining two separate security models. This also keeps passwords out of connection strings.
Take the time to audit SQL logins for null or weak passwords
Use the following code to check for null passwords:
Use master
Select name,
Password
from syslogins
where password is null
order by name
There are a multitude of free and commercial tools to check for weak passwords. SQLPing2 is free and can be used to check for weak and null passwords.
Frequently check group and role memberships
While the SQL Server 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. There’s also the spectre of user’s who have changed roles within the company but the SQL Server permissions structure has not been adjusted. This goes back to assigning object access to groups and not individuals.
Physically secure the SQL Server
Lock it behind a door and lock away the key while you’re at it. Someone sitting in front of the server will always find a way.
Rewrite applications to use more user-defined stored procedures and views
This minimizes the need to give direct access to tables. It gives the developer more control over how data can be accessed.
Enable logging of all user login events
You can also do this via script but using the following code:
xp_instance_regwrite N’HKEY_LOCAL_MACHINE’, N’SOFTWAREMicrosoftMSSQLServerMSSQLServer’,N’AuditLevel’, REG_DWORD,3
Check master..Sp_password for trojan code
Compare your production scripts to the default script on a fresh installation and keep that code handy.
Check master..Sp_helpstartup for trojan procedures
Make sure no one has placed a backdoor here. Use Sp_unmakestartup to remove any rogue procedures.
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. By itself it is fairly harmless but it can be used to help an attacker.
Remove the Guest user from databases to keep unauthorized users out
This is the default but vigilant in case some dbo gets loose with the access controls. The exception to this is the master and tempdb databases as the guest account is required
Make sure all SQL Server data and system files are installed on NTFS partitions
If someone should gain access to the OS, make sure that the necessary permissions are in place to prevent a catastrophe.
Use a low-privilege user account for SQL Server service rather than LocalSystem or Administrator
This account should only have minimal privileges (a local user is best) and should help contain an attack to the server in case of compromise. Notice that when using Enterprise Manager or SQL Server Configuration Manager (SQL 2005) to make this change, the ACLs on files, the registry, and user rights are done for you automatically.
Secure the “sa” account with a strong password
This assumes you are using the SQL Server and Windows security mode. If possible, use the Windows Only mode and don’t worry about people brute-forcing your ‘sa’ accounts. Of course, even so you’ll want to set a strong password in case someone changes modes on you.
Choose only the network libraries you absolutely require
Better yet, if the SQL Server is local-only then why not disable all network libraries and use shared memory to access the SQL Server? Just use the name ‘(local)’ as the server name. If your SQL Server requires connectivity from other hosts, use the TCP/IP netlib and then determine if SSL is needed.
Make sure the latest OS and SQL Server Service Packs/Hot-Fixes are applied
This goes without saying but I’ll say it anyway for completeness. Simply perform a “select @@version” on your SQL Server and compare the results to the linked page.
Restrict to sysadmins-only access to dangerous stored procedures and extended stored procedures
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:
sp_sdidebug
xp_availablemedia
xp_cmdshell
xp_deletemail
xp_dirtree
xp_dropwebtask
xp_dsninfo
xp_enumdsn
xp_enumerrorlogs
xp_enumgroups
xp_enumqueuedtasks
xp_eventlog
xp_findnextmsg
xp_fixeddrives
xp_getfiledetails
xp_getnetname
xp_grantlogin
xp_logevent
xp_loginconfig
xp_logininfo
xp_makewebtask
xp_msver
xp_regread
http://www.sqlsecurity.com/