SQLXpertise

In the previous episodes, we saw How to create Asymmetric key and How to use Asymmetric key to encrypt a Symmetric key, in this Episode of Encryption in SQL Server 2012, We will see How to encrypt data using Asymmetric Key functions

To encrypt data using Asymmetric Key, we need to use EncryptByAsymKey function and to decrypt data using Asymmetric Key, we need to use DecryptByAsymKey function

Snippet demonstrating Encrypting/Decrypting of Data using Asymmetric Key created using Password

USE AdventureWorks2012

--Encrypting the data 

DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) 
    = N'This is simple plain text for encryption'

SET @encryptedtext = EncryptByAsymKey
                    (
                        AsymKey_ID(N'AsymKey1_AdventureWorks2012'), 
                        @plaintext
                    )

--Decrypting the data
SELECT 
    @plaintext AS [PlainText], 
    @encryptedtext AS [EncryptedText],
    CAST
    (
        DecryptByAsymKey
        (
            AsymKey_ID(N'AsymKey1_AdventureWorks2012'), 
            @encryptedtext,
            N'UseAReallyStrongPasswordHere' –Specify Asym Key Password here
        ) 
        AS nvarchar(100)
    ) As [DecryptedText]

 

c

Snippet demonstrating Encrypting/Decrypting of Data using Asymmetric Key created using Strong Name Key Pair file

USE AdventureWorks2012

--Encrypting the data 

DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

SET @encryptedtext = EncryptByAsymKey
                    (
                        AsymKey_ID(N'AsymKey2_AdventureWorks2012'), 
                        @plaintext
                    )

--Decrypting the data
SELECT 
    @plaintext AS [PlainText], 
    @encryptedtext AS [EncryptedText],
    CAST
    (
        DecryptByAsymKey
        (
            AsymKey_ID(N'AsymKey2_AdventureWorks2012'), 
            @encryptedtext
        ) 
        AS nvarchar(100)
    ) As [DecryptedText]

 

The results are as follows:

Hope you find this helpful !!!

In the last episode, we saw How to create Asymmetric Keys, and in this episode of Encryption in SQL Server 2012, We will see how to encrypt a Symmetric Key using an Asymmetric Key

First Step is to create the Symmetric Key based using Asymmetric Key

USE AdventureWorks2012

-- Creating a Symmetric Key using Asymmetric Key
CREATE SYMMETRIC KEY SymKey2_AdventureWorks2012
WITH ALGORITHM = AES_256 
    /* Other Supported Algorithms 
        
        DES
        TRIPLE_DES
        TRIPLE_DES_3KEY
        RC2
        RC4
        RC4_128
        DESX
        AES_128
        AES_192
        
    */
ENCRYPTION BY ASYMMETRIC KEY AsymKey1_AdventureWorks2012;

 

Next Open the Symmetric Key using Asymmetric Key to Encrypt/Decrypt the data

--Opening Symmetric using Asymmetric Key

OPEN SYMMETRIC KEY SymKey2_AdventureWorks2012
DECRYPTION BY ASYMMETRIC KEY AsymKey1_AdventureWorks2012
WITH PASSWORD = 'UseAReallyStrongPasswordHere';
GO

 

Once the Symmetric Key is successfully opened, You can Encrypt or Decrypt the data using EncryptByKey / DecryptByKey functions

--Encrypting the data 

DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

SET @encryptedtext = EncryptByKey(Key_Guid(N'SymKey2_AdventureWorks2012'), @plaintext)

--Decrypting the data
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText],
    CAST(DECRYPTBYKEY(@encryptedtext) AS nvarchar(100)) [DecryptedText]

 

The output of the above query is as follows:

 

Once the Encryption / Decryption process is complete, Please make sure to close the Symmetric Key

 

--Close the Symmetric Key
CLOSE SYMMETRIC KEY SymKey2_AdventureWorks2012;
GO

 

Hope this helps !!!

In the next Episode of Encryption in SQL Server 2012, We will see how to encrypt data directly using Asymmetric Key and its functions…

In this episode of Encryption in SQL Server 2012, We will see how to create Asymmetric Keys in databases using Password and Strong Name Key file

We can create Asymmetric Keys using the following options

1. Creating using Password

USE AdventureWorks2012

--Creating Asymmetric Key using Password
CREATE ASYMMETRIC KEY AsymKey1_AdventureWorks2012
    WITH ALGORITHM = RSA_2048 
        /* 
            Other Algorithms Supported as follows

            RSA_512
            RSA_1024
            AES_256
        */
    ENCRYPTION BY PASSWORD = 'UseAReallyStrongPasswordHere'

 

2. Creating using Strong Name Key Pair file

To generate a Strong Name Key Pair, we have to use sn.exe (For more information, Please refer http://msdn.microsoft.com/en-us/library/k5b5tt23(v=vs.110).aspx)

Command for Generating a Strong Name Key Pair file

sn –k C:\Workspace\AsymKey_AdventureWorks2012.snk

Once the key file is generated successfully, we can use that to create a Asymmetric key

--Creating Asymmetric Key using Strong Name Key file
CREATE ASYMMETRIC KEY AsymKey2_AdventureWorks2012
FROM FILE = N'C:\Workspace\AsymKey_AdventureWorks2012.snk';

 

The third option to generate an Asymmetric Key is to use EKM Device, for more information, Please refer http://technet.microsoft.com/en-us/library/bb895340.aspx

 

To View the list of Asymmetric Key available in the database, you can use the below query

 

SELECT 
    asymmetric_key_id, name, 
    pvt_key_encryption_type, pvt_key_encryption_type_desc, 
    algorithm, algorithm_desc, key_length
FROM sys.asymmetric_keys

 

In the next episode, we will see how we can use Asymmetric Keys

1. To Encrypt a Symmetric Key

2. To Encrypt and Decrypt Data

Hope this helps !!!

In this Episode of Encryption in SQL Server 2012, We will see How to create  Symmetric Key and use Symmetric Key based functions

Before creating the Key, Please make sure to create the certificate used for encrypting the key, for more information, Please refer to the previous article

First Step is to create Symmetric Key

USE AdventureWorks2012

--Create Symmetric Key protected by Certificate

CREATE SYMMETRIC KEY SymKey1_AdventureWorks2012
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Cert1_AdventureWorks2012;

 

Once the key is created successfully, you can verify the list of Symmetric Keys available using the below query

--View available Symmetric Keys
SELECT symmetric_key_id, name, key_algorithm, algorithm_desc, 
    key_length, key_guid FROM Sys.symmetric_keys

 

While creating a Symmetric Key you can choose the following list of available Encryption Algorithms

1. DES

2. TRIPLE_DES

3. TRIPLE_DES_3KEY

4. RC2

5. RC4

6. RC4_128

7. DESX

8. AES_128

9. AES_192

10. AES_256

Similarly for encrypting the Key, in the above snippet we have use Certificate, instead we can use the following other options as well

1. Password

2. Asymmetric Key

3. Another Symmetric Key

 

After we successfully created the Symmetric key, we can use open and encrypt/decrypt the data as shown follows

 

To open the Symmetric Key

OPEN SYMMETRIC KEY SymKey1_AdventureWorks2012
DECRYPTION BY CERTIFICATE Cert1_AdventureWorks2012;
GO

 

Now call the EncryptByKey and DecryptByKey functions to encrypt and decrypt the data

DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

-- Encrypt the data with the Certificate Registered
SET @encryptedtext = EncryptByKey(Key_Guid(N'SymKey1_AdventureWorks2012'), @plaintext)

-- Decrypt the data with the same Certificate
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText],
    CAST(DECRYPTBYKEY(@encryptedtext) AS nvarchar(100)) [DecryptedText]

 

The results are as follows

Authenticator

We can also use Authenticator to add more protection to the encrypted data

 

DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with Authenticator
SET @encryptedtext = EncryptByKey
            (
                Key_Guid(N'SymKey1_AdventureWorks2012'), 
                @plaintext, 
                1, -- To Enable Authenticator Specify 1
                CONVERT(Varbinary, 'AuthenticatorText') -- Authenticator Text should be converted to VARBINARY
            );

-- Decrypt the data with Authenticator
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(
        DECRYPTBYKEY(
                @encryptedtext, 
                1, 
                CONVERT(Varbinary, 'AuthenticatorText')
        ) AS nvarchar(100)) As [DecryptedText]

 

Once we complete the encryption and decryption operations, Please make sure to close the key

 

CLOSE SYMMETRIC KEY SymKey1_AdventureWorks2012;
GO

Hope this helps !!!

In the previous article, we saw how to create X 509 Certificate and register it in SQL Server,

Now we will see how we can use the registered certificates

SQL Server 2012 provides the following two functions to encrypt and decrypt data directly using X 509 Certificates

1. EncryptByCert

2. DecryptByCert

Please refer to the below sample on how we can use these functions

DECLARE @CertID INT
DECLARE @encryptedtext varbinary(300);
DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption'

-- Get the Certificate ID
SET @CertID = Cert_ID(N'Cert1_AdventureWorks2012')

-- Encrypt the data with the Certificate Registered
SET @encryptedtext = EncryptByCert(@CertID, @plaintext) 

-- Decrypt the data with the same Certificate
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText],
    CAST(DECRYPTBYCERT(@CertID, @encryptedtext) AS nvarchar(100)) [DecryptedText]

 

The result of the above are as follows

Please make sure to use the correct Certificate ID to decrypt the data and also make sure to document which certificate is used for Encryption, if you are planning to use multiple certificates for different tables.

Hope this helps !!!

In the upcoming articles, we will see How we can use Certificates to

1. Encrypt Symmetric Keys

2. To Enable TDE based Encryption for Database

3. To digitally sign data using SignByCert() function

Internal use X-509 Certificates can be generated using MakeCert.exe utility, Please find the below syntax

 

makecert.exe -sv "C:\Workspace\Cert1_AdventureWorks2012.pvk" 
    -pe 
    -a sha1 
    -b "01/01/2013" 
    -e "12/31/2013" 
    -len 2048 
    -r 
    -n "CN=SQL Server 2012 - Encryption Demo Certificate" 
    C:\Workspace\Cert1_AdventureWorks2012.cer

The details of the parameters are as follows

-sv        Private Key file
-pe        Private Key Exportable
-a         Signature Algorithm (md5 [Default] or sha1)
-b        Beginning Date 
-e        Expiring Date
-len      Length of Private Key
-r        Self-Signed Certificate
-n        Name of Certificate (Subject of Certificate)

 

You can run the above utility from Command Prompt, it will prompt you to create Password for accessing Private Key

Once you have created the Password, it prompt you again to confirm and create the certificate

Once the certificate is created, you will get the “Succeeded” message

Also you can open the created Certificate file to view the details

 

Register Certificates

Once we create the X-509 certificate, we can use this to Register Certificates in SQL Server using the below T-SQL command, Please make sure you have created the Service Master Key and Database Master Key before this step

 

USE AdventureWorks2012
-- Register Certificate in SQL Server

CREATE CERTIFICATE Cert1_AdventureWorks2012
FROM FILE = N'C:\Workspace\Cert1_AdventureWorks2012.cer'
WITH PRIVATE KEY
(
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012.pvk',
    DECRYPTION BY PASSWORD = N'p@ssw0rd'
);

Backup Certificates

To Backup Certificate from SQL Server, Please use the below T-SQL Command

USE AdventureWorks2012

--Backup Certificates

BACKUP CERTIFICATE Cert1_AdventureWorks2012
TO FILE = N'C:\Workspace\Cert1_AdventureWorks2012_BACKUP.cer'
WITH PRIVATE KEY
(
    FILE = N'C:\Workspace\Cert1_AdventureWorks2012_BACKUP.pvk',
    ENCRYPTION BY PASSWORD = N'p@ssw0rd~123'
);

View Certificates registered in Database

You  can view the certificates registered in a database using the below query

SELECT 
    name, certificate_id, principal_id, pvt_key_encryption_type
    pvt_key_encryption_type_desc, is_active_for_begin_dialog, issuer_name
    cert_serial_number, sid, string_sid, subject, expiry_date, 
    start_date, thumbprint, attested_by, pvt_key_last_backup_date
FROM sys.certificates

 

Deleting Certificates registered in Database

To delete a certificate registered in database, please use the below query

-- Deleting Certicates

DROP CERTIFICATE Cert1_AdventureWorks2012

In SQL Server, Certificates can be used to

1. Encrypt data using EncryptByCert and DecryptByCert functions

2. Encrypt Symmetric Keys

3. To Enable TDE based Encryption for Database

4. To digitally sign data using SignByCert() function

Hope this helps !!!

SQL Server 2012 supports PassPhrase based encryption. PassPhrase method uses TRIPLE DES algorithm with a 128 key bit length.

The following are the 2 functions

1. EncryptByPassPhrase

2. DecryptByPassPhrase

Its very simple and easy to use as well

 

DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with a passphrase
DECLARE @encryptedtext varbinary(300);
SET @encryptedtext = EncryptByPassPhrase(N'p@ssw0rd', @plaintext);

-- Decrypt the data with the same passphrase
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(DecryptByPassPhrase(N'p@ssw0rd', @encryptedtext) AS nvarchar(100)) [DecryptedText]

The output will look as follows

Also you can use an Authenticator as well along with your PassPhrase

DECLARE @plaintext nvarchar(100) = N'This is simple plain text for encryption';

-- Encrypt the data with a passphrase with Authenticator
DECLARE @encryptedtext varbinary(300);

SET @encryptedtext = EncryptByPassPhrase
            (
                N'p@ssw0rd', 
                @plaintext, 
                1, -- To Enable Authenticator Specify 1
                CONVERT(Varbinary, 'AuthenticatorText') -- Authenticator Text should be converted to VARBINARY
            );

-- Decrypt the data with the same passphrase with Authenticator
SELECT 
    @plaintext [PlainText], 
    @encryptedtext [EncryptedText], 
    CAST(
        DecryptByPassPhrase(
                N'p@ssw0rd', 
                @encryptedtext, 
                1, 
                CONVERT(Varbinary, 'AuthenticatorText')
        ) AS nvarchar(100)) As [DecryptedText]

 

Hope this helps !!!