Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server 2005 Secures Your Data Like Never Before : Page 2

Right out of the box, SQL Server 2005 does not install many of its services (such as SQL Server Reporting Services) or does not have features turned on by default (.NET integration), thereby reducing the attack vectors that hackers could use to compromise your data security.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Database Security Features
The database is the central concern of data-based applications. As far as the typical application knows, the server is just a name in a connection string, the gatekeeper that makes it sometimes difficult to get at precious data. There are three major features in regards to database security that I find most interesting as a developer: data encryption, execution context, and user/schema separation.

Native Data Encryption
I once thought that encrypting data stored within any of the industrial-strength database engines was a total waste of processing cycles and storage space. The data is stored in the digital equivalent of a well-protected vault that made it virtually impossible to get past the guard, right? Wrong, of course. SQL Server 2000 proved to have way too many ways of circumventing its authentication protections, starting with the very common blank sa password. And while SQL Server 2005 is far more secure than its predecessors, it is still possible that an attacker will get access to the stored data. Thus, encrypting data becomes a defense in-depth strategy that provides a layer of protection of last resort. Even if an attacker gets access to the data, she still has to decrypt the data, which is neither an easy nor quick chore.

In SQL Server 2000 you had to either purchase a third-party product to encrypt data, make COM calls outside the server, or perform the encryption in the client application before sending it to the server. Worst of all, it also meant that you had to take responsibility for protecting the keys or certificates used to encrypt the data. Protecting keys is the hardest thing to get right with encryption, so many applications had weak data protection even though the data was strongly encrypted. I wince whenever I see a private key embedded in an application EXE or DLL file!

SQL Server 2005 solves these problems by making encryption a native feature of the database, with rich support for key hierarchies and many encryption algorithms. The flexibility is impressive, and best of all you can opt to have the database server manage all your keys for you.

Encryption makes incredible demands on the processing power of a server, so encrypting every field in every row in a 10 million row table and then doing a SELECT without a WHERE clause is likely to bring the server to its knees. But it allows some interesting security options, such as code signing that allows access to resources only through code such as a stored procedure.
SQL Server 2005 provides all the most common types of encryption, along with a rich selection of algorithms. I'm not going to go into the details of how all these options implement encryption in this article, but here is a quick summary of encryption support in the server:

  • Symmetric Key Encryption uses the same key for both encryption and decryption, making the key a shared secret. Normally this kind of encryption is difficult to implement in an application because sharing the keys in a secure way is hard. But this type of encryption is ideal when the data is stored in SQL Server and you let it manage the keys. SQL Server 2005 provides the RC2 and RC2 algorithms as well as the AES and DES families of algorithms.
  • Asymmetric Key Encryption uses a public/private key pair so that the public key can be widely shared and revealed. This form of encryption is handy when you need to transmit data in encryption form outside of the server. SQL Server 2005 provides RSA with 512-, 1,024-, and 2,048-bit key lengths.
  • A certificate is a kind of asymmetric key encryption, but a digital certificate protects the public and private keys, as a way to associate a private key with its owner. SQL Server 2005 supports the Internet Engineering Task Force's X.509v3 specification. You can have SQL Server generate certificates for its own use or you can import certificates provided by trusted outside certificate authorities.
As I mentioned earlier, keeping keys secure is the hardest thing about encryption to get right. SQL Server 2005 provides a key hierarchy to protect keys it uses both internally and to protect your data, shown in Figure 1.

Figure 1.SQL Server 2005's Encryption Key Hierarchy: The Service Master Key protects all Database Master Keys, which in turn protect all SQL Server-managed keys you use within the database.
In the figure, an arrow points from a key or service used to protect the key pointed to. So a service master key protects database master keys, which in turn protects both certificates and asymmetric keys. Symmetric keys can be protected by certificates, asymmetric keys, or other symmetric keys (as shown by the arrow that points back at itself). And you can take responsibility for protecting the key secrets of any of these keys in the database by supplying a strong password.

The root of the key hierarchy (shown, as is usual in such diagrams, at the top) is the Service Master Key. This is created and installed automatically when you install a new instance of SQL Server. You cannot eliminate this key, but administrators can do basic maintenance tasks such as back it up to an encrypted file, regenerate it if it is ever compromised, and restore it. (Regenerating a service master key is not something you should take lightly or do often. It could require decrypting and re-encrypting all the encrypted data in the database, a painfully slow and processor-intensive process.)

The service master key is managed by DPAPI, the Data Protection API, which was introduced in Windows 2000. Built on the Crypt32 API in Windows, it strongly encrypts keys and stores them safely. SQL Server 2005 manages the interface with DPAPI for you, so you don't need to worry about it at all.

The service master key is a symmetric key and is used to encrypt any database master keys on the server. Unlike the service master key, you have to explicitly create a database master key before encrypting data in the database. Normally you'll supply a password when you create this key so that it is encrypted with both the service master key and the password. This allows you to explicitly open the key if necessary, but can also have it opened automatically, assuming you have the credentials to do so. Here's the T-SQL code for creating a database master key.


There are various ALTER MASTER KEY statements for dropping encryption by the service master key or the password, changing the password, or dropping the database master key entirely. Normally you won't have to worry about those options, or at least you can let your favorite DBA handle it. You can only have one database master key per database.

Once you have the database master key, you're ready to start encrypting data. T-SQL has features built into it that support encryption, such as CREATE statements to create the various keys and ALTER statements to modify them. The actual encryption is performed with new paired sets of functions such as EncryptByKey and DecryptByKey for symmetric key encryption.

I suspect that a best practice will eventually emerge that you should use either EXECUTE AS 'user' or EXECUTE AS OWNER, but time will tell if such a recommendation emerges.
Let's look at an example of using encryption. I'll use symmetric key encryption here because I think that will be the most common form of encryption that is written to the database and read from it in clear text. Imagine that you have a Customer table with a few typical fields: customer ID, name, city, and various credit card details. The credit card details should be encrypted but the other data doesn't need to be. Imagine also that User1 owns the symmetric key and that the login causing this code to run has the required permissions to encrypt data using this key.

Start by creating a symmetric key in the database containing the Customers table, using Triple DES as the encryption algorithm. In this case, the key is itself protected by a certificate that already exists in the database. Referring back to Figure 1, symmetric keys can also be protected by asymmetric keys and existing symmetric keys.


A symmetric key must be explicitly opened before use. This step retrieves the key, decrypts it, and places it in protected server memory, ready for use.


Finally you're ready to encrypt the data. In the code below, I use a regular T-SQL INSERT statement to put a row of data in the table. The id, name, and city are saved as clear text but the credit card type and number, as well as some potentially confidential notes about the customer, are stored in encrypted form, using the strong Triple DES encryption algorithm specified when the key was created.

INSERT INTO Customer VALUES (4, 'John Doe', 'Fairbanks', EncryptByKey(Key_GUID( 'User1SymmetricKeyCert'), 'Amex'), EncryptByKey(Key_GUID( 'User1SymmetricKeyCert'), '1234-5678-9009-8765'), EncryptByKey(Key_GUID( 'User1SymmetricKeyCert'), 'Window shopper. Spends $5 at most.'))

When you're done with the symmetric key, close it to release the memory and prevent it from being misused.


Figure 2. SELECTing Encrypted Data: The figure shows the result of running a regular SELECT statement on a table with encrypted data.
That's all there is to it! No messy key management, no complicated calls to algorithm-specific methods. Doing a normal SELECT * on the table produces the results shown in Figure 2. The fields that store encrypted data are varbinary types of a length sufficient to hold the expanded data (cipher text takes up more room than clear text, sometimes much more).

To read the data as clear text, you need to re-open the symmetric key (if you've closed it), use the DecryptByKey function to read the text, and close the symmetric key. Figure 3 shows the resultset.

OPEN SYMMETRIC KEY User1SymmetricKeyCert

Figure 3. Using DecryptByKey: The figure shows the results of running a SELECT statement against encrypted data, using the DecryptByKey function.

DECRYPTION BY CERTIFICATE User1Certificate SELECT CustID, Name, City, CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CardType, CONVERT(VARCHAR, DecryptByKey(CreditCardNumber)) AS CardNumber, CONVERT(VARCHAR, DecryptByKey(Notes)) AS Notes FROM Customer WHERE CustID = 4 CLOSE SYMMETRIC KEY User1SymmetricKeyCert

This example shows one of many ways to let SQL Server 2005 manage encryption keys for you. But you virtually always have the option of taking on the chore yourself by supplying a strong password. So you could have instead created a symmetric key that uses the RC4 encryption algorithm.


SQL Server creates a key based on the password you supply and encrypts the data. Just be sure to keep the password a secure secret, otherwise anyone can decrypt the data. It is not stored within SQL Server unless you explicitly store it.

Data encryption in SQL Server 2005 is a marvelous feature; one that provides a significant layer of defense that protects your data. But don't get carried away! Protect only the data that is sensitive or confidential enough to require this level of protection, or that you have a statutory requirement to encrypt. Encryption makes incredible demands on the processing power of a server, so encrypting every field in every row in a 10 million row table and then doing a SELECT without a WHERE clause is likely to bring the server to its knees. But it allows some interesting security options, such as code signing that allows access to resources only through code such as a stored procedure.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date