Security Encryption

Question 1

T-SQL to show Service Master Key?

Answer 1

SELECT * FROM sys.symmetric_keys

Question 2

T-SQL to regenerate the service master key?

Answer 2

ALTER SERVICE MASTER KEY REGENERATE

GO

Question 3

T-SQL to create database master key for encryption?

Answer 3

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘pass@word’

Question 4

T-SQL to fix user logins on encrypted database restored to a different instance?

Answer 4

sp_change_users_login ‘Auto_Fix’, ‘Username’

Question 5

Define the T-SQL steps to enable Transparent Database Encryption?

Answer 5

1. CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<StrongPassword>’;
2. CREATE CERTIFICATE MyCert WITH SUBJECT = ‘My Certificate’;
3. CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyCert;
4. ALTER DATABSE myDatabase SET ENCRYPTION ON;

Question 6

How do you monitor the state of database encryption?

Answer 6

SELECT * FROM sys.dm_database_encryption_keys

Column encryption_state:

• State 2 means background process has not completed.
• State 3 means the database is fully encrypted.