This module is obsolete

Latest version of DbDefence implements same functionality: dbd_add_login, dbd_del_login, dbd_list_login

DbDefence starting from version 6.1 implements automatic access for SQL Server logins. It can greatly simplify security settings for all applications and client computers at once.

The main feature is that you can grant access to a login or to a login with specified password. User must be expliciply logged to get access.

Activecrypt Automatic Keys module (AAK)

Activecrypt Automatic Keys (AAK) is a server side module for Microsoft SQL Server that opens symmetric keys automatically for any given SQL Server login. It can be a useful addition to DbDefence encryption tool or to your own scripts or applications. For example you can setup DBDX symmetric key to be opened automatically when someone logs in.

The advantage of AAC is that key can be opened by the server module without any changes on the client side!

Currently supported SQL Servers:

SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014

SQL Server 2005 not supported.

Installation

Installation done automatically without complicated configuration. During the installation, the installer creates several extended stored procedures in the master database and a stored procedure that starts the AAK with SQL Server.

After installation you need to restart SQL Server instance.

After restart you may check if AAK started successfully with the command:

exec master..ac_ak_status

It should display status ON

Quick Start for DbDefence users

1. After installation restart SQL Server instance.

2. Make sure AAK started: exec master..ac_ak_status.

3. Encrypt the database with DbDefence. Add the password to a list of automatically opened keys. In the following SQL statement change database name, login name and password values .

exec master..ac_ak_add_autokey @keyname='dbdx', @dbname='yourdbname', @login='sa', @passwd='MySuperEncryptionPass'

4. Run your application and see if everything works.

Silent installation

ac_ak_setup.exe /server .\myserver /verysilent /suppressmsgboxes

ac_ak_setup.exe /server .\myserver /login sa /password secret /verysilent /suppressmsgboxes

Backup

Keys are not saved into the backup. If you want to transfer the database, setup keys on the new server again. Keys settings are stored at HKEY_LOCAL_MACHINE\SOFTWARE\ActivecryptSoftware\AC_AK\KEYS. You may backup those values, remove unnecessary values, but it is not transferable.

Usage

AAK provides a set of extended stored procedures for SQL Server to manage a list of automatically opened keys. There is no GUI or command line.

It is important for AAK to start automatically with SQL Server. It can't be started or stopped manually. If you want to temporarily disable AAK read here.

ac_ak_status

Checks if AAK is running.

exec master..ac_ak_status

It should display status ON

ac_ak_add_autokey

Registers key name for automatic opening when certain SQL Server login logs in. The key in the database isn't modified. Changes will affect only newly logged users. Key information securely stored in the registry and can't be transferred to another computer.

For DbDefence users: the key name is always dbdx

Example:

exec master..ac_ak_add_autokey @keyname='dbdx', @dbname='testdb', @login='sa', @passwd='MySuperPass'

exec master..ac_ak_add_autokey @keyname='dbdx', @dbname='testdb', @login='mycomputer\Administrator', @passwd='MySuperPass'

AAK does not validate the user input. If you provided wrong database or wrong password or the key will not be opened. If database user associated with the login does not have permissions to open the key the key will not be opened.

If you use Windows Authentication you need to provide the login too. To see a list of available logins use the following SQL query:

SELECT name AS Login_Name, type_desc AS Account_Type FROM sys.server_principals WHERE TYPE IN ('U', 'S', 'G') and name not like '%##%' ORDER BY name, type_desc

If you have changed the key password, re-add the key with the new password. It will overwrite the old value.

ac_ak_del_autokey

Permanently deletes key information from storage. Changes will affect only newer logged users. The key in the database in untouched.

exec master..ac_ak_del_autokey @keyname='dbdx', @dbname='testdb', @login='sa' -- removes info about key1 for the database testdb for login sa

exec master..ac_ak_del_autokey @keyname='', @dbname='', @login='sa' -- removes info about all keys for all databases for login sa

exec master..ac_ak_del_autokey @keyname='', @dbname='', @login='' -- removes info about all keys for all databases for all logins. Deletes everything.

ac_ak_list_autokeys

Lists keys in the storage.

Example:

exec master..ac_ak_list_autokeys

Output:

Login         Database    Key
------------- ----------- ----------
Testuser3     ac_ak_test  dbdx
Testuser2     ac_ak_test  dbdx

Uninstallation or disabling

If you wish to disable AAK temporarily you need to turn of autostart on ac_ak_startup procedure:

use master
exec sp_procoption N'ac_ak_startup', N'startup',N'false'

then restart SQL Server instance.

To uninstall you may drop info about all keys from AAK storage:

exec master..ac_ak_del_autokey @keyname='', @dbname='', @login=''

Then drop all procedures:

drop procedure ac_ak_startup
drop procedure ac_ak_on
drop procedure ac_ak_scan_autokeys
drop procedure ac_ak_add_autokey
drop procedure ac_ak_add_autokey2
drop procedure ac_ak_del_autokey
drop procedure ac_ak_list_autokeys
drop procedure ac_ak_status

then restart SQL Server instance.