The SQL Server encryption model includes built-in encryption key management patterned after the ANSI X9.17 standard. This standard defines several layers of encryption keys, which are used to encrypt other keys, which in turn are used to encrypt actual data. The layers of encryption keys defined by the ANSI X9.17 standard and the roughly analogous layers defined by SQL Server are listed in Table.
SQL Server and ANSI X9.17 Encryption Key Layers Comparison
The service master key (SMK) is the top-level key, the granddaddy of all SQL Server keys. There is a single SMK defined for each instance of SQL Server 2008. The SMK is secured by the Windows Data Protection API (DPAPI), and it is used to encrypt the next layer of keys, the database master keys (DMKs). The SMK is automatically created by SQL Server the first time it is needed. DMKs are used to encrypt symmetric keys, asymmetric keys, and certificates. Each database can have a single DMK defined for it.
The next layer of keys includes symmetric keys, asymmetric keys, and certificates. Symmetric keys are the primary means of encrypting data in the database. While asymmetric keys and certificates can be used to encrypt data (with some work on your part, which we will discuss in the “Asymmetric Keys” section later in this chapter), Microsoft recommends that you encrypt data exclusively with symmetric keys.
In addition to all the keys and certificates previously supported by SQL Server 2005, SQL Server 2008 introduces the concept of server certificates and database encryption keys in support of the new transparent data encryption functionality. The server certificate is simply a certificate created in the master database. The database encryption key is a special symmetric key used to encrypt an entire database at once. We’ll discuss the server certificate and database encryption key in detail in the “Transparent Data Encryption” section later in this chapter.
SQL Server encryption key hierarchy
Service Master Key
SQL Server 2008 includes the following T-SQL statements to alter, back up, and drop SMKs:
After installing a new SQL Server 2008 instance, you should immediately back up the SMK and store it in a safe location. The BACKUP SERVICE MASTER KEY statement takes the following form:BACKUP SERVICE MASTER KEY TO FILE = 'c:MKackup_master_key.dat' ENCRYPTION BY PASSWORD = 'p@$$w0rD';
In this example, the SMK is backed up to the file c:MKackup_master_key.dat, and it is encrypted with the password p@$$w0rD. The encryption password is required if you need to restore the SMK. If you need to alter, restore from backup, or regenerate your SMK, SQL Server will attempt to decrypt and re-encrypt all keys in the encryption key hierarchy. If any of these decryptions fail, the whole process will fail. If that happens, you can use the FORCE option on the ALTER SERVICE MASTER KEY and RESTORE SERVICE MASTER KEY statements. However, be aware that if you must use the FORCEoption, you can count on data loss.
Database Master Keys
As mentioned previously, the SQL Server encryption key hierarchy includes a single DMK for each database. The DMK directly encrypts asymmetric keys and certificates that can be used to encrypt symmetric keys. Symmetric keys are used, in turn, to encrypt other symmetric keys and data.
Unlike the SMK, which is generated automatically the first time it is needed, a DMK must be created explicitly with the CREATE MASTER KEY statement. SQL Server includes the following T-SQL statements to manage DMKs:
The ALTER MASTER KEY and RESTORE MASTER KEY statements attempt to regenerate the hierarchy of encryption keys that the DMK protects. That is to say, these statements try to automatically decrypt and re-encrypt all encryption keys below the DMK in the hierarchy. If any of these decryptions fail, the entire ALTER or RESTORE statement will fail. The FORCE option can be used to force an ALTER or RESTORE statement to complete regardless of errors. But be warned: the FORCE option should be used only as a last resort, since it always results in data loss.
All DMK management statements require CONTROL permission on the database, and they must be executed in the context of the current database.
The following statement creates a DMK in the AdventureWorks database:USE AdventureWorks;
You should back up all of your DMKs and store them in safe locations as soon as you create them. You can back up the DMK with a statement like the following:BACKUP MASTER KEY TO FILE = N'c:MKAwMasterKeyBackup.bak'
If you ever need to restore the DMK, use the RESTORE MASTER KEY statement:RESTORE MASTER KEY FROM FILE = 'c:MKAwMasterKeyBackup.bak'
When restoring a DMK, you need to supply the same password in the DECRYPTION BY PASSWORD clause that you used when you performed the BACKUP operation.
SQL Server 2008 provides two methods of securing DMKs. Using the first method requires you to explicitly supply a password when you create, alter, or restore your DMK. This password will be used to encrypt the DMK and store it in the database. If you encrypt your DMK with a password, you must supply the same password every time you need to access the keys the DMK protects. This also means you need to use the OPEN MASTER KEY and CLOSE MASTER KEY statements to explicitly open and close the DMK.
By default, SQL Server also provides a second method of securing your DMKs. When you create a DMK, it is automatically encrypted using the SMK and Triple DES algorithm, with copies stored in both the current database and the master database. This allows SQL Server to automatically open and close your DMK when it is needed, without the need for you to supply a password.
On the plus side, this automatic SMK-based security makes development easier, since you don’t need to use explicit OPEN MASTER KEY and CLOSE MASTER KEY statements to open and close the DMKvery time you encrypt your data. You also don’t need to worry about managing, storing, and/or transmitting a password to SQL Server every time you want to perform an encryption or a decryption operation. The downside to this method (and you knew there would be one) is that every sysadmin can decrypt the DMK. In many businesses, this could be the deciding factor against using this feature.
You can use the ALTER MASTER KEY statement to turn off automatic encryption of your DMK by SMKUSE AdventureWorks;
Dropping the DMK is as simple as executing the DROP statement:DROP MASTER KEY;
The OPEN MASTER KEY and CLOSE MASTER KEY statements are used to open and close the DMK so that it can be used to encrypt and decrypt the other keys and certificates that it protects. These keys can then be used to encrypt and decrypt symmetric keys and data. As we noted, SQL Server can implicitly open and close your DMK if it is not encrypted by the SMK. We will describe how to use the DMK with and without automatic SMK encryption in the “Symmetric Keys” section later in this chapter.
SQL Server encryption provides support for asymmetric keys, which are actually composed of a pair of encryption keys: a public key and a private key. The private key can have a key length of 512, 1,024, or 2,048 bits. SQL Server provides the following statements to manage asymmetric keys:
The algorithm/key length identifiers provided by SQL Server for use in the WITH ALGORITHM clause of the CREATE and ALTER ASYMMETRIC KEY statements are listed in Table.
Asymmetric Key Algorithm/Key Length Identifiers
When you create an asymmetric key, its private key is protected by the DMK by default. If the DMK does not exist, you must supply a password to encrypt the private key at creation time. However, if the DMK does exist, the ENCRYPTION BY PASSWORD clause of the CREATE statement is optional.
When altering an asymmetric key pair with the ALTER ASYMMETRIC KEY statement, the following rules apply:
SQL Server provides the built-in EncryptByAsymKey and DecryptByAsymKey T-SQL functions to encrypt and decrypt data via asymmetric keys. EncryptByAsymKey requires you to supply the asymmetric key pair ID number, obtained with the AsymKey_ID function. AsymKey_ID takes the name of the asymmetric key as a parameter and returns the integer ID of the key as a result. EncryptByAsymKey also accepts its plain text to encrypt in the form of a char, nchar, varchar, nvarchar, binary, or varbinary constant, expression, variable, or column name (in a DML statement). EncryptByAsymKey returns a varbinary result, regardless of the type of the plain text passed in.
The DecryptByAsymKey function decrypts data that was previously encrypted using Encrypt ByAsymKey. Decrypt ByAsymKey accepts the asymmetric key pair ID number, just like the Encrypt ByAsymKey function. It also accepts the varbinary encrypted text and an optional asymmetric key password, which is required if the asymmetric key is encrypted by password. The asymmetric key password can be omitted if the asymmetric key is secured by the DMK, but must be of nvarchar type if it is used.
Here is an example of encryption and decryption by asymmetric key:CREATE ASYMMETRIC KEY SampleAsymKey
Although SQL Server 2008 provides the EncryptByAsymKey and DecryptByAsymKey encryption functions, Microsoft recommends that you use asymmetric keys to encrypt symmetric keys only, and use symmetric keys to encrypt your data. One reason for this is speed. Symmetric encryption is considerably faster than asymmetric encryption. Another reason for encrypting data with symmetric encryption is the limitation on the sizes of data that asymmetric encryption can handle. Table shows the limitations of the asymmetric algorithms based on the private key lengths implemented in SQL Server 2008.
Asymmetric Algorithms, Key Lengths, and Limitations
For an asymmetric key with a private key 1,024 bits long, for instance, the RSA_1024 algorithm will encrypt a varchar value with only a maximum length of 117 characters, or an nvarchar value with a maximum length of 58 characters. This limitation makes asymmetric encryption a poor choice for data of any considerable length. If, however, you get stuck encrypting lengthy data asymmetrically (perhaps because of business requirements, for example), you can use a work-around like the user defined functions (UDFs).
The BigAsymEncrypt function in this listing divides up the nvarchar(max) plain text passed into it and encrypts it in chunks. The size of the plain text chunks is equal to the number of bits in the asymmetric encryption key’s private key divided by 16 (if the plain text were varchar instead of nvarchar, it would be divided by 8 instead), minus 11 bytes. The 11 extra bytes are used by the Microsoft Enhanced Cryptographic Provider for PKCS #1 padding. The UDF performs a loop, incrementing the loop counter by the calculated chunk size after each iteration. The BigAsymDecrypt function divides up the encrypted
cipher text, decrypting it in chunks and appending the decrypted plain text chunks to the nvarchar result. The chunk size of the varbinary encrypted text is calculated as the length of the asymmetric encryption key’s private key divided by 8. Here is an example of these UDFs in action:USE AdventureWorks;
This example uses the asymmetric encryption functions to encrypt and decrypt a large nvarchar(max) string. Although you can use methods like this to work around the asymmetric encryption size limitations, symmetric encryption is considerably faster and should generally be used to encrypt your data.
Certificates are another tool provided by SQL Server for asymmetric encryption. A certificate is basically an asymmetric key public key/private key pair containing additional data describing the certificate. The additional data includes a start date, expiration date, and certificate subject. Unlike SQL Server’s asymmetric keys, certificates can be backed up to and restored from files. If you need SQL Server to generate your public key/private key pairs for asymmetric encryption, the ability to create backups makes certificates a better option than asymmetric keys.
Certificates are signed by a certifying authority, which is often a trusted third party, although SQL Server can generate self-signed certificates as well. SQL Server supports certificates that follow the International Telecommunication Union Telecommunication Standardization Sector (ITU-T) X.509 standard (available ).
SQL Server provides the following T-SQL extensions for managing certificates:
The example demonstrates how to generate and back up a self-signed SQL Server certificate and its private key (which is backed up to a separate file).USE AdventureWorks;
To restore the backed-up certificate and its private key, you could run a CREATE CERTIFICATE statementCREATE CERTIFICATE SampleCert
Microsoft recommends that certificates, like asymmetric keys, be used to encrypt your symmetric keys, and symmetric keys be used to encrypt your data. T-SQL does, however, provide the functions EncryptByCert and DecryptByCert to encrypt data using certificates.
Encryption by certificate has the same limitations on length as asymmetric encryption. The maximum length of the plain text you can pass to EncryptByCert can be calculated using this formula: clear_text_max_bytes = ( private_key_length_bits / 8 ) – 11. The length of the encrypted text returned can be calculated using this formula: cipher_text_bytes = ( private_key_length_bits / 8 ).
The EncryptByCert and DecryptByCert functions both require a certificate ID, which is the int ID number for a given certificate. The Cert_ID function can be used to retrieve the ID number for a certificate by name. To use Cert_ID, pass it the name of the certificate as an nvarchar or a varchar. The EncryptByCert function accepts the plain text you wish to encrypt using a certificate. The DecryptByCert function accepts the previously encrypted text you wish to decrypt. The DecryptByCert function includes a third (optional) parameter, the certificate password, which is the same password specified when you created the certificate. If the certificate is secured by the DMK, this parameter should be left out of the call to DecryptByCert.
The example shows how to use EncryptByCert and DecryptByCert, assuming that the SampleCert certificate created earlier in this section currently exists in the AdventureWorks database.USE AdventureWorks;
Symmetric keys are at the bottom of the SQL Server encryption key hierarchy. A symmetric key is used to encrypt other symmetric keys or data. Because symmetric key encryption is so much faster than asymmetric encryption and does not suffer the same data-length limitations as SQL Server’s asymmetric encryption implementations, Microsoft recommends encrypting your data exclusively with symmetric keys.
While asymmetric encryption requires two keys (a public key/private key pair), symmetric encryption requires only a single key to both encrypt and decrypt your data. Symmetric encryption is performed using block cipher algorithms, which encrypt your data in blocks of a constant size, and stream cipher algorithms, which encrypt your data in a continuous stream. Block cipher algorithms have a set encryption key size and encryption block size.
SQL 2008 Supported Algorithms
You can calculate the size of the cipher text based on the length of the plain text using one of the formulas:
For either formula, add 20 bytes to the total length if you use an authenticator.
SQL Server provides the following statements to manage symmetric keys:
SQL Server does not provide backup or restore statements for symmetric keys. Because symmetric keys are stored in the current database, they are backed up during the normal database backup process. You can also re-create a symmetric key from scratch with the CREATE SYMMETRIC KEY statement. In order to re-create a symmetric key from scratch, you must supply a KEY_SOURCE and IDENTITY_VALUE. The KEY_SOURCE is a value SQL Server hashes and performs bitwise manipulations on to generate a symmetric encryption key. If not specified, SQL Server randomly generates a KEY_SOURCE.
The IDENTITY_VALUE is a value SQL Server uses to generate a key GUID. Copies of the key GUID are stored with the data the key is used to encrypt. In order to re-create a symmetric key, you must supply the same KEY_SOURCE and IDENTITY_VALUE originally used to create the key. SQL Server guarantees that supplying duplicate IDENTITY_VALUE and KEY_SOURCE values will generate an identical key.
The following example creates a symmetric key and then drops it:CREATE SYMMETRIC KEY SymTest
Of course, creating a symmetric key is not very useful if you can’t use it to encrypt things. And as we mentioned, symmetric keys in SQL Server can be used to protect other symmetric keys or data. To protect a symmetric key with another symmetric key, use the ENCRYPTION BY SYMMETRIC KEY clause of the CREATE SYMMETRIC KEY statement. To encrypt and decrypt data, use the EncryptByKey and DecryptByKey functions. The following example creates a symmetric key, which is used to encrypt another symmetric key, which is used in turn by EncryptByKey and DecryptByKey to encrypt and decrypt some data.USE AdventureWorks;
The EncryptByKey function requires the key GUID of the symmetric key to encrypt your data. The symmetric key GUID can be retrieved by passing the name of the key to the Key_GUID function. The plain_text passed into the function is char, varchar, nchar, nvarchar, binary, or varbinary data. The return value of EncryptByKey is varbinary(8000). Block mode ciphers on SQL Server, like Triple DES and AES, automatically use an encryption mode known as Cipher Block Chaining (CBC) mode and random initialization vectors (IVs) to further obfuscate your encrypted data.
In addition, the EncryptByKey function also accepts an optional authenticator value to help defeat whole-value substitutions of your data. The authenticator value passed in is a sysname, which is synonymous with nvarchar(128). When an authenticator value is provided, it is encrypted together with the plain text to even further obfuscate your data. The authenticator value can be used to “tie” your encrypted data to a specific row. If you do use an authenticator, the add_authenticator parameter to EncryptByKey must be set to 1.
The DecryptByKey function accepts your encrypted data as a varbinary(8000), and returns the decrypted plain text as a varbinary(8000). If your original data was varchar or nvarchar, then you will need to CAST or CONVERT the result back to its original datatype. If you used an authenticator value when you encrypted the plain text, you must supply the same authenticator value to decrypt your cipher text. Note that you don’t need to supply the Key_GUID when you call DecryptByKey. This is because SQL Server stores the key GUID with the encrypted data during the encryption process.
In addition to padding your plain text before encrypting it, SQL Server stores additional metadata with your cipher text, including the following:
When you use the EncryptByKey and DecryptByKey functions, and the symmetric key you are using to encrypt or decrypt data is protected by another key, you must explicitly open the symmetric key with the OPEN SYMMETRIC KEY statement. SQL Server provides the following additional functions that automatically open and decrypt your symmetric key before decrypting your data:
Keys are available to users who have been granted access in all current SQL Server sessions at any given time (apart from temporary symmetric keys, which were mentioned earlier in this section). Each session can open and close keys independently of the other sessions. For instance, if users Joe and Lisa had open SQL Server sessions at the same time, both could use the same symmetric key simultaneously. If Joe closed the symmetric key in his session, this would have no effect on Lisa’s session or her open symmetric key.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.