SQL Server Encryption Example

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



-- Create database Key
USE VAS_SeedScriptTest;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO


-- Create self signed certificate
USE VAS_SeedScriptTest;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO



-- Create symmetric Key
USE VAS_SeedScriptTest;
GO
CREATE SYMMETRIC KEY SymmetricKey1
 WITH ALGORITHM = AES_128
 ENCRYPTION BY CERTIFICATE Certificate1;
GO


USE VAS_SeedScriptTest;
GO
ALTER TABLE CompsType
ADD CompsTypeNameEncrypted varbinary(MAX) NULL
GO



-- Populating encrypted data into new column
USE VAS_SeedScriptTest;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE CompsType
SET CompsTypeNameEncrypted = EncryptByKey (Key_GUID('SymmetricKey1'),CompsTypeName)
FROM dbo.CompsType;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO




USE VAS_SeedScriptTest;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID
SELECT CompsTypeId, CompsTypeNameEncrypted AS 'Encrypted Type Name',
CONVERT(NVARCHAR(250), DecryptByKey(CompsTypeNameEncrypted)) AS 'Decrypted Type Name'
FROM dbo.CompsType;

 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO





USE VAS_SeedScriptTest;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
-- Performs the update of the record
INSERT INTO dbo.CompsType
        ( CompsTypeName ,
          IsActive ,
          DateCreated ,
          CompsTypeNameEncrypted
        )
VALUES ('Rent Approch',1,GETDATE(), EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(NVARCHAR(250),'Rent Approch') ) );   
GO






--Grant Permissions to the Encrypted Data
--Permissions can be granted to a set of users to decrypt and read data using the commands below.

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO mjawad;
GO
GRANT VIEW DEFINITION ON Certificate::Certificate1 TO mjawad;
GO