Microsoft ODBC Driver 17.3.1.1 or later needs to be installed so that j5 can connect to Microsoft SQL Server.
-
Database Type: Select SQLServer.
-
Database Server: Enter the hostname or IP address of the database server. If you use a named instance, the name must be specified with the format "<host name>\<instance name>" (for example, "SQLSERVER1.EXAMPLE.COM\MSSQLSERVER"). By default, Microsoft SQL Server uses a named instance, "MSSQLSERVER".
If you have performance issues while connecting to the database from j5, change the
Database Server field from hostname to its IP address. This forces the connection to use TCP/IP instead
of Named Pipes. This has made noticeable changes to the performance when the connectivity
has been strained or hampered due to excessive load and additional routing overhead
(for example, when using a VPN).
-
Database Name / SID: Enter the name of the database.
-
Use Windows Authentication: Select this checkbox to use Windows Authentication to connect to the database. We recommend this method of authentication.
If Windows Authentication is used, appropriate database permissions must be given to the following users:
-
The j5 service user (by default, this is NT AUTHORITY\NETWORK SERVICE)
-
Any administrators that can run the j5 Setup Wizard (i.e. the current user)
-
Any administrators that can use the j5 System Management Console
-
-
Username: Alternatively, if SQL Server Authentication is used, enter the username that j5 uses to connect to the database.
-
Password: Enter the password for the j5 database user specified for SQL Server Authentication.
-
Port (optional): Specify the port number when the database port is different to the default. When the default port is used, this box can be left blank.
-
Bypass Database Connection Validation (optional): This is available when using Windows Authentication. It bypasses validation of the database certificate to allow connections when certificates have been misconfigured.
-
Encode all parameters: When True, j5 is forced to encode all parameters as VARCHAR before passing them to the database. This option should be used with caution (refer to the Encode all parameters note below).
Encode all parameters can be used to improve performance on SQL Server. However, there are limits to its
usage:
-
j5 automatically detects the correct encoding to use (based on the codepage of the database collation) when this option is selected. Not all codepages are supported. The Database Validation fails if this option is selected for a database with an unsupported codepage.
-
If your j5 database uses NVARCHAR columns in circumstances where you require j5 to store characters which cannot be encoded in the database codepage, this option should not be used, because it will cause data corruption for those characters.
-
The Reporting service uses the TCP/IP protocol to connect to the database. Ensure that the Server Protocol is enabled as follows:
-
Open the SQL Server Configuration Manager.
-
In the tree pane, select SQL Server Network Configuration to expand it, and then select Protocols for the relevant instance. If you specified the default instance during installation, the instance name is MSSQLSERVER
-
In the results pane, verify that, under the Status column, Enabled appears next to the name of the TCP/IP protocol. If Disabled appears, right-click TCP/IP, and then select Enable.
-
In Services, right-click SQL Server (MSSQLServer/MyInstanceName) and then select Restart.
-
In addition, it may be necessary to start the SQL Server Browser service, if SQL Server is not listening on the default TCP/IP port (1433), or you are connected to a non-default instance.