Encrypting Database with Encryption Certificates

DbDefence can use installed certificates to encrypt databases. The benefit of this method is that you can install a certificate in Windows system storage and this certificate is not exportable by default. This will ensure your database copy is protected. Nobody will be able to copy the database to the server without an installed certificate. Usually certificates are a part of corporate infrastructure and issued by authorities. If you do not have a real certificate, you may use a "self-signed" certificate generated for free here: http://www.mobilefish.com/services/ssl_certificates/ssl_certificates.php

Generate

Fill in the information (maybe just for fun), choose RSA 1024 or higher, enter the pass phrase and remember it. After clicking Generate, download the resulting file called "Personal Information Exchange PKCS#12" at the bottom.

To install the resulting certificate start mmc.exe, in its File menu select "Add / Remove Snap-In..." It will display installed snap-ins.

Snapins 650x456

Select the snap-in called Certificates and click Add. Several additional dialogs will then be displayed. Choose "Computer Account," Local Computer on the next page and then Finish.

Snapin1 650x432

Import the downloaded certificate file. At this stage you will be asked for a password used to generate the certificate.

Import

If everything went ok, you will see a newly added certificate. Now, when you encrypt the database with Encryptor, go to the Change Options dialog and try to add your newly added certificate. It must appear amongst several others or just one.

Selectcert

Certificate

Try to encrypt the database now. Before applying the certificate, Encryptor will try to check if the currently selected SQL Server has permissions to make encryption / decryption operations. If it can't be used, you will get the error saying that. If you did everything properly and set up the certificate into the Local Computer storage, you need to setup permissions on private key.

Properties 644x252

Allow the key to be used by the SQL Server service account. Encryption should proceed after setting appropriate permissions.
A SQL Server with DbDefence but without an installed certificate will not be able to attach the database. It will fail with an Access Denied error.

Certificate rotation

You may change encryption certificate without re-encryption by using function dbd-change-cert