Tag: SQL Server 2012

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 !!!

All Version of SQL Server 2012 including the Express Edition supports Encryption

The first steps to start Encrypting data in SQL Server 2012 are as follows.

1. Backup Service Master Key and keep it in a secure location

Use master

BACKUP SERVICE MASTER KEY TO FILE = N'C:\Workspace\MYServiceMasterKey.Key' 
                                       ENCRYPTION BY PASSWORD = N'p@ssw0rd'

2. Create Database Master Key for Encryption for the databases you want to protect

Use AdventureWorks2012
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';

3.  Backup the Database Master Key to a secure location

Use AdventureWorks2012
BACKUP MASTER KEY TO FILE = N'C:\Workspace\DatabaseMasterKey.Key' 
    ENCRYPTION BY PASSWORD = N'p@ssw0rd'

4. Open the Database Master Key and Test it

Use AdventureWorks2012
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssw0rd';

CLOSE MASTER KEY;

If you specify the wrong password to open the database master key, you will be getting the below error

To view the current database master key in your database, you can use the following system security catalog view

Hope you find this information useful !!! In the upcoming articles, we will go through the different encryption options available in SQL Server 2012.