Microsoft SQL Server

There are two basic authentication schemes used to connect to a Microsoft SQL Server, SQL Server Authentication and Windows Authentication.

SQL Server Authentication

With SQL Server Authentication you are connecting to the database with a database user.

 1# yaml-language-server: $schema=https://json-schema.axonivy.com/ivy/12.0.11/ivy.json
 2# Microsoft SQL Server as System Database
 3# [engineDir]/configuration/ivy.yaml
 4SystemDb:
 5  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
 6  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
 7  UserName: root
 8  Password: 1234
 9
10  # Microsoft recommends not to use the instanceName and prefers specifying
11  # the port in the connection url. If you need to work with the instanceName
12  # you must remove the port in the connection url.
13  #DriverProperties:
14  #  instanceName: SqlServer

Note

On an SQL Server installation, only Windows Authentication is enabled by default. If you face problems connecting to the database, check if SQL Server Authentication is activated.

Windows Authentication

Microsoft SQL Server can be configured to use Windows credentials to log into the database.

There are two ways to do that:

  • configure a specific Windows user (and password),

  • user credentials of the user running the Axon Ivy Engine are used.

Using specific Windows user:

 1# yaml-language-server: $schema=https://json-schema.axonivy.com/ivy/12.0.11/ivy.json
 2# Microsoft SQL Server as System Database, specifying a Windows user to log
 3# into the database
 4# [engineDir]/configuration/ivy.yaml
 5SystemDb:
 6  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
 7  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
 8  UserName: myWindowsUser
 9  Password: myWindowsPassword
10  DriverProperties:
11    integratedSecurity: true
12    authenticationScheme: NTLM
13    domain: myWindowsDomain

Using user credentials of the process owner:

 1# yaml-language-server: $schema=https://json-schema.axonivy.com/ivy/12.0.11/ivy.json
 2# Microsoft SQL Server as System Database, using credentials of user running the
 3# Ivy Engine process
 4# [engineDir]/configuration/ivy.yaml
 5# NOTE: In order to use this feature, you need to copy the mssql-jdbc_auth DLL
 6#       file matching the JDBC driver version used by the Axon Ivy Engine into
 7#       the [engineDir]/jre/bin directory before starting the Axon Ivy Engine.
 8#       The easiest way to obtain this DLL is by downloading it from a Maven
 9#       repository, e.g.
10#       https://search.maven.org/artifact/com.microsoft.sqlserver/mssql-jdbc_auth.
11SystemDb:
12  # Format: jdbc:sqlserver://[<host>[:<port>]][;databaseName=<database name>]
13  Url: jdbc:sqlserver://localhost:1433;databaseName=AxonIvySystemDatabase
14  DriverProperties:
15    integratedSecurity: true

Instance Name

Microsoft recommends not to use the instanceName and prefers specifying the port in the connection URL, as for each connection, there is a round trip to determine the port. Therefore you will need to configure a static TCP port (e.g., 1433) in the SQL Server Configuration Manager.

If you need to work with the instanceName, you have to define it as an additional connection property and remove the port specification in the connection URL.

Tip

If you cannot connect to a Microsoft SQL Server, check first if the TCP/IP protocol for SQL Server is activated (by default, it is deactivated). If you still face problems, then you may check if SQL Server Authentication is activated. By default, only Windows Authentication is enabled.

If the database is created by the Axon Ivy Engine the collation is set to Latin1_General_CI_AI for SQL Server older than 2019 and Latin1_General_100_CI_AI_SC_UTF8 for SQL Server 2019 and newer.