[cc lang=”SQL”]
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
[/cc]
0 Comments.