programming4us
programming4us
SECURITY

SQL Server 2005 Data Protection

10/2/2010 7:14:55 PM
SQL Server 2005 introduces various methods to protect your data. As discussed throughout this chapter, data encryption uses a key to create nondeterministic ciphertext (encrypted data), which can only be decrypted with the appropriate encryption key. Before we look at key-based data encryption, we will examine the HashBytes() function, which returns a deterministic representation of the input data.

HashBytes()

The HashBytes() function, new in SQL Server 2005, allows you to transform data passed into the function, but you cannot recover the original value . This function is suitable for when you need the identifying nature of the data, but the actual value does not matter. Consider password authentication or user and customer validation; these situations require that a user submit a secret identification value, but the actual value does not need to be recovered from the database in the future. In other words, you will not need to "unhash" the data.

The HashBytes() function in action

The HashBytes() function will support the MD2, MD4, MD5, SHA, and SHA1 algorithms. In most cases, you will use the SHA1 algorithm.

SELECT HashBytes('SHA1', 'ClearText')
GO

NOTE

Hashing data applies a case-sensitive algorithm to deterministically transform your data into a new series of characters. It is useful for authentication, but limited in function, as you cannot later recover the plaintext value. The standard algorithms in SQL Server 2005 are theoretically vulnerable to collisions, where two distinct values passed to the same algorithm will produce the same hash output.

The beauty of this form of data protection is that it is deterministic, which is also its shortcoming. When you hash the same text using the same hash algorithm, the result will always be the same. This is very important when you want to execute equality searches on the data. You can add an index to a column that has been hashed and be confident that the database engine will properly use this index to improve the performance of queries that filter based on this data.

The deterministic output of the hash is also the greatest risk to the function. Bad guys can build a dictionary of hash results for different hash algorithms and attempt reverse lookups against your data. This poses a real threat to sensitive data. Typically, a salt is combined with the plaintext data to strengthen the hash and protect against these dictionary attacks.

Asymmetric Key and Certificate Encryption

Both certificate encryption and asymmetric encryption embody a pair of keys. Data encryption is achieved with a single public key, and decryption is achieved with a separate, mathematically related private key . Certificate encryption and asymmetric encryption are very slow—significantly slower than symmetric key encryption. As a rule, certificate encryption or asymmetric encryption is only recommended when you need to encrypt and decrypt one or two records at a time. As noted in the next section, asymmetric keys and certificates are ideal for protecting symmetric keys.

Asymmetric encryption uses public keys on one end and private keys on the other.

A certificate is typically a CER file that is created by an outside certification authority and imported into SQL Server. You may generate self-signed certificates in SQL Server, but these are limited with a private key length of 1024 bits. SQL Server supports private key lengths between 384 and 3456 bits on certificates that are imported from external sources. Longer private key lengths will strengthen the encryption, but will also impact the performance of decrypting and encrypting data.

An asymmetric key is also a container for a pair of keys—a public key and a private key. As with a certificate, you can import an asymmetric key from an external source. When you create an asymmetric key within SQL Server 2005, you have the option of specifying a private key length of 512, 1024, or 2048 bits.

NOTE

Both certificate and asymmetric encryption use the RSA algorithm to protect the data. Assuming equal key length, certificate encryption and asymmetric encryption will deliver the same encryption strength.

The choice between a certificate and an asymmetric key is typically one of culture. If your environment uses certificates from external certification authorities, you will probably prefer to use certificates in your SQL Server encryption implementation. If you are generating your keys in SQL Server, you may prefer to use asymmetric keys when you would like to use longer private key lengths to strengthen your encryption.

The private keys of asymmetric and certificate key pairs are protected with either the database master key or a password. By default, the private key is protected by the database master key—if you have created one. If the database master key was not created, a password is required when you create or import a certificate or asymmetric key.

If your principal concern is to protect your database and backup files in the event they are stolen, the default nature that protects the private key with the database master key will suffice. If you are comfortable that the individuals in the db_owner database role have access to your database master key and all dependent private keys, this default private key protection is sufficient.

In some extreme implementations, you may want to secure your data from the db_owner. If so, you will protect your private key with a password. All of the same aforementioned cautions apply where you must take care in how you are supplying the password from the application to prevent the db_owner from viewing the passwords from Profiler or directly in stored procedures.

The following example will create a self-signed certificate and an asymmetric key in SQL Server 2005:

CREATE CERTIFICATE MyCert
WITH Subject = 'My Person Cert'
GO

CREATE ASYMMETRIC KEY AsymKeyPerson
WITH Algorithm = RSA_1024
GO

Permissions defined on the certificate and asymmetric key will determine whether the user has access to the private key. If you would like to grant a user permission to encrypt data, but not decrypt data, the user is granted VIEW DEFINITION permission on the securable and will only have access to the public key. If the user is allowed to decrypt and encrypt data, the user is granted CONTROL permission on the securable and has access to both the public and the private key. The current permissions assume that any user who has access to decrypt data may also encrypt data. Permissions may not be set to allow a user to only decrypt data, not encrypt data.

In the following example, Pam may access the public key to encrypt data using the AsymKeyPerson asymmetric key. Tom is able to access the private key and decrypt data that was encrypted with the AsymKeyPerson key.

GRANT VIEW DEFINITION ON ASYMMETRIC KEY AsymKeyPerson TO Pam
GO

GRANT CONTROL ON ASYMMETRIC KEY AsymKeyPerson TO Tom
GO

SQL Server 2005 has introduced the EncryptByAsymKey() and EncryptByCert() functions to encrypt your data. The following example will encrypt the SocialSecurityNumber data from a table named Source using an asymmetric key named MyAsymKey and insert the encrypted data into the Person table:

INSERT INTO Person
(
SocialSecurityNumber
)
SELECT
EncryptByAsymKey(AsymKey_ID('MyAsymKey'), SocialSecurityNumber)
FROM Source
GO

The DecryptByAsymKey() and DecryptByCert() functions will return the varbinary representation of your decrypted data. You may need to convert the output to a meaningful format for your application. When decrypting the data, the DecryptByAsymKey() function is applied to the example as follows:

SELECT
CONVERT(
NVARCHAR(9),
DecryptByAsymKey(
AsymKey_ID('MyAsymKey'),
SocialSecurityNumber))
FROM Person
GO

Asymmetric encryption is stronger than symmetric encryption, but performance will make it unsuitable for most data stored in tables. Asymmetric encryption is recommended for protecting symmetric keys and configuration values stored in tables. Avoid using asymmetric encryption on any column that requires filter activity on the encrypted data or decryption of more than one or two records at a time. These data requirements will perform better when encrypted with a symmetric key.

Symmetric Key Encryption

The nature of symmetric encryption uses a single key to encrypt and decrypt the data. Symmetric encryption is significantly faster than asymmetric encryption, and it is the recommended method for encrypting data stored in user tables. In fact, it has been my experience that decrypting data that is encrypted with a symmetric key can be more than a thousand times faster than asymmetric encryption. Because of the performance differences, asymmetric keys are typically used to protect the symmetric keys that are used to encrypt data.

Symmetric encryption uses the same password for both encryption and decryption.

SQL Server 2005 symmetric encryption supports a number of algorithms. The recommended algorithms in order of key length and encryption strength are TRIPLE_DES, AES_128, AES_192, and AES_256. TRIPLE_DES is the strongest algorithm you may choose if you are running SQL Server 2005 on Windows XP. Microsoft does not recommend the RC4 or RC128 stream ciphers, as these are not salted, which results in a much weaker encryption implementation.[]

[] See the SQL Server 2005 Books Online topic "CREATE SYMMETRIC KEY (Transact-SQL)" for Microsoft's recommended algorithms.

Symmetric keys are protected by one or more of the following methods: asymmetric keys, certificates, passphrases/passwords, or other symmetric keys.

Encrypting your symmetric key with a password will generate a TRIPLE-DES key to secure your symmetric key. If you are using a stronger encryption algorithm, such as AES_256, your symmetric key is protected with a weaker algorithm. This may not be an acceptable level of protection in your business and should be considered prior to adopting this method of protection.

When you protect your symmetric key using a certificate or asymmetric key, as in Figure 5-4, you are implementing what is commonly known as the hybrid approach to encryption. This is the recommended approach to blend the strength of asymmetric encryption with the speed of symmetric encryption. You are ensured a stronger method of securing your symmetric key, while benefiting from the enhanced performance of symmetric encryption.

NOTE

While asymmetric keys and certificates can use only one form of protection, symmetric keys may use multiple forms of protection. If multiple forms of protection are applied to a symmetric key, you need to use only one form to open the key. A single symmetric key may be used between multiple applications, and each application will have its own password, certification, or asymmetric key to open the symmetric key.

The following example creates a symmetric key that is protected by a certificate. User Jack must have CONTROL permission on the certificate, so that the symmetric key may be decrypted. The user must also have VIEW DEFINITION permission on the symmetric key to encrypt and decrypt data using the symmetric key. Since this is a single key used for both encryption and decryption, you will not specify a different set of permissions to control each of these activities.

CREATE SYMMETRIC KEY MySymKey WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE MyCert
GO

GRANT CONTROL ON CERTIFICATE MyCert TO Jack
GO

GRANT VIEW DEFINITION ON SYMMETRIC KEY MySymKey TO Jack
GO

If you want to encrypt data with a symmetric key, you must first open—or decrypt—the key. Once the key is open, the EncryptByKey() function is used to encrypt to your data. When you are done using the symmetric key, it is a good practice to explicitly close the key—although the key is closed when the session is terminated.

OPEN SYMMETRIC KEY MySymKey
DECRYPTION BY CERTIFICATE MyCert;
GO

INSERT INTO Person
(
SocialSecurityNumber
)
SELECT
EncryptByKey(
Key_GUID('MySymKey'),
SocialSecurityNumber)
FROM Source
GO

CLOSE SYMMETRIC KEY MySymKey;
GO

Depending on how you protected your symmetric key, you will use one of a few new functions exposed by SQL Server 2005 to support data decryption. The first we will discuss is the DecryptByKey() function, which requires that you explicitly open your key. You may then pass in the Key_GUID of your symmetric key and the data to decrypt.

OPEN SYMMETRIC KEY MySymKey
DECRYPTION BY CERTIFICATE MyCert;
GO

SELECT
DecryptByKey(
Key_GUID('MySymKey'),
SocialSecurityNumber)
FROM Person
GO

CLOSE SYMMETRIC KEY MySymKey;
GO

Alternatively, if your symmetric key is protected with a certificate or asymmetric key, you can use the DecryptByKeyAutoCert() or DecryptByKeyAutoAsymKey() function to decrypt your data in a single operation. When the functions are executed, SQL Server will automatically open the symmetric key associated with the encrypted data if the user has the appropriate permission.

These functions are useful when you decrypt data from a single column where the data has been encrypted with multiple different symmetric keys. For example, we have a single table, PersonID, to store Social Security numbers and birthdates. We will create a single certificate and two separate symmetric keys that are protected with our single certificate.

CREATE TABLE PersonID
(
PersonID INT IDENTITY(1,1),
SocialSecurityNumber VARBINARY(100)
)
GO

CREATE CERTIFICATE CertPerson
ENCRYPTION BY PASSWORD = 'pJBp4bb92548d243Ll12'
WITH Subject = 'Person Cert',
Expiry_Date = '01/01/2009'
GO

CREATE SYMMETRIC KEY SymKeyPerson_1
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertPerson
GO

CREATE SYMMETRIC KEY SymKeyPerson_2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE CertPerson
GO

When we insert data, we specify a different symmetric key for each row. This may simulate an environment where a single table is used across multiple departments, and varying access is controlled at the row level by symmetric keys.

OPEN SYMMETRIC KEY SymKeyPerson_1
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
GO
INSERT INTO PersonID
SELECT
EncryptByKey(
Key_GUID('SymKeyPerson_1'),
N'111111111')
GO
CLOSE SYMMETRIC KEY SymKeyPerson_1;
GO

OPEN SYMMETRIC KEY SymKeyPerson_2
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
GO

INSERT INTO PersonID
SELECT
EncryptByKey(

Key_GUID('SymKeyPerson_2'),
N'222222222')
GO

CLOSE SYMMETRIC KEY SymKeyPerson_2;
GO

When executed, the DecryptByKeyAutoCert() function will automatically open the associated symmetric keys, decrypt the data, and close the symmetric keys. Because each symmetric key is protected with the same certificate, and we have permission to open all of the associated keys, we can use the DecryptByKeyAutoCert() function to view all of the data in a single statement.

SELECT
PersonID,
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert(
Cert_ID('CertPerson'),
N'pJBp4bb92548d243Ll12',
SocialSecurityNumber)
) AS SocialSecurityNumber
FROM PersonID;

The results of the preceding example :

DecryptByKeyAutoCert()used to decrypt Social Security numbers

The user must have CONTROL permission on the certificate to open the certificate and VIEW DEFINITION on the associated symmetric keys. If the user does not have VIEW DEFINITION on one of the symmetric keys, the data is not returned for those rows that depend on that symmetric key.

This is also an ideal method if you are interested in building a view to return decrypted data to the user. A user will be granted SELECT permission on the view, and must also have the aforementioned permissions on the certificate and symmetric key to view the encrypted data. If the user is not granted permissions on the certificates and symmetric keys, the view will return NULL values.

CREATE VIEW vw_Person
AS
SELECT
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert (

Cert_ID('CertPerson'),
N'pJBp4bb92548d243Ll12',
SocialSecurityNumber)
) AS SocialSecurityNumber
FROM PersonID
GO

You can also use encryption to control which users can access data at the column level. The following example implements a solution to secure data by column for different sets of users.

NOTE

This example is not limited to symmetric encryption. You can control security to each column by encrypting the data with a certificate or an asymmetric key as well. As discussed earlier, certificate encryption and asymmetric encryption are not recommended methods of encrypting data in tables due to performance. If your business is willing to accept the significant performance difference and absolutely requires that data be encrypted with asymmetric keys or certificates, the following example may be used to secure data in a single table for different sets of users.

We will work with our PersonID table, but add another column to store BirthDate. We will use the SymKeyPerson_1 key to encrypt the SocialSecurityNumberSymKeyPerson_2 key to encrypt the new BirthDate column. column and the

IF  EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE
TABLE_NAME = 'PersonID'
)
DROP TABLE PersonID
GO

CREATE TABLE PersonID
(
PersonID INT IDENTITY(1,1),
SocialSecurityNumber VARBINARY(100),
BirthDate VARBINARY(100)
)
GO

Create two users, CustomerService and HR_User. The SocialSecurityNumber column is encrypted with the SymKeyPerson_1 key, and the BirthDate column is encrypted with the SymKeyPerson_2 key. HR_User has access to all of the data in the table and is granted VIEW DEFINITION on both keys. CustomerServiceBirthDate column and is granted VIEW DEFINITION on the SymKeyPeron_2 symmetric key. Grant these users CONTROLSELECT permission on the table. will only have access to the permission on the certificate and

CREATE USER HR_User WITHOUT LOGIN
CREATE USER CustomerService WITHOUT LOGIN
GO

GRANT CONTROL ON CERTIFICATE::CertPerson TO CustomerService, HR_User
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKeyPerson_1 TO HR_User
GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymKeyPerson_2
TO CustomerService, HR_User
GRANT SELECT ON PersonID TO CustomerService, HR_User
GO

Insert two rows, encrypting each column with a different symmetric key:

OPEN SYMMETRIC KEY SymKeyPerson_1
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
OPEN SYMMETRIC KEY SymKeyPerson_2
DECRYPTION BY CERTIFICATE CertPerson
WITH PASSWORD = 'pJBp4bb92548d243Ll12';
GO

INSERT INTO PersonID
SELECT
EncryptByKey(Key_GUID('SymKeyPerson_1'), N'111111111'),
EncryptByKey(Key_GUID('SymKeyPerson_2'), N'02/02/1977')
GO

INSERT INTO PersonID
SELECT
EncryptByKey(Key_GUID('SymKeyPerson_1'), N'222222222'),
EncryptByKey(Key_GUID('SymKeyPerson_2'), N'01/01/1967')
GO

CLOSE SYMMETRIC KEY SymKeyPerson_1;
CLOSE SYMMETRIC KEY SymKeyPerson_2;
GO

Using the new CustomerService login, execute a SELECT statement to decrypt our two columns. CustomerService has access to the SymKeyPerson_2 symmetric key and is able to see the data in the BirthDate column. The data in the SocialSecurityNumber column returns NULL values when CustomerService executes the statement. Data is available from both columns when executed as the HR_User. Notice that although the columns are using different symmetric keys, the DecryptByKeyAutoCert() is used to decrypt each column. A single set of code may be deployed to your application, and access to the data is controlled through your encryption keys.

EXECUTE AS USER = 'CustomerService'
GO

SELECT
PersonID,
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert (
Cert_ID('CertPerson') ,
N'pJBp4bb92548d243Ll12',
SocialSecurityNumber)
) AS SocialSecurityNumber,
CONVERT(
NVARCHAR(9),
DecryptByKeyAutoCert (
Cert_ID('CertPerson') ,
N'pJBp4bb92548d243Ll12',
BirthDate)
) AS BirthDate
FROM PersonID;
GO

REVERT
GO


Symmetric encryption and decryption is much better performing than asymmetric key or certificate encryption. Even with this better-performing type of encryption, searching a large set of encrypted data is still an issue. The results of encrypting data with a symmetric key are nondeterministic, which renders indexes less useful when searching the data. Applying filters on your encrypted data will require decrypting all data prior to applying the filter. This will result in an index or table scan, which can negatively affect server resources while seriously impacting the scalability and availability of your data for concurrent user activity.

EncryptByPassphrase

If ease of use is the only factor in determining which method of encryption to use, EncryptByPassphrase() is worth considering. It may not be the best method based on most other criteria, as I will discuss in this section.

Encrypting data with a passphrase is incredibly easy to implement. This form of encryption does not require creating or securing certificates or keys, as a symmetric key is generated at the time of encryption and stored with the encrypted data. In fact, the Triple-DES symmetric key derived by the passphrase is not stored in any system table; it is not directly accessible by anyone.

To encrypt data using a passphrase, pass the clear text followed by a passphrase into the EncryptByPassphrase() function:

EncryptByPassPhrase(@PassphraseEnteredByUser, 'ClearText')

Data is decrypted by passing the passphrase and the encrypted text into the DecryptByPassphrase() function. As with asymmetric and symmetric encryption, the decrypted data is returned in varbinary and may require that you convert it to a useful format for your application.

DecryptByPassphrase(@PassphraseEnteredByUser, 'EncryptedText')

Now I will address the drawbacks to using this form of encryption. First, the encrypted data can be decrypted only when passed the correct passphrase—there are no other options for decrypting the data, which introduces a level of risk. If the passphrase is lost, the data is lost. Securely storing the passphrase is vitally important to protecting your ability to decrypt the data.

EncryptByPassphrase() does not provide an option for using stronger keys, as with other forms of encryption. The EncryptByPassphrase() function will only use the Triple-DES symmetric key algorithm. SQL Server 2005 symmetric encryption provides stronger encryption keys, like AES 128, AES 192, and AES 256. Your business requirements and service-level agreements may require that the data be protected with stronger encryption algorithms with larger keys.

The EncryptByPassphrase() function in SQL Server 2005 will not enforce passphrase complexity. The SQL Server 2005 symmetric key and asymmetric key/certificate encryption may be protected by a password, and the CREATE process will check for password complexity if required on your server. If your business requires or may someday require password complexity, and you will be encrypting data using a passphrase, you will need to develop a passphrase validation routine that will check the passphrase prior to using it to encrypt the data.

Finally, while this form of encryption appears to protect the data from individuals in the sysadmin fixed server role and db_owner database role, your implementation may prove that this may not be the case without significant changes to the application, as I will discuss in the next section.

Other  
 
 
Video tutorials
- How To Install Windows 8

- How To Install Windows Server 2012

- How To Install Windows Server 2012 On VirtualBox

- How To Disable Windows 8 Metro UI

- How To Install Windows Store Apps From Windows 8 Classic Desktop

- How To Disable Windows Update in Windows 8

- How To Disable Windows 8 Metro UI

- How To Add Widgets To Windows 8 Lock Screen

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010
programming4us programming4us
programming4us
 
 
programming4us