In-Depth

SQL Server 2000 Security Secrets

What you need to know that you didn't find on Books Online.

• By Chip Andrews
• 09/01/2001

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.

Certified Obscurity
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:.

yourserver.yourdomain.com

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 a certificate.

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 Security Dilemma
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 accounts.

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 version.)

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;
Integrated Security=SSPI;
Initial Catalog=mydatabase;
Data Source=myserver;"
>

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 Spooks
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 osql.exe:

exec sp_configure 'C2 Audit Mode',1
go
reconfigure
go

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 analysis.

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 Encryption
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 method.

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 www.vtc.ru/~andrey/xp_crypt. 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 algorithms.

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 SQL Server.

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.