Pre-SQL Server 2016 – Dynamic Data Masking with Symmetric Certificates

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
CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'p@ssword123'
GO
CREATE SYMMETRIC KEY skey WITH ALGORITHM = AES_256 ENCRYPTION BY ASYMMETRIC KEY akey
GO

CREATE TABLE PhoneTable (
   cid INT IDENTITY(1,1) NOT NULL,
   PhoneNumber CHAR(12),
   Encrypted  VARBINARY(MAX),
   UnEncrypted  CHAR(12)
)

INSERT INTO PhoneTable (PhoneNumber)
VALUES ('238-555-0197'), ('664-555-0112')

OPEN SYMMETRIC KEY skey DECRYPTION BY ASYMMETRIC KEY akey WITH PASSWORD = 'p@ssword123'

UPDATE PhoneTable SET Encrypted = ENCRYPTBYKEY(KEY_GUID('skey'), PhoneNumber)

UPDATE PhoneTable SET UnEncrypted = CAST(DECRYPTBYKEY(Encrypted) AS VARCHAR)

SELECT * FROM PhoneTable

-- cleanup
DROP TABLE PhoneTable
CLOSE SYMMETRIC KEY skey
DROP SYMMETRIC KEY skey
DROP ASYMMETRIC KEY akey

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>