Encryption in SQL

create database DEMOSTART
Go
Use DEMOSTART


--Encryption through PassPhrase Method

create table CreditDetails
(CCardId int identity primary key,
CustId int not null,
creditCardNo varbinary(128)
)

insert into CreditDetails (CustId ,creditCardNo )
values(1,EncryptByPassPhrase('PassPhrase','4617123456781234'))

select * from CreditDetails

select c.CCardId ,c.CustId ,convert(varchar(30),DECRYPTBYPASSPHRASE ('PassPhrase',c.creditCardNo )) as [Credit card No] from CreditDetails c

--Enhansing encryption by authenticator

insert into CreditDetails (CustId ,creditCardNo )
values(2,EncryptByPassPhrase('PassPhrase','4617123456781234',1,'Fixed Value'))

--not work
select c.CCardId ,c.CustId ,convert(varchar(30),DECRYPTBYPASSPHRASE ('PassPhrase',c.creditCardNo )) as [Credit card No] from CreditDetails c
--need to use the internal value
select c.CCardId ,c.CustId ,convert(varchar(30),DECRYPTBYPASSPHRASE ('PassPhrase',c.creditCardNo ,1,'Fixed Value')) as [Credit card No] from CreditDetails c

--Through Symmetric key

Create Symmetric key CCardKey
with algorithm=triple_des
encryption by password='Pa##w0rd'

--Open The key

Open symmetric key CCardKey
Decryption by password='Pa##w0rd'

--Using the symmetric key

insert into CreditDetails (CustId ,creditCardNo )
values(2,EncryptByKey(Key_GUID('CCardKey'),'4617123456781234'))

--Decrypting the data
select c.CCardId ,c.CustId ,
convert(varchar(50),DecryptBYKey (creditCardNo)) as [Credit card No] from CreditDetails c

--Close The key

Close symmetric key CCardKey

--Working with Assymetric Key

Create Asymmetric key CCardKeyAsy
with Algorithm=RSA_512
encryption by password='Pa$$w0rd'



--Insert into our table
INSERT INTO CreditDetails (CustId,CreditCardNo) VALUES (1, ENCRYPTBYASYMKEY(ASYMKEY_ID('CCardKeyAsy'), '123456789'))

--Show what the encrypted form looks like
SELECT * FROM CreditDetails

--DecryptByAsymKey(KeyId, EncryptedText, AsymetricKeyPassword), returns byte array that needs to be re-cast to original type
--Also, very important, the password MUST be passed in as nvarchar
SELECT *, CONVERT(varchar(128), DECRYPTBYASYMKEY(ASYMKEY_ID('CCardKeyAsy'), creditcardno, N'Pa$$w0rd')) FROM CreditDetails


use master
Go

create master key encryption by password='Pa$$w0rd'
Go

Create certificate SQLCert with subject='SQLCert'
Go


use DEMOSTART
create database encryption key with algorithm=AES_128
encryption by server certificate SQLCert

Go

Alter database DEMOSTART
set encryption on