Thursday, December 02, 2010

SQL Server Encryption - Encrypt a database

USE master;
GO
--master key password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '****';
--DROP MASTER KEY
go
--database encryption key
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'Certificate'
--DROP CERTIFICATE MyServerCert
go


USE master
GO
BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\File.cer'
WITH PRIVATE KEY (FILE = 'C:\Key.pvk' ,
ENCRYPTION BY PASSWORD = '*****' )
GO


USE [cleint]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE [cleint]
SET ENCRYPTION ON
GO

SELECT db_name(database_id), encryption_state, *
FROM sys.dm_database_encryption_keys with (nolock)


SELECT * FROM sys.symmetric_keys
SELECT * FROM sys.certificates
SELECT * FROM sys.dm_database_encryption_keys

No comments: