Step 1: What to do after DBDefence installation

Step 2: Database encryption

Step 3: Accessing protected database

There are TWO ways to access protected database:

First method works for all kinds of applications if you have the source code. Does not matter if it is C#, ASP.NET, C++ or PHP. You need to insert SQL statement which unlocks access for all further queries. To be exact:

OPEN SYMMETRIC KEY DBDX DECRYPTION BY PASSWORD='YourEncryptionPassword'

The above statement must be executed in the context of the protected database. If that statements returns OK, then access is granted. Check the list of protected databases with "exec master..dbd_listdb" and see that you now have access granted.

Second (beta version) method allows you to unlock the database just after application connected to the server with protected database. Best of all it works for desktop application where user can input the password directly. In the future versions of the DBDefence we will automate the input or provide another authorization methods such as smart cards. Read more in DBDefence Manual

Detailed view

When database is encrypted, it can be attached only if DBDefence is installed and running on the instance. In all other cases, SQL Server will fail to attach the database and turn it into "Suspect" status. SQL Server will not let you detach suspected database, there are only REPAIR and DELETE options. So to make the database available, you would need to start DBDefence prior attaching. Usually we recommend to run DBDefence in Autostart mode. In this case it starts before any database attached.

If you move protected database to another server without installed DBDefence or without database key file it will be impossible to attach the database. Most probably it will be displayed with SUSPECT status in Management Studio.

Protected database denies all SELECT, INSERT, UPDATES to tables inside it including system tables. So when you try to access protected database with SQL Management Studio it fails. Current version of SQL Management Studio has a bug, it actually trying to display some tables inside the database. We noticed that it make take a couple of names from the next unprotected database and shows them like they are in the protected database. However you can't access it in any way.

Here is screenshot of Query Tool when someone tries to select from protected database. In this example the database is called DBDefence however name does not matter:

From our experience we know that some applications without properly written error handling can even crash accessing the database. But of course the SQL Server itself does not crash! You will see the same error "Could not find database ID 0 ..." in SQL log each time when access is denied by DBDefence. We find it a bit annoying, but this is a matter of SQL Server.

You can backup the database without knowing the password. Actually it does not matter if you unlocked the database or not, the backup is always encrypted. You can't recover it on the server without DBDefence or to unencrypted database

To view a list of protected databases execute master..dbd_listdb. It will show a list of protected and attached databases. Second field shows if the access is granted to this database or not.

You can find in-depth information in the Manual which is available Online.