SQL Server 2005 Security - Part 4

Friday Mar 11th 2005 by Marcin Policht

This article concludes our coverage of security related changes in SQL Server 2005 Beta 2. Topics focused on here are code and module signing, modifications of SQL Server Agent and SQL Profiler operations, as well as monitoring and auditing changes.

In this article, we will conclude our coverage of security related changes in SQL Server 2005 Beta 2 (although we will continue discussion of improvements in other functionality areas throughout the reminder of this series). The topics we will focus on here are code and module signing, modifications of SQL Server Agent and SQL Profiler operations, as well as monitoring and auditing changes.

Code and Module Signing

As explained in our previous article, key infrastructure built-into SQL Server 2005 can be used for both encryption (protecting content of data from unauthorized access) and signing (ensuring data integrity and verifying authenticity of its origin). While encryption can be performed with both symmetric and asymmetric keys, signing requires the latter (however, both encryption and signing rely on certificates for their functionality). A digital signature is created by applying a private key to data content. Verification of integrity and authenticity of this content involves applying a corresponding public key to it and comparing the result with the digital signature (if both are identical, then data has not been modified).

In the case of SQL Server 2005, signatures also offer a convenient method of ensuring that a particular resource (such as a table or a view) can be accessed only via a designated module (such as a stored procedure). While such capabilities existed in previous versions of SQL Server, they were to some extent limited. It was possible, for example, to restrict EXECUTE permissions on the module (but this might have required SELECT permission on the target resource in case the ownership chain between the two was broken). With SQL Server 2005, you can also use the EXECUTE AS T-SQL Statement, which we discussed earlier (but this alters, at the same time, security context in which the underlying statement executes, which might not be desired if auditing of all user actions is required). With digital signatures, you can accomplish the same goal, without concern for ownership chaining or granting direct access to the underlying table or view, as well as without changing the execution context. In order for this to happen, the module is signed with a private key contained in a certificate, to which a user has been given permissions (by creating a mapping between the user and certificate). Afterwards, whenever the module is launched, the certificate gets added to user's access token.

Let's look into how this process is implemented. Let's assume that we have a stored procedure called usp_H2Ogate, which runs the SELECT statement on a table in the same database. We want to restrict access to it to (and effectively protect the content of the table, allowing only designated users to execute it). First, we need to create a certificate along with the key pair associated with it. This is done with the CREATE CERTIFICATE T-SQL statement introduced in the previous article, which at the very least needs to contain its name and a subject (optional fields include validity period and certificate owner). The statement can use either the master database key or an encryption password to secure the private key included in the certificate (this password, if assigned, needs to be specified at the time of module signing). In our example, we are using the second approach:

WITH SUBJECT = 'TopSecret',

Next, we use the ADD SIGNATURE T-SQL statement to sign our usp_H2Ogate stored procedure with the private key associated with the newly created certificate. The statement can also be used to sign functions, triggers, or event notifications and requires ALTER permissions on referenced objects as well as CONTROL permissions on the certificate. You can also apply the asymmetric key directly when executing ADD SIGNATURE, by specifying the ASYMMETRIC KEY clause. The first option would look similar to the following:

WITH PASSWORD =  'r1Ch@rDn1x0n'

At this point, we are ready to associate a database user with the certificate by running the CREATE USER statement including the FOR CERTIFICATE clause (or with an asymmetric key, if that option was used when running the ADD SIGNATURE statement - in which case the FOR ASYMMETRIC KEY clause would be needed instead). In either case, the designated user is allowed to run our usp_H2Ogate stored procedure, but can not execute the SELECT statement on the underlying table directly without having explicit permissions to do so. In our example, the CREATE USER statement takes the form:


As mentioned before, an additional benefit of assigning a certificate to a module is the ability to detect any modification to it. If the content of the module is changed, its signature is invalidated. This, in turn, prevents any users associated with it from successfully executing it. If this level of protection does not satisfy your requirements, you can additionally remove the private key from the certificate (after backing it up to a password-protected file and storing it in a secure location). This can be accomplished by running the DUMP CERTIFICATE statement (requiring knowledge of the encryption password), which creates copies of a certificate and its private key in the form of two separate files, followed by the ALTER CERTIFICATE statement with the REMOVE PRIVATE KEY clause, as in the example below:

DUMP CERTIFICATE Y0urEy3s0Nly TO FILE='C:\Documents and Settings\
  DeepThroat\My Documents\Certificates\H20.cer'
PRIVATE_KEY TO FILE = 'C:\Documents and Settings\DeepThroat\My Documents\Private Keys\H20.prv'
USING PASSWORD = 'r1Ch@rDn1x0n'


To restore the private key and include it with the certificate, you can launch the ALTER CERTIFICATE statement using the WITH PRIVATE_KEY clause specifying the location of the private key and the password used to encrypt it, as in the following:

WITH PRIVATE_KEY (FILE = 'C:\Documents and Settings\DeepThroat\My Documents\Private Keys\H20.prv',

We will return to the topic of certificate and key management tasks when discussing SQL Server 2005 Reporting Services.

SQL Server Agent Operations

In the previous versions of SQL Server, the SQL Server Agent service account had to be a member of the local Administrators group when executing the xp_cmdshell extended stored procedure or ActiveX scripting and CmdExec jobs owned by users who were not members of the SysAdmin fixed server roles. This was necessary in order for it to be able to retrieve SQL Agent proxy account credentials stored in the form of LSA secrets (Windows-specific secure mechanism for storing credentials, which makes them accessible only to members of the local Administrators group). With SQL Server 2005, this is no longer required. In addition, it is also possible to create multiple proxy accounts (from the Proxies subnode of SQL Server Agent node in the SQL Server Management Studio) and assign separate ones to individual steps of any SQL Server Agent job (although the legacy configuration option, which assigns instance-wide proxy account is still available from the Job System page of the SQL Server Agent Properties window, in case you use SQL Server Management Studio to manage older versions of SQL Server).

Permissions on SQL Server Agent jobs and their configuration options changed as well. In order to create or execute jobs, a user must be a member of SQLAgentUserRole in msdb. Management (creations, modifications, and deletions) of proxy accounts is reserved for members of the Sysadmin fixed server role. Similarly, only they can define job steps that run in the security context of the SQL Server Agent account (rather than using a proxy).

Monitoring and Auditing

One of the long awaited improvements in this area is delegating the ability to run SQL Profiler without granting membership in the SysAdmin fixed server role (this is done by including a designated login in the Analysis Services server role). In addition, SQL Profiler auditing now covers not only SQL Server 2005 activities but also events generated by SQL Server 2005 Analysis Services. Finally, enhanced auditing is also possible through assigning triggers to Data Definition Language operations, such as CREATE, ALTER, or DROP.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved