Upgrading the Microsoft SQL JDBC driver (database driver)

This document details how to upgrade the PaperCut MS SQL JDBC driver to a more recent version.

Why upgrade?

PaperCut NG/MF versions up to (and including) version 24 come with the MS JDBC Driver 7.0 for SQL Server.

Consider upgrading the JDBC driver to take advantage of the improvements Microsoft have 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 later 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 later version of the MS driver.

1. Pre-upgrade checks

Before updating the driver, ensure the following. Note that this assumes a default PaperCut version 24 or earlier installation. If you have made changes to the PaperCut installation, these steps might be different.

• Check you have a properly configured SQL Server.
  • Make any backups/snapshots of the Application Server as necessary.
  • Ensure that remote connections are enabled.
  • If you’re using Windows Authentication with MS SQL , confirm that authentication mode allows Windows Authentication.
    • Ensure the Application Server is joined to the correct domain.
    • Verify that the service account running the Application Server has the necessary permissions to access the SQL Server.
  • If using named instances, ensure that the SQL Server Browser service is running .
• Check your existing PaperCut NG/MF driver and dll files.
  • In [app server install dir]/server/lib/
    • Confirm that there is only one mssql jar file in this directory, i.e. mssql-jdbc-7.0.0.jre10.jar, which is the one that comes packaged with PaperCut NG/MF 24.x and earlier.
  • In [app server install dir]/server/lib-ext/
    • Ensure there are no MSSQL JDBC driver files in this directory, that is, no .jar files like mssql-jdbc-*.jar.
  • In [app server install dir]/server/bin/win/lib64/
    • Confirm there is a current working auth.dll file, i.e. sqljdbc_auth.dll.

2. Download the 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 using successfully using that version. If you need to use a more recent version or specific version due to environment specific requirements, we highly recommend testing beforehand.
3. Unzip the JDBC file. You should see a folder structure including two relevant subfolders: jars and auth/x64.

3. Upgrade the JDBC Driver and auth dll

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. Copy the new jars/mssql-jdbc-12.x.x.jre11.jar file from the MS JDBC Driver download folder into [app server install dir]/server/lib-ext/
5. [Windows only] Navigate to [app server install dir]/server/bin/win/lib64/
6. [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.
7. [Windows only] Copy the new auth/x64/mssql-jdbc_auth-12.x.x.x64.dll file from the MS JDBC Driver download folder into [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 MS SQL 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.

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. 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.