There are two ways that procedural code can be executed on a remote instance of SQL Server: configuring a linked server definition with the remote SQL Server and configuring a remote server definition for it. Remote servers are supported only for backward compatibility with earlier versions of SQL Server and should be phased out in preference to linked servers. Linked servers allow more granular security than remote servers. Ad hoc queries through linked servers (OPENROWSET and OPENDATASOURCE) are disabled by default in a newly installed instance of SQL Server 2005.
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.
SQL Server always executes SQL statements and procedural code as the currently logged on user. This behavior is a SQL Server-specific behavior and is made possible, in the case of procedural code, by the concept of ownership chains. That is, although a stored procedure executes as the caller of the stored procedure rather than as the owner, if ownership chaining is in place, permissions are not checked for object access and stored procedures can be used to encapsulate tables, as mentioned previously in this paper. In SQL Server 2005, the creator of a procedure can declaratively set the execution context of the procedure by using the EXECUTE AS keyword in the CREATE PROCEDURE, FUNCTION, and TRIGGER statements. The execution context choices are:
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.
To maintain backward compatibility, EXECUTE AS CALLER is the default. The distinction between AS OWNER and AS SELF is needed because the creator of the procedure may not be the owner of the schema in which the procedure resides. In this case, AS SELF refers to the procedure owner, AS OWNER refers to the object owner (the schema owner). In order to use EXECUTE AS 'username', the procedure creator must have IMPERSONATE permission on the user named in the execution context.
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
EXECUTE AS can also be used to set the execution context within an SQL batch. In this form, the SQL batch contains an EXECUTE AS USER='someuser' or EXECUTE AS LOGIN='somelogin' statement. This alternate execution context lasts until the REVERT statement is encountered. EXECUTE AS and REVERT blocks can also be nested; REVERT reverts one level of execution context. As with EXECUTE AS and procedural code, the user changing the execution context must have IMPERSONATE permission on the user or login being impersonated. EXECUTE AS in SQL batches should be used as a replacement for the SETUSER statement, which is much less flexible.
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.
SQL Server 2005 has built-in data encryption. The data encryption exists at a cell level and is accomplished by means of built-in system procedures. Encrypting data requires secure encryption keys and key management. A key management hierarchy is built into SQL Server 2005. Each instance of SQL Server has a built-in service master key that is generated at installation; specifically, the first time that SQL Server is started after installation. The service master key is encrypted by using both the SQL Server Service account key and also the machine key. Both encryptions use the DPAPI (Data Protection API). A database administrator can define a database master key by using the following DDL.
CREATE MASTER KEY
WITH ENCRYPTION BY PASSWORD = '87(HyfdlkRM?_764#GRtj*(NS£”_+^$('
This key is actually encrypted and stored twice by default. Encryption that uses a password and storage in the database is required. Encryption that uses the service master key and storage in the master database is optional; it is useful to be able to automatically open the database master key without specifying the password. The service master key and database master keys can be backed up and restored separately from the rest of the database.
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.
SQL Server 2005 supports login auditing, trigger-based auditing, and event auditing by using a built-in trace facility. Password policy compliance is automatically enforceable through policy in SQL Server 2005 for both Windows logins and SQL logins. Login auditing is available by using an instance-level configuration parameter. Auditing failed logins is the default, but you can specify to audit all logins. Although auditing all logins increases overhead, you may be able to deduce patterns of multiple failed logins followed by a successful login, and use this information to detect a possible login security breech. Auditing is provided on a wide variety of events including Add Database User, Add Login, DBCC events, Change Password, GDR events (Grant/Deny/Revoke events), and Server Principal Impersonation events. SQL Server 2005 SP2 also supports login triggers.
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:
SELECT * FROM AUDIT_DATABASE_PRINCIPAL_IMPERSONATION_EVENT
SQL Server can be configured to support auditing that is compliant with C2 certification under the Trusted Database Interpretation (TDI) of the Trusted Computer System Evaluation Criteria (TCSEC) of the United States National Security Agency. This is known as C2 auditing. C2 auditing is configured on an instance level by using the C2 audit mode configuration option in sp_configure.
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.
You can configure an instance to provide these three elements for Common Criteria compliance by setting the configuration option common criteria compliance enabled as shown in the following code.
sp_configure 'show advanced options', 1;
sp_configure 'common criteria compliance enabled', 1;
In addition to enabling the Common Criteria options in a SQL Server instance, you can use login triggers in SQL Server 2005 SP2 to limit logins based upon time of day or based on an excessive number of existing connections. The ability to limit logins based on these criteria is required for Common Criteria compliance.
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
Microsoft Baseline Security Analyzer (MBSA) is a utility that scans for common insecurities in a SQL Server configuration. Run MBSA on a regularly scheduled basis, either locally or across the network. MBSA scans for Windows operating system, security principal, network, and file system insecurities and tests for SQL Server 2000 and MSDE-specific insecurities, but does not incorporate SQL Server 2005-specific checks yet. It will check to see if SQL Server 2005 is patched to the latest Service Pack version.
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
The best way to ensure the security of the server software and to ensure the security of SQL Server 2005 is to install security hotfixes and service packs as soon as possible. Use manual updates on an operating system basis by using Windows Update or Microsoft Update. You can enable automatic updates using Windows Update or Microsoft Update as well, but updates should be tested before they are applied to production systems. SQL Server 2005 incorporates SQL Server hotfixes and service packs into Windows Update. All hotfixes should be installed immediately and service packs should be tested and installed as soon as possible. This requirement cannot be emphasized enough. For suggestions on minimizing downtime when installing hotfixes and service packs, see Preventing Reboots, Installing Multiple Updates, and More on Microsoft TechNet.
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.
Security is a crucial part of any mission-critical application. To implement security for SQL Server 2005 in a way that is not prone to mistakes, security setup must be relatively easy to implement. The "correct" security configuration should be the default configuration. This paper describes how it is a straightforward task to start from the SQL Server 2005 security defaults and create a secure database configuration according to the Trustworthy Computing Initiative guidelines.
For more information:
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?