Skip to Content

Configure a Microsoft SQL database target 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.

Caution

Make sure that you have granted the domain user a privilege of Log on as a service and Act as part of the operating system. Also make sure the domain user has read and write rights on the Jitterbit install directory.

Note

An alternative to the above steps 1 to 4 is to grant the account being used on the private agent machine permissions to the SQL Server. This can be done by the SQL Server administrator by setting the private agent machine account in Windows Active Directory (e.g. <domainName>\<machineName>$).

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:

    To use Windows authentication with the Microsoft JDBC drivers, copy the mssql-jdbc_auth-x.x.x.x64.dll file bundled with the driver's download package to the C:\Program Files\Jitterbit Agent\jre\bin folder on 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 Microsoft [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]," 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.