Securing an Installationg of SQL Server 2005

To determine the SQL version on SQL Server 2005 open SQL Server Management Studio, connect to the database, then run the following query

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

Column 1. The product version (for example, "9.00.1399.06").
Column 2. The product level (for example, "RTM").
Column 3. The edition (for example, "Enterprise Edition").

Current versions can be found here

Taken from http://www.databasejournal.com/features/mssql/article.php/3461471

It is worth pointing out that the new version of SQL Server has been designed with the "secure by default" principle in mind, resulting in a system with optimum, from the security standpoint, settings. The typical setup avoids installing or activating non-essential components and features which can expose the server and its data to potential attacks. This applies, for example, to SQL Server Agent, Full-Text Search, and Data Transformation Services (all set to manual startup), Analysis, Reporting, and Notification Services, SQL Browser, Service Broker Network Connectivity, Database Mirroring, SQLMail, or SQL Debugging.

Taken from http://www.sqlsecurity.com/FAQs/SQLSecurityChecklist/tabid/57/Default.aspx

How to secure SQL Server

• Keep up to date on SQL Server service packs and patches
• Audit SQL Server accounts for weak passwords
• Restict access to the SQL Server to only trusted clients
• Use Windows Only authentication where possible
• Store SQL Server backup files in a secure location and encrypted
• Disable all netlibs if the SQL Server is local-only
• Regularly scan the installation with Microsoft's Baseline Security Analyzer

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

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 xp_perfend xp_perfmonitor xp_perfsample xp_perfstart xp_readerrorlog xp_readmail xp_revokelogin xp_runweb

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

What different network protocol libraries should I use?

If the database is local-only then disable ALL netlibs and use shared memory to access the SQL Server. If you must connect from remote clients, use TCP/IP and enable encryption (non-trusted subnets) . Only use the alternative netlibs (NwLink^?, Names Pipes, Banyan, etc) if need be in those integration scenarios.

SQL Server 2000 includes the new Super Sockets net-lib which can do SSL over any of the protocols. It is highly recommended you make use of this in lieu of the multi-protocol encryption. Remember that a server certificate must be installed before the encryption can occur. The certificate must match the DNS name of the server and be issues by a certificate authority that the client trusts.

Yet another option if you are using Windows 2000 is IPsec. IPsec will allow you to encrypt ALL traffic between the client and server. This is a good solution when you have complete control over the server and the clients can take the time to learn IPsec policy deployment.

Additional Links

http://msdn2.microsoft.com/en-us/library/ms161948.aspx