Security

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'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',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/