Microsoft SQL Server Database Installation and Configuration

If you are using Microsoft SQL Server, the information below will help you get set up.

You can also refer to the applicable section of the Install Guide: go to Installing the Akana API Platform, choose the version, and then search for the Database notes: Microsoft SQL Server section.

Table of Contents

Downloading the Microsoft SQL Server Driver

You can download the driver from the following URL:

https://www.microsoft.com/en-us/download/details.aspx?id=57782

File name after extracting from the .exe file:

mssql-jdbc-7.2.2.jre8.jar

Certificate requirements

The certificate requirements are:

  • Certificate must be present in the Local computer certificate store or the current user certificate store.
  • Certificate age must be present within the validity period.
  • Certificate must be meant for server authentication. (EKU should specify Server Authentication [1.3.6.1.5.5.7.3.1])
  • Certificate must be created using the KEY_SPEC option of AT_KEYEXCHANGE (KEY_SPEC=1)
  • Common name of the certificate should be the host name or the FQDN of the server computer.

Connection String Properties

The connection string properties are:

  • encrypt=true: Driver will encrypt.
  • trustServerCertificate=true: Driver will not validate certificate and will always trust it.
  • trustServerCertificate=false: Driver will validate SQL Server certificate against JVM default trust store or given trust material that is supplied at connection time by trustStore and trustStorePassword connection properties.
  • trustStore=C:\akana\plat\akana-qa-35-sqlserver.jks: path, including filename, to the certificate trust store file.
  • trustStorePassword=password: Password for the trust store.
  • hostNameInCertificate=akana-qa-35: Host name as defined in the certificate.

Connection string properties: usage example

If the trustStore and trustStorePassword are defined as admin properties, the URL is constructed as shown in the example below.

jdbc:sqlserver://{server}:{port}/{database};user={user};password={password};trustStore={trustStore};trustStorePassword={trustStorePassword}

Additional information about connection string properties is available in this Microsoft article: https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-2016.

Changing the Akana Platform to use a MS SQL Server Driver

Follow these procedures:

Changing the Akana Platform to use the MS Driver

  1. Place the jre8 driver in the following directory: {install_dir}/instances/{instance}/deploy.
  2. Log in to the Akana Administration Console and go to Configuration > Settings.
  3. In the com.soa.database.config category, change the settings as follows:
    1. driver, old value: net.sourceforge.jtds.jdbc.Driver
    2. driver, new value: com.microsoft.sqlserver.jdbc.SQLServerDriver
    3. url, old value: jdbc:jtds:sqlserver://{server}:{port}/{database};user={user};password={password}
    4. url, new value: jdbc:sqlserver://{server}:{port}/{database};user={user};password={password}
  4. Restart the containers.

Configuring MS SQL Driver to use SSL Encryption

  1. Set up the platform to use Microsoft SQL driver, following the procedure above.
  2. Generate and register the server certificate with the SQL Server Configuration Manager. Follow the instructions in this link:

    https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-2016/

    Note: There are several ways to create and register SSL certificates. This example uses one option, using the PowerShell command. For an example command, see Example PowerShell command to create certificate below.

  3. Restart SQL Server.
  4. To use encryption, one of the following two approaches in the connection string:
    1. Basic trust: encrypt=true;trustServerCertificate=true
    2. Verify Certificate: encrypt=true;trustServerCertificate=false;hostNameInCertificate=akana-qa-35
  5. Optional: If you chose to verify the certificate, use a tool such as Keystore Explorer to import the server certificate into the platform jre cacerts file.
  6. Start up the containers and verify that they connect.

Example PowerShell command to create certificate

Example command:

New-SelfSignedCertificate -DnsName akana-qa-35 -CertStoreLocation cert:\LocalMachine\My -friendlyName akana-qa-35 -KeySpec KeyExchange

MS SQL Server: Troubleshooting

Refer to the following troubleshooting information:

Issues with restarting Microsoft SQL Server

If you run into issues with restarting SQL Server, check the article below. You might need to change the user login for the SQL Server process in SQL Server Configuration Manager.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/db68b3bf-33a8-42d6-8dac-f7229a2f9cde/sql-server-express-2008-instance-fails-to-start?forum=sqlexpress