Шифрование БД

USE master
GO
/* Verify master key */
SELECT * FROM sys.symmetric_keys WHERE name LIKE '%MS_DatabaseMasterKey%'
GO
 
/* if there are no records found, then it means there was no predefined Master Key. 
 To create a Master Key, you can execute the below mentioned TSQL code. */
 
/* Create master key */
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'rev$$@admin';
GO
/* Backup master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'rev$$@admin';
GO
BACKUP MASTER KEY TO FILE = 'D:\mssqlbackup\master\masterkey.mk' 
    ENCRYPTION BY PASSWORD = 'rev$$@admin';
GO
 
/* Create Certificate */
CREATE CERTIFICATE rev_cert WITH SUBJECT = 'REV Server Certificate';
GO
 
/* Verify Certificate */
SELECT * FROM sys.certificates where [name] = 'rev_cert'
GO
 
/* Backup certificate */
BACKUP CERTIFICATE rev_cert TO FILE = 'D:\mssqlbackup\master\rev.cer'
   WITH PRIVATE KEY (
         FILE = 'D:\mssqlbackup\master\rev.pvk',
         ENCRYPTION BY PASSWORD = 'rev$$@admin');
GO
 
--use rev database
USE rev
GO
/* Create Encryption key */
CREATE DATABASE ENCRYPTION KEY
   WITH ALGORITHM = AES_256
   ENCRYPTION BY SERVER CERTIFICATE rev_cert;
GO
 
/* Encrypt database */
ALTER DATABASE revdb SET ENCRYPTION ON;
GO
 
/* Verify Encryption */
SELECT 
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO
SELECT 
NAME AS DatabaseName
,IS_ENCRYPTED AS IsEncrypted 
FROM sys.databases where name ='revdb'
GO