SQL 가용성그룹의 데이터베이스에 대한 TDE 활성화

SQL AlwaysOn 환경에서 데이터베이스를 데이터베이스 암호화(TDE)를 활성화 할 수 있다.

이미 TDE 된 데이터베이스도 가용성그룹에 추가를 할 수 있고, 가용성데이터베이스를 TDE 활성화를 할 수 있다.

   

[시나리오]

가용성그룹에 포함된 데이터베이스를 데이터베이스암호화(TDE) 하는 방법

   

[Action Plan]

 

-- Primary Replica

1.DMK 생성 여부 확인

 

    USE MASTER;

    GO

    SELECT *

    FROM sys.symmetric_keys

    WHERE name = '##MS_DatabaseMasterKey##'

 

2. DMK 생성

 

    -- master 데이터베이스에 DMK를 생성함.

    USE master;

    GO

    CREATE MASTER KEY

        ENCRYPTION BY PASSWORD = 'P@ssw0rd1';

    GO

    -- DROP MASTER KEY

 

    SELECT * FROM sys.symmetric_keys;

    -- ##MS_DatabaseMasterKey## 생성됨

    SELECT name, is_master_key_encrypted_by_server, is_encrypted

    FROM sys.databases;

    -- master db 가 is_master_key_encrypted_by_server = 1 로 변경됨.

 

3. 인증서 생성

 

    use master

    GO

    CREATE CERTIFICATE DemoSQLServerCert

        WITH SUBJECT = 'Demo Certificate to protect TDE key';

    GO

 

    -- 생성된 인증서 확인하기

    SELECT * FROM sys.certificates;

    /*

    DemoSQLServerCert    258    1    MK

    */

 

4. 인증서 백업 - 복구시 사용하는 인증서 임.

    -- 권한이 있는 폴더에 백업하자

    BACKUP CERTIFICATE DemoSQLServerCert

    TO FILE = 'Z:\SQLData\DemoSQLServerCert' -- or 'C:\DemoSQLServerCert.cer'

    WITH PRIVATE KEY

    (

        FILE = 'Z:\SQLData\SQLPrivateKeyFile',    -- or 'C:\SQLPrivateKeyFile.pvk'

        ENCRYPTION BY PASSWORD = 'P@ssword2'

    );

 

    -- 인증서를 백업한 적이 있는지 확인하는 쿼리.

    -- pvt_key_last_backup_date GMT 시간 기준 ?? 애매하지만 그런것 같다.

    USE master;

    GO

    SELECT pvt_key_last_backup_date,

         Db_name(dek.database_id) AS encrypteddatabase ,c.name AS Certificate_Name

    FROM sys.certificates c

         LEFT OUTER JOIN sys.dm_database_encryption_keys dek

             ON c.thumbprint = dek.encryptor_thumbprint;

    /*

    2016-02-16 02:52:27.533    NULL    DemoSQLServerCert

    */

 

5. DEK 생성하자 - Creation of Database Encryption Key

 

    use TD

    GO

 

    CREATE DATABASE ENCRYPTION KEY

        WITH ALGORITHM = AES_256

        ENCRYPTION BY SERVER CERTIFICATE DemoSQLServerCert;

    GO

 

 

10. TDE 활성화 하기

 

    use master

    GO

 

    ALTER DATABASE TD2 SET ENCRYPTION ON;

    GO

 

    --ALTER DATABASE TD2 SET ENCRYPTION OFF;

    --GO

 

11. 확인

 

    USE MASTER;

    GO

    SELECT DB_NAME(database_id), encryption_state, *

    FROM sys.dm_database_encryption_keys;

    -- WHERE encryption_state = 3;

    GO

 

    SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,

    dm.key_algorithm,dm.key_length

    FROM sys.databases db

    LEFT OUTER JOIN

    sys.dm_database_encryption_keys dm

    ON db.database_id = dm.database_id;

    GO

 

 

-- Secondary Replica

 

6. DMK 생성여부 확인

 

    USE MASTER;

    GO

    SELECT *

     FROM sys.symmetric_keys

     WHERE name = '##MS_DatabaseMasterKey##'

 

    SELECT * FROM sys.symmetric_keys;

    -- ##MS_DatabaseMasterKey## 생성됨

    SELECT name, is_master_key_encrypted_by_server, is_encrypted

    FROM sys.databases;

    -- master db 가 is_master_key_encrypted_by_server = 1 로 변경됨.

 

7. DMK 생성

 

    -- master 데이터베이스에 DMK를 생성함.

    USE master;

    GO

    CREATE MASTER KEY

        ENCRYPTION BY PASSWORD = 'P@ssw0rd222'; -- 암호는 동일하지 않아도 되는듯.

    GO

    -- DROP MASTER KEY

 

    SELECT * FROM sys.symmetric_keys;

    -- ##MS_DatabaseMasterKey## 생성됨

    SELECT name, is_master_key_encrypted_by_server, is_encrypted

    FROM sys.databases;

    -- master db 가 is_master_key_encrypted_by_server = 1 로 변경됨.

 

8. 인증서 생성

    use master

    GO

    -- UNC 잘 안되네. 음

    CREATE CERTIFICATE DemoSQLServerCert        -- CREATE 처럼 보이지만, 복사해온 것으로 복원하는 것임.

    FROM FILE = '\\AG1\z$\SQLData\DemoSQLServerCert'

    WITH PRIVATE KEY

    (

        FILE = '\\AG1\z$\SQLData\SQLPrivateKeyFile',                -- 일반적으로 Data 폴더에

        DECRYPTION BY PASSWORD = 'P@ssword2' -- 복원시 암호도 동일해야하나 , 테스트안해봄

    );

    GO

 

    -- COPY 해서 가져와서 복원했다.

    CREATE CERTIFICATE DemoSQLServerCert        -- CREATE 처럼 보이지만, 복사해온 것으로 복원하는 것임.

    FROM FILE = 'Z:\SQLData\DemoSQLServerCert'

    WITH PRIVATE KEY

    (

        FILE = 'Z:\SQLData\SQLPrivateKeyFile',                -- 일반적으로 Data 폴더에

        DECRYPTION BY PASSWORD = 'P@ssword2'

    );

    GO

 

 

9. 인증서 백업 및 백업여부 확인

 

    -- 인증서를 백업한 적이 있는지 확인하는 쿼리. - 뭔가 좀 그시기

    USE master;

    GO

 

    BACKUP CERTIFICATE DemoSQLServerCert

    TO FILE = 'Z:\SQLData\DemoSQLServerCert_Sec' -- or 'C:\DemoSQLServerCert.cer'

    WITH PRIVATE KEY

    (

        FILE = 'Z:\SQLData\SQLPrivateKeyFile_Sec',    -- or 'C:\SQLPrivateKeyFile.pvk'

        ENCRYPTION BY PASSWORD = 'P@ssword2'

    );

 

 

    -- 인증서를 백업한 적이 있는지 확인하는 쿼리. - 뭔가 좀 그시기

    USE master;

    GO

    SELECT pvt_key_last_backup_date,

         Db_name(dek.database_id) AS encrypteddatabase ,c.name AS Certificate_Name

    FROM sys.certificates c

         LEFT OUTER JOIN sys.dm_database_encryption_keys dek

             ON c.thumbprint = dek.encryptor_thumbprint;

 

12. 확인

 

    USE MASTER;

    GO

    SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,

    dm.key_algorithm,dm.key_length

    FROM sys.databases db

    LEFT OUTER JOIN

    sys.dm_database_encryption_keys dm

    ON db.database_id = dm.database_id;

    GO

   

<TDE 제거 하는 방법>

꺼꾸로 삭제하기

 

    use master

    go

    ALTER DATABASE TD2 SET ENCRYPTION OFF;

    GO

 

    use TD2

    go

    DROP DATABASE ENCRYPTION KEY

 

    USE master

    GO

    DROP CERTIFICATE DemoSQLServerCert;

    DROP MASTER KEY

 

    -- 위 상황에서 재구성하려면 아래 오류가 발생한다. 그래서 로그백업을 한번 해줘야한다.

    /*

    메시지 33122, 수준 16, 상태 1, 줄 79

    이 명령을 수행하려면 데이터베이스 'TD2'에 대해 데이터베이스 암호화 검색이 필요합니다. 하지만 데이터베이스에 이전 암호화 검색 이후 로그 백업 보류 중인 변경 내용이 있습니다. 로그 백업을 수행하고 명령을 다시 시도하십시오.

    메시지 5069, 수준 16, 상태 1, 줄 79

    ALTER DATABASE 문이 실패했습니다.

    */

 

    Backup log TD2 to disk='Z:\SQLData\TD2_all.bak'