Installing or upgrading the Microsoft SQL JDBC driver (database driver)

This document describes how to install the MS SQL JDBC driver or upgrade the driver to a more recent version.

Driver new installation vs driver upgrade

There are important differences between PaperCut NG/MF versions 24.x and earlier, and PaperCut NG/MF version 25.x and later:

• PaperCut NG/MF versions 25 and later include a stable, tested JDBC driver in the server/repository/database-drivers/sqlserver folder. The instructions below describe how to activate it.
• PaperCut NG/MF versions 24 and earlier came with the MS JDBC Driver 7.0 for SQL Server. If you’re currently using PaperCut NG/MF version 24.x or earlier, or if you’ve upgraded from a pre-25.0 version to 25.0 or later, you’re likely using this driver.

You should consider upgrading the MS JDBC driver (especially if you’re using the default JDBC version 7.0 driver — see Check the database driver in use ) to take advantage of the improvements Microsoft has made:

• SQL Server version support — Stay in line with Microsoft’s SQL version compatibility recommendations, especially if you are using MS SQL Server versions 2019, 2022, or later.
• Performance improvements — When using MS SQL Server 2016 or later, there are performance improvements that can be gained by using a newer version of the driver. If you are seeing high CPU on your SQL Server, this is a good indication that you should use an updated JDBC driver. See MS SQL JDBC driver performance at the end of this article for more information on performance.
• Security preferences — Security requirements for your organization might require a newer version of the MS driver.

1. Pre-upgrade checks (upgrade only)

If you’re updating the database driver, we highly recommend making a backup or snapshot of the Application Server, which can be used to roll back the changes if necessary.

2. Download the MS SQL JDBC driver

Version 25.0 and later

If you are running PaperCut NG/MF version 25 and later you will find our endorsed MS SQL JDBC drivers in the Application Server file system, under [app server install dir]/server/repository/database-drivers/sqlserver.

In this case you don't need to download the driver unless you need to use a specific version of the MS JDBC driver.

Version 24.x and earlier (or if you require a specific driver version)

If you are running a PaperCut version prior to version 25.0, or if you require a specific driver version that is not included in the [app server install dir]/server/repository/database-drivers/sqlserver folder, follow these steps to download a suitable MS JDBC driver.

1. Visit the Microsoft JDBC Driver for SQL Server download page (or view their previous releases page if necessary).

2. Download the driver version you require (see Microsoft's JDBC Driver for SQL Server support matrix for more information). We have tested with version 12.8.1 and a number of customers are successfully using that version. This driver version is compatible with MS SQL Server version 2016 and later.

If you need to use a more recent version or a specific version due to environment-specific requirements, we highly recommend testing beforehand. For example, if you're using MS SQL Server 2014, Microsoft lists version 12.6 as the most recent MS database driver version supported with SQL Server 2014.

3. Install or upgrade the MS SQL JDBC Driver and auth dll

Installing or upgrading the JDBC Driver with Version 25.0 and later (brand new installation)

1. Copy the mssql-jdbc-12.x.x.jre11.jar file:
   from [app server install dir]/server/repository/database-drivers/sqlserver (or from the jars folder in the MS JDBC Driver you downloaded earlier)
   to [app server install dir]/server/lib-ext/

2. [Windows only] Copy the mssql-jdbc_auth-12.x.x.x64.dll file:
   from [app server install dir]/server/repository/database-drivers/sqlserver (or from the auth/x64 folder in the MS JDBC Driver you downloaded earlier)
   to [app server install dir]/server/bin/win/lib64/

Upgrading the JDBC Driver with Version 25.0 and later (after an upgrade from a 24.x or earlier version)

1. Stop the PaperCut Application Server Service.

2. Navigate to [app server install dir]/server/lib/.

3. Move any existing mssql jdbc .jar files (for example, mssql*.jar) out of this location into a temporary folder that can be used to roll back the changes if necessary. Make sure this folder is on the desktop, for example, C:\Users\[username]\Desktop\, or other location outside of the PaperCut NG/MF installation directory structure entirely.

4. Navigate to [app server install dir]/server/lib-ext/.

5. Move any existing mssql jdbc .jar files (e.g. mssql*.jar) out of this location into a temporary folder that can be used to roll back the changes if necessary. Make sure this folder is on the desktop, for example, C:\Users\[username]\Desktop\, or other location outside of the PaperCut NG/MF installation directory structure entirely.

6. Copy the mssql-jdbc-12.x.x.jre11.jar file:
   from [app server install dir]/server/repository/database-drivers/sqlserver (or from the jars folder in the MS JDBC Driver you downloaded earlier)
   to [app server install dir]/server/lib-ext/.

7. [Windows only] Navigate to [app server install dir]/server/bin/win/lib64/.

8. [Windows only] Move any existing mssql auth .dll files (for example, sqljdbc_auth.dll or mssql-jdbc_auth*.dll) out of this location into a temporary folder that can be used to roll back the changes if necessary. Make sure this folder is on the desktop, for example, C:\Users\[username]\Desktop\, or other location outside of the PaperCut NG/MF installation directory structure entirely.

9. [Windows only] Copy the mssql-jdbc_auth-12.x.x.x64.dll file:
   from [app server install dir]/server/repository/database-drivers/sqlserver (or from the auth/x64 folder in the MS JDBC Driver you downloaded earlier)
   to [app server install dir]/server/bin/win/lib64/.

Upgrading the JDBC Driver with Version 24.x and earlier (new installation or upgrade)

1. Stop the PaperCut Application Server Service.

2. Navigate to [app server install dir]/server/lib/.

3. Move any existing mssql jdbc .jar files (e.g. mssql*.jar) out of this location into a temporary folder that can be used to roll back the changes if necessary. Make sure this folder is on the desktop, for example, C:\Users\[username]\Desktop\, or other location outside of the PaperCut NG/MF installation directory structure entirely.

4. Navigate to [app server install dir]/server/lib-ext/.

5. Move any existing mssql jdbc .jar files (e.g. mssql*.jar) out of this location into a temporary folder that can be used to roll back the changes if necessary. Make sure this folder is on the desktop, for example, C:\Users\[username]\Desktop\, or other location outside of the PaperCut NG/MF installation directory structure entirely.

6. Copy the mssql-jdbc-12.x.x.jre11.jar file:
   from the jars folder in the MS JDBC Driver you downloaded earlier
   to [app server install dir]/server/lib-ext/.

7. [Windows only] Navigate to [app server install dir]/server/bin/win/lib64/.

8. [Windows only] Move any existing mssql auth .dll files (for example, sqljdbc_auth.dll or mssql-jdbc_auth*.dll) out of this location into a temporary folder that can be used to roll back the changes if necessary. Make sure this folder is on the desktop, for example, C:\Users\[username]\Desktop\, or other location outside of the PaperCut NG/MF installation directory structure entirely.

9. [Windows only] Copy the mssql-jdbc_auth-12.x.x.x64.dll file:
   from the auth/x64 folder in the MS JDBC Driver you downloaded earlier
   to [app server install dir]/server/bin/win/lib64/.

4. Modify the PaperCut NG/MF SQL connection string

Update the connection string that PaperCut NG/MF uses to connect to the database.

Why does the string need updating?

Microsoft’s SQL Server JDBC driver updates have changed the ‘default behavior’ when using a particular driver version. For example in Microsoft’s release notes for version 10.2 there was a breaking change that enables TLS encryption by default.

This means that to do a like-for-like upgrade (for example, from using the defaults used with the packaged MS JDBC version 7 driver, to upgrading to the MS JDBC version 12 driver), certain new defaults have to be overridden in the connection string.

Even if you’re not upgrading, but setting up an external SQL database for the first time, we recommend working with your database administrator to confirm which parameters are required in your environment.

Updating the string

1. Navigate to [app server install dir]/server/
2. Open server.properties in a text editor. (You will need to open the text editor as an administrator.)
3. Update the connection string listed after database.url= to include:
   ;encrypt=false;trustServerCertificate=true

For example (this is only an example depending on your organization’s previous requirements) your current connection string might look like:
jdbc:sqlserver://sqlservername:1433;databaseName=databasename;integratedSecurity=true;sendStringParametersAsUnicode=false;socketTimeout=600000

Your new string would then look like:
jdbc:sqlserver://sqlservername:1433;databaseName=databasename;integratedSecurity=true;sendStringParametersAsUnicode=false;socketTimeout=600000;encrypt=false;trustServerCertificate=true

Database connection string parameter notes

• encrypt=false
  • Chosen because the default value of encrypt is “true” in driver versions 10.2 and later and “false” in driver versions 9.4 and earlier. Since this article discusses migrations from version 7 to 12, in a like-for-like configuration, this should be set to “false” to preserve the current (old) behavior. However, it can be changed if your organization has already configured encryption to be enabled, or does so in the future. Note: if your SQL server has been configured to require encryption, then encryption is enforced, regardless of this setting.
• trustServerCertificate=true
  • Chosen because the default value of “false” will force validation of the TLS/SSL certificate, which can’t be done with the default self-signed certificate used by the SQL Server. Since this is a like-for-like migration with default settings, “true” will mean that it doesn’t validate the certificate. This can be changed if your organization configures encryption with a trusted certificate. Note: if you don’t want to trust the self-signed certificate, work with your DBA to configure a CA signed certificate.
• integratedSecurity=true
  • If you are Using Windows Authentication with Microsoft SQL Server , this will have been set to “true” in your old string, too.
• Other optional parameters:
  • See Configuring MS SQL Server (Choose a database collation) for more information on sendStringParametersAsUnicode.

5. Start the Application Server and test

1. Start PaperCut Application Server Service.
2. Log in to the PaperCut admin interface, then go to About > System info.
3. Check if the connection works:
   1. Attempt to load the UI.
   2. Review logs in [app server install dir]/server/logs/server.log for errors.

If authentication fails

• Ensure the correct version of the SQL Driver, for example, mssql-jdbc-12.x.x.jre11.jar sql driver is in [app server install dir]/server/lib-ext/. Note that any other mssql-jdbc driver .jar files should be moved out of this directory.
• Ensure that no mssql*.jar files are in [app server install dir]/server/lib/.
• Ensure the correct version of the Authentication DLL e.g. mssql-jdbc_auth-12.x.x.x64.dll is located in [app server install dir]/server/bin/win/lib64/. Note that any other mssql-jdbc DLLs should be moved out of this directory.
• Double-check the connection string for typos.
• If any changes have been made as a result of troubleshooting, restart the Application Server service and test again.

MS SQL JDBC driver performance

In our load testing, we have measured the difference between the version 7 driver and the version 12 driver. This testing was done under load and performed against an SQL Server 2022 instance.

Our testing showed up to a 50% reduction in the CPU load on the SQL Server.

Confirming the version of driver in use

To check the version of the database driver (and database version) in use, check out the Database version and database driver version section of the “How to check PaperCut NG/MF component version numbers” article.