Configure a Microsoft SQL database source in Jitterbit Design Studio

This page describes how to configure Microsoft SQL Server as a source or target within Jitterbit Studio using either Windows or SQL Server authentication. See Database for information on the types of authentication supported for various systems.

Windows authentication

Windows authentication is supported using ODBC and JDBC on private agents only, and can be used with only one domain account. To use Windows authentication, set up these properties in Windows:

1. Open the Services Administrative tool (Start > Administrative Tools > Services).
2. If using ODBC, right-click the Jitterbit Apache Server service and select Properties. If using JDBC, right-click the Jitterbit Tomcat Server service and select Properties.
3. Go to the Log On tab. Select This account and enter the name and credentials of the account you want to use for authentication. Then click Apply.
4. Repeat for the Jitterbit Process Engine service.
5. Set TempDir in the agent configuration file (jitterbit.conf) to C:\Windows\Temp\jitterbit.
6. Restart Jitterbit services.

Once the above steps are completed, go to Jitterbit Studio and set up your source or target as normal. In the database source/target definition screen under Connection Parameters, specify the following:

• Driver Type: Select ODBC or JDBC as appropriate.

  Important

  Windows authentication is supported with the following JDBC drivers:

  • SQL Server jTDS [JDBC]
  • SQL Server MS JDBC [JDBC]
  • Newer versions of the Microsoft JDBC Driver for SQL Server

  To use Windows authentication with the Microsoft JDBC drivers, copy the mssql-jdbc_auth-x.x.x.x64.dll file from the driver's download package to the C:\Program Files\Jitterbit Agent\jre\bin folder on the agent. The DLL version must match the version of the JDBC JAR file bundled with the agent. Make a backup copy of the file as it may be deleted during major agent upgrades.

• Server Name: Enter the name or IP address of server that runs the SQL Server that Jitterbit needs to connect to. You may have to specify the SQL Server instance name (HostName\InstanceName).

• Database Name: Enter the name of the database on the server that Jitterbit needs to integrate to.
• Login: Leave this field blank.
• Password: Leave this field blank.

• Options: Click to expand additional settings. In the field Additional Connection String Parameters, enter the following depending on your driver:

  • SQL Server [ODBC]: If you are using the "SQL Server [ODBC]" driver enter integratedSecurity=true. If this does not work, enter Trusted_Connection=yes.
  • ODBC Driver 11 for SQL Server [ODBC], SQL Server Native Client 10.0 [ODBC], SQL Server Native Client 11.0 [ODBC]: If you are using another SQL server driver, enter Trusted_Connection=yes.
  • SQL Server jTDS [JDBC], SQL Server MS JDBC [JDBC]: If you are using a JDBC SQL Server driver enter integratedSecurity=true.

The driver will now authenticate as the Windows domain user specified above.

SQL server authentication

Go to Jitterbit Studio and set up your source or target as normal. In the source/target definition screen under Connection Parameters, specify the following:

• Driver: The SQL Server driver may be an ODBC or JDBC driver.

  Note

  When selecting a JDBC driver, we recommend using "SQL Server MS JDBC [JDBC]," Microsoft's JDBC driver for SQL Server, which is bundled with Jitterbit agents as of version 9.3.

• Server Name: Enter the name or IP address of server that runs the SQL Server that Jitterbit needs to connect to. You may have to specify the SQL Server instance name (HostName\InstanceName).

• Database Name: Enter the name of the database on the server that Jitterbit needs to integrate to.

• Login: Enter the username for SQL Server authentication.

• Password: Enter the password for SQL Server authentication.

The driver will now authenticate using the SQL Server authentication credentials as specified.

Connection encryption and server certificates

Current versions of the SQL Server MS JDBC driver request an encrypted connection by default (encrypt=true) and validate the certificate that the database server presents. If the driver can't validate the server's certificate against the agent's Java keystore, the connection fails with an error similar to this one:

"encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: (certificate_unknown) PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

This affects any SQL Server whose certificate can't be traced to a certificate authority (CA) that the agent already trusts, including a self-signed or internally issued certificate. Amazon RDS for SQL Server is one example: an RDS instance presents a certificate issued by an Amazon RDS CA that isn't in the agent's default keystore. A database can have encryption enabled and a valid certificate installed and still fail this validation, because the failure is one of trust rather than of encryption.

Agent version 12.8 updated the bundled Microsoft SQL Server JDBC driver to version 13.4.0. Microsoft changed the default value of encrypt from false to true in driver version 10.2 and documents that change as a breaking one, described under encrypt in Set the connection properties in the Microsoft documentation. A source that worked on an agent earlier than 12.8 can therefore begin to fail after the upgrade.

To resolve this, expand Options and enter encrypt=false; in the Additional Connection String Parameters field, or include it in the connection string if you selected Construct connection string manually. This works on cloud and private agents.

Your SQL Server setup may require other connection properties as well. For the properties the driver supports, see Set the connection properties in the Microsoft documentation.