Connecting to a MS-SQL-Server Database

MSSQL JDBC Connections

When you want to connect to a MS SQL Server database you can do this via a user created in your SQL Server environment. But if you are using integrated security, you have to login with a user which is authenticated via your AD to your SQL Server database engine.

When you do this you have to use a different approach when connecting to this database.

Requirements

To connect to a database, for example an AnalyzeDB database you need the following:

  • The username to connect to the database engine
  • A password of this user
  • Rights to the database, in case of Analyze all rights except grant rights.
  • The MSSQL JDBC Driver installed (download the zip file)
  • When installing the Analyze database, create a void database (database without any tables)

Configuring with integrated security active

  • Create a directory on your disk, but not in your ConnectAgent Install directory. For instance c:\lib.
  • Place the mssql-jdbc_auth-<version>.dll in this directory.
  • Place the correct .jar file in the <connectplaza-install-directory>\endorsed>
  • The connection string looks like this:
    jdbc:sqlserver://<HOSTNAME>;integratedSecurity=true;databaseName=<DATABASE-NAME>;​
  • Open the file <connetplaza-install-directory>\agent\conf\connectplaza-jvm-process.properties. Add the line:
    connectplaza.process.arg.mssqlserverlibpath = -Djava.library.path=C:\lib​
    Where c:\lib is the directory where you placed the .dll file.
  • When starting the agent, start the agent with the credentials of the user used to connect to the database. The user starting the service ConnectPlaza Agent must be the same as the user connecting to the database!
  • (Re)start the ConnectPlaza Agent

Configuring with no integrated security

This connection is much simpler.

  • Place the correct .jar file in the <connectplaza-install-directory>\endorsed>
  • The connection string looks like this:
    jdbc:sqlserver://<HOSTNAME>;databaseName=<DATABASE-NAME>;​
  • (Re)start the ConnectPlaza Agent

SSL issues starting the connectAgent connecting to Analzye

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.