It is possible to apply encryption to the database and make it fully transparent for the 3rd party application without the source code. Currently it will only work in 32-bit desktop applications. Desktop limitation appears only because existing client DLL can only show a dialog box and prompt for a password. This is in most cases not suitable for web applications or system services. For example it can be used for SQL Server Management Studio (SSMS) to edit the database after it has been encrypted. SSMS uses 32-bit .exe files on both platforms (X86 and X64) and can be used as a target for DBDefence client DLL. However there is some preparation work required beforehand.

Overview

We have a special DLL called dbd_clnt.dll which intercepts connections to SQL Server and prompts the user for a password, although at the moment only TCP/IP connections without SSL can be intercepted. When an application makes a connection to the SQL server, dbd_clnt.dll intercepts it and waits for Server authorization. After authorization has been completed successfully, the DLL checks to see if DBDefence is installed on the connected server and also to see if there are any protected databases present. If there are protected databases found the DLL shows following dialog:

At this point you can unlock access to the databases. The main interface is quite simple and straightforward. Currently there is no warning shown if you enter an invalid password. Checking "Do not ask anymore" will turn off prompts for that process until it restarted.

Configuration

First of all, the DLL must be loaded into the target process. If we consider the situation when no source code available or we do not want make any changes we must use possibilities given by Windows. Windows can load any DLL automatically into each process started.

Setting up registry

If you use Windows XP then take a look at registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Windows\AppInit_DLLs. If you do not have it yet, create it as string. On most computers this key is empty. Add name of the DLL: dbd_clnt.dll to that key. If there were values already, separate them by space. From now on, each starting application will look for dbd_clnt.dll in system path and in their folder. We do not recommend you to copy dbd_clnt.dll into the system folder to avoid unexpected problems with other network applications.

Vista OS and Windows 2008 Server: Set additional key called LoadAppInit_DLLs to 1.

Copying the DLL

The next step is copying the DLL to the target application folder. Let's consider an example with SSMS. It's execution file called SqlWb.exe located at "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\" Note: That path is in system PATH variable, but we will ignore it. Actually you can create a new folder called, for example, "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SQLWB" and copy all SqlWb.* files there. It starts from that folder as well. The original DBDefence client DLL called dbd_clnt.dll located at "C:\Program Files\DBDefence for SQL Server 2005\client\". Copy the DLL to SqlWb.exe folder. From this point on, all TCP/IP connections of SSMS to SQL Servers must be intercepted and checked for protected databases. But what if you login to SQL Server but do not see DBDefence dialog? There are several possible reasons:

DBDefence is not installed on the server.
There are no protected databases with prohibited access.
The DLL not copied to application folder (or to system path)
The Connection from SSMS to SQL Server is being made using Shared Memory or Named Pipes library.

If you installed and tried DBDefence on the server then you can be sure that the first two items have nothing to do with your problem. Check the third, but not the last one. If you connect to the local server on the same computer then the connection is probably created using Shared Memory as this is the fastest method and it is normally used by default.

Creating server alias

There is a way to force almost any application (and SSMS too) to connect to the server using only TCP/IP. That method will not break any other settings and can be considered as safe. The idea is to create an Alias for the server. An SQL Server Tool called "SQL Server Configuration Manager" can create an alias for certain SQL Servers and specify TCP/IP as a generic library to use. For example we created an alias called ipserv which points to our local server.

Complete information on creating server aliases can be found here;
http://msdn.microsoft.com/en-us/library/ms191260.aspx

Now, if you try to connect to the server called ipserv from SSMS you should be prompted for a DBDefence password. But remember, if you connect to the same server but without using the TCP/IP protocol, the client DLL will not be able to intercept the connection.