Using MS SQL Server as the Backend Database

12 minutes to read

PAM360 uses PostgreSQL as the default backend database that comes bundled with the product. However, you can configure the product with other databases as well, such as MS SQL standalone, MS SQL cluster, Azure SQL, Azure Postgres, etc. In this document, you will learn in detail the steps to set up MS SQL as the backend database for your PAM360 installation.

Prerequisites

We recommend using a dedicated service account to run the PAM360 service. Ensure that the service account is part of the local admin group in the PAM360 server. The service account that is used for PAM360 service and the change DB process must have the following permissions:

  1. In the SQL server, select the required account. Under Server roles, provide 'dbcreator' privileges.
  2. Once the db is created, select the account and provide 'dbowner' privileges.

Steps Required

  1. Creating the SSL Certificate and Installing it in the Windows Certificate Store
  2. Importing the SSL Certificate to PAM360
  3. Enabling SSL Encryption in SQL Server
  4. Executing ChangeDB.bat in PAM360

Note: To ensure high level of security, PAM360 has been configured to connect to the SQL server only through SSL.

1. Creating the SSL Certificate and Installing it in the Windows Certificate Store
(in the machine where SQL server is running)

Prior to connecting PAM360 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.

a. Generating Private Key: Execute the following command:

openssl genrsa -des3 -out server.key 2048

b. 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 PAM360 server.
c. Installing the server certificate in the machine where the SQL server is running: Use 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.

d. Installing the CA's root certificate in PAM360:
  1. Copy the CA's root certificate and paste it under <PAM360 Installation Folder >/bin directory.
  2. From <PAM360 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 PAM360 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. Importing the SSL Certificate to PAM360

Follow the below steps:

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

    importCert.bat <name of the server certificate>

  3. This adds the certificate to the PAM360 certificate store.

3. Enabling 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 Force Encryption 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 Force Encryption 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 PAM360

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

  1. Navigate to the <PAM360 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 PAM360. If the instance name is not specified, PAM360 will try establishing connection with the default instance on port 1433.

      Since PAM360 connects to MS SQL only in SSL mode, it is recommended that you create a dedicated database instance running in a specific port for PAM360. 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 PAM360 database. Default is PassTrix. If you want to have a different database name, specify it here. PAM360 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 PAM360 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 PAM360 can connect to the database.

      The User Name and Password entered here will be stored in the database_params.conf file in PAM360. 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 PAM360 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: PAM360 allows both 'Local Instance' (on-premise) and 'Azure SQL' as backend databases. Here, select 'Local Instance' as your instance type to continue with the DB change configuration.
    10. JDBC Driver: The driver works seamlessly with the 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".

      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.


    IMPORTANT NOTES: After performing the above steps, navigate to the <PAM360 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 PAM360 under the <PAM360 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/Azure MS SQL server setup all over again.

For more details on encryption and key management in MS SQL, refer to this MSDN document.
Top
Back to Top