USE SQL2008SBS
GO

--Review the symmetric keys in the database
SELECT * FROM sys.symmetric_keys
GO

--If you do not already have a database master key created, execute the following code
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<InsertStrongPassword>'
GO


--Different hash algorithms produce different hash values
DECLARE @HashValue varchar(100)
SELECT @HashValue = 'SQL Server'
SELECT HashBytes('MD5', @HashValue)
SELECT @HashValue = 'SQL Server'
SELECT HashBytes('SHA1', @HashValue)
GO

--Hash values are case sensitive
DECLARE @HashValue varchar(100)
SELECT @HashValue = 'sql'
SELECT HashBytes('SHA1', @HashValue)
SELECT @HashValue = 'SQL'
SELECT HashBytes('SHA1', @HashValue)
GO

CREATE TABLE SalaryHashes
(Salary     INT             NOT NULL,
MD2Hash     VARBINARY(500)  NOT NULL,
MD4Hash     VARBINARY(500)  NOT NULL,
MD5Hash     VARBINARY(500)  NOT NULL,
SHAHash     VARBINARY(500)  NOT NULL,
SHA1Hash    VARBINARY(500)  NOT NULL)

DECLARE @salary         INT,
        @salarylimit    INT

SET @salary = 10000
SET @salarylimit = 300000

WHILE @salary <= @salarylimit
BEGIN
    INSERT INTO SalaryHashes
    (Salary, MD2Hash, MD4Hash, MD5Hash, SHAHash, SHA1Hash)
    SELECT @salary, Hashbytes('MD2',cast(@salary as varchar(6))), Hashbytes('MD4',cast(@salary as varchar(6))), Hashbytes('MD5',cast(@salary as varchar(6))), 
            Hashbytes('SHA',cast(@salary as varchar(6))), Hashbytes('SHA1',cast(@salary as varchar(6)))

    SET @salary = @salary + 10
END

SELECT Salary, MD2Hash, MD4Hash, MD5Hash, SHAHash, SHA1Hash
FROM SalaryHashes
GO

--Symmetric keys
CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = RC4
    ENCRYPTION BY PASSWORD = '<InsertStrongPassword>'
GO

SELECT * FROM sys.symmetric_keys
GO

CREATE TABLE SymmetricKeyDemo
(ID             int             IDENTITY(1,1),
PlainText       varchar(30)     NOT NULL,
EncryptedText   varbinary(80)   NOT NULL)
GO

--Symmetric key must be opened before being used
OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = '<InsertStrongPassword>'
GO

INSERT INTO SymmetricKeyDemo
(PlainText, EncryptedText)
VALUES('SQL Server', EncryptByKey(Key_GUID('MySymmetricKey'),'SQL Server'))
GO

SELECT ID, PlainText, EncryptedText, cast(DecryptByKey(EncryptedText) AS varchar(30))
FROM SymmetricKeyDemo
GO

CLOSE SYMMETRIC KEY MySymmetricKey
GO

--Certificates
CREATE TABLE CertificateDemo
(ID             int             IDENTITY(1,1),
PlainText       varchar(30)     NOT NULL,
EncryptedText   varbinary(500)   NOT NULL)
GO

CREATE CERTIFICATE MyCert AUTHORIZATION dbo
    WITH SUBJECT = 'Test certificate'
GO

SELECT * FROM sys.certificates
GO

INSERT INTO CertificateDemo
(PlainText, EncryptedText)
VALUES('SQL Server',EncryptByCert(Cert_ID('MyCert'), 'SQL Server'))
GO

SELECT ID, PlainText, EncryptedText, CAST(DecryptByCert(Cert_Id('MyCert'),
    EncryptedText) AS varchar(max))
FROM CertificateDemo
GO

--Transparent data encryption
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<InsertStrongPassword>';
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'TDE Certificate'
GO
USE SQL2008SBS
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE SQL2008SBS
SET ENCRYPTION ON
GO

