SQL Server 2000 Security Secrets
What you need to know that you didn't find on Books Online.
- By Chip Andrews
Despite the plentiful information in Books Online
and on the official “SQL Server 2000 Security”
document Microsoft offers online, setting up a
truly secure SQL Server remains a difficult task.
In this article I discuss some of the more obscure
and troublesome aspects of SQL Server security
and what you can do to make sure your shop doesn’t
become a statistic.
Unless you’ve been locked up in a bunker somewhere
you’ve heard of the recent rash of Web site intrusions
where thousands of credit card numbers have been
whisked away and ransomed by unscrupulous thugs.
If those credit card numbers were stored in a
database that had been protected properly, these
attacks wouldn’t have happened. Your company’s
data is your most valuable resource, so start
treating it with the level of respect it deserves.
This is just as true on an intranet, where you
have to worry about potentially alienated employees,
as on the wider Internet.
I begin with a discussion on the details of setting
up a server certificate to support SSL encryption
over any network library, followed by a demonstration
of how to implement an integrated security strategy
that doesn’t require individual users to be trusted
by SQL Server. I also discuss advanced auditing
techniques as well as encryption of data stored
in a SQL Server database. These topics are quite
hard to find information on if you’re limited
to the easily available Microsoft documentation.
SQL Server 2000 is designed to provide
administrators with the holy grail of encrypted
communication, via its secure socket layer (SSL)
support over any network library. As a bonus,
when a valid certificate is installed on a SQL
Server 2000 host, the server can encrypt all authentication
packets even when SSL data encryption isn’t being
used. In previous versions administrators were
forced to use the somewhat cumbersome and restrictive
(due to TCP port randomness) multi-protocol net
library’s encryption support to gain a similar
measure of security. But there’s very little information
available on how to configure SQL Server 2000
to use the newfound power of SSL encryption.
If you scour the SQL Server security document
referenced at the end of this article, you’ll
find a mention of obtaining a certificate from
a certificate authority that matches the fully
qualified DNS name of the SQL Server and storing
it in certificate store. For someone familiar
with this process (such as an IIS administrator),
this might seem straightforward. For many SQL
Server administrators, the documentation might
as well be written in hieroglyphics. Here are
step-by-step instructions that should demystify
this process, so that every SQL Server installation
can enjoy the security of SSL-encrypted communications.
Note that I’ll use Windows 2000 for the operating
system in this demonstration, but the same technique
works on Windows NT with a few minor changes.
To begin, you need to determine the fully qualified
domain name of the SQL Server on which you’re
installing a certificate. You can obtain this
by selecting Start | Settings| Control Panel and
launching the System applet. On the Network Identification
tab (see Figure 1) you’ll see a value for “full
computer name.” That’s the name to use when you
obtain a certificate. Typically this will be something
along the lines of:.
|Figure 1. Use the System
Properties dialog box to determine the fully
qualified domain name of the computer where
SQL Server is installed. Notice that in this
case the server doesn’t have a DNS suffix.
(Click image to view larger version.)
Next, you need to choose a Certificate Authority
(CA) such as a Win2K machine with Certificate
Services installed or a public CA such as VeriSign
(www.verisign.com). For this demonstration I’ll
use a Win2K machine with Certificate Services
installed as the source for the certificate. The
important distinction between using your own CA
or a public CA is that the public CA’s certificates
are already present in the list of “Trusted Root
Certification Authorities” and are, thus, already
trusted by the client. If you use your own CA,
you’ll need to have the client add your CA’s certificate
to the trusted store. You can do this by using
a browser on the client to navigate to http:///certsrv
and clicking on the “Retrieve the CA certificate“
option. If you don’t already have Certificate
Services installed on the server, you can do this
via the Add/Remove Programs option in the Control
Panel under “Add/Remove Windows Components.”
Make sure to perform the rest of this procedure
while logged in to the SQL Server using the same
account used as the service account for SQL Server.
Yes, this means you can’t use the LocalSystem
account to run SQL Server if you wish to install
Once you’ve logged in as the SQL Server service
account, use the IIS-based certificate request
tool (http://servername/certsrv) to navigate to
the certificate server using Internet Explorer.
From there click “Request a Certificate,” “Advanced
Request,” and finally “Submit a certificate request
to this CA using a form.”
Here’s where things start to look a bit scary.
In Figure 2 you can see that many fields are already
filled in by the CA administrator, so the only
fields you really have to enter are the name and
e-mail address. The important thing to do on this
screen is to make sure the name field is exactly
the same as the fully qualified name of the SQL
Server that you obtained earlier.
|Figure 2. Win2K Certificate
Services provides a browser-based tool for
automatically obtaining a new certificate.
You must be sure to enter the fully qualified
domain name of the SQL Server that you obtained
earlier. (Click image to view larger version.)
Once you’ve completed the form, click Submit.
If the CA has been configured to issue certificates
immediately, you’ll be greeted with a link to
install the new certificate immediately. If not,
you get a message telling you to check back later
when the CA administrator has approved your certificate
request. You can approve a request on your own
certificate server by selecting Start | Programs
| Administrative Tools | Certification Authority
on the machine where Certificate Services is installed
and looking under “Pending Requests.” To approve
the request, right-click on the pending request
and select All Tasks and then Issue.
Once the certificate’s been approved, the browser-based
tool will show a link to allow you to install
the certificate. Once you click the link, the
certificate will be installed and you should be
able to start using it immediately. You’ll need
to stop and start the SQL Server to make it use
the new certificate.
To test the certificate-based encryption, you
can either click “Force protocol encryption” under
the Server Network Utility (and perform another
restart of the SQL Server) and sniff some sample
traffic using Network Monitor. Figure 3 shows
a sample packet after SSL encryption is enabled.
If all’s well, there won’t be any plain text transmission
of credentials or data as long as you use either
client-requested or server-required encryption.
|Figure 3. Here a SQL
Server with a certificate installed is configured
to force protocol encryption and is now doing
so over TCP/IP sockets. Can you see the data?
(Click image to view larger version.)
Solving the Integrated
SQL Server 2000 offers two security modes: Windows
Authentication Mode and Mixed Mode. Windows Authentication
Mode requires the user to be authenticated by
NT or Win2K and is the new default for SQL Server
2000. In Mixed Mode, both SQL Server and Windows
Authentication Mode logins are allowed access.
Native SQL Server authentication lacks strong
controls such as password complexity, expiration,
lockout, or history when using SQL Server logins.
It’s provided for backward compatibility and for
use with Windows 98/Me installations where Windows
authentication isn’t an option.
If you spend any time reading Microsoft’s recommendations
about SQL security models, it’s clear that the
preferred mode is Windows Authentication mode.
What’s not clear to many administrators or developers
is how to make this work in an Internet configuration
where all users share a single context—usually
that of the anonymous IIS user account.
Much of the confusion comes from the fact that
most SQL Server developers and administrators
aren’t also NT or Win2K administrators, and they
may not be familiar with the intricacies of domain
trust models, local machine accounts or anonymous
IIS account. The bottom line is that using Windows
authentication in SQL Server doesn’t mean having
to use Integrated Windows Authentication in IIS.
It’s perfectly acceptable practice to use the
anonymous IIS context and let that account use
Windows authentication to connect to the SQL Server.
So, you might be asking, what’s the catch? In
situations where the IIS server and the SQL Server
are stand-alone servers or in separate domains,
the SQL Server may not trust the anonymous IIS
user. To remedy this situation, you can use the
steps documented in Knowledge Base article Q184566,
“HOWTO: Set Up Duplicate Anonymous Accounts on
Separate Server.” Another option is to have the
IIS Servers and SQL Server exist in a small domain
of their own for the sole purpose of sharing user
If you properly configure the SQL Server to trust
accounts from another domain or server, then you
can easily grant permissions to that account for
access to the SQL Server as shown in Figure 4.
|Figure 4. Here we’ve
granted the anonymous IIS user access to SQL
Server. Notice that there are no SQL Server
passwords to worry about when using this method
of authentication. (Click image to view larger
The real advantage to this technique is that
connection strings for this server no longer need
to contain embedded authentication credentials.
For example, you might use a connection string
like this one:
ConnString = "Provider=SQLOLEDB.1;
Also, if your shop implements rules about password
complexity or rotation, they can easily be enforced
and handled at the operating system level where
these mechanisms already exist. Anyone who has
had to constantly change connection strings in
SQL Server applications will surely appreciate
the end of that nightmare. As a side bonus, when
the next IIS security hole exposes your connection
strings, the attacker won’t be greeted with a
working username and password for your SQL Server.
Auditing for Government
Anyone who has ever tried to troubleshoot a SQL
Server by looking at the error log has probably
been somewhat disappointed. The log contains little
more than failure messages and information messages
about a few events. If you need to record detailed
information about what SQL Server is doing at
any given moment, you may want to try your hand
at the new C2 auditing mode available in SQL Server
2000. In order to enable C2 auditing, enter the
following T-SQL commands in Query Analyzer or
exec sp_configure 'C2 Audit Mode',1
When you enable C2 auditing, SQL Server automatically
creates a trace file called audit_YYYYMMDDHHMMSS_
[seq].trc in the \microsoft sql server\mssql\data
directory. Note that sometimes a server stop and
start are needed to initiate the logging process.
You can analyze these trace logs later using the
Profiler utility. You also have the option of
exporting the trace log to a table for further
Keep an eye on the size of these trace logs,
as they can grow quite large when left unchecked
in production environments. Also, don’t underestimate
the power of exporting the logs into SQL Server
where you can see what users are doing to your
data and what types of commands they’re entering.
It might be interesting, for example, to see how
many users are trying to run xp_cmdshell and what
kinds of things they’re attempting to do with
an operating system shell.
If you’re really feeling adventurous, you can
go all the way and configure your SQL Server to
be C2 compliant by following the directions posted
at Microsoft’s official C2 site (www.microsoft.com/technet/security/sqlc2.asp).
Be forewarned however that quite a number of extended
stored procedures aren’t supported on C2 servers.
Be sure you don’t need one of them before you
go dropping them left and right.
The Truth About SQL Data
One question that comes up constantly
on SQL Server security newsgroups is how to encrypt
data stored in SQL Server. The simple answer is
that you can’t—using SQL Server alone. Microsoft’s
recommended method is to encrypt the data outside
of SQL Server and then place it in SQL Server.
Here are some methods for encrypting SQL Server
data and the advantages and disadvantages of each
Microsoft designed extended stored procedures
to allow developers (mostly its own developers
up to this point) to extend the capabilities of
the server. There’s no reason someone couldn’t
create an extended stored procedure to perform
encryption of data and store the encrypted data
in a field using a stored procedure. In fact,
if you’re interested in such a product, someone
has written one. You can download a copy from
But proceed at your own risk, as you can’t see
the source code. Using Microsoft’s CryptoAPI and
your own Visual C++ developers, you should really
have no problem at all rolling your own extended
stored procedures implementing Microsoft’s built-in
implementations of publicly available cryptographic
The real advantage of such a server-based approach
is that, if properly implemented, it could allow
you to enable encryption without changes to existing
applications. A disadvantage of a server-based
approach, besides the added complexity in development,
is that the encryption key will likely be stored
right on SQL Server. This is something akin to
taping the key to your safe right on the side.
Another option for those less adventurous in
the world of CryptoAPI programming is to purchase
a third-party COM component that allows you to
be mostly abstracted from the details. A popular
and easy-to-implement solution is Persits Software
Inc.’s AspEncrypt component at www.aspencrypt.com.
One nice advantage of this approach is that you
can easily switch out encryption components without
having to visit SQL Server. You can also manage
secrets (such as symmetric encryption keys) outside
of SQL Server and thus keep the data and the key
as far apart as possible.
One disadvantage to the COM-based approach is
that every Web server in the farm will need the
object as well as shared access to an encryption
key if the encryption is symmetric. If you’re
simply hashing data such as passwords, this shouldn’t
pose a problem. Also, this approach could mean
changing quite a bit of code to perform the encryption
at the ASP level instead of the SQL Server level.
Overall, however, this has been the more popular
and accepted approach to encrypting data within
One final method for encrypted data that I hesitate
to mention is the use of the pwdencrypt and pwdcompare,
undocumented functions built into SQL Server.
Pwdencrypt takes a single parameter, the text
to be encrypted, and returns the encrypted text.
Pwdcompare takes two parameters, a plaintext and
an encrypted text, and returns true if the encrypted
text was derived from the plaintext.
My hesitation in using these functions exists
on several levels. For one, the encryption is
one-way and therefore not a good option for things
like credit card information. These functions
are not supported by Microsoft and are totally
undocumented. Also, the encryption and hashing
algorithms used are unknown (although Microsoft
claims that all proprietary encryption has been
replaced with CryptoAPI calls) and subject to
change at any time. If you feel adventurous and
need a simple hashing algorithm, these functions
might prove to be a good solution.
It’s also worth mentioning that Microsoft’s
.NET framework has easy-to-use encryption and
hashing functions built in. These will allow any
application that uses the framework to implement
encryption without third-party controls. The next
version of SQL Server (code-named “Yukon”) is
also rumored to contain the .NET Common Language
Runtime and, thus, may have access to these encryption
classes from within SQL Server itself.
Filling in the Gaps
I hope this article has filled in some of the
gaps in your general knowledge of SQL Server 2000
security. If you spend any time monitoring security
newsgroups, you’ll see many of these questions
asked over and over. Of course, there’s always
more to learn, and you should never feel bashful
about asking questions. Take comfort in the fact
that the same people answering those questions
weren’t born with that knowledge either.
Chip Andrews, MCSE+I, MCDBA is a software security architect at (Clarus Corp.). Chip maintains the (sqlsecurity.com) Web site and speaks at security conferences on SQL Server security issues.