--TABLE SCRIPT
CREATE TABLE [dbo].[tblLogin](
[Id]
[int] IDENTITY(1,1) NOT NULL,
[firstName]
[varchar](50) NULL,
[middleName]
[varchar](50) NULL,
[lastName]
[varchar](50) NULL,
[email]
[varchar](50) NULL,
[Password]
[varchar](50) NULL,
[Pass_encrypted]
[varbinary](max) NULL,
CONSTRAINT [PK_tblLogin] PRIMARY
KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--CHECK SERVER SERVICE MASTER KEY
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';
--CHECK DATABASE MASTER KEY
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##';
--CREATE DATABASE MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pass@123';
--CREATE SELF SIGNED SQL SERVER
CERTIFICATE
CREATE CERTIFICATE
MyCertificate WITH SUBJECT
= 'Protect COLUMN Data';
--CHECK SQL SERVER CERTIFICATE
SELECT * FROM sys.certificates WHERE name LIKE '%MyCertificate%'
--SQL Server Symetric Key
CREATE SYMMETRIC KEY MySymmetricKey
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE MyCertificate;
--Change Table Schema (create new column with data type
varbinary to encrypt column data)
ALTER TABLE tblLogin
ADD Pass_encrypted varbinary(MAX) NULL
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY
CERTIFICATE MyCertificate;
GO
UPDATE tblLogin
SET Pass_encrypted =
EncryptByKey (Key_GUID('MySymmetricKey'),Password)
FROM tblLogin;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY MySymmetricKey;
--Remove Old Column
ALTER TABLE tblLogin
DROP COLUMN Password;
--Reading the Encrypted Data
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY
CERTIFICATE MyCertificate;
GO
-- Now list the original ID, the
encrypted ID
SELECT Pass_encrypted AS
'Encrypted Password',
CONVERT(varchar, DecryptByKey(Pass_encrypted)) AS 'Decrypted Password'
FROM dbo.tblLogin;
-- Close the symmetric key
CLOSE SYMMETRIC KEY MySymmetricKey;
--Adding record into table
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY
CERTIFICATE MyCertificate;
-- Performs the update of the
record
INSERT INTO dbo.tblLogin(Pass_encrypted)
VALUES (EncryptByKey( Key_GUID('MySymmetricKey'), CONVERT(varchar,'pass@vedp') ) );
--Accessing the Encrypted data
Execute as
user='dbo'
SELECT Pass_encrypted AS
'Encrypted Password',
CONVERT(varchar, DecryptByKey(empId_encrypted)) AS 'Decrypted Password'
FROM dbo.tblLogin;
--Grant Permission to users
GRANT VIEW DEFINITION ON SYMMETRIC KEY::MySymmetricKey TO
dbo;
GO
GRANT VIEW DEFINITION ON Certificate::MyCertificate
TO dbo;
--Clean Up Database (Symmetric
keys, certificates etc)
/* Clean up database */
GO
CLOSE SYMMETRIC KEY MySymmetricKey
GO
DROP SYMMETRIC KEY MySymmetricKey
GO
DROP CERTIFICATE
MyCertificate
GO
DROP MASTER KEY
GO
Comments