Monday 16 November 2015

SQL Symmetric Encryption TSQL

Recently my client wanted to create a password vault in SQL database to store SQL Server service account, SQL users and their respective passwords. I used symmetric key to create encryption for the encrypting the password, Find the T-SQL below to accomplish this.

Assumption:-

DB Name - SQLDBA
TableName - SQLAccounts

--********create password encrypted column*********
USE SQLDBA
GO
ALTER TABLE SQLAccounts
ADD EncryptedSQLPassword varbinary(MAX) NULL
GO



--********Create Master Key*********
USE master;
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';

--**********Create database Key*********
USE SQLDBA
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO

--*********Create self signed certificate*********
USE SQLDBA;
GO
CREATE CERTIFICATE SQLAccountCertificate
WITH SUBJECT = 'Protect SQL Password';
GO

--**********Create Symmetric Key***********
USE SQLDBA;
GO
CREATE SYMMETRIC KEY SQLAccountSymmetricKey
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE SQLAccountCertificate;
GO

--*********TSQL to Insert New row with encrypted Password**********
USE SQLDBA;
GO
OPEN SYMMETRIC KEY SQLAccountSymmetricKey
DECRYPTION BY CERTIFICATE SQLAccountCertificate;
GO
INSERT INTO SQLAccounts VALUES ('ServerName\Instance','SQLusername',EncryptByKey (Key_GUID('SQLAccountSymmetricKey'),'Password'))
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SQLAccountSymmetricKey;
GO

--*************TSQL to view decrypted Password**************
USE SQLDBA;
GO

OPEN SYMMETRIC KEY SQLAccountSymmetricKey
DECRYPTION BY CERTIFICATE SQLAccountCertificate;
GO
-- Now list the original ID, the encrypted ID
SELECT *,CONVERT(varchar, DecryptByKey(EncryptedSQLPassword)) AS 'EncryptedSQLPassword'
FROM dbo.SQLAccounts;
 -- Close the symmetric key
CLOSE SYMMETRIC KEY SQLAccountSymmetricKey;

--*********TSQL to update the encrypted column*************
USE SQLDBA;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SQLAccountSymmetricKey
DECRYPTION BY CERTIFICATE SQLAccountCertificate;
GO
UPDATE SQLAccounts
SET EncryptedSQLPassword = EncryptByKey (Key_GUID('SQLAccountSymmetricKey'),Password)
FROM dbo.SQLAccounts;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SQLAccountSymmetricKey;
GO