Sql-server – How to stop decrypting data after opening master key in SQL Server

encryptionsql serversymmetric-key

I have implemented data encryption using SQL Server symmetric key encryption technique using a certificate, master key with password.

When we backup database from PROD server and restore to a different server, we open the master key using OPEN MASTER KEY DECRYPTION BY PASSWORD = ''… statement.

Password for master key is being managed by a SQL admin.

After opening the master key, we are able to decrypt the column data using DecryptByKey function. However, once we have done some analysis, we want to stop decrypting the data. No developer should be able to decrypt the data after a while.

How to stop decrypting the data or close the master key so that no developer can see data in plain-text?

Best Answer

The command you're looking for is CLOSE MASTER KEY;

From docs.microsoft.com:

This statement reverses the operation performed by OPEN MASTER KEY. CLOSE MASTER KEY only succeeds when the database master key was opened in the current session by using the OPEN MASTER KEY statement.

Related Solutions

Sql-server – SYMMETRIC key & certificates role in sql server

You are protecting agains accidental media loss (a lost laptop with the database, your drive showing up on a flea market with a copy of the database or of a backup) etc etc. Any scheme in which the processing itself (the database engine or the application) requires to access the data without the user providing a password only offer access agains media loss. The encryption key hierarchy is rooted on the system DPAPI encrypted key, in other words on the password of the service account. Such a scenario never protects agains a hacker that gets access to your SQL Server, and is not meant to protect such.

The alternative is to ask the user for a password each time it uses the application and use this password to open the key at the top of the encryption key hierarchy (usually a certificate in the database). This scheme is very seldom deployed in such cases as some multi-tenant scenarios when the tenants do no trust the hosting operations/administrators.

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY

For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.

Best Answer

Related Solutions

Sql-server – SYMMETRIC key & certificates role in sql server

Sql-server – Copy SQL Server database to same server & decrypt by symmetric key return null

Related Question