SQL Server 2005 Security – Part 4

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

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',
ENCRYPTION_PASSWORD = '[email protected]'

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 =  '[email protected]'

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 = '[email protected]'


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',
DECRYPTION_PASSWORD = '[email protected]',
ENCRYPTION_PASSWORD = '[email protected]') 

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

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.
Previous articleDaffodil Replicator v1.7 launched
Next articleCalendar

Latest Articles