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 at http://www.itu.int/ITUT/index.phtml).

SQL Server provides the following T-SQL extensions for managing certificates:

• CREATE CERTIFICATE: Allows you to generate self-signed SQL Server certificates, load certificates from Distinguished Encoding Rules (DER)-encoded files, or create them from certificate-signed dynamic link library (DLL) files. If the ENCRYPTION BY PASSWORD clause is omitted, SQL Server will use the DMK to secure the certificate by default.

• BACKUP CERTIFICATE: Allows you to export a certificate to a file. The exported private key is encrypted with a password you supply in the ENCRYPTION BY PASSWORD clause. There is no RESTORE CERTIFICATE statement; to restore a backed-up certificate, use the CREATE CERTIFICATE statement.

• ALTER CERTIFICATE: Allows you to add or remove a private key from a certificate, change a certificate’s private key, or make a certificate available for Service Broker dialogs.

• DROP CERTIFICATE: Drops an existing certificate. A certificate that is currently being used to protect symmetric keys cannot be dropped.

SQL Server supports certificate private key lengths from 384 to 3,456 bits, in multiples of 64 bits, for private keys imported from DER-encoded files or certificate-signed DLLs. Certificate private keys generated by SQL Server are 1,024 bits long.

The following 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;
GO

CREATE CERTIFICATE SampleCert
ENCRYPTION BY PASSWORD = N'p$@1k-#tZ'
WITH SUBJECT = N'Sample Certificate',
EXPIRY_DATE = N'10/31/2026';

BACKUP CERTIFICATE SampleCert
TO FILE = N'c:\MK\BackupSampleCert.cer'
WITH PRIVATE KEY (
FILE = N'c:\MK\BackupSampleCert.pvk' ,
ENCRYPTION BY PASSWORD = N'p@$$w0rd',
DECRYPTION BY PASSWORD = N'p$@1k-#tZ'
);

DROP CERTIFICATE SampleCert;
GO

To restore the backed-up certificate and its private key, you could run a CREATE CERTIFICATE statement like the following:

CREATE CERTIFICATE SampleCert
FROM FILE = N'c:\MK\BackupSampleCert.cer'
WITH PRIVATE KEY (
FILE = N'c:\MK\BackupSampleCert.pvk',
DECRYPTION BY PASSWORD = N'p@$$w0rd',
ENCRYPTION BY PASSWORD = N'p$@1k-#tZ'
);
GO

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 following 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;
GO

-- Initialize the plain text
DECLARE @plain_text NVARCHAR(58);

SET @plain_text = N'This is a test!';
PRINT @plain_text;

-- Encrypt the plain text using the certificate
DECLARE @cipher_text VARBINARY(127);
SET @cipher_text = EncryptByCert(Cert_ID(N'SampleCert'), @plain_text);
PRINT @cipher_text;

-- Decrypt the cipher text using the certificate
SET @plain_text = CAST(DecryptByCert(Cert_ID(N'SampleCert'),
@cipher_text, N'p$@1k-#tZ') AS NVARCHAR(58));
PRINT @plain_text;
GO

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

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:

• CREATE ASYMMETRIC KEY: Allows you to generate a new asymmetric key public key/private key pair, import the key pair from a file, or import a public key from a .NET assembly. This statement requires CREATE ASYMMETRIC KEY permissions on the database.

• ALTER ASYMMETRIC KEY: Allows you to modify the properties of an existing asymmetric key. With this statement, you can remove the private key from the public key/private key pair or change the password used to encrypt a private key in the public key/private key pair. This statement requires CONTROL permission on the asymmetric key if you are removing the private key from it.

• DROP ASYMMETRIC KEY: Drops an asymmetric key from the database. This statement requires CONTROL permission on the asymmetric key.

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:

RSA_512. 512-bit private key for use with RSA public key/private key encryption algorithm

RSA_1024. 1,024-bit private key for use with RSA public key/private key encryption algorithm

RSA_2048. 2,048-bit private key for use with RSA public key/private key encryption algorithm

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:

• If you are changing the password used to encrypt the private key or if the private key is currently protected by the DMK and you want to change it to be encrypted by password, the ENCRYPTION BY PASSWORD clause is mandatory.

• If the private key is currently protected by password and you want to change it to encryption by DMK or you are changing the password used to encrypt the private key, the DECRYPTION BY PASSWORD clause is required.

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 EncryptByAsymKey. DecryptByAsymKey accepts the asymmetric key pair ID number, just like the EncryptByAsymKey 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.

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.

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

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

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:

• CREATE MASTER KEY: Creates a DMK within a database. A password must be supplied to encrypt the DMK in the database when it is created.

• ALTER MASTER KEY: Allows you to regenerate your DMK or to change how the DMK is secured by adding or removing encryption by password or SMK. If you regenerate the DMK, all the keys it protects will be decrypted and re-encrypted.

• DROP MASTER KEY: Drops the DMK from the current database. If any private keys in the current database are protected by the DMK, the DROP statement will fail.

• BACKUP MASTER KEY: Backs up the DMK to a file. You must specify a password, which will be used to encrypt the DMK in the file.

• RESTORE MASTER KEY: Restores the DMK from a file. You must supply the same password you used when backing up the DMK for a RESTORE operation to succeed. You must also supply a second password to encrypt the DMK in the database after it is restored. During the restore process, SQL Server attempts to decrypt and re-encrypt all keys protected by the DMK.

• OPEN MASTER KEY: Opens the DMK so that it can be used for encryption and decryption. The DMK must be open in order for any encryption or decryption operation to succeed, although SQL Server can implicitly open your DMK when it’s protected by the SMK.

• CLOSE MASTER KEY: Closes a DMK that was explicitly opened using OPEN MASTER KEY after you are finished using it for encryption and decryption.

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;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Avx3$5*!';

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:\MK\AwMasterKeyBackup.bak'
ENCRYPTION BY PASSWORD = N'#%e3)Fr';
GO

If you ever need to restore the DMK, use the RESTORE MASTER KEY statement, as follows:

RESTORE MASTER KEY FROM FILE = 'c:\MK\AwMasterKeyBackup.bak'
DECRYPTION BY PASSWORD = N'#%e3)Fr'
ENCRYPTION BY PASSWORD = N'Avx3$5*!';
GO

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 DMK every 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 SMK, as in the following T-SQL code:

USE AdventureWorks;
GO
ALTER MASTER KEY
DROP ENCRYPTION BY SERVICE MASTER KEY;
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.

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

SQL Server 2008 includes the following T-SQL statements to alter, back up, and drop SMKs:

• ALTER SERVICE MASTER KEY: Allows you to change or regenerate the SMK. This statement can be used to change the SMK and to automatically decrypt and re-encrypt the entire encryption key hierarchy.

• BACKUP SERVICE MASTER KEY: Backs up your SMK to a file. The SMK is encrypted before backup and stored in encrypted format. You must supply a password to be used to encrypt the SMK backup.

• RESTORE SERVICE MASTER KEY: Restores your SMK from a file. The SMK RESTORE statement requires you to supply the same password used when you backed up the SMK. Like ALTER SERVICE MASTER KEY, the RESTORE SERVICE MASTER KEY statement regenerates the entire encryption key hierarchy.

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:\MK\backup_master_key.dat'
ENCRYPTION BY PASSWORD = 'p@$$w0rD';

In this example, the SMK is backed up to the file c:\MK\backup_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 FORCE option, you can count on data loss.

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

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.

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

The ANSI X9.17 standard is the “Financial Institution Key Management (Wholesale)” standard. This standard defines methods for securely storing and transferring encryption keys, an area of encryption that has been the subject of debate and research throughout the field of cryptography for decades. Encryption key management is not easy. After all, modern encryption theory has only one primary mandate: the security of a system lies with the encryption key. No matter how secure the algorithm you use to encrypt your data, if your encryption key is not properly secured, your data can be easily compromised. SQL Server’s encryption key management is built on top of the secure Data Protection API (DPAPI), making it as secure as possible on a computer that runs Windows.

Source of Information : Apress Accelerated SQL Server 2008

• 0 comments »

There are several different ways to get XML into SQL Server. First, you can just dump your XML into a nvarchar column in the database using a simple INSERT statement. Using this technique is just like entering any text into a column. With SQL Server, you can use the XML datatype rather than a text column.

There are three other ways of getting XML into your database:

• Shred your XML into multiple columns and rows in a single database call. To do this, you can use the OPENXML rowset provider. OPENXML provides a rowset view over an XML document and allows you to write T-SQL statements that parse XML.

• Use updategrams, which are data structures that can express changes to your data by representing a before-and-after image. SQLXML takes your updategram and generates the
necessary SQL commands to apply your changes.

• Use SQLXML’s XML BulkLoad provider. Using this provider, you can take a large set of XML
data and quickly load it into your SQL Server.

SQLXML is an additional set of technologies that include updategram support, the SQLXML BulkLoad provider, client-side FOR XML support, and SOAP support. For SQL Server 2000, SQLXML 3.0 shipped separately; it doesn’t need to run on a server. With SQL Server 2005 and SQL Server 2008, SQLXML 4.0 ships with the product, but it can also be redistributed on its own. Don’t confuse SQLXML with the SQL Server XML datatype.

Each technique for getting XML into the database has its strengths and weaknesses. If you are just looking for the fastest and highest performance way to get XML data into SQL Server, consider the BulkLoad provider. The BulkLoad provider doesn’t attempt to load all your XML into memory, but instead reads your XML data as a stream, interprets it, and loads it into your SQL Server. The BulkLoad provider is a separate component, so you cannot use it inside a stored procedure or user-defined function (UDF). You could use it in an extended stored procedure (XP) by calling out to it, but that is an uncommon scenario and has its own set of issues (XPs are complex, hard to debug, and can open up your server to security issues if written incorrectly).

On the other hand, OPENXML can be used in stored procedures and UDFs, since it ships as part of the native T-SQL language. You’ll pay a performance penalty for this integration though. OPENXML requires you to use a stored procedure, sp_xml_preparedocument, to parse the XML for consumption. This stored procedure loads a special version of the MSXML parser called MSXMLSQL to process the XML document and, in turn, loads the entire XML document into memory.

Updategrams are very useful for applications where you want to modify your database and you are OK with building an annotated schema and applying those changes through this annotated schema. SQLXML takes the updategram and translates it to SQL Data Manipulation Language (DML) statements. However, if you need to apply business logic to the SQL DML statements, you’ll be unable to use updategrams, since you cannot access the generated DML statements.

Don’t confuse SQLXML with the SQL/XML standard, also known as the SQLX standard. SQLX is an ANSI/ISO standard that defines how to make XML data work in relational databases. Microsoft is a member of the working committee for the SQLX standard. SQL Server currently doesn’t support the SQLX standard but provides equivalent functionality for the activities covered in the standard. For example, SQLX defines XML publishing, which SQL Server can do using the FOR XML statement. For the XML decomposition, you can use the XML datatype or OPENXML. Plus, there are things that the standard doesn’t define that SQL Server implements, such as combining XQuery into relational queries.

Source of Information : Apress Accelerated SQL Server

• 0 comments »