Encryption Profiles

Profiles are used to encrypt new databases automatically

Profiles are securely stored set of database encryption settings for DbDefence. Currently, profiles are used only for newly created databases (excluding restored and attached). A profile can be set for:

How it can be used

A security admin can pre-define encryption settings as a profile. Profiles are applied only to new databases created with CREATE DATABASE statement. It does not work when a new database is attached or restored. In such cases the database should be explicitly encrypted. Currently you can't encrypt an existing database with a profile. It will be implemented in the future.

When a new database is created DbDefence checks if a specific profile is set for the current session. Then it checks profile for the login and then default profile.

Password templates

Password in a profile can be set as template. It can allow generating a unique and unpredictable (for unauthorized person) password for each database.

Supported template variables:

%LOGIN% - current SQL login

%DBNAME% - database name to be created

%SHA% - If password template starts with string %SHA% it will generate SHA256 hash from the resulting string

Example:

exec master..dbd_create_profile 'prof3','-p','mypassword%LOGIN%%DBNAME%'
exec master..dbd_set_profile @profile='prof3' -- sets the profile for all logins

When the profile is set, and a login sa creates a database called Reports with CREATE DATABASE statement the database is encrypted with password mypasswordsaReports. Such password, if revealed could be the key to guessing other passwords. To make passwords more secure, use %SHA% template variable.

Example:

exec master..dbd_create_profile 'prof4','-p','%SHA%:test%LOGIN%%DBNAME%'
exec master..dbd_set_profile @profile='prof4', @login='sa' -- sets the profile only for login sa. No difference in this case, just as a demo.

In this case when login sa creates a database called Reports with CREATE DATABASE statement the database is encrypted with the password 9147dd235a940a464b4f5b89ff62bd04a423d2e1e2120f6e9faff5d17e2bb9cf.
then if sa creates database Reports2 the password will be 0110165ff41a0b14243345317c0d923ad6b7442031e6a5d79c3298ab0155117d.

There are a plenty of implementations to generate SHA256. You can use SQL Server's HASHBYTES:

SELECT HASHBYTES('SHA2_256','testsaReports2')

Output: 0x0110165FF41A0B14243345317C0D923AD6B7442031E6A5D79C3298AB0155117D

Just trim leading 0x.

In the later example, the profile is set only for login sa. If other logins create databases it will be unencrypted.

Related functions

master..dbd_create_profile - Creates and stores a profile

master..dbd_list_profiles - Lists profiles

master..dbd_set_session_profile - Sets and unsets a profile for the current session

master..dbd_set_profile - Sets (activates) or unsets (deactivates) a profile

master..dbd_del_profile - Deletes a profile