SQL Server Security

SQL Server 2000 SP3 Security Features and Best Practices: Security Best Practices Checklist

SQL Server 2005 Security Best Practices

SQL Server 2005 Security Best Practices

SQL Server 2005 security enhancements for data at rest

SQL Server 2005 Security Webcasts:

Measuring the Security Quality of SQL Server 2005 – Eric Ogren, Security Analyst, Enterprise Strategy Group

Authentication and Authorization [pdf]

Encryption

Execution Context

SQL Server Security Best Practices

1. Review web applications to insure proper input validation on web forms and URL parameters. Database queries to the backend database, especially SELECT, INSERT, UPDATE and DELETE queries, should never be created by simply concatenating a SQL query string with input from web form fields. The input should be sanitized and then parameterized queries should be used to interact with the database.

Note: ASP.NET does this by default. A web developer actually has to bypass some of the features, or disable some default security configurations to make a site vulnerable.

Use parameterized SQL queries instead of concatenated strings. (Concatenated string example: “SELECT last, first, userid, full_name FROM users WHERE last = ‘x’;”.) Introductory tutorials teach you how to query by using concatenated strings. This approach opens you to SQL injection attacks. Instead, use the more efficient and more secure parameterized SQL query approach. See, for example, Give me parameterized SQL, or give me death.

2. Harden your database. Unpatched database vulnerabilities can be exploited by SQL Injection attacks. They can give an attacker elevated privileges and access to sensitive data. For example, unsecure configurations can allow an attacker to execute OS commands through xp_cmdshell, providing full access to database assets. A program of database vulnerability assessment and rights management should be considered a high priority.

3. Enforce proper separation of duties for the web app accounts used to connect to the backend database. Unauthenticated visitors should only have ‘read only’ access to the database backend, and only administrators or content managers should be given insert, or update privileges to the backend tables used to serve up pages. User rights management tools should be used to enforce minimal privileges.

4. Monitor your database traffic. A properly configured database activity monitoring solution will help detect malicious SQL code and the fine grained-audit trail created by a good database activity monitoring solution will also help greatly with forensic analysis needed to reconstruct events in the aftermath of an attack.

Each of these steps incorporated into a defense in depth strategy will protect your data assets and IT infrastructure from SQLi attacks.

5. No default passwords. No default accounts. No default directories. Obscurity is a helpful security measure, but it cannot be your only measure.

6. Reduce or remove default stored procedures. ‘xp_cmdshell’, for example allows an arbitrary command line to be executed.

7. Reduce or remove services and applications. Most databases include add-ons. If you don’t need them, don’t leave them.

Encryption / decryption built-ins:
DDLs for creating symmetric keys

DDLs for creating asymmetric keys and certificates

Symmetric keys and private keys of asymmetric keys are always stored encrypted

Securing the keys can be done with user-specified passwords or automatic (using SQL Server key management)

When encrypting data at rest in SQL Server 2005, the data type is varbinary, up to 8000 bytes, and under application control.

Microsoft SQL Server 2005 encryption, step-by-step.

With SQL Server 2008, add Transparent Data Encryption (TDE).

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

Do not store the encryption keys with the database.

If your database vendor is Microsoft or Oracle, they provide native encryption capabilities built-in to their databases. They can also assist with services to deploy encryption or point you to other professionals.

Peter Wayner’s book Translucent Databases takes the approach that there will be data stored which you cannot trust the administrators with. Since administrators have the capability to override encryption managed by the DBMS, you cannot trust encryption managed by the DBMS.

Note that changes in security may have unintended consequences.

If meaningful keys were used (thinking “this field is unique, must be unique, would always be unique, so why add another field to make a unique key?”), then encrypting the meaningful key breaks indexing that key.

If someone maintains their own database by extracting from yours and adding their own information, you have not secured their extract.

John Magnabosco on Encrypting Large Values:

The devil in the details is that the output of the ENCRYPTBYKEY method is a maximum size of 8,000 bytes. Therefore despite our column being set to accept cipher text larger than the row limits the encryption process in SQL Server does not allow us to create cipher text of that size. This is true for the other encryption methods including the HashBytes method.

Obfuscate by changing the database prefix. Obscurity isn’t bad; when obscurity is your only defense, that’s bad. For example, WordPress uses the wp prefix for tables and columns in their database. An attacker knows that WordPress is used, and so queries for data from the wp-example table. If the prefix were abc, it would slow the attacker down.

SQL Server For the ‘Reluctant’ DBA

SQL Queue & A: Maintaining Logs and Indexes

performing log backup during index rebuild / reorganize task

Top 10 Tips for Optimizing SQL Server Performance [pdf]