Data Encryption i SQL Server 2008


Som databasadministratör, är säkerhet en av de viktigaste områden att beakta när det gäller att skydda de databaser som du stöder. Vi använder olika metoder och teknik för att säkra våra data och databaser som brandväggar, certifikat och datakryptering. Med detta sagt även om vi har säkrat vår miljö, frågor som alltid kommer att vara raisesd om databas säkerhet. Även om vi har skyddat våra databaser, vad skulle hända om någon stjäl mdf fil eller om någon stjäl säkerhetskopian.

Även om det finns några sätt att kontrollera detta senario med lösningar från tredje part fram till SQL Server 2008 inte har native sätt att hantera detta problem. SQL Server 2008 introducerar en ny funktion som skyddar den databas som kallas Transparent Data Encryption – TDE som ger skydd för hela databasen.

•What is Transparent Data Encryption?
•Implementation of TDE
•Is my database secure now, as it says?
•What needs to be considered before enabling TDE?
•What is the impact when TDE is enabled?

Vad är Transparent Data Encryption?
Microsoft SQL Server 2008 införs ytterligare en nivå av kryptering, Transparent Data Encryption. TDE är full databas kryptering som inte begränsas till kolumner och rader, men skyddar både data-och loggfiler. Genomförandet av TDE på en databas är jämförelsevis mycket enkla och tydliga att de ansökningar som ansluter till den valda databasen. Det är inte kräver några ändringar i befintliga tillämpningar. Skyddet appliceras på datafiler och loggfiler samt säkerhetskopiorna. När TDE är aktiverat på en databas, återställande av en backup till en annan SQL Server-instans eller som kopplar datafiler till en annan SQL Server-instans kommer inte att tillåtas innan de certifikat som användes för att säkra de centrala databasen kryptering (DEK) är tillgänglig.

Den kryptering av TDE tillämpas på sidnivå. När aktiverat är sidor krypteras innan de skrivs till disken och dekrypteras innan de läses in i minnet. Det är viktigt att komma ihåg att kommunikationskanalen mellan SQL Server och klienten ansökan inte kommer att säkras och krypterat via TDE.

Transparent Data Encryption använder en databas Encryption Key (DEK) för kryptering av den databas som lagras i databasen Boot Record. Den DEK är säkrad genom ett intyg som lagras i master databasen. Alternativt kan DEK säkras med en asymmetrisk nyckel som finns i ett Hardware Security Module (HSM) med stöd av Extensible Key Management (EKM). Den privata nyckeln för certifikatet är krypterad med databasen huvudnyckel som en symmetrisk nyckel, som normalt skyddas av ett starkt lösenord. Observera att även om certifikat kan säkras genom ett lösenord, kräver TDE att certifikatet är säkras genom databasen huvudnyckeln. Databasen huvudnyckel skyddas av tjänst huvudnyckeln som är skyddad av personuppgiftsombudet API.

Genomförande av TDE
Som nämnts ovan, är genomförandet av TDE ganska enkelt. Här är ett exempel script för att möjliggöra TDE på en databas som kallas TestDatabase.

— If the master key is not available, create it.
USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE ‘%MS_DatabaseMasterKey%’)
BEGIN
        CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd4545’;
END
GO
— Create the certificate in the master database.
USE master;
GO
— Since ENCRYPTION BY PASSWORD is not mentioned, the private key of the certificate
— will be encrypted by database master key created above.
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name LIKE ‘%DEKCertificate%’)
BEGIN
        CREATE CERTIFICATE DEKCertificate WITH SUBJECT = ‘DEK Certificate’
END
GO
— Create Database Encryption Key (DEK) in the user database
USE TestDatabase
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID(‘TestDatabase’))
BEGIN      
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO
— Check whether the key is created
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
— This should return one row (or more if DEKs have been generated in other databases)
— with the encryption_state of 1 (1 = unencrypted).
— Set the DEK on in the TestDatabase.
ALTER DATABASE TestDatabase
SET ENCRYPTION ON
GO

— Check whether the encryption_state is changed to 3. It should be.
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys

De första två steg visar hur du creat de centrala databasen befälhavaren och intyg i master databasen. Observera att kryptering av lösenord inte anges med CREATE INTYG, därav den privata nyckeln i självsignerat certifikat kommer att säkras genom databasen huvudnyckeln. Nästa steg visar vägen för att skapa de DEK i TestDatabase. Kör koden. Det lägger till DEK till TestDatabase. Om certifikatet privata nyckel är skyddad med ett lösenord, kommer du få felmeddelande som nedan;

Msg 33101, Level 16, State 1, Line 4

Kan inte använda certifikat DEKCertificateTest “, eftersom den privata nyckeln inte finns eller inte skyddas av databasen huvudnyckeln. SQL Server kräver förmåga att automatiskt tillgång till den privata nyckeln för certifikatet användas för denna operation.

Den sys.dm_database_encryption_keys kan du se att DEKs har lagts till servern. Kolumnen encryption_state visar om DEK är i okrypterad följande stater, kryptering pågår, krypterad, viktiga förändringar som pågår och dekryptering pågår för värden på 1, 2, 3, 4, och 5. När du kör DMV innan du ställer in kryptering, visas staten som en, och när som, staten skall visades som 3. Klar. Den TestDatabase är nu helt säkrad.

Är min databas säker nu?
Även om vi har lyckats aktiverat TDE i vår databas, behöver vi se till att den är säkrad på alla nivåer. Vi kommer att göra två tester på detta. Först kommer vi att säkerhetskopiera databasen och försöka återställa säkerhetskopian på en annan SQL Server 2008 instans. Återställningen måste misslyckas om inte certifikatet som används för att säkerställa DEK: s privata nyckel finns i master databasen. För det andra kommer vi att försöka fästa MDF-och LDF-filer av TestDatabase i en annan instans. Det borde inte fungera också. Här är koden för testning.

— First step is backing up the TestDatabase.
BACKUP DATABASE [TestDatabase]
TO DISK = N’D:TestDatabaseFull.bak’
WITH NOFORMAT, NOINIT, NAME = N’TestDatabase-Full Database Backup’
GO
— Now connect with another SQL Server 2008 instance.
— Try to restore the backup we have taken, in the new instance.
— This statement should be failed.
RESTORE DATABASE [TestDatabase]
FROM DISK = N’D:TestDatabaseFull.bak’
WITH FILE = 1,
MOVE N’TestDatabase’ TO N’F:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATATestDatabase.mdf’,
MOVE N’TestDatabase_log’ TO N’F:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATATestDatabase_log.ldf’,
NOUNLOAD, STATS = 10
GO

De första stegen ryggen upp databasen. Den andra delen behöver köras i en annan SQL Server 2008 instans. När du försöker återställa säkerhetskopian i en annan instans av SQL Server, kommer du få ett felmeddelande nedan;

10 procent bearbetas.
20 procent bearbetas.
31 procent bearbetas.
41 procent bearbetas.
52 procent bearbetas.
62 procent bearbetas.
73 procent bearbetas.
83 procent bearbetas.
94 procent bearbetas.
100 procent bearbetas.

Msg 33111, Level 16, State 3, Line 1

Kan inte hitta server certifikat med tumavtryck 0 x8AD8C0A89476752FCC3D7A7005A2DCF546C38C68 “.
Msg 3013, Level 16, State 1, Line 1

Återställ databas är avslutande onormalt.

Du kommer att möta på samma fråga när du försöker bifoga databasen till en annan instans
USE [master]
GO
CREATE DATABASE [TestDatabase] ON
( FILENAME = N’F:TestTestDatabase.mdf’),
( FILENAME = N’F:TestTestDatabase_log.ldf’)
FOR ATTACH
GO

The result of above code is;
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint ‘0x8AD8C0A89476752FCC3D7A7005A2DCF546C38C68’.

Det fungerar. Vår databas är säkrad. Det enda sättet att återställa eller bifoga TestDatabase i en annan instans, lägga till samma certifikat i andra instans. Studera den kod som anges nedan;

— backup the certificate
— The private key will be encrypted by the password specified
BACKUP CERTIFICATE DEKCertificate TO FILE = ‘E:DEKCertificate.cert’
         WITH PRIVATE KEY
         (
                  FILE = ‘D:Instance1PrivateKey.key’,
                  ENCRYPTION BY PASSWORD = ‘Pa$$w0rd5454’
         )
— create the certificate in the second instance by using the backup
— Private key will be loaded from the saved file.
USE [master]
GO
CREATE CERTIFICATE DEKCertificate
         FROM FILE = ‘E:DEKCertificate.cert’
         WITH PRIVATE KEY (
                  FILE = ‘D:Instance1PrivateKey.key’
                  , DECRYPTION BY PASSWORD = ‘Pa$$w0rd5454’
                  )

Den första delen av koden backar upp certifikatet i en fil. Förbundet den privata nyckeln av intyget också. Lösenordet som anges i koden för kryptering av privata nyckeln. Den andra delen av koden måste köras i andra instans av SQL Server 2008. Det skapar ett certifikat med hjälp av backup certifikatet. När koden körs, kan du antingen återställa eller bifoga TestDatabase databasen till den nya instans.

Vad måste beaktas innan så att TDE?
Det finns få saker du behöver tänka på innan du aktiverar TDE på din databas. De är;

Har TDE skada genomfört katastrofplan för återställning?
Tänk på en enkel katastrofplan för återställning, säkerhetskopiering och återställning. Du kan ha utvecklat denna plan och det fungerar utan problem. Du aktiverat TDE, fortfarande inga problem, schemalagda jobb säkerhetskopiera dina databaser. Antag att servern börjat producera allvarliga fel som gjorde att du installerar OS och SQL Server igen. Du kan lätt gå för en ominstallation utan att tänka två gånger, eftersom du har din databas backup med er. Problemet kommer när databaserna återställs. Du kan ha full säkerhetskopiering av databaser som inte är krypterat format, kan du ha några affärsbeslut säkerhetskopior som vidtas efter det att TDE aktiverat, därav krypterad. Du har inte säkerhetskopian av certifikat du använde för TDE. Detta kommer att leda dig i en oväntad situation. Eftersom du inte har backup på det certifikat som används, kommer du inte att kunna återställa affärsbeslut säkerhetskopior.

Tänk om utvecklingen katastrofplan innan du aktiverar TDE. Om du har planen, se till att planen fungerar även efter så att TDE. Detta gäller inte bara för säkerhetskopiering och återställning strategi gäller det andra planer som log sjöfart och databasspegling också.

Finns det skrivskyddad fil grupper i din databas?
Om databasen har skrivskyddad fil grupper kommer TDE misslyckas. När jag testar detta, när TDE är aktiverad, kommer encryption_state aldrig 3 (krypterad) men 2 (kryptering pågår). Jag tyckte att SQL Server inte tillför några undantag när du kör den TDE koden. På något sätt, om du öppnar fönstret Egenskaper i databasen efter så TDE kommer du att se att värdet av egendomen Kryptering Enabled är satt till true. Låt oss testa detta med koden nedan:

— create a new database for testing TDE on readonly file groups
USE master
GO
CREATE DATABASE [TestDatabase2] ON PRIMARY
( NAME = N’TestDatabase2_Primary’, FILENAME = N’D:TestDatabase2_Primary.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG1_Default]
( NAME = N’TestDatabase2_FG1′, FILENAME = N’D:TestDatabase2_FG1.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [FG2_ReadOnly]
( NAME = N’TestDatabase2_FG2′, FILENAME = N’D:TestDatabase2_FG2.ndf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N’TestDatabase2_log’, FILENAME = N’D:TestDatabase2_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO

— Set the FG1_Default file group as the default one.
USE [TestDatabase2]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N’FG1_Default’)
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG1_Default] DEFAULT
GO

— Add a table to the default file group
USE [TestDatabase2]
GO
CREATE TABLE TestTable1 (Id int PRIMARY KEY, [Text] varchar(100))
GO
INSERT INTO TestTable1 VALUES (1, ‘hello’)

— Add a table to the FG2_ReadOnly file group
CREATE TABLE TestTable2 (Id int PRIMARY KEY, [Text] varchar(100))
ON [FG2_ReadOnly]
GO
INSERT INTO TestTable2 VALUES (1, ‘hello’)
GO

— Set the file group FG2_ReadOnly file group as READONLY
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N’FG2_ReadOnly’)
ALTER DATABASE [TestDatabase2]
MODIFY FILEGROUP [FG2_ReadOnly] READONLY
GO

— Create Database Encryption Key (DEK) in the user database
USE TestDatabase2
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID(‘TestDatabase2’))
BEGIN
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO

— Enable TDE on the database
ALTER DATABASE TestDatabase2
SET ENCRYPTION ON
GO

Koden skapar en databas först med tre datafiler som heter TestDatabase2_Primary, TestDatabase2_FG1 och TestDatabase2_FG2. Filen grupp FG1_Default är satt som standard fil gruppen och TestTable1 skapas i det. Den TestTable2 skapas i FG2_ReadOnly filen gruppen. Senare FG1_ReadOnly fil grupp markeras som skrivskyddad.

Slutligen är DEK skapas i TestDatabase2 och kryptering fastighet som sant. Alla uttalanden som genomförda. Om du fråga sys.dm_database_encryption_keys kommer du se att encryption_state av TestDatabase2 är 2 som säger att kryptering sker men inte slutförts. Jag är inte säker på orsaken till att inte kasta ett undantag när så TDE

Är FileStream datatyp används?
Databaser som filestream typ som används kan krypteras med hjälp av TDE men filströmmen uppgifter kommer inte att krypteras.

Vilka effekter när TDE är aktiverat?
Aktivera TDE i en databas effekter par saker;

Transaction log
När TDE är aktiverat SQL Server att Loggfilen innehåller ingen tydlig text data genom nollställning det. SQL Server börjar med nya VLF med en krypterad form.

Tempdb systemets databas
Detta kommer att krypteras automatiskt när du aktiverar TDE på någon databas. Detta kan införa dålig prestanda på icke-krypterade databaser som använder tempdb databas.

Log shipping och databasspegling
Om du aktiverat TDE på en databas att fartyg loggarna till en annan databas (medel, log shipping aktiverad databas), logga rederiet kommer att ske vid den sekundära databasen, om inte intyget finns i den sekundära servern. Jag testade TDE på en stock sjöfart aktiverad databas, fann att det inte snart TDE är aktiverat men startar arbeten böter när certifikatet som används för TDE är tillgänglig. BOL säger att möjliggöra TDE på en stock sjöfart aktiverad databas, krypterar både primära och sekundära databaser men när jag kollade, fann jag encryption_state av den sekundära databasen är 1 (okrypterat). Jag är inte helt säker på om anledningen kommer jag att utforska mer om detta i alla fall.

Komprimerade backup
Jag testade detta genom att ta en komprimerad säkerhetskopia på en TDE aktiverad databas verkar komprimering är inte mycket effektiv på en TDE aktiverad databas. Det är så jag testade;

— create a new database for testing compressed backup on TDE enabled database
USE master
GO
CREATE DATABASE [TestDatabase3] ON PRIMARY
( NAME = N’TestDatabase3′, FILENAME = N’D:TestDatabase.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB ),
LOG ON
( NAME = N’TestDatabase3_log’, FILENAME = N’D:TestDatabase3_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO

— Create a table and insert some records
USE TestDatabase3
GO
CREATE TABLE TestTable (Id int primary key, [Value] char(8000))
GO
INSERT INTO TestTable VALUES (1, ‘value1’)
INSERT INTO TestTable VALUES (2, ‘value1’)
INSERT INTO TestTable VALUES (3, ‘value1’)
INSERT INTO TestTable VALUES (4, ‘value1’)
INSERT INTO TestTable VALUES (5, ‘value1’)
INSERT INTO TestTable VALUES (6, ‘value1′)

— Backup the TestDatabase3 without compressing it
BACKUP DATABASE [TestDatabase3]
TO DISK = N’D:TestDatabase3Full.bak’
WITH NOFORMAT, NOINIT, NAME = N’TestDatabase3-Full Database Backup’
GO

— Backup the TestDatabase3 with  compression
BACKUP DATABASE [TestDatabase3]
TO DISK = N’D:TestDatabase3Full_Compressed.bak’
WITH NOFORMAT, NOINIT, NAME = N’TestDatabase3-Full Database Backup (Compressed)’, COMPRESSION
GO

— Now enable TDE on this
— Use the code we used with first example

— Backup the TestDatabase3 again (after TDE enabled) without compressing it
BACKUP DATABASE [TestDatabase3]
TO DISK = N’D:TestDatabase3Full_Encrypted.bak’
WITH NOFORMAT, NOINIT, NAME = N’TestDatabase3-Full Database Backup (encrypted)’
GO

— Backup the TestDatabase3 with compression
BACKUP DATABASE [TestDatabase3]
TO DISK = N’D:TestDatabase3Full_Encrypted_Compressed.bak’
WITH NOFORMAT, NOINIT, NAME = N’TestDatabase3-Full Database Backup (Encrypted and compressed’, COMPRESSION
GO
Koden skapar en databas och lägger in vissa poster i en tabell. Då databasen backas upp två gånger, en utan komprimering och en annan med komprimering. Det måste du aktivera TDE i databasen och kör resten som är samma kod för säkerhetskopiering vi använde innan du aktiverar TDE. Säkerhetskopiera filer storlekar;

Full backup before enabling TDE  1,765 KB
Full backup with compression before enabling TDE 134KB
Full backup after enabling TDE 1,765 KB 
Full backup with compression after enabling TDE  1,878 KB  
 
Du kan se skillnaden. Resultatet visar att komprimera backup filer för TDE aktiverat databaser är inte mycket effektiva. Den här skriv innehåller det jag testade på TDE. Jag kan ha missat något, kan du ha något som måste läggas till denna.

Khan sqldba -MCTS

Advertisements

8 thoughts on “Data Encryption i SQL Server 2008

  1. Svenskan i artikeln är katastrof, men väldigt bra lösning även om det var lite sådär med texten.

  2. My brother recommended I might like this blog. He was entirely right.

    This post truly made my day. You cann’t imagine just
    how much time I had spent for this information! Thanks!

  3. Wow, that’s what I was looking for, what a stuff! existing here at this blog, thanks admin
    of this site.

  4. Fantastic post but I was wanting to know if you could write a
    litte more on this topic? I’d be very thankful if you could elaborate a little
    bit more. Many thanks!

  5. I’m usually to blogging and i honestly appreciate your content regularly.
    This information has truly caught my interest.
    I’ll bookmark your site and keep on checking for more information.

  6. It’s a pity you don’t have a donate button! I’d without a doubt
    donate to this superb blog! I guess for now i’ll settle for bookmarking and
    adding your RSS feed to my Google account. I look forward to brand new updates and will talk about this website with my Facebook group.
    Chat soon!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s