Skip to content

encrypt-a-column-of-data.md

Latest commit

 

History

History
189 lines (140 loc) · 6.88 KB

encrypt-a-column-of-data.md

File metadata and controls

189 lines (140 loc) · 6.88 KB

title: "Encrypt a Column of Data | Microsoft Docs" ms.custom: "" ms.date: "05/22/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "security" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "article" helpviewer_keywords:
  • "encryption [SQL Server], columns"
  • "cryptography [SQL Server], columns"
  • "column level encryption"
  • "cell level encryption" ms.assetid: 38e9bf58-10c6-46ed-83cb-e2d76cda0adc caps.latest.revision: 27 author: "edmacauley" ms.author: "edmaca" manager: "craigg" monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"

Encrypt a Column of Data

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

This topic describes how to encrypt a column of data by using symmetric encryption in [!INCLUDEssCurrent] using [!INCLUDEtsql]. This is sometimes known as column-level encryption, or cell-level encryption.

In This Topic

Before You Begin

Security

Permissions

The following permissions are necessary to perform the steps below:

  • CONTROL permission on the database.

  • CREATE CERTIFICATE permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.

  • ALTER permission on the table.

  • Some permission on the key and must not have been denied VIEW DEFINITION permission.

Using Transact-SQL

To use the following examples you must have a database master key. If your database does not already have a database master key, create one by executing the following statement providing your password:

CREATE MASTER KEY ENCRYPTION BY   
PASSWORD = '<some strong password>';

Always backup your database master key. For more information on database master keys, see CREATE MASTER KEY (Transact-SQL).

To encrypt a column of data using a simple symmetric encryption

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  

GO  

CREATE CERTIFICATE Sales09  
   WITH SUBJECT = 'Customer Credit Card Numbers';  
GO  

CREATE SYMMETRIC KEY CreditCards_Key11  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE Sales09;  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE Sales.CreditCard   
    ADD CardNumber_Encrypted varbinary(128);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  

-- Encrypt the value in column CardNumber using the  
-- symmetric key CreditCards_Key11.  
-- Save the result in column CardNumber_Encrypted.    
UPDATE Sales.CreditCard  
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')  
    , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary  
    , CreditCardID)));  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  

OPEN SYMMETRIC KEY CreditCards_Key11  
   DECRYPTION BY CERTIFICATE Sales09;  
GO  

-- Now list the original card number, the encrypted card number,  
-- and the decrypted ciphertext. If the decryption worked,  
-- the original number will match the decrypted number.  

SELECT CardNumber, CardNumber_Encrypted   
    AS 'Encrypted card number', CONVERT(nvarchar,  
    DecryptByKey(CardNumber_Encrypted, 1 ,   
    HashBytes('SHA1', CONVERT(varbinary, CreditCardID))))  
    AS 'Decrypted card number' FROM Sales.CreditCard;  
GO

To encrypt a column of data using symmetric encryption that includes an authenticator

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
GO  

CREATE CERTIFICATE HumanResources037  
   WITH SUBJECT = 'Employee Social Security Numbers';  
GO  

CREATE SYMMETRIC KEY SSN_Key_01  
    WITH ALGORITHM = AES_256  
    ENCRYPTION BY CERTIFICATE HumanResources037;  
GO  

USE [AdventureWorks2012];  
GO  

-- Create a column in which to store the encrypted data.  
ALTER TABLE HumanResources.Employee  
    ADD EncryptedNationalIDNumber varbinary(128);   
GO  

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  

-- Encrypt the value in column NationalIDNumber with symmetric   
-- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber.  
UPDATE HumanResources.Employee  
SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber);  
GO  

-- Verify the encryption.  
-- First, open the symmetric key with which to decrypt the data.  
OPEN SYMMETRIC KEY SSN_Key_01  
   DECRYPTION BY CERTIFICATE HumanResources037;  
GO  

-- Now list the original ID, the encrypted ID, and the   
-- decrypted ciphertext. If the decryption worked, the original  
-- and the decrypted ID will match.  
SELECT NationalIDNumber, EncryptedNationalIDNumber   
    AS 'Encrypted ID Number',  
    CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber))   
    AS 'Decrypted ID Number'  
    FROM HumanResources.Employee;  
GO

For more information, see the following: