Connect to Microsoft using the Database connector in Jitterbit Integration Studio

This page details how to connect to several Microsoft services using the Database connector. There are instructions for connecting to Microsoft Access, Microsoft Excel, and Microsoft SQL Server.

Microsoft Access

You must be running a private agent in order to connect to Microsoft Access. Microsoft Office must be installed on the same system that the private agent is running on, and that system must be a Windows operating system. Note that the Jitterbit team has not tested Linux ODBC drivers for Microsoft Access.

Install the ODBC driver

The Microsoft Access ODBC driver is typically installed as part of Windows. Older drivers support the .mdb file extension. However, if your database is Access 2007 or later with the .accdb extension, you may need to obtain a later version of the ODBC driver such as the Microsoft Access 2016 Redistributable.

Configure the Database connection

After the driver is installed, specify these inputs during configuration of the Database connection:

Driver Type: Select ODBC.
Driver: Select the Microsoft Access driver.
Use Connection String: Under Optional Settings, select this checkbox.
Connection String: Under Optional Settings, enter the manual connection string using this format:
```
DRIVER=<driver>;DBQ=<database-path>;UID=<username>;PWD=<password>;
```
Use one of these connection strings, depending on the version and whether you have a password. Replace the folder path and file name with those specific to the file located on your private agent.
Microsoft Access Driver (\*.mdb) without Password
```
DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\myfolder\mydatabase.accdb;
```
Microsoft Access Driver (\*.mdb, \*.accdb) with Password
```
DRIVER={Microsoft Access Driver(*.mdb, *accdb)};DBQ=C:\myfolder\mydatabase.accdb;UID=username;PWD=password;
```

Configure database activities

During Database activity configuration, note that the Microsoft Access ODBC driver does not support searching for objects. Do not enter search criteria into the table selection search box, as no results will be returned.

WHERE clause examples

These WHERE clause examples are provided for reference purposes when configuring a Database query activity:

WHERE [LastName]='Bagel'

WHERE field1 LIKE field2

WHERE City = "Chicago" OR BirthDate < DateAdd (" yyyy ", -40, Date())

For additional examples, refer to Microsoft's documentation:

Microsoft Excel

You must be using a private agent in order to connect to a Microsoft Excel spreadsheet.

Install the ODBC driver

To connect to Excel spreadsheets, you may need to install a driver if the appropriate driver is not already included with the private agent's operating system.

For example, Windows 10 comes with x32 .xls ODBC drivers that support Excel versions 3.0, 4.0, 5.0/95, and 97-2000. However, to get the newer ODBC drivers that support all of these versions and Excel 12.0 (Excel 2007), you can install one of these drivers:

Microsoft Access 2016 Runtime (Win 7/8/10/2008 R2/2012)
Microsoft Access 2016 Redistributable

Configure the Database connection

After the driver is installed, specify these inputs during configuration of the Database connection:

Driver Type: Select ODBC.
Driver: Select the Microsoft Excel driver.
Use Connection String: Under Optional Settings, select this checkbox.
Connection String: Under Optional Settings, use one of these connection strings, depending on your version of Excel. Replace the folder path and file name with those specific to the file located on the private agent.
Excel 97, Excel 2000, Excel 2002, Excel 2003
```
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\Folder Path\My File Name.xls;
```
Excel 2007, Excel 2010, Excel 2013
```
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\Folder Path\My File Name.xlsx;
```
To specify the connection to be updateable (for example, if you want to allow writing to a target file), you can add ReadOnly=0; to the end of the connection string. For example:
Updateable
```
Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
DBQ=C:\Folder Path\My File Name.xlsx;ReadOnly=0;
```
Tip

See additional options for Excel connection strings at Microsoft Excel 2007 ODBC Driver connection strings in the The Connection Strings Reference.

Configure database activities

During Database activity configuration, you can select and join worksheets within the Excel spreadsheet:

attachment

Note

On running the operation, if you receive an error "[Microsoft][ODBC Excel Driver] Operation must use an updateable query," then the Excel file may be read only. You can allow your Excel file to be updateable by appending ReadOnly=0; to your connection string while configuring the endpoint as described above.

Microsoft SQL Server

Microsoft SQL Server databases are accessible from Jitterbit agents running on Windows or Linux, with Windows ODBC on private agents recommended for optimized performance. However, note that if you want to use additional features, such as being able to provide a manual query, these are available using the JDBC driver only. Harmony supports these authentication options:

MS SQL ODBC on Windows using Windows authentication or SQL Server authentication
MS SQL JDBC on Windows using Windows authentication or SQL Server authentication
MS SQL JDBC on Linux using SQL Server authentication

The procedures below show how to configure Microsoft SQL Server as a Database connection using either Windows or SQL Server authentication, followed by examples of WHERE clauses that can be used with database activities.

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:

Open the Services Administrative tool (Start > Administrative Tools > Services).
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.
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.
Repeat for the Jitterbit Process Engine service.
Set TempDir in the agent configuration file (jitterbit.conf) to C:\Windows\Temp\jitterbit.
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. Make sure the domain user has read and write privileges on the private agent install directory.

Note

An alternative to 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 (<domainName>\<machineName>$).

After these steps are completed, go to Integration Studio and set up your database as usual, specifying these inputs during configuration of the Database connection:

Driver Type: Select ODBC or JDBC as appropriate.
Important

Windows authentication is supported with the following JDBC drivers:
- SQL Server jTDS [JDBC]
- SQL Server Microsoft [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 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.
Driver: Select the SQL Server driver.
Server Name: Enter the name or IP address of the server running the SQL Server that Harmony needs to connect to. You may need to specify the SQL Server instance name (HostName\InstanceName).
Database Name: Enter the name of the database on the server that Harmony needs to connect to.
Login and Password: Leave these fields blank.
Additional Connection String Parameters: Under Optional Settings, enter the following depending on your driver:
- ODBC SQL Server: If you are using the ODBC SQL Server driver enter integratedSecurity=true. If this does not work, enter Trusted_Connection=yes.
- ODBC Driver 11 for SQL Server, SQL Server Native Client 10.0, SQL Server Native Client 11.0: If you are using another ODBC 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 Integration Studio and set up your database as usual, specifying these inputs during configuration of the Database connection:

Driver Type: Select either ODBC or JDBC as appropriate.
Driver: Select the SQL Server driver. When selecting a JDBC driver, we recommend using SQL Server MS JDBC.
Server Name: Enter the name or IP address of the server running the SQL Server that Harmony needs to connect to. You may need to specify the SQL Server instance name (HostName\InstanceName).
Database Name: Enter the name of the database on the server that Harmony needs to connect to.
Login and Password: Enter the username and password for SQL Server authentication.

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

WHERE clause examples

These WHERE clause examples are provided for reference purposes when configuring a Database query activity:

WHERE LastName = 'Smith' ;

WHERE LastName LIKE ('%Smi%');

WHERE EmployeeKey  <= 500;

WHERE EmployeeKey = 1 OR EmployeeKey = 8 OR EmployeeKey = 12;

WHERE EmployeeKey <= 500 AND LastName LIKE '%Smi%' AND FirstName LIKE '%A%';

WHERE LastName IN ('Smith', 'Godfrey', 'Johnson');

WHERE EmployeeKey Between 100 AND 200;

For additional examples, refer to Microsoft's documentation: