Technical Guide Reference Guide Frequently Asked Questions Tutorials
Show menu

Connecting to a MS-SQL-Server Database

Microsoft SQL Server - JDBC Connections

When connecting Microsoft SQL Server as a database for Analyze you may need review the authentication settings for the JDBC Driver. The SQL Server driver support several modes including basic SQL authentication as well as Windows Integrated Security. Using the Integrated Security authentication mode has impact on the way the ConnectPlaza software is configured and started under Windows Server.

Requirements

To connect to a SQL Server database, you will need:

  • A running SQL Server instance either local or on Azure.
  • Credentials for connecting to the database when using local SQL Authentication, or credentials for a Windows user for integrated security.
  • Administrative rights to the database, in case of Analyze: all rights except grant rights.
  • The MSSQL JDBC Driver installed (download the bundle from Microsoft) 
  • (Optional when using Integrated Security, you will need the necessary DLL file for either x86 or x64 as well)
  • When installing the Analyze database, create an empty database (database without any tables)

Configuring with integrated security active

This mode will allow you to configure a connection for Analyze which uses Microsoft SQL Server with integrated security. Authentication for this mode will be handled by the Windows Service that hosts the ConnectAgent under Windows Server. The configured user for this service will determine the authentication credentials for SQL Server.

  • Download and unpack the Microsoft SQL Server JDBC Driver for your SQL Server. This will contain both the driver jarfile as well as the DLL.
  • Create a folder lib under your root ConnectPlaza folder, which can be shared accross all versions of the Agent.
  • Place the mssql-jdbc_auth-<version>.dll in this directory.
  • Place the mssql-jdbc-<version>.jre11.jar file in the <connectplaza-install-directory>\agent\jars\endorsed folder.
  • Next please make sure you have correctly configured the settings for SQL Server in the conf\connectplaza-analyze.properties
  • Please configure:
    • Dialect: org.hibernate.dialect.SQLServerDialect
    • Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • The connection string looks like this:
    jdbc:sqlserver://<HOSTNAME>;integratedSecurity=true;databaseName=<DATABASE-NAME>;​
  • Open the file <connectplaza-install-directory>\agent\conf\connectplaza-jvm-process.properties. Add the line:
    • connectplaza.process.arg.lib.path = -Djava.library.path=../lib
  • When starting the agent, start the Agent as a Windows Service with the credentials of the user used to connect to the database. The user credentials used when starting the service ConnectPlaza Agent will be used to connect to the database!

  • (Re)start the ConnectPlaza Agent

Configuring with local SQL Authentication

This connection is much simpler.

  • Place the mssql-jdbc-<version>.jre11.jar file in the <connectplaza-install-directory>\agent\jars\endorsed
  • Next please make sure you have correctly configured the settings for SQL Server in the conf\connectplaza-analyze.properties
  • Please configure:
    • Dialect: org.hibernate.dialect.SQLServerDialect
    • Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
    • Username: <your local sql username>
    • Password: <your local sql password>
  • The connection string looks like this:
    jdbc:sqlserver://<HOSTNAME>;databaseName=<DATABASE-NAME>;​
  • (Re)start the ConnectPlaza Agent

Potential performance improvement

When dealing with SQL Server through the Java JDBC Driver it is recommended to add the following parameter to the SQL Server Database connection URL:

  • sendStringParametersAsUnicode=false

This will avoid the conversion of all database query parameters from/to Unicode on the database, significantly improving performance.

Potential SSL issues starting the connectAgent connecting to Analyze

If you encounter any SSL issues connecting to Analyze you can add a parameter to the connection string:

trustServerCertificate=true

An example of an analyze configuration can look as follows:

connectplaza.analyze.jdbc.driverclass=com.microsoft.sqlserver.jdbc.SQLServerDriver
connectplaza.analyze.jdbc.url=jdbc:sqlserver://192.168.25.41:1433;database=AnalyzeAgent1;trustServerCertificate=true
connectplaza.analyze.jdbc.username=sa
connectplaza.analyze.jdbc.password=PASSWORDUNKNOWN
#
connectplaza.analyze.jdbc.dialect=org.hibernate.dialect.SQLServerDialect
connectplaza.analyze.jdbc.debug=false
connectplaza.analyze.jdbc.hbm2dll=update
connectplaza.analyze.jdbc.prefix=ema

There a several other SQL Server parameters, look in your SQL Server documentation for more information.