SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
21 Hot Tips for Improving Your SQL Server Security
Web Monkey About the Author

With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at Norb Technologies and a real database guru.

A regular contributor to SQL Server Club, his background includes software development, hardware, a smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...

Typepad Profile RSS Feed Web Monkey on Twitter
Send to a Friend Printer Friendly Version

Some of these tips are quick and easy, others with take longer to implement. Not all of them will apply to your particular environment, and some apply to some types of server more than others. They do however apply to all versions of SQL Server.

Remember, it's all about restricting who has access to your data - the smallest number of people, and the smallest amount of data they need to do their job.

Physical Security

Security Tip # 1

It’s essential that you physically secure your SQL Servers. Keep all of your critical servers in a locked server room - even if it’s only a broom cupboard.

If you've not got physical access, getting into a server is always more difficult. The fewer people with physical access to your servers, the safer your data.

Security Tip # 2

Blank off, or remove the internal cabling to your USB ports if you are in a shared server room environment. This prevents database backups being moved onto external storage media and taken off site.

Security Tip # 3

If you must send data off site, use a secure channel. This can be secure FTP, or a trusted parcel carrier, depending on your requirements. Encrypt your data: never, EVER send it plaintext, whether as a CSV, text file or a backup.

Send the password by a separate channel, whether it's email or telephone, but only pass it on to the recipient once they have the data safely in their hands.

Always password protect your files, and preferably encrypt them. And only send the data that is required. Any other data should be removed or obfuscated, as you don't know if the recipient of the data is going to treat it with the same care as you.

Account Security

Security Tip # 4

Always set a strong password for the sa account, as this is the one account that is almost always guaranteed to be available on a SQL Server. Brute force password testing is a reality.

Security Tip # 5

Never use LocalSystem or Administrator as the SQL Server service account. Always create a local user account with minimal privileges, as this prevents the entire server being compromised should the server be attacked from outside.

To cut down on workload, use SQL Server Management Studio (or Enterprise Manager if you've not moved to SQL Sever 2005/2008) as this will also handle adjusting the registry, user rights and file ACL attributes for you without any extra effort.

Security Tip # 6

With the exception of tempdb and master, always remove any guest users that have been added to a database. Master and tempdb are the only databases that do require a guest.

DBA and Developer Tips

Security Tip # 7

Always remember to password protect your database backups. At least if they fall into the wrong hands, they can't be easily restored or the data removed.

Security Tip # 8

Store data such as credit card and social security numbers in encrypted format. If the database finds its way off site, at least sensitive data is not readily available to an outsider.

Security Tip # 9

If developers must work with copies of live data, run a simple obfuscation script over the more sensitive data in the database after it has been restored to a development server.

As a DBA you have a moral duty to protect that data. Many companies employ outside contractors and third parties who can read and query data for which they have no development use. Truncating data in columns so that only the first half of the data can be read is better than nothing.

Security Tip # 10

Never allow developers and users to have direct access to tables. Only allow user-defined stored procedures and views to access tables.


Security Tip # 11

Wherever possible, use NT Authentication (Windows Only) mode when setting up your SQL Server.

Security Tip # 12

Always use NTFS as your base file system, as file system security can be used to restrict access to SQL Server database files to just those users you specify.

Security Tip # 13

Wherever practical, use Windows Only Authentication mode, also known as Integrated Security, or NT Security. This not only simplifies administration, it also prevents developers from putting passwords in connection strings.

Security Tip # 14

Unless you need it, disable SQL Mail, as although it is harmless, an attacker can use it to deliver Trojan code, including viruses.

Security Tip # 15

If you SQL Server is only used locally and never connected to from outside, disable ALL of the network libraries except for shared memory, as this can only talk to locally hosted applications.

Security Tip # 16

However, if you must access your SQL Server from outside (and most people do!), disable any unused network libraries to reduce your attack surface area.

Network Libraries (NetLibs) are is one of the most misunderstood areas of SQL Server. Most people will only need TCP/IP, some Named Pipes. Very few installations require the VIA netlib, so disable it unless you know that your network adapter is VIA enabled.

Likewise, if you are using SQL Server 2000 or below, disable Vines, Multiprotocol, Appletalk and IPX/SPX if you're not using them.

Security Tip # 17

Periodically run master..sp_helpstartup to check for Trojan stored procs which run when SQL Server starts. Use sp_unmakestartup to remove any unrecognized stored procedures.

Security Tip # 18

Audit your connections by logging all user login events, both successful and failed. This can easily be done by altering the registry key that determines SQL Server logging, as follows:

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

Security Tip # 19

Apply the latest Service Pack to your SQL Server once the dust has settled after it's been released. Although it is one of the most secure enterprise database platforms, it's quite feasible that an unintended backdoor is open, which is usually fixed promptly by Microsoft in a Service Pack release once known. The same goes for Windows Server service packs.


Security Tip # 20

Remove execute permissions on any extended stored procedures that you absolutely do not need in your installation, or at least limit execute permissions to the sa account.

If you do nothing else, at the very least restrict execute permissions on xp_cmdshell as this allows anyone with execute permissions to run any command line statement with the privilege level of the SQL Server service account.

Security Tip # 21

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 specter 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.

Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form