content top
Transparent Data Encryption ב SQL 2016 הלכה למעשה ל DBA ואנשי האבטחה

Transparent Data Encryption ב SQL 2016 הלכה למעשה ל DBA ואנשי האבטחה

TDE מבוא

את המאמר מלווה script בסוף הפוסט, לשימושכם .
ל SQL Server יש מספר מנגנוניים להגנה על המידע אבל החשוב ביותר הוא Transparent Data Encryption ובקיצור TDE . זהו feature שקיים מ SQL 2008 . במאמר זה נסקור בקצרה מה זה TDE , איך זה עובד , איך מאפשרים אותו , מה המגבלות ומה שופר ב SQL 2016 וב SQL 2014.

TDE מאפשר הצפנה של קבצי ה Data files של SQL Server ו SQL Azure. בד"כ למערכת SQL מאובטחת יש Security checklist שכולל הרשאות ע"פ עיקרון של מינימום הרשאות נדרשות , מנגנון Audit ,בדיקות SQL injection ועוד . SQL יושב בד"כ מאחורי Fire Wall. אבל אין הגנה מפני מי שנכנס לחדר שרתים וגונב מדיה או תוקף שיש לו הרשאות למחיצה של SQL Serve בה מאוחסנים קבצי Data ( MDF) ו Log (LDF) או קובץ גיבוי . התוקף יכול פשוט להעתיק את הקבצים , לבצע attach לקבצים ו\או לבצע שחזור מתוך קובץ הגיבוי בשרת SQL אחר , בו יש לו הרשאות Sysadmin . בצורה כזו תהיה לו גישה למידע . הפתרון : הצפנה של הקבצים בעזרת מפתח , שמוגן ע"י Certificate אבל גם את זה יש לתכנן בזהירות . אם פורץ ינסה לבצע attach לקובצי ה DB הוא לא יצליח , כי הוא יצטרך לבצע decrypt ל Database Encryption key ( Key) שמוגן ע"י certificate שנמצא ב master DB בשרת בו בוצעה ההצפנה ל DB . אם הפורץ ינסה לפתוח את הקבצים בעזרת עורך , הטקסט יהיה מוצפן ולא קריא . אני אדגים בהמשך .

מה התכונות העיקריות של TDE :

· ההצפנה נעשית ע"י Database Encryption key שמאוחסן ב Database Boot Record כדי שהיה זמין בזמן ביצוע Recovery . Database Encryption key הוא Symmetric key שמאוחסן ב master DB בשרת MSSQL או Asymmetric key שמוגן ע"י AKM Model .

· TDE כאמור מגן על קבצי ה Data וה log , מה שנקרא Data at rest , וזה ע"פ תקנות וחוקים בשוק האבטחה .

· שימוש ב TDE מצפין גם את הגיבויים של DB המוצפן .

· TDE מאפשר להשתמש באלגוריתם של הצפנה מסוג AES או 3DES בלא צורך לשנות את הקוד של האפליקציה בצורה שקופה.

· ההצפנה נעשית ברמה של Page לפני הכתיבה לדיסק ו Decryption לפני הקריאה של הדף לזיכרון וכתיבתו ל Buffer Pool .

· נתמך בגרסת Enterprise בלבד .

בוא נראה דוגמא מעשית בנושא .מה שיפה בשימוש ב TDE שאין צורך לבצע שינויי סכימה או לתת הרשאות או ליצור roles כלשהו או לבצע שינוי באפליקציה .

קודם כול נבצע גיבוי ל DB , כדי שנוכל לחזור אחורה במקרה כשל . אחרי שהגיבוי יסתיים בהצלחה . בוא נראה מה אפשר לעשות עם הגיבוי לפני שנמשיך הלאה . נפתח את הגיבוי WideWorldImporters-Full.bak עם התוכנה Freeware Hex Editor XVI32 אותו ניתן להוריד בקישור

http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm

בתחילה נראה הרבה ספרות לא ברורות . בוא נחפש אחר מילים שאנו מניחים שיהיו בקובץ כמו המילה USB . נשתמש באופציה Find מהתפריט ונחפש אחר המילה USB ואחריה רווח .

image

מיד לאחר החיפוש נמצא המילה USB .המילה מופיעה בטבלה Warehouse בסכמה StockItems. זאתי דוגמא פשוטה אבל ממחישה עד כמה קל לתחקר את הנתונים ללא TDE .

image

נחזור כעת לדוגמא שלנו . ניצור כעת MASTER KEY ב master DB ונגן עליו בעזרת סיסמא .

USE master;

GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P$ssword';

בשלב הבא ניצור certificate ב master DB , שמוצפן ע"י ה master key ב master DB שיצרנו. יש לספק שם ו Subject בעת היצירה .

CREATE CERTIFICATE WICert

WITH SUBJECT = ' WideWorldImporters Certificate';

נגבה מיד את ה certificate לדיסק ביחד עם ה private key ונעביר את הגיבוי למקום בטוח . אם נצטרך לשחזר את ה DB שלנו בשרת אחר אנו נזדקק ל certificate וה- Private key. ה certificate מצפין את המפתח של ה DB אותו נצפין . אם נאבד את ה master db לא נוכל לשחזר את ה DB המוצפן .

USE master

GO


BACKUP CERTIFICATE WICert TO FILE = 'T:\Cert_Backups\HSCert'

WITH PRIVATE KEY (

FILE = 'T:\Cert_Backups\HSCertKey',

ENCRYPTION BY PASSWORD = 'P$ssword');

נוודא שאין אף משתמש שמחובר ל DB ועכשיו ניצור בתוך ה DB Database Encryption key ובקיצור DEK . DEK ישמש לביצוע את הצפנה בכדי להגן על הקבצים הפיזיים של ה DB. הפרמטרים של המפתח הם אלגוריתם ההצפנה ע"י הפרמטר With ALGORITHM . במקרה שלנו AES_256 .AES הוא אלגוריתם ההצפנה החזק ביותר כיום שמאושר ע"י הרשויות NSA ו FBI בארה"ב . האפשרויות הן : 128AES_ , AES_192 , AES_256 .ככול שהמפתח ארוך יותר האלגוריתם חזק יותר . מומלץ לעבוד עם AES_256 . במידה ויש בעיית ביצועים נעבוד עם AES_192 . לא מומלץ לעבוד עם AES_128 בגלל ניסיונות פריצה של AES_128 של פצחנים שדווחו לאחרונה .פרמטר נוסף הוא ECRYPTION BY בוא מגדירים לפי מה נעשית ההצפנה : ה certificate או המפתח האסימטרי ב master DB . במקרה שלנו נשתמש ב certificate שיצרנו מקודם .

USE WideWorldImporters;

GO


CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE WICert;

השלב האחרון הוא לאפשר את ה TDE ברמת DB . ברגע שזה מאופשר מתבצע סריקה של קבצי ה data וה transaction log . משך הסריקה תלוי בגודל ה DB .

ALTER DATABASE WideWorldImporters SET ENCRYPTION ON;

אחרי שהתהליך יסתיים, העמודה encryption_state ב sys.dm_dtabase_encryption_keys צריכה להיות שווה ל 3 עבור ה DB שלנו . שימו לב שגם tempdb מוצפן . אם היינו מקבלים 2 המשמעות הוא שזה עדיין בתהליך .

SELECT DB_NAME(database_id) AS database_name, encryption_state,

key_algorithm, key_length, encryptor_type

FROM sys.dm_database_encryption_keys ;

פלט :

image

בוא נבצע גיבוי נוסף ל DB , נפתח שוב את הגיבוי בעזרת העורך xvi32 . כעת נראה שכול התווים נראים אחרת ואם נבצע חיפוש בעזרת העורך כמו מקודם לא נמצא את התווים .

אם ניקח את הגיבוי המוצפן ל Instance אחר וננסה לשחזר אותו . נקבל את ההודעה שה certificate ברמת ה master DB לא קיים והשחזור יכשל . אני אדגם זאת בהמשך .

לבסוף נתחבר כמשתמשים שונים ל DB , ונראה שהנתונים חוזרים מהטבלה ו Encryption ו decryption נעשה באופן שקוף למשתמש .

USE WideWorldImporters

GO


SELECT top 10 * from [Sales].[Orders];

ביטול TDE

כמובן שאפשר לבטל את ה TDE. בוא נראה איך עושים את זה . נבטל את ההצפנה של ה DB .הנה קטע הקוד המתאים :

use master


ALTER DATABASE WideWorldImporters

SET ENCRYPTION OFF;

GO


SELECT database_id, encryption_state

FROM sys.dm_database_encryption_keys;

GO

רק כאשר נקבל 1 בעמודה encryption_state עבור ה database_id שלנו בשאילתה הקודמת , תהליך ביטול ההצפנה הסתיים . בדוגמא : הנה הפלט כאשר תהליך decryption לא הסתיים . encryption_state שווה ל 5 עבור database_id של WideWorldImporters השווה ל 17.

image

כעת "נזרוק" את ה DMK באופן הבא :

USE WideWorldImporters;

GO


DROP DATABASE ENCRYPTION KEY;

GO

אם תהליך ה decryption לא הסתיים , וננסה לזרוק את DMK נקבל את ההודעה השגיאה הבאה :

Msg 33105, Level 16, State 1, Line 80

Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.

עכשיו אם ניגש ל master db וננסה לזרוק את ה master key באופן הבא :

use master


DROP MASTER KEY ;

נקבל את הודעת השגיאה הבאה:

Msg 15580, Level 16, State 1, Line 90

Cannot drop master key because certificate 'WICert' is encrypted by it.

ולכן מה שנעשה הוא ל"זרוק" את ה certificate שיצרנו מקודם ואז לזרוק את ה master key באופן הבא:

use master;


Drop certificate WICert ;

DROP MASTER KEY ;

שימו לב : גם אם בטלנו TDE מומלץ לשמור את הגיבויים ה master Key וה certificate שהשתמשנו מהם מקודם .

שחזור בסיס נתונים עם TDE לשרת ללא TDE

עכשיו נניח ונרצה לשחזר את הגיבוי המוצפן שלנו WideWorldImporters_TDE.bak לשרת אחר או לשרת שלנו , בו ביטלנו את ההצפנה .השחזור לא יצלח . נקבל את ההודעה הבאה :

Msg 33111, Level 16, State 3, Line 2

Cannot find server certificate with thumbprint '0x8C02EDC38EB8AA069FD2C4D4A74CC1B8E716524D'.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

אז מה עושים ? מיצרים master key בשרת היעד :

USE master;

GO


CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P$ssword';

GO

בשרת היעד מיצרים מחדש את ה certificate מתוך ה certificate וה private key מהגיבוי שיצרנו מקודם ל certificate בשרת המקור. שימו לב שאין פקודה לשחזור של certificate . הסיסמא של ה- CERTIFICATE צריכה להיות זהה לסיסמה בזמן הגיבוי של ה CERTIFICATE המקורי .

CREATE CERTIFICATE WICert

FROM FILE = 'T:\Cert_Backups\HSCert'

WITH PRIVATE KEY

(

FILE = 'T:\Cert_Backups\HSCertKey',

DECRYPTION BY PASSWORD = 'P$ssword'

);

GO

בעזרת השאילתה הבאה נוודא שה certificate נטען :

select

name

, certificate_id

, pvt_key_encryption_type_desc

, subject

, expiry_date

, start_date

, thumbprint

, pvt_key_last_backup_date

from sys.certificates where name like '%WICert%'

פלט :

image

לסיום נוכל לבטל את ה TDE באותו אופן כמו מקודם .

הצפנת גיבויים

ב SQL 2012 הדרך היחידה להצפין גיבויים היא ע"י שימוש ב TDE . החל מ SQL 2014 ניתן לבצע הצפנה לגיבויים גם אם ה DB עצמו לא מוצפן . בדוגמא שלנו מכיוון שיצרנו כבר Master Key ו Certificate נשתמש בהם ונגבה את ה DB WideWorldImportersDW ,שלא מוצפן באופן הבא . בפקודת הגיבוי נכניס את שם ה certificate ואלגוריתם הצפנה .

BACKUP DATABASE WideWorldImportersDW

TO DISK = 'T:\mssql_dump\WideWorldImportersDW_TDE.bak'

WITH ENCRYPTION

(ALGORITHM = AES_256, SERVER CERTIFICATE =WICert), FORMAT ,init;

דגשים :

א. אם לא יצרנו Master Key ו certificate יש ליצור אותם מראש לפני הגיבוי כפי שהודגם מקודם.

ב. יש לבצע גיבוי אחד בלבד פר device או פר קובץ ולבצע format ו init לגיבוי כל פעם מחדש, ואז לגבות את ה db וה certificate כמו בדוגמא . לא ניתן לבצע מספר גיבויים של certificate לאותו קובץ .

סיכום TDE

בוא נדבר קצת על החסרונות של TDE :

· כשעובדים עם TDE יש ליצור מפתח שהוא תלוי ב master key ו certificate או מפתח אסימטרי ב master DB . נוכל ליצור DEK אחד פר DB . זאת אומרת שכול ה users בכול בסיסי הנתונים השונים משתמשים באותו master key שב master DB .

· יש השפעה על הביצועים של tempdb . כאשר מצפינים DB מסוים גם הוא מוצפן . tempdb משמש את כל ה DBS לטובת שימוש ב cursors , טבלאות זמניות ועוד . זאת אומרת שכול בסיסי הנתונים , שלא משתמשים בהצפנה , אבל משתמשים ב tempdb יצטרכו לבצע encryption ו decryption , למרות שהם לא משתמשים בהצפנה . ההצפנה של tempdb שקופה עבורם . מיקרוסופט מעריכה את ירידה בביצועים בין 2 עד 5 אחוז בתלות בפעילות ונפח הביצועים .

· לא מומלץ לבצע גם TDE וגם Database Compression , מניסיון הם לא עובדים טוב ביחד .

· ב SQL 2016 יש כעת תמיכה ב TDE עבור Memory-optimized OLTP tables .

· TDE לא יכול לספק הצפנה ברזולוציה כמו cell-level encryption .

כמו שאמרנו ההצפנה היא רק ברמת קבצים ""data at rest , ברמת דפים על הקבצים שנמצאים על הדיסק בלבד . כלומר הצפנה של הנתונים כאשר הקבצים נפתחים ע"י עורך text כמו שראינו. לעומת זאת אם משתמש יש לו גישה לטבלה המידע לא מוצפן . החדשות הטובות שלמיקרוסופט יש פתרון למידע ב"תנועה" שנקרא Always Encrypted . על כך במאמרים הבאים .

סקריפט מצורף:


/**************************************************************************************

Script Name        SQL_Server_2016_TDE_Demo.sql

Purpose :        SQL Server 2016 TDE DEMO 

Author:            David Itshak 

Email :            shaked19@gmail.com        

Modification History:

Date              Who              What


=============  ===============  ===================================================================

15/10/2015        David    itshak 


**************************************************************************************/



BACKUP DATABASE WideWorldImporters TO DISK = N'T:\mssql_dump\WideWorldImporters_demo.bck' with init ;

--1. BACKUP DATABASE HappyScoopers TO DISK = N'C:\Pluralsight\DB Backups\HappyScoopers.bak' WITH COMPRESSION;


-- Create a database master key and a certificate in the master database.

-- 2. Create key in master db

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P$ssword';


-- 3.  Create the certificate in master db

CREATE CERTIFICATE WICert

    WITH SUBJECT = ' WideWorldImporters  Certificate';



-- 4. Create a backup of the server certificate in the master database.  

-- The following code stores the backup of the certificate and the private key file in T:\Cert_Backups.  

-- The default data location for this instance of SQL Server   (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).  

USE master

GO

BACKUP CERTIFICATE WICert TO FILE = 'T:\Cert_Backups\HSCert'

    WITH PRIVATE KEY (

                        FILE = 'T:\Cert_Backups\HSCertKey',

                        ENCRYPTION BY PASSWORD = 'P$ssword'

                        );



-- 5: Create database encryption key which is protected by the server certificate in the master database. 

USE WideWorldImporters;

GO

CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_256

    ENCRYPTION BY SERVER CERTIFICATE WICert;


--  6: Encrypt the database

ALTER DATABASE WideWorldImporters SET ENCRYPTION ON;


-- 7: Check that  WideWorldImporters is encrypted (encryption_state = 3). Note that Tempdb is also encrypted .

SELECT DB_NAME(database_id) AS database_name, encryption_state, key_algorithm, key_length, encryptor_type

FROM sys.dm_database_encryption_keys


/*

Output: 

database_name        encryption_state    key_algorithm    key_length    encryptor_type

tempdb                3                    AES                256            ASYMMETRIC KEY

WideWorldImporters    3                    AES                256            CERTIFICATE

*/



-- 8: Backup the database 

BACKUP DATABASE WideWorldImporters TO DISK = N'T:\mssql_dump\WideWorldImporters_TDE.bak' with COMPRESSION, init;

--BACKUP DATABASE WideWorldImporters TO DISK = N'T:\mssql_dump\WideWorldImporters_TDE.bak' with  init;



-- 9 : Check that we can still read the data from inside the DB

USE WideWorldImporters

GO

SELECT  top 10 *  from  [Sales].[Orders];



use master

--10 :  Removes the database encryption and drops the database encryption key.

ALTER DATABASE WideWorldImporters

SET ENCRYPTION OFF;

GO

/* Wait for decryption operation to complete, look for a   

value of  1 in the query below. */  


SELECT database_id, encryption_state

FROM sys.dm_database_encryption_keys;

GO


USE  WideWorldImporters;

GO

DROP DATABASE ENCRYPTION KEY;

GO

/*

If decryption operation is in process we will get the following error message : 

Msg 33105, Level 16, State 1, Line 80

Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.

*/


-- 11 : Drop certificate then  master key 

use master;

DROP MASTER KEY ;

/*

We will get the following error message:

Msg 15580, Level 16, State 1, Line 90

Cannot drop master key because certificate 'WICert' is encrypted by it.

*/


use master;

Drop certificate WICert ;

DROP MASTER KEY ;



-- Try to restore database backup with TDE

USE [master]

RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'T:\mssql_dump\WideWorldImporters_TDE.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5


GO


/*

Our error message:

Msg 33111, Level 16, State 3, Line 2

Cannot find server certificate with thumbprint '0x8C02EDC38EB8AA069FD2C4D4A74CC1B8E716524D'.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

*/




/*

 12 : Move or copy the database files from the source server to the same location on the destination server.   

 Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.   

 Create a database master key on the destination instance of SQL Server.   

*/


USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P$ssword';

GO


/*

13: Recreate the server certificate by using the original server certificate backup file.   

 The password must be the same as the password that was used when the backup was created.  

*/



CREATE CERTIFICATE WICert

FROM FILE = 'T:\Cert_Backups\HSCert'

WITH PRIVATE KEY

(

    FILE = 'T:\Cert_Backups\HSCertKey',

    DECRYPTION BY PASSWORD = 'P$ssword'

);

GO



-- This will load both the public and private sides of the encryption key into SQL Server and you can view them with this DDL:

select

  name

, certificate_id

, pvt_key_encryption_type_desc

, subject

, expiry_date

, start_date

, thumbprint

, pvt_key_last_backup_date

 from sys.certificates where name like '%WICert%'


 /*

name    certificate_id    pvt_key_encryption_type_desc    subject                            expiry_date                start_date                thumbprint                                    pvt_key_last_backup_date

WICert    260                ENCRYPTED_BY_MASTER_KEY            WideWorldImporters  Certificate    2017-09-15 12:36:55.000    2016-09-15 12:36:55.000    0x8C02EDC38EB8AA069FD2C4D4A74CC1B8E716524D    NULL

*/



-- 14 : Restore DB backeup with TDE 

USE [master]

ALTER DATABASE [WideWorldImporters]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master]

ALTER DATABASE [WideWorldImporters] SET MULTI_USER WITH ROLLBACK IMMEDIATE

GO

-- Try to restore database backup with TDE

USE [master]

RESTORE DATABASE [WideWorldImporters] FROM  DISK = N'T:\mssql_dump\WideWorldImporters_TDE.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5


GO


/*

 Since SQL Server 2014, you can use separately backup encryption to encrypt the backup when the database you take backup of is not encrypted. 

It uses the same encryption algorithms as TDE, meaning AES and Triple DES.

 In SQL Server 2012, the only way to natively encrypt a backup is to use TDE, so it is to encrypt the database itself.

 */

 -- 15 : Backup DB without encryption 


BACKUP DATABASE WideWorldImportersDW

TO DISK = 'T:\mssql_dump\WideWorldImportersDW_TDE.bak'

WITH ENCRYPTION

  (ALGORITHM = AES_256, SERVER CERTIFICATE =WICert), FORMAT ,init;




  -- 16 : Cleanup 

  use master

-- Removes the database encryption and drops the database encryption key. 

ALTER DATABASE WideWorldImporters

SET ENCRYPTION OFF;

GO

/* Wait for decryption operation to complete, look for a   

value of  1 in the query below. */  


SELECT database_id, encryption_state

FROM sys.dm_database_encryption_keys;

GO


/*

Output when decryption operation is stil in process :


database_id    encryption_state

2            3

17            5

*/



USE  WideWorldImporters;

GO

DROP DATABASE ENCRYPTION KEY;

GO

/*

If decryption operation is in process we will get the following error message : 

Msg 33105, Level 16, State 1, Line 80

Cannot drop the database encryption key because it is currently in use. Database encryption needs to be turned off to be able to drop the database encryption key.

*/


-- Drop certificate then  master key 

use master;

DROP MASTER KEY ;

/*

We will get the following error message:

Msg 15580, Level 16, State 1, Line 90

Cannot drop master key because certificate 'WICert' is encrypted by it.

*/


use master;

Drop certificate WICert ;

DROP MASTER KEY ;


SELECT database_id, encryption_state

FROM sys.dm_database_encryption_keys;

GO

/*

database_id    encryption_state

2            3

*/


תגובה אחת על “Transparent Data Encryption ב SQL 2016 הלכה למעשה ל DBA ואנשי האבטחה”

  1. מאת מוישה:

    לא מובן בכלל!!!!!!!!!!!!!!!!!!!!!!

כתיבת תגובה

האימייל לא יוצג באתר. (*) שדות חובה מסומנים

twelve + 12 =