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 protects your files, and preferably encrypts 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. 

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. 

Configuration

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.

Permissions 

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.