Using Azure MS SQL as the Backend Database
(Feature available only in Enterprise Edition)

Password Manager Pro supports both PostgreSQL and MSSQL databases as the backend database. By default, PostgreSQL comes bundled with Password Manager Pro and is configured as the backend database. However, Password Manager Pro allows you to configure other databases including Azure MS SQL database as the backend database.

This document explains in detail the process of using Azure MS SQL as a backend database in Password Manager Pro.

Steps Required

  1. Create SSL certificate and install it in the Windows Certificate Store
  2. Import the SSL certificate to Password Manager Pro
  3. Enable SSL Encryption in SQL Server
  4. Execute ChangeDB.bat in Password Manager Pro

Important Notes:

  • To ensure high level of security, Password Manager Pro has been configured to connect to the SQL server only through SSL.
  • Password Manager Pro supports MS SQL server as the backend database, only from the version 6400 and above.
  • If you are using an earlier version of Password Manager Pro with MySQL as the backend database, here are the steps to migrate the data to MS SQL.

1. Create SSL certificate and install it in the Windows Certificate Store

(in the machine where the SQL server is running )

Prior to connecting Password Manager Pro with the SQL server, you need to enable SSL encryption in the SQL Server. For this, you need to create an SSL Certificate and get it signed by either a Certificate Authority (CA) or self-sign it (See more).

A) Generating the certificate and getting it signed by a third-party CA:

Create the certificate using openssl. This involves two steps - generating private key and generating certificate request. Use the following commands to create the certificate.

  1. Generating Private Key: Execute the following command:

    openssl genrsa -des3 -out server.key 2048

  2. Generating Certificate Request: Follow the below steps:
    1. Use the server's Private Key to create a certificate request. Enter the Passphrase for the key, Common Name, Hostname or IP Address, when prompted. For the Common Name, specify the FQDN of the SQL Server.
      openssl req -new -key server.key -out server.csr
    2. Once the certificate is generated, get it signed by a third-party CA such as VeriSign, Thawte, RapidSSL, etc, or self-sign it, based on your environment's requirement. For more details on submitting the CSRs, refer the corresponding CA's documentation/website. Remember, this is a paid service. In a few days, you will receive your signed SSL certificate and the CA's root certificate as .cer files.
    3. Install the server certificate in the machine where the SQL server is running.
    4. Install the CA root certificate in the Password Manager Pro server.
  3. Installing the server certificate in the machine where the SQL server is running: (Using MMC)
    1. Click Start >> Run in the machine where the SQL server is running. In the Run dialog box type: MMC. The MMC console is displayed.
    2. From 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 and click All Tasks >> Import.
    5. Browse and select the certificate to be installed.
  4. Installing the CA's root certificate in Password Manager Pro:
    1. Copy the CA's root certificate and paste it under <Password Manager Pro Installation Folder >/bin directory.
    2. From <Password Manager Pro 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 Password Manager Pro certificate store.

B) Creating a self-signed certificate using Powershell:

To create a self-signed certificate and use it, carry out the following steps in the machine where SQL server is installed:

  1. Navigate to the SQL Server and open Powershell (run as Administrator).
  2. Execute the following command:

    New-SelfSignedCertificate -DnsName FQDN of the SQL server -CertStoreLocation cert:\LocalMachine\My

  3. The above command will install and store a self-signed certificate in your local store.

2. Import the SSL certificate to Password Manager Pro

To import SSL certificates to Password Manager Pro,

  1. Copy the server certificate and paste it under the <Password Manager Pro Installation Folder>/bin directory.
  2. Execute the following command:

    importCert.bat <name of the server certificate>

  3. This adds the certificate to the Password Manager Pro certificate store.

3. Enable SSL Encryption in SQL Server

  1. Click Start in the machine where the SQL server is running. From the Microsoft SQL Server program menu, click Configuration Tools, and then click SQL Server Configuration Manager.
  2. Expand the SQL Server Network Configuration, right-click the Protocols for the server you want, and then click Properties. (Remember to click the Protocols for section in the left pane of the tool and not the specific Protocols in the right pane.)
  3. On the Certificate tab, configure the Database Engine to use the certificate.
  4. Set the ForceEncryption option for the Database Engine to Yes, so that all the client/server communication is encrypted and the clients that cannot support encryption are denied access (recommended). Set the ForceEncryption option for the Database Engine to No, if you want the encryption to be requested by the client application (not recommended).
  5. Restart the SQL Server.

For more details, refer to the section Configuring SSL for SQL Server in the Microsoft's knowledge base article.

4. Execute ChangeDB.bat in Password Manager Pro

Note: Skip this step, if you are already using Password Manager Pro with MySQL as the backend database and migrating your data to the MS SQL server.

Provide the details about the SQL server to Password Manager Pro by editing the file ChangeDB.bat (Windows) or ChangeDB.sh (Linux). Follow the below steps:

  1. Navigate to the <Password Manager Pro Installation Folder>/bin folder and execute the file ChangeDB.bat (Windows) or sh ChangeDB.sh (Linux).
  2. In the pop-up that opens up, read the Best Practices Guide first by clicking the Open the best practices document button. Click the Continue with setup button to proceed with the DB change configuration.
  3. In the window displayed, enter the below details:
    1. Select the Server Type as SQL Server.
    2. Host Name: The name or the IP address of the machine, where the MS SQL server is installed.
    3. Instance Name: Specify the named instance of the SQL server, to be used for Password Manager Pro. If the instance name is not specified, Password Manager Pro will try establishing connection with the default instance on port 1433.

      Since Password Manager Pro connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for Password Manager Pro. 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 Password Manager Pro database. Default is "PassTrix". If you want to have a different database name, specify it here. Password Manager Pro will take care of creating the Master Key, Symmetric Key, etc.
    5. Authentication: The way by which you wish to connect to the SQL server. Choose Windows, if you are connecting to the SQL server from Windows. Make use of the Windows Single Sign On facility, provided the Password Manager Pro service is running with a service account, which has the privilege to connect to the SQL server. Otherwise, select the option SQL.

      It is recommended to choose the option Windows, as the Username and Password used for authentication are not stored anywhere.

    6. User Name and Password: If you have selected the option SQL in step v, specify the user name and password with which Password Manager Pro can connect to the database.

      The User Name and Password entered here will be stored in the database_params.conf file in Password Manager Pro. So, take care of hardening the host.
      You can use even your Windows login credentials, if you are connecting to the database from Windows. In this case, you need to enter the User Name as <domain-name>\<username>.

    7. Encryption Key: The key to encrypt your data and store it in the SQL server. You may either leave it "Default" allowing Password Manager Pro to generate a key. If you want to have your custom key, select the option Custom.

      If you have selected the option Custom, do the following:

      Create Database >> For details, refer to http://msdn.microsoft.com/en-us/library/aa258257(v=sql.80).aspx
      Create Master Key >> For details, refer to http://technet.microsoft.com/en-us/library/ms174382.aspx
      Create Certificate >> For details, refer to http://msdn.microsoft.com/en-us/library/ms187798.aspx
      Create Symmetric Key >> For details, refer to http://msdn.microsoft.com/en-us/library/ms188357.aspx

    8. Provide the certificate name and symmetric key name in the GUI.
    9. Instance Type: Password Manager Pro allows both Local Instance (on-premise) and Azure SQL as backend database. Here, select Azure SQL as your instance type to continue with the DB change configuration.
    10. JDBC Driver: The driver works seamlessly with MS SQL server database. Microsoft (Recommended) is the default driver selected in the field and is recommended for most installations because it is compatible with the latest SQL server versions and features. The alternative JDBC driver available for selection is jTDS, which supports specific compatibility features.
    11. Connection Properties: After selecting this checkbox, you will have the option to specify custom connection properties in the following format: "key1=value1;key2=value2;".

      12. Note: The existing server details in the DB migration setup window and properties, such as SSL and encrypt, cannot be added as custom connection properties.


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

    4. Important Notes:

    After performing the above steps, navigate to the <Password Manager Pro Installation Folder>/conf directory and move the masterkey.key file to a secure location. The SQL Server encrypts the data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. One among them is the Database Master Key, which in turn is created by the Service Master Key and a Password. This password is stored in Password Manager Pro under the <Password Manager Pro Installation Folder>/conf directory in a file named masterkey.key. It is highly recommended that you move the masterkey.key file to a secure location. This is to ensure data security. Take care to keep this key safe. You will require it while performing High Availability and Disaster Recovery. If you lose this key, you will have to configure MS SQL server setup all over again.

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.
Top