Scribd
Upload
4 views17 pages

SQL Server Column Level Encryption

The document outlines the steps to create a database and a table for storing employee information, including sensitive data like Social Security Numbers. It details the process of applying column-level encryption to the SocialSecurityNumber column, including creating keys and exporting certificates for security. Finally, it emphasizes the importance of managing certificates for data decryption and the implications of enabling or disabling column encryption settings in connection strings.

Uploaded by

praveen.sql234
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views17 pages

SQL Server Column Level Encryption

The document outlines the steps to create a database and a table for storing employee information, including sensitive data like Social Security Numbers. It details the process of applying column-level encryption to the SocialSecurityNumber column, including creating keys and exporting certificates for security. Finally, it emphasizes the importance of managing certificates for data decryption and the implications of enabling or disabling column encryption settings in connection strings.

Uploaded by

praveen.sql234
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Column Encryption Setting = Enabled

------------------------------------------------------------------
Step1: create a new database and switch to it
CREATE DATABASE EncryptDataDB1;
GO
USE EncryptDataDB1;
GO

Step2: create a basic table and insert record in it.

CREATE TABLE Employees (


EmployeeID int IDENTITY(1000,1) PRIMARY KEY,
Name nvarchar(100) NOT NULL,
Phone nvarchar(8) NULL,
SocialSecurityNumber char(11)
);
GO

INSERT Employees
VALUES ('Malcom','555-0123', '123-00-9812'),
('Rory','555-1234', '123-00-2154'),
('Brianne','555-7890', '123-00-5081');
GO

SELECT * FROM Employees;


GO
Step2: Apply Column Level encryption on SocialSecurityNumber column
Of Employee Table.

Step2.1: Create Column Master Key


Step2.1.1: View Newly Created Certificate In Current User Certificates

Step2.1.2: Identify Your Certificate


Step2.1.3: Identify Your Certificate
Step2.2: Create Column Encryption Key
Step2.3: Apply Column Level encryption on SocialSecurityNumber
column Of Employee Table
Step2.4: Verify is Column Level encryption applied on
SocialSecurityNumber column Of Employee Table?
Its verified that data is encrypted.

Step2.5: Export the Certificate (remember password and save


certificate on a secured place) and Delete from the database server
Step2.6: Delete Certificate from the database server
Note after deleting the certificate you can not decrypt encrypted
column using (Column Encryption Setting = Enabled) in
connection string

With Column Encryption Setting = Enabled in


connection string

Without Column Encryption Setting = Enabled in


connection string

Step2.7: Install Exported Certificate on Application Server


After Installation Certificate on Client you can now
decrypt data

You might also like