Data encryption is basically a scrambling of the data with a secret key to produce an encoded copy of the data called the cipher data. Without key, data is impossible to unscramble. Types of Encryption: Symmetric, Asymmetric, Certificates & Passphrase.
– Symmetric Encryption: Symmetric encryption uses the same key to both encrypt and decrypt the data. The algorithm defines how the data
will be encrypted using this key. There are nine possible algorithms: DES (Data Encryption Standard), TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128 (Advanced Encryption Standard), AES_192, and AES_256. They differ in speed and strength.
– Asymmetric Encryption: Asymmetric encryption is considered stronger than symmetric encryption because one key, a private key, is paired with a second public key. If the data is encrypted with one of those two keys, then it can be decrypted with the other. SQL Server supports RSA_512, RSA_1024, and RSA_2048 (algorithms for public-key cryptography) as possible asymmetric algorithms. The difference is the bit length of the private key. Asymmetric Keys doesn’t need to be open in order to be used.
-Certificates: Certificates are similar to keys but are generally issued by an organization, such as VeriSign, to certify that the organization associated with the certificate is legitimate. Certificates are used to safeguard encryption keys, which are used to encrypt data in the
database. SQL Server 2005 and above has the capability to generate self-signed X.509 certificates and use these certificates to create the Symmetric and Asymmetric keys.
-Passphrase: Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length. One of the easiest ways is to use the ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions. The encrypted data will be varbinary with maximum size of 8,000 bytes. Note: Data can also be encrypted and decrypted using .NET at the middle tier or at the front end. This offers the advantage that the database server never sees readable data.
.Database Master Key: A database master key is used to symmetrically encrypt keys and certificates that are stored in the database. A database master key can be encrypted by using the service master key or by using a user-specified password. When a database master key is created, a password must be supplied to encrypt and decrypt it. When a database is attached to an instance of SQL Server, the database administrator must either supply the master key password or make an unencrypted copy of the master key that is available for encryption with the service master key.
.Service Master Key: The service master key is the top layer in the encryption hierarchy that is used to encrypt lower layers in the encryption hierarchy. The service master key is generated automatically by Windows. It can be opened only by the Windows service account under which it was created, or by a user who knows both the service account name and its password.
. Microsoft recommends against using certificates or asymmetric keys for encrypting data directly. Asymmetric key encryption is many times slower and the amount of data that you can protect using this mechanism is limited, depending on the key modulus. You can protect
certificates and asymmetric keys using a password instead of by the database master key.