Configuring MS SQL Server as the Backend Database

Key Manager Plus supports PostgreSQL and MS SQL as the backend databases. PostgreSQL database is bundled with the product, and by default, it is configured to run with PostgreSQL. In case, you wish to use MS SQL Server as the backend database, follow the steps elaborated below.

To ensure a high level of security, Key Manager Plus has been configured to connect to the SQL server only through SSL.

Steps Required

1. Create SSL certificate, install it in Windows Certificate Store (where SQL server is running) and import it to Key Manager Plus

1.1 Generating the certificate and getting it signed by third-party CA

1.2 Creating a self-signed certificate

2. Enable SSL encryption in SQL server

3. Execute changeDB.bat in Key Manager Plus

1. Create SSL Certificate, Install it in Windows Certificate Store (in the machine where SQL server is running) and Import it to Key Manager Plus

Before connecting Key Manager Plus with SQL server, you need to enable SSL encryption in SQL Server. Here, you may create an SSL Certificate and get it signed by a Certificate Authority (CA) or use a self-signed certificate which you can generate using Key Manager Plus itself.

1.1 Generating the certificate and getting it signed by third-party CA

You can generate a certificate and get it signed by a third party CA from Key Manager Plus interface itself. Refer to the detailed help to accomplish this task.
After a few days from raising request to third party CAs using Key Manager Plus, you will receive signed server SSL certificate and CA's root certificate as .cer files. You then have to install the server certificate in the machine where the SQL server is running. The CA root certificate has to be installed in the Key Manager Plus server.

a. Install the server certificate in the machine where SQL server is running. You may use MMC to do this as shown below:

1. Open the MMC console by clicking Start >> Run (in the machine where SQL server is running). In the Run dialog box type MMC.
2. On the Console menu, click Add/Remove Snap-in. Click Add and then click Certificates. Click Add again. You will be prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
3. Select Certificates (Local Computer) >> Personal >> Certificates
4. Right click Certificates --> click All Tasks >> Import
5. Browse and select the certificate to be installed

b. Install the CA's root certificate in Key Manager Plus as follows:

1. Copy the CA's root certificate and paste it under <Key Manager Plus installation folder>/bin directory
2. From <Key Manager Plus Installation Folder>/bin directory, execute the following command:
   importCert.bat<name of the root certificate pasted as explained above>
3. This adds the certificate to the Key Manager Plus certificate store.

1.2 Creating a self-signed certificate

If you want to create and use a self-signed certificate, you can do the same using Key Manager Plus. Refer to the detailed help to accomplish this task.

After creating the certificate, follow the below steps to install it in the machine where the SQL server is running and the Key Manager Plus server.

a. Install the server certificate in the machine where SQL server is running. You may use MMC to do this as shown below:

1. Open the MMC console by clicking Start --> Run (in the machine where SQL server is running). In the Run dialog box type: MMC
2. On the Console menu, click Add/Remove Snap-in. Click Add and then click Certificates. Click Add again. You will be prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
3. Select Certificates (Local Computer)--> Personal--> Certificates
4. Right click Certificates --> click All Tasks--> Import
5. Browse and select the certificate to be installed

b. Install the server certificate in Key Manager Plus as follows:

1. Copy the server certificate and paste it under <Key Manager Plus installation folder>/bin directory
2. From <Key Manager Plus Installation Folder>/bin directory, execute the following command:
   importCert.bat<name of the server certificate>
3. This adds the certificate to the Key Manager Plus certificate store

2. Enable SSL Encryption in SQL Server

To enable SSL for SQL Server,

1. In the machine where SQL server is running, click Start, in the Microsoft SQL Server program group, click Configuration Tools, and then click SQL Server Configuration Manager.
2. Expand SQL Server Network Configuration, right-click the protocols for the server you want, and then click Properties. (This is the Protocols for section in the left pane of the tool, not a specific protocol in the right pane.)
3. On the Certificate tab, configure the Database Engine to use the certificate.
4. When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access.
5. When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required.
6. SQL Server must be restarted after you change the ForceEncryption setting.

For more details, refer to the section 'Configuring SSL for SQL Server' in Microsoft's knowledge base article available at http://msdn.microsoft.com/en-us/library/ms189067.aspx

3. Execute ChangeDB.bat in Key Manager Plus

You need to provide the details about the SQL server to Key Manager Plus by editing the file ChangeDB.bat (Windows) or ChangeDB.sh (Linux)

Navigate to <Key Manager Plus Installation Folder>/bin folder and execute ChangeDB.bat (Windows) or sh ChangeDB.sh(Linux)

In the window that opens up, enter the details:

1. Select 'Server Type' as SQL Server.
2. Host Name: The name or the IP address of the machine where MS SQL server is installed.
3. Instance Name: Helps to specify the named instance of SQL server, which has to be used for Key Manager Plus. If instance name is not specified, Key Manager Plus will try establishing connection with the default instance on port 1433.
   
   Since Key Manager Plus connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Key Manager Plus. If you want to specify a port number other than 1433, you can specify it in the Host Name parameter above as <hostname>:<port>
4. Database Name: Name of the Key Manager Plus database. Default is 'pki'. If you want to have a different database name, you may specify here. Key Manager Plus will take care of creating the Master Key, Symmetric Key etc.
5. Authentication: The way in which you would like to connect to the SQL server. If you are connecting to the SQL server from Windows, you have the option to make use of the Windows Single Sign On facility provided Key Manager Plus service is running with a service account, which has the privilege to connect to SQL server. In that case, choose the option "Windows". Otherwise, select the option "SQL".

6. User Name and Password: If you have selected the option "SQL", specify the user name and password with which Key Manager Plus needs to connect to the database.

Here, you have the option to use even your Windows login credentials, if you are connecting to the database from Windows. In this case, you need to enter the username as <domain-name>\<username>

7. Encryption Key: The key with which your data is to be encrypted and stored in the SQL server. You may either leave it 'Default' making Key Manager Plus to generate a key. If you want to have your custom key, select the option 'Custom'.
8. If you have selected the option Custom: If you have chosen the option 'Custom', you need to create a new database, create Master Key, create Certificate (this will be certificate name) and Create the Symmetric Key using AES 256 encryption.
9. You need to do the following steps:
   1. Create Database --> For details, refer to http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx
   2. Create Master Key --> For details, refer to http://technet.microsoft.com/en-us/library/ms174382.aspx
   3. Create Certificate --> For details, refer to http://msdn.microsoft.com/en-us/library/ms187798.aspx
   4. Create Symmetric Key --> For details, refer to http://msdn.microsoft.com/en-us/library/ms188357.aspx
   5. After doing the above, you need to provide certificate name and symmetric key name in the GUI.

10. Finally, click Test to ensure that the connection settings are proper and then click Save

For more details on encryption and key management in MS SQL, refer to this MSDN document http://msdn.microsoft.com/en-us/library/ms189586.aspx