Let’s face it: data that is stored in
databases is interesting not only to us as users but to many others in
the world whom we might not know. With the increased leverage of the
power of relational databases by businesses and consumers, database
vendors are under increasing pressure to provide more security-related
features.
Other than locking down access to databases such
as SQL Server, administrators and developers can provide another layer
of protection against the bad guys, and that is encryption. At a high
level, encryption takes the interesting information, such as your Social
Security number, and translates it into a binary representation that
can be understood only by authorized parties. Data can be encrypted for
use in transit, such as when you are passing your password back to a Web
server, or it can be stored in an encrypted format (on the file system
or in a database, for example).
Data in SQL Server 2005 is encrypted by using the concept of encryption keys.
These keys can be either symmetric or asymmetric, and there are pros
and cons to using either one. In symmetric key encryption, both the
sender and receiver of the data have the same key. The sender encrypts
the data using the key and an encryption algorithm. When the data
reaches the recipient, it is decrypted using the same encryption
algorithm and key. The main benefit of this approach is the better
performance of the encryption and decryption compared to using
asymmetric keys. The problems with symmetric key encryption come into
play when we consider what happens when someone else somehow gets a hold
of our symmetric key. Because we are encrypting with just one key,
anyone who has that key can decrypt the data.
Asymmetric key encryption uses two keys, a public
key and a private key. The sender encrypts the data using the
recipient’s public key, which is freely obtainable by anyone. The
security comes in when the recipient receives the data; the recipient
decrypts it via her private key. The public key in this case cannot
decrypt the data. Thus, the private key is the valuable asset when using
asymmetric encryption.
The other concept that pops up in encryption
discussions is certificates. Certificates are basically asymmetric keys
that contain extra metadata. This metadata includes information such as
an expiration period and the certificate authority that issued the
certificate. Certificates can be created by anyone, and in some
circumstances you want to make sure the sender or recipient of the data
is who he says he is. This is where certificate authorities come into
play. These companies act as a middleman between the sender and
receiver. After you pay a nominal fee and they conduct an identity
check, they can provide you with a certificate that they have signed.
Now when you use this signed certificate to send data, the recipient can
validate the certificate with the certificate authority, and because
both of you trust the certificate authority, odds are the message was in
fact signed by the sender.
There is another type of certificate call a self-signed certificate,
which anyone can create. In some cases, it is acceptable to use a
self-signed certificate. SQL Server 2005 automatically creates a
self-signed certificate the first time it starts. This certificate is
used to encrypt the connection during SQL Server authentication.
Encrypting Data on the Move
All connection login requests made to SQL Server
2005 are encrypted if the client is using the SQL Server Native Access
Client APIs. This is a huge step forward because using previous
versions, if a user wants to authenticate using SQL Server
Authentication, the username and password are basically sent in clear
text across the wire. SQL Server 2005 can automatically encrypt the
login packet information via the self-signed certificate it created the
first time the service started.
Login packets are not all that’s encrypted in
the connection. The whole connection itself can optionally be encrypted
for the lifetime of the connection. The request for an encrypted channel
can be forced by the server (so that all connections are encrypted by
default) or requested by the client making the connection. It is
recommended that administrators use a real certificate rather than the
self-signed certification because of potential “man-in-the-middle”
attacks.
To force encryption on the server, launch the
SQL Server Configuration Manager tool (available in the Configuration
Tools folder of the SQL Server 2005 Program Files menu item). This tool
is used for managing the protocols and services of SQL Server. Select
Properties from the Protocols for MSSQLSERVER shortcut menu to launch
the Protocol Properties dialog box (Figure 1).
The
dialog box has two tabs. The Flags tab allows you to force encryption
on the server. The Certificate tab allows you to select a certificate
that is already installed on the local machine to be used by SQL Server
to encrypt the data. If you do not select a certificate and choose to
force encryption, SQL Server uses its self-signed certificate to encrypt
the data. Remember that SQL Server’s self-signed certificate is not
considered trusted by the client connections. For clients to be able to
use SQL Server’s self-signed certificate, they must set this option in
the Properties dialog box of the SQL Native Client Configuration node in
the same Configuration Manager tool. This dialog box is shown in Figure 2.
This dialog box offers two options. The Force
Protocol Encryption option forces clients to always make encryption
connections. The second option is what needs to be set if the client
wants to make encryption connections to SQL Server and have it leverage
SQL Server’s self-signed certificate.
Encrypting Data at Rest
Social Security numbers, credit card numbers,
salary information—the list of sensitive information just keeps going.
Access to this information stored in the database has traditionally been
secured by permissions. This is still the case, but another layer of
protection is now available natively in SQL Server 2005. Sensitive data
can now be encrypted using symmetric keys, asymmetric keys, or
certificates.
Before we jump in and encrypt data in a column,
it is important to define the building blocks of encryption support
within SQL Server. The first time SQL Server starts, it creates a
special symmetric key called the Service Master Key (SMK). This key is
used to encrypt
all database master keys (DMKs) as well as all server-level secrets
such as credential secrets or linked server login passwords. The key
itself is a 128-bit 3DES key. The 3DES algorithm is used because of its
availability on all Windows platforms supported by SQL Server 2005.
Note
Encryption algorithm
availability depends on the cryptographic service provider of the
operating system that SQL Server 2005 is running on. For example,
Windows XP SP2 supports DES, 3DES, RC2, RC4, and RSA, while Windows
Server 2003 supports all those plus AES128, AES192, and AES256. |
The SMK is encrypted using the Windows security
API, DPAPI, and the credentials of the SQL Server service account.
Because the SMK is used to encrypt all DMKs and other server-level
secrets, it is very important and should be regularly backed up. You can
back up and restore the SMK by using the BACKUP SERVICE MASTER KEY or RESTORE SERVICE MASTER KEY
DDL. In the event of a compromise of the SMK, or if you want to change
the SMK as part of implementing a normal security best practice, you can
regenerate it using the ALTER SERVICE MASTER KEY REGENERATE DDL statement.
With respect to encrypting data, the SMK is used
by SQL Server to decrypt the DMK so that the DMK can in turn decrypt
the requested data for the client. There is only one DMK per database,
and none are created by default because a DMK is used only for data
encryption.
To create a DMK, we can use the new DDL statement as follows:
USE <<insert user-defined database>>
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '<<insert favorite password here>>'
When DMKs are created, they are encrypted by the
SMK (so SQL Server can decrypt the data for the client) and by a
password. It is possible to remove the SMK encryption, but the password
of the DMK would need to be specified by the user every time she
accesses this key.
Now that we have discussed the key components of
encryption, let’s consider an example. This example will include a
table called SalaryInfo that contains the name, department, and salary
of employees at our company. There is a user HR_User that needs to be
able to insert and view data into this table.
Because a lot of steps are involved in setting up this example, it is best to walk through it line by line. (See Listing 1.) We have included in-line comments throughout the example that explain exactly what each T-SQL statement does and why.
Listing 1. Encryption_HR.sql
CREATE LOGIN HR_Login WITH PASSWORD='SomeComplexPassword'
GO
CREATE DATABASE ExampleDB
GO
use ExampleDB
GO
CREATE USER HR_User FOR LOGIN HR_Login
GO
--Create the database master key for the ExampleDB database
CREATE MASTER KEY ENCRYPTION BY PASSWORD='AComplexPassword'
GO
--Create the table that will store sensitive information
--Notice we use a varbinary for our salary information
--This is because the ciphertext (encrypted data) is binary
CREATE TABLE SalaryInfo
(employee nvarchar(50),
department nvarchar(50),
salary varbinary(60))
GO
--Give access to this table to HR_User so they can add data
GRANT SELECT,INSERT TO HR_User
GO
--Create a Symmetric Key
--Encrypt the key with a password
--Give access to the key to HR_User
CREATE SYMMETRIC KEY HR_User_Key
AUTHORIZATION HR_User
WITH ALGORITHM=TRIPLE_DES
ENCRYPTION BY PASSWORD='CompensationPlansRule'
GO
--Now, let's login as HR_User and encrypt some data
EXECUTE AS LOGIN='HR_Login'
GO
--First, we need to open the key that will be used to encrypt data
--Notice we have to pass the password for the key
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY PASSWORD='CompensationPlansRule'
GO
--This system view shows open keys that can be used for encryption
select * from sys.openkeys
--Insert sensitive data into the table
--encryptByKey takes the GUID of the key and the text of the data
--Since remembering GUIDs is not easy, Key_GUID is a function
--that does the lookup for us
INSERT INTO SalaryInfo VALUES
('Bryan','Sales',encryptByKey(Key_GUID('HR_User_Key'),'125000'))
INSERT INTO SalaryInfo VALUES
('Tammie','Sales',encryptByKey(Key_GUID('HR_User_Key'),'122000'))
INSERT INTO SalaryInfo VALUES
('Frank','Development',encryptByKey(Key_GUID('HR_User_Key'),'97500'))
INSERT INTO SalaryInfo VALUES
('Fran','Marketing',encryptByKey(Key_GUID('HR_User_Key'),'99500'))
--When we are done, always close all keys
CLOSE ALL SYMMETRIC KEYS
GO
--View the table as it lives in the database, notice the binary
select * from SalaryInfo
--Now, let's decrypt and view the contents
--We use decryptByKey and pass the column name
--We don't have to specify a key GUID because SQL will look
--at all your open keys and use the appropriate one automatically
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY PASSWORD='CompensationPlansRule'
GO
SELECT employee,department,
CONVERT(varchar,decryptByKey(salary))
FROM SalaryInfo
GO
CLOSE ALL SYMMETRIC KEYS
GO
--Revert back to sysadmin
REVERT
GO
--When encrypting by password, need to know the password
--and pass it every time you encrypt something.
--Alternatively you can create a certificate and give access to
--the HR User. With this, the user doesn't have to provide a password
--and you can easily revoke access to that encrypted data by simply
--removing the certificate for that user
CREATE CERTIFICATE HRCert1
AUTHORIZATION HR_User
WITH SUBJECT='Certificate used by the Human Resources person'
--Open the key so we can modify it
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY PASSWORD='CompensationPlansRule'
GO
--We cannot remove the password because we would leave the key
--exposed without encryption so we need to add the certificate first
ALTER SYMMETRIC KEY HR_User_Key
ADD ENCRYPTION BY CERTIFICATE HRCert1
GO
--Now we can remove the password encryption from the key
ALTER SYMMETRIC KEY HR_User_Key
DROP ENCRYPTION BY PASSWORD= 'CompensationPlansRule'
GO
CLOSE ALL SYMMETRIC KEYS
GO
--Now change context to HR_Login to test our changes
EXECUTE AS LOGIN='HR_Login'
GO
--Notice we opened the key without a password!
--This is because we created the certificate and gave authorization
--on it explicitly to HR_User
OPEN SYMMETRIC KEY HR_User_Key DECRYPTION BY CERTIFICATE HRCert1
GO
SELECT employee,department,
CONVERT(varchar,decryptByKey(salary))
FROM SalaryInfo
GO
|
The
preceding example described the basics of encrypting and decrypting in
SQL Server 2005. The amount of data in this example is trivial to
encrypt and decrypt, even for my three-year-old laptop. Performance for
encryption depends on two factors other than how big of a box you are
running on: size of the data to encrypt and the algorithm you use to
encrypt the data. If you use RSA2048 to encrypt a larger file, this
might take a bit longer than encrypting a Social Security number. It is
difficult to give a nice graph of size versus time because it depends on
so many factors. The best thing to do is set up a test environment that
mimics your production environment and run some performance tests:
encrypt and decrypt various data sizes in different algorithms or at
least the algorithm you are planning to use.
Another interesting issue with encrypted columns
is indexing and searching. To SQL Server itself, these are binary
columns so there is no effective way to create an index on them because
we cannot predict a random stream of bytes. The best thing to do in this
case is to create or use another unencrypted column to index on. The
problem with this is that you might inadvertently give information about
the data that is encrypted. Imagine we want to index on salaries and
create a column called range. Anyone who has SELECT
permission on the table can guess what the employee makes. If you must
index or search encrypted data, be creative about your unencrypted
columns.
