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
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
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
