Encryption of tables

Question

Encryption of tables

page853

Mr or Mrs. 500

Points: 580
More actions
November 2, 2015 at 11:52 am

#304824

Hi
wonder if anyone can point I=me in the direction of how to encrypt a either columns or tables in SQL server (2008 R2).
Do I need to encrypt a whole table of can I encrypt certain columns of a table ?
Thnks in adavnce

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27056 More actions · Answer 1

https://msdn.microsoft.com/en-US/library/ms179331(v=sql.100).aspx

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Lowell SSC Guru Points: 323518 More actions · Answer 2

typically, you look at at a specific table, and decide which specific columns need to be encrypted, either because they contain personally identifying information, health information, or sensitive info like credit card info.

here's an old example i have.

First pretend you have a preexisting table, and your boss says "OMG, that company in the news got hacked, lets encrypt our data! encrypt EVERYTHING."

IF OBJECT_ID('[dbo].[Patients]') IS NOT NULL

DROP TABLE [dbo].[Patients]

GO

CREATE TABLE [dbo].[Patients] (

[ID] INT IDENTITY(1,1) NOT NULL,

[ExternalID] VARCHAR(30) NOT NULL,

[FirstName] VARCHAR(50) NULL,

[LastName] VARCHAR(50) NULL,

[CreditCardNumber] VARCHAR(30) NULL,

[CreditCardExpiration] DATE NULL,

[CreditCardCVS] VARCHAR(5) NULL,

[Gender] CHAR(1) NULL,

[Address1] VARCHAR(55) NULL,

[City] VARCHAR(15) NULL,

[State] VARCHAR(2) NULL,

[ZipCode] INT NULL,

CONSTRAINT [PK_Customers_ID] PRIMARY KEY CLUSTERED ([ID] asc))

First you have to explain that if you encrypt EVERYTHING, it's unusable, you cannot search for lastname, address, etc anymore. someone needs to see how the biz will be impacted, if you encrypt the columns they used to always search on.

you will cripple the company/performance if you try to decrypt the entire table, and then search for a plain string.

you have to get used to the idea that you will have to search for exact matches..not lastname like 'Mc%' but lastname = 'McCoy'; you need to evaluate how many things need to be modified to change that.

that gets done by encrypting the value sent instead, and getting exact matches...you'll end up seeing if you can add an index on those columns (the 900 byte rule)

WHERE LastName = EncryptByCert(Cert_ID('LowellDataSecurityCertificate'), 'McCoy')

so back to the issue,you select which items you need to encrypt, and understand that the business needs to start searching on something that uniquely identifies the record, without compromising PII.

In my Patients Table, either PatientId or ExternalID might qualify, for example.

Lets get some fake data in place to play with:

--some fake sample data.

;With MYFirstNames(FName) AS

(

Select 'Leonardo' UNION ALL

Select 'Brad' UNION ALL

Select 'Arnold' UNION ALL

Select 'Mark' UNION ALL

Select 'Matt' UNION ALL

Select 'Bruce'

),

MyLastNames(LName) AS

(

Select 'DeCaprio' UNION ALL

Select 'Pitt' UNION ALL

Select 'Schwarzenegger' UNION ALL

Select 'Wahlberg' UNION ALL

Select 'Damon' UNION ALL

Select 'Willis'

),

AnsiColors([TheColor])

AS

(

SELECT 'Ivory' UNION ALL

SELECT 'MediumOrchid' UNION ALL

SELECT 'DarkCyan' UNION ALL

SELECT 'PaleVioletRed' UNION ALL

SELECT 'MediumPurple'

)

INSERT INTO Patients([FirstName],[LastName],[CreditCardNumber],[CreditCardExpiration],[CreditCardCVS],[Gender],[Address1],[City],[State],[ZipCode])

SELECT TOP 10

T1.FName,

T2.LName,

RIGHT('0000' + CONVERT(varchar,(ABS(CHECKSUM(NEWID()))%9999+1)),4)

+ RIGHT('0000' + CONVERT(varchar,(ABS(CHECKSUM(NEWID()))%9999+1)),4)

+ RIGHT('0000' + CONVERT(varchar,(ABS(CHECKSUM(NEWID()))%9999+1)),4) As CreditCard,

DATEADD(week, ABS(CHECKSUM(NEWID()))% 36,getdate()),

ABS(CHECKSUM(NEWID()))% 1000 + 1,

'M' As Gender ,

CONVERT(varchar,ABS(CHECKSUM(NEWID()))%50000+1) + ' ' + T3.TheColor + CASE ABS(CHECKSUM(NEWID()))% 3 WHEN 0 THEN ' Street' WHEN 1 THEN ' Avenue' ELSE ' Road' END As Address1,

CASE ABS(CHECKSUM(NEWID()))% 5 WHEN 0 THEN ' Hollywood' WHEN 1 THEN ' Plantation' WHEN 2 THEN ' Davie' WHEN 3 Then 'Fort Lauderdale' ELSE ' Sunrise' END As City,

'FL' AS State,

33300 + ABS(CHECKSUM(T3.TheColor))% 100 AS ZipCode

FROM MYFirstNames T1

CROSS JOIN MyLastNames T2

CROSS JOIN AnsiColors T3

ORDER BY NEWID()

Now, i have experience with using Certificates, so that's what my example will assume.

encryption by certificate will require me to replace my varchar/nvarchar columns with varbinary columns that contain encrypted binary values instead.

let me create a certificate.

:

--#################################################################################################

--database master key ALWAYS has an Id of 101

--#################################################################################################

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)

BEGIN

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AsG00dOf@Pa$$w0rdAsAny'

END

--#################################################################################################

--create our certificate.

--#################################################################################################

IF NOT EXISTS(SELECT *

FROM sys.certificates

WHERE name = 'LowellDataSecurityCertificate')

BEGIN

CREATE CERTIFICATE LowellDataSecurityCertificate WITH SUBJECT = 'Lowell DataSecurity Certificate', EXPIRY_DATE = '12/31/2024'

PRINT 'LowellDataSecurityCertificate Created'

END

ELSE

BEGIN

PRINT 'LowellDataSecurityCertificate Already Exists.'

END

GO

Now that that is in place, i can start modifying the table:

rename the original columns, and add the new columns with the right names.

--Now we know we need to encrypt the columns.

--rename the old, and add new ones! also, while we are at it

EXEC sp_rename 'dbo.Patients.FirstName', 'FirstNameOrig', 'COLUMN';

EXEC sp_rename 'dbo.Patients.LastName', 'LastNameOrig', 'COLUMN';

EXEC sp_rename 'dbo.Patients.CreditCardNumber', 'CreditCardNumberOrig', 'COLUMN';

EXEC sp_rename 'dbo.Patients.Address1', 'Address1Orig', 'COLUMN';

EXEC sp_rename 'dbo.Patients.City', 'CityOrig', 'COLUMN';

ALTER TABLE Patients ADD FirstName varbinary(max);

ALTER TABLE Patients ADD LastName varbinary(max);

ALTER TABLE Patients ADD CreditCardNumber varbinary(max);

ALTER TABLE Patients ADD LastFourCC VARCHAR(4)

ALTER TABLE Patients ADD Address1 varbinary(max);

ALTER TABLE Patients ADD City varbinary(max);

ok, so now that there is columns, i can update using the certificate.

--migrate the data using our certificate!

UPDATE Patients

SET

[FirstName] = EncryptByCert(Cert_ID('LowellDataSecurityCertificate'), [FirstNameOrig]),

[LastName] = EncryptByCert(Cert_ID('LowellDataSecurityCertificate'), [LastNameOrig]),

[CreditCardNumber] = EncryptByCert(Cert_ID('LowellDataSecurityCertificate'), [CreditCardNumberOrig]),

[LastFourCC] = RIGHT(CreditCardNumberOrig,4),

[Address1] = EncryptByCert(Cert_ID('LowellDataSecurityCertificate'), [Address1Orig]),

[City] = EncryptByCert(Cert_ID('LowellDataSecurityCertificate'), [CityOrig])

at this point, i don't believe anything i didn't see, so a simple SELECT * FROM Patients lets me see the encryption populated those new columns.

once i'm satisfied, I've got to prove to myself i can get the old values back:

--prove we can decrypt it!

SELECT

Cast(DECRYPTBYCERT(CERT_ID('LowellDataSecurityCertificate'), P.FirstName) AS VARCHAR(100)) AS FirstName,

Cast(DECRYPTBYCERT(CERT_ID('LowellDataSecurityCertificate'), P.LastName) AS VARCHAR(100)) AS LastName,

Cast(DECRYPTBYCERT(CERT_ID('LowellDataSecurityCertificate'), P.CreditCardNumber) AS VARCHAR(100)) AS CreditCardNumber,

Cast(DECRYPTBYCERT(CERT_ID('LowellDataSecurityCertificate'), P.[Address1]) AS VARCHAR(100)) AS [Address1],

Cast(DECRYPTBYCERT(CERT_ID('LowellDataSecurityCertificate'), P.[City]) AS VARCHAR(100)) AS [City]

FROM Patients P

ok, i'm happy, everything is going according to plan

so it's time to completely remove the unencrypted columns.

--DROP the unencrypted columns!

ALTER TABLE Patients DROP COLUMN [FirstNameOrig]

ALTER TABLE Patients DROP COLUMN [LastNameOrig]

ALTER TABLE Patients DROP COLUMN [CreditCardNumberOrig]

ALTER TABLE Patients DROP COLUMN [Address1Orig]

ALTER TABLE Patients DROP COLUMN [CityOrig]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!