Creating a MSSQL JDBC connection pool

Ambience 2020+
1

To create a MSSQL connection pool on the Ambience 202x Server:

  1. Deploy the sqldbc driver in the \lib or \ext directory of the Ambience 202x installation

  2. Go to \etc directory, make a copy and rename hikari-sample.properties, e.g. mssql.properties

  3. Edit the mssql.properties file as follows:

  • dataSourceClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver or com.microsoft.sqlserver.jdbc.SQLServerDataSource
  • dataSource.user=<username>
  • dataSource.password=<password>
  • dataSource.databaseName=<dbname>
  • dataSource.portNumber=<port>
  • dataSource.serverName=<servername>
  • dataSource.url=jdbc:sqlserver://localhost:<port>;encrypt=true;trustServerCertificate=true;

  1. Edit application.conf to point to the MSSQL connection properties. Substitute “sample” with the actual database name e.g.
    elixir.data.jdbc {
    connection-pool {
    implementation = “hikari”
    definition {
    sample = “./etc/mssql.properties”
    }
    }
    }

  2. You can encrypt the connection pool password by running bin\ambience-cli.bat at the command line, e.g.

    \Ambience2020\bin>ambience-cli.bat encrypt password

    The command will return the following:
    password = {enc}67uXBxd5Ecxp1BvBgQWGew==

    Go to the properties file and substitute the password, e.g.
    dataSource.password= {enc}67uXBxd5Ecxp1BvBgQWGew==