Sql server 2005 Security Best Practices Operational and Administrative Tasks

Remote Data Source Execution

When you use Windows to authenticate to SQL Server, you are using a Windows network credential. Network credentials that use both NTLM and Kerberos security systems are valid for one network "hop" by default. If you use network credentials to log on to SQL Server and attempt to use the same credentials to connect via a linked server to a SQL Server instance on a different computer, the credentials will not be valid. This is known as the "double hop problem" and also occurs in environments that use Windows authentication to connect to a Web server and attempt to use impersonation to connect to SQL Server. If you use Kerberos for authentication, you can enable constrained delegation, that is, delegation of credentials constrained to a specific application, to overcome the "double hop problem." Only Kerberos authentication supports delegation of Windows credentials. For more information, see Constrained Delegation in SQL Server Books Online.

Best practices for remote data source execution

• 
  Phase out any remote server definitions.
  
• 
  Replace remote servers with linked servers.
  
• 
  Leave ad hoc queries through linked servers disabled unless they are absolutely needed.
  
• 
  Use constrained delegation if pass-through authentication to a linked server is necessary.
  

Execution Context

• 
  EXECUTE AS CALLER - the caller of the procedure (no impersonation). This is the only pre-SQL Server 2005 behavior.
  
• 
  EXECUTE AS OWNER - the owner of the procedure.
  
• 
  EXECUTE AS SELF - the creator of the procedure.
  
• 
  EXECUTE AS 'username' - a specific user.
  

One reason to use an alternate execution context would be when a procedure executes without a particular execution context. An example of this is a service broker queue activation procedure. In addition, EXECUTE AS OWNER can be used to circumvent problems that are caused when ownership chains are broken. For example, ownership chains in a procedure are always broken when dynamic SQL statements (such as sp_executeSQL) are used.

Often what is needed is to grant the appropriate permissions to the procedural code itself, rather than either changing the execution context or relying on the caller's permissions. SQL Server 2005 offers a much more granular way of associating privileges with procedural code—code signing. By using the ADD SIGNATURE DDL statement, you can sign the procedure with a certificate or asymmetric key. A user can then be created for the certificate or asymmetric key itself and permissions assigned to that user. When the procedure is executed, the code executes with a combination of the caller's permissions and the key/certificate's permissions. An example of this would be:
CREATE CERTIFICATE HRCertificate

WITH ENCRYPTION BY PASSWORD = 'HacdeNj162kqT'

CREATE USER HRCertificateUser

FOR CERTIFICATE HRCertificate WITHOUT LOGIN

GRANT UPDATE ON pension_criteria TO HRCertificate

-- this gives the procedure update_pension_criteria

-- additional privileges of HRCertificate

ADD SIGNATURE TO update_pension_criteria BY CERTIFCATE HRCertificate

-- backup the private key and remove it from the certificate,

-- so that the procedure cannot be re-signed without permission

BACKUP CERTIFICATE HRCertificate

TO FILE = 'c:\certs_backup\HRCertificate.cer'

WITH PRIVATE KEY (FILE = 'c:\certs_backup\ HRCertificate.pvk',

ENCRYPTION BY PASSWORD = 'jBjebfP43j1!',

DECRYPTION BY PASSWORD = 'eWyveyYqW96A@!q')

ALTER CERTIFICATE HRCertificate REMOVE PRIVATE KEY

If the execution context is set but should not be reverted without permission, you can use EXECUTE AS ... WITH COOKIE or EXECUTE AS ... WITH NO REVERT. When WITH COOKIE is specified, a binary cookie is returned to the caller of EXECUTE AS and the cookie must be supplied in order to REVERT back to the original context.

When a procedure or batch uses an alternate execution context, the system functions normally used for auditing, such as SUSER_NAME(), return the name of the impersonated user rather than the name of the original user or original login. A new system function, ORIGINAL_LOGIN(), can be used to obtain the original login, regardless of the number of levels of impersonation used.

Best practices for execution context

• 
  Set execution context on modules explicitly rather than letting it default.
  
• 
  Use EXECUTE AS instead of SETUSER.
  
• 
  Use WITH NO REVERT/COOKIE instead of Application Roles.
  
• 
  Consider using code signing of procedural code if a single granular additional privilege is required for the procedure.
  

Encryption

WITH ENCRYPTION BY PASSWORD = '87(HyfdlkRM?_764#GRtj*(NS£”_+^$('

SQL Server 2005 can use DDL to define certificates, asymmetric keys, and symmetric keys on a per-database basis. Certificates and asymmetric keys consist of a private key/public key pair. The public key can be used to encrypt data that can be decrypted only by using the private key. Or, for the sake of performance, the public key can be used to encrypt a hash that can be decrypted only by using the private key. Encrypted checksum generation to ensure non-repudiation is known as signing.

Alternatively, the private key can be used to encrypt data that can be decrypted by the receiver by using the public key. A symmetric key consists of a single key that is used for encryption and decryption. Symmetric keys are generally used for data encryption because they are orders of magnitude faster than asymmetric keys for encryption and decryption. However, distributing symmetric keys can be difficult because both parties must have the same copy of the key. In addition, it is not possible with symmetric key encryption to determine which user encrypted the data. Asymmetric keys can be used to encrypt and decrypt data but ordinarily they are used to encrypt and decrypt symmetric keys; the symmetric keys are used for the data encryption. This is the preferred way to encrypt data for the best security and performance. Symmetric keys can also be protected by individual passwords.

SQL Server 2005 makes use of and also can generate X.509 certificates. A certificate is simply an asymmetric key pair with additional metadata, including a subject (the person the key is intended for), root certificate authority (who vouches for the certificate's authenticity), and expiration date. SQL Server generates self-signed certificates (SQL Server itself is the root certificate authority) with a default expiration date of one year. The expiration date and subject can be specified in the DDL statement. SQL Server does not use certificate "negative lists" or the expiration date with data encryption. A certificate can be backed up and restored separately from the database; certificates, asymmetric keys, and symmetric keys are backed up with the database. A variety of block cipher encryption algorithms are supported, including DES, Triple DES, and AES (Rijndael) algorithms for symmetric keys and RSA for asymmetric keys. A variety of key strengths are supported for each algorithm. Stream cipher algorithms, such as RC4 are also supported but should NOT be used for data encryption. Some algorithms (such as AES) are not supported by all operating systems that can host SQL Server. User-defined algorithms are not supported. The key algorithm and key length choice should be predicated on the sensitivity of the data.

SQL Server encrypts data on a cell level—data is specifically encrypted before it is stored into a column value and each row can use a different encryption key for a specific column. To use data encryption, a column must use the VARBINARY data type. The length of the column depends on the encryption algorithm used and the length of the data to be encrypted (see Choosing an Encryption Algorithm in SQL Server Books Online). The KEY_GUID of the key that is used for encryption is stored with the column value. When the data is decrypted, this KEY_GUID is checked against all open keys in the session. The data uses initialization vectors (also known as salted hashes). Because of this, it is not possible to determine if two values are identical by looking at the encrypted value. This means, for example, that I cannot determine all of the patients who have a diagnosis of Flu if I know that Patient A has a diagnosis of Flu. Although this means that data is more secure, it also means that you cannot use a column that is encrypted by using the data encryption routines in indexes, because data values are not comparable.

Data encryption is becoming more commonplace with some vendors and industries (for example, the payment card industry). Use data encryption only when it is required or for very high-value sensitive data. In some cases, encrypting the network channel or using SQL Server permissions is a better choice because of the complexity involved in managing keys and invoking encryption/decryption routines.

Because unencrypted data must be stored in memory buffers before being transmitted to clients, it is impossible to keep data away from an administrator who has the ability to debug the process or to patch the server. Memory dumps can also be a source of unintended data leakage. If symmetric keys are protected by asymmetric keys and the asymmetric keys are encrypted by using the database master key, a database administrator could impersonate a user of encrypted data and access the data through the keys. If protection from the database administrator is preferred, encryption keys must be secured by passwords, rather than by the database master key. To guard against data loss, encryption keys that are secured by passwords must have an associated disaster recovery policy (offsite storage, for example) in case of key loss. You can also require users to specify the database master key by dropping encryption of the database master key by the instance master key. Remember to back up the database in order to back up the symmetric keys, because there are no specific DDL statements to back up symmetric and asymmetric keys, just as there are specific DDL statements to back up certificates, the database master key, and the service master key.

Best practices for data encryption

• 
  Encrypt high-value and sensitive data.
  
• 
  Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.
  
• 
  Password-protect keys and remove master key encryption for the most secure configuration.
  
• 
  Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.
  
• 
  Always back up your database to back up your symmetric and asymmetric keys.
  

Auditing

SQL Server 2005 introduces auditing based on DDL triggers and event notifications. You can use DDL triggers not only to record the occurrence of DDL, but also to roll back DDL statements as part of the trigger processing. Because a DDL trigger executes synchronously (the DDL does not complete until the trigger is finished), DDL triggers can potentially slow down DDL, depending on the content and volume of the code. Event notifications can be used to record DDL usage information asynchronously. An event notification is a database object that uses Service Broker to send messages to the destination (Service Broker-based) service of your choosing. DDL cannot be rolled back by using event notifications.

Because the surface area of SQL Server 2005 is larger than previous versions, more auditing events are available in SQL Server 2005 than in previous versions. To audit security events, use event-based auditing, specifically the events in the security audit event category (listed in SQL Server Books Online). Event-based auditing can be trace-based, or event notifications-based. Trace-based event auditing is easier to configure, but may result in a large event logs, if many events are traced. On the other hand, event notifications send queued messages to Service Broker queues that are in-database objects. Trace-based event auditing cannot trace all events; some events, such as SQL:StmtComplete events, are not available when using event notifications.

There is a WMI provider for events that can be used in conjunction with SQL Server Agent alerts. This mechanism provides immediate notification through the Alert system that a specific event has occurred. To use the WMI provider, select a WMI-based alert and provide a WQL query that produces the event that you want to cause the alert. WQL queries use the same syntax for naming as does event notifications. An example of a WQL query that looks for database principal impersonation changes would be:

When C2 auditing is enabled, data is saved in a log file in the Data subdirectory in the directory in which SQL Server is installed. The initial log file size for C2 auditing is 200 megabytes. When this file is full, another 200 megabytes is allocated. If the volume on which the log file is stored runs out of space, SQL Server shuts down until sufficient space is available or until the system is manually started without auditing. Ensure that there is sufficient space available before enabling C2 auditing and put a procedure in place for archiving the log files.

SQL Server 2005 SP2 allows configuring an option that provides three elements required for Common Criteria compliance. The Common Criteria represents the outcome of efforts to develop criteria for evaluation of IT security that are widely useful within the international community. It stems from a number of source criteria: the existing European, US, and Canadian criteria (ITSEC, TCSEC, and CTCPEC respectively). The Common Criteria resolves the conceptual and technical differences between the source criteria. The three Common Criteria elements that can be configured by using an instance configuration option are:

• 
  Residual Information Protection, which overwrites memory with a known bit pattern before it is reallocated to a new resource.
  
• 
  The ability to view login statistics.
  
• 
  A column-level GRANT does not override table-level DENY.
  

GO

RECONFIGURE;

sp_configure 'common criteria compliance enabled', 1;

GO

RECONFIGURE;

Best practices for auditing

• 
  Auditing is scenario-specific. Balance the need for auditing with the overhead of generating addition data.
  
• 
  Audit successful logins in addition to unsuccessful logins if you store highly sensitive data.
  
• 
  Audit DDL and specific server events by using trace events or event notifications.
  
• 
  DML must be audited by using trace events.
  
• 
  Use WMI to be alerted of emergency events.
  
• 
  Enable C2 auditing or Common Criteria compliance only if required.
  

Microsoft Baseline Security Analyzer and SQL Server Best Practices Analyzer

SQL Server 2005 Best Practices Analyzer 2.0 CTP has been released in conjunction with Service Pack 2. You can download it from the Microsoft Download Center, SQL Server 2005 Best Practices Analyzer (February 2007 CTP) page. SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. Best Practices Analyzer uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential security issues in the database environment.

Best practice analysis utilities recommendations

• 
  Run BPA against SQL Server 2005.
  
• 
  Regularly run MBSA 2.0 to ensure latest SQL Server 2005 patch level
  
• 
  Regularly run MBSA 2.0 for SQL Server 2000 instances
  

Patching

Best practices for patching SQL Server

• 
  Always stay as current as possible.
  
• 
  Enable automatic updates whenever feasible but test them before applying to production systems.
  

Conclusion

Did this paper help you? Please give us your feedback. On a scale of 1 (poor) to 5 (excellent), how would you rate this paper?