November 2, 2015 at 11:52 am
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
November 2, 2015 at 12:12 pm
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/
November 2, 2015 at 1:07 pm
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)
+ 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply