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
• 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
Post a Comment