Skip to Content

SQL Server connection guide for Jitterbit App Builder

Overview

This guide describes the system requirements and instructions for connecting App Builder to a Microsoft® SQL Server database server. Two different methods of Connections will be described in this guide: SQL Server Authentication and Integrated Windows Authentication.

The SQL Server Authentication method is where you provide the SQL Server Name and Password credentials as part of the connection information. SQL Server performs the authentication by checking to see if a SQL Server login account has been set up and if the specified password matches the one stored. If SQL Server does not have a login account configured, authentication fails, and the user receives an error message.

Integrated Windows Authentication is where you omit the SQL Server Name and Password credentials as part of the connection information. With this method App Builder attempts to authenticate to the SQL Server as the Windows User that App Builder is running as, which is defined in the application pool settings of IIS.

Important

Integrated Windows Authentication requires that the IIS application pool for the instance is set to a Windows User, and that User has permissions to the SQL Server database.

System requirements

Database server

Microsoft® SQL Server

Client web browser

  • Chrome: 84+

  • Firefox: 73+

  • Safari: 13.0.1+

  • Edge: 84+

Connection instructions

Adding a server

  1. Navigate to the App Builder IDE

  2. Click the Data Servers button from the Connect menu

  3. Click the + Server button on the Data Servers panel. This will bring up the Server page.

Filling in credentials

To connect to a Microsoft ® SQL Server, provide the following information:

  • Server Name: An identifiable and unique server name.

  • Type: Select "Microsoft SQL Server" from the menu.

  • Host Name: The hostname of your server. For example: localhost

  • Port: The port number for the server. The default port is 1433.

  • Instance Name: If you installed the server with a named instance, enter the name you supplied for that instance here.

  • Advanced Settings: Includes additional configuration options including the Ping Timeout setting, which determines the amount of time (in seconds) that App Builder will wait for a ping back from the server before declaring a timeout.

  • Click Security Settings to expand:

    • User Name:

      • For SQL Server Authentication: provide the user name with which you access the database server

      • For Integrated Windows Authentication: leave this field blank

    • Password:

      • For SQL Server Authentication: provide the password associated with the supplied user name

      • For Integrated Windows Authentication: leave this field blank

    attachment

After filling in all necessary credentials, click the Save button to save the settings.

Testing the connection

Click Testing to expand, and then click the Test Connection button to make sure the server is properly connected.

Adding a data source

After the SQL Server settings have been saved, the database needs to be connected to App Builder. Databases that are connected to App Builder are referred to as a "data source".

  1. Navigate to the App Builder IDE

  2. Click the Data Servers button from the Connect menu

  3. Click to select the SQL Server entry from the Data Servers panel, then click + Source > + Create Database from the right-hand panel. Some data sources are automatically imported to the App Builder data storage page. If the data source has already been added, click on the associated Details button to bring up the details page.

  4. Fill in the following credentials for the data source:

    1. Connection: The name of the server being drawn from

    2. Data Source Name: Give the data source a unique display name

    3. Generated Database Name: Autogenerates based on provided Data Source Name

  5. Click Next

  6. Review and click the Done button

  7. Click the Details button for the newly created Data Source

  8. Click the Edit button, then click Edit again to make any changes

  9. The Schema Name field allows the user to select a Schema name from a drop down menu

  10. Click to expand the Import Capabilities section

    1. Import Pattern: This allows users to import only certain tables by listing them in the box. Prefixes can also be used to only import a subset of tables (for example, MyApp_* will only import tables that start with "MyApp_"). If the space is left blank, it will import all tables.

      attachment

    2. Click Save to update any changed data source settings

    3. Once the page refreshes, click the Import button that appears below the source credentials. When prompted, click Proceed. The import will be scheduled by App Builder to run in the background.

      attachment