In this article

What is happening?

We are updating the JDBC driver for SQL Server that is bundled with the SQL Server Snap Pack.

There are some changes in how the JDBC driver connects to the SQL Server instances that might require changes in your SQL Server account configuration. To minimize the impact on production pipelines, this update is being rolled out in a way that allows impacted customers to take updates gradually and within a timeframe that works for them.

This document explains everything you need to know, and what action you need to take.

Why is this change being made?

This change is necessary to keep the libraries in the SQL Server Snap current. Keeping dependencies in our Snap Packs current is important to reduce security vulnerabilities and to allow customers to leverage features that are only available with the latest dependency versions.

What is the timeline of delivery?

Currently, the SQL JDBC driver upgrade can be selected in any Org by setting the SQL Server Snap Pack to 433patches21386 or 433patches21535.

With the July 2023 release, the JDBC driver upgrade for SQL Server Snap Pack will be set to the latest distribution.

With the August 2023 release, the upgrade will be available on both the stable and latest releases across all Orgs as they are updated to the August 2023 Snaplex version.

Will I be impacted?

You will be impacted if all of the following are true:

• You are using the SQL Server Snap Pack

• You do not define a JDBC driver JAR file

  • If you are not sure, refer to What JDBC driver am I using?

• Your SQL Server instance certificate is not signed through a trusted certificate authority

What JDBC driver am I using?

If you do not specify a value in the JDBC Driver field, you are using the default driver of the Snap Pack. This means you are probably impacted by the changes.

Open

If you renamed the JAR file to remove the version number, you need to download and unzip the JAR file to look at the manifest to determine its version. (For example, the screenshot above did not remove the 10.2.0 version number.)

What do I need to change?

Typically, SQL Server SSL certificates are not signed by a Trusted Certificate Authority (CA). As a result, your Snaplex will not trust the certificate that SQL Server uses for connection. If this is the case, you must add the trustServerCertificate URL connection property set to true in the Url properties field (as shown in the following screenshot).

Open

How can I verify that the changes work?

In a non-production Org

1. In your non-production Org, set your SQL Server Snap Pack version to 433patches21386 or 433patches21535.

2. Connect to the same account as your production instances (if possible).

In a production Org

1. If you have only a production Org to validate connections to your SQL Server instance(s), you may upload a SQL Server JDBC driver JAR compatible with the update to the Snap Pack (version 12.2, available on the Microsoft Download Website).

2. Upload that JAR file to your Org’s shared folder and specify it in the SQL Server Account (as described in the “What JDBC driver am I using?” section).

3. Validate the account and modify properties specified in the “What do I need to change?” section.

   After you verify that the updated JDBC driver works, you may remove it, but keep the property setting. We recommend that you keep the trustServerCertificate set to true (if you need to specify it) to ensure readiness for the August 2023 release.

Automate the required SQL server account updates

Use this pipeline to automatically update SQL server accounts.

1. Import the project into your organization. Learn more.
   The /<org>/shared folder is an appropriate location.

2. Update the pipeline parameters as necessary:
   
   

    

3. The value for updateAccounts should be false until you are ready to make changes to your SQL Server accounts.

4. The value for orgName is the name of your Org in SnapLogic. Your Org is displayed in the dropdown in the upper-right corner of the Designer:

5. You should first run with updateAccounts set to false. This simply catalogs all SQL Server accounts (for the SQL Server Snap Pack) and places them into one of three buckets:

   1. Accounts unaffected by the change because they specify their own JDBC driver JAR.

   2. Accounts unaffected by the change because they already have the trustServerCertificate URL connection property set to true in the Url properties field.

   3. Accounts affected by the change because they don’t specify their own JDBC driver JAR, and they don’t set the URL connection property. (These are the ones that would be updated to include the property by this pipeline when running with updateAccounts set to true.)

6. This pipeline writes a file (sql_server_accounts.csv) in the folder where the pipeline has been imported. This file has the following structure (it is a semicolon-delimited file):

   action;count;accounts "has_custom_jar";"#";"Account_Paths" "has_trustServerCertificate_property";"#";"Account_Paths" "needs_trustServerCertificate_property";"#";"Account_Paths"

7. If you want those accounts to be updated automatically, you can set the pipeline parameter updateAccounts to true so that all the accounts pulled out and specified in the dry run will be updated when you run the pipeline. You may update the pipeline to add filters to prevent some accounts from being updated.

8. If you prefer to manually update accounts, you are now aware of the accounts that need to be reconfigured (the CSV file contains the path of the accounts in the specific category).

Attachments