-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.sql
More file actions
113 lines (100 loc) · 2.26 KB
/
Copy pathSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.sql
File metadata and controls
113 lines (100 loc) · 2.26 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
/*
http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx
*/
-- Create Database
USE master
GO
CREATE DATABASE EncryptTest ON PRIMARY
( NAME = N'EncryptTest', FILENAME = N'C:\EncryptTest.mdf')
LOG ON
( NAME = N'EncryptTest_log', FILENAME = N'C:\EncryptTest_log.ldf')
GO
-- Create table and insert data in the table
USE EncryptTest
GO
CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))
GO
INSERT INTO TestTable (FirstCol, SecondCol)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
-- Check the content of the TestTable
USE EncryptTest
GO
SELECT *
FROM TestTable
GO
-- Create Database Master Key
USE EncryptTest
GO
CREATE MASTER KEY ENCRYPTION BY
PASSWORD = 'SQLAuthority'
GO
-- Create Encryption Certificate
USE EncryptTest
GO
CREATE CERTIFICATE EncryptTestCert
WITH SUBJECT = 'SQLAuthority'
GO
-- Create Symmetric Key
USE EncryptTest
GO
CREATE SYMMETRIC KEY TestTableKey
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE EncryptTestCert
GO
-- Encrypt Data using Key and Certificate
-- Add Columns which will hold the encrypted data in binary
USE EncryptTest
GO
ALTER TABLE TestTable
ADD EncryptSecondCol VARBINARY(256)
GO
-- Update binary column with encrypted data created by certificate and key
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
UPDATE TestTable
SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)
GO
-- DROP original column which was encrypted for protect the data
USE EncryptTest
GO
ALTER TABLE TestTable
DROP COLUMN SecondCol
GO
-- Check the content of the TestTable
USE EncryptTest
GO
SELECT *
FROM TestTable
GO
-- Decrypt the data of the SecondCol
USE EncryptTest
GO
OPEN SYMMETRIC KEY TestTableKey
DECRYPTION BY CERTIFICATE EncryptTestCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol
FROM TestTable
GO
-- Clean up database
USE EncryptTest
GO
CLOSE SYMMETRIC KEY TestTableKey
GO
DROP SYMMETRIC KEY TestTableKey
GO
DROP CERTIFICATE EncryptTestCert
GO
DROP MASTER KEY
GO
DROP TABLE dbo.TestTable
GO