Before you begin
The following steps to configure Microsoft SQL are necessary to avoid connection errors from a blacklisted host. Before setting up a connection to Microsoft SQL, enable TCP/IP and Named pipes protocols in MS SQL server:
- Sign in to SQL Server Configuration Manager.
- Open SQL Server Network Configuration and verify that both TCP/IP and Named pipes are allowed.
- Right-click the TCP/IP protocol and select Properties to view the TCP/IP Communication Port. The default port is 1433, which you can change for security purposes. Record this value for your connection’s settings.
Verify that Allow remote connections for this server is enabled:
- Open SQL Server Management Studio (SSMS).
- Right-click the instance name and select Properties.
- Open the Properties Connections tab.
- Click Allow remote connections to this server if it is not already checked.
TIP: As mentioned, the default port is 1433, and the backup port is 1434. To verify which port MS SQL is running on, open SSMS, navigate to Management > SQL Server Logs, and search all logs for the string “Server is listening on,” which contains the current port value.
A. Set up a Microsoft SQL connection
Start establishing a connection to Microsoft SQL in either of the following ways:
- From the Resources menu, select Connections. Then, click + Create connection at the top right.
– or –
- While working in a new or existing integration, you can add an application to a flow simply by clicking Add source or Add destination/lookup.
In the resulting Application list, click Microsoft SQL.
B. Supply general Microsoft SQL settings
At this point, you’re presented with initial options for providing the new MS SQL connection.
Name (required): How you want to refer to this connection in integrator.io.
Application: default value is Microsoft SQL.
Mode (required): Select one of the following options:
- Cloud to connect to a publicly accessible server application.
- On-premise to connect to a server that is publicly inaccessible and has integrator.io agent installed on it, such as a production MS SQL Server environment.
Agent (required, if On-premise selected for Mode; otherwise hidden): Select an agent from the list. To connect to an on-premise application, integrator.io requires that an agent be installed on a networked computer. An agent is a small application that allows you to connect to data behind your firewall. When installing an agent, you will specify a unique access token, which then populates the Agent drop-down list. The installed agents connect to integrator.io and establish a reverse SSH tunnel, allowing secure communication without the need to whitelist integrator.io’s IP addresses in your firewall settings. A single agent can be used by multiple different connections.
C. Provide Microsoft SQL authentication
SQL server version (required): Select one of the following server versions:
- SQL Server 2008 R2 (no longer supported by Microsoft)
- SQL Server 2012
- SQL Server 2014
- SQL Server 2016
- SQL Server 2017
Important: Celigo strongly recommends NOT using MSSQL server version 2008 R2 since it's no longer supported by Microsoft. If you are unable to connect, or if you run into trouble in any way while integrating, you won't get any support from Microsoft for version 2008 R2, and the Celigo Support team will not be able to help you resolve your issues. Upgrade to a newer MSSQL server version before building integrations.
Host (required): Enter the hostname or IP address of the server to connect to. These values were created during SQL Server setup.
Database name (required): Provide the database name created during SQL Server setup.
Username (required): Enter the username created by the SQL Server administrator for this account.
Password (required): Enter the password created by the SQL Server administrator for this account.
Instance name (or Port required): Specify the instance name to connect to, assuming that the SQL Server Browser service is running on the database server and UDP port 1434 is reachable. If you set the instance name, you cannot also set the Port, below; they are mutually exclusive connection options.
Port (or Instance name required): Enter the TCP port for the database engine, which is 1433 by default in SQL Server. Port 1433 is also the official Internet Assigned Number Authority (IANA) socket number for SQL Server, and SQL Server Management Studio (SSMS) uses it to manage SQL Server instances across the network.
D. Edit advanced Microsoft SQL settings
Before continuing, you have the opportunity to provide additional configuration information, if needed, for the Microsoft SQL connection.
Configure properties (optional): You can use these fields to provide additional name-value pairs to be sent to SQL Server with the connection.
Borrow concurrency from (optional): Select another connection from the list....
Concurrency level (optional): Up to 25 allowed....
E. Test the connection
Once you have configured the Microsoft SQL connection, you have a few options for continuing:
- Save – click this button to test the connection, commit the new connection so that it will be available to all integrations for your account
- Save & close – click to test and save the connection and exit the Create connection pane
- Cancel – click to exit without saving any new changes
- Test connection – click this button to verify that your new connection is free of errors
When you test or save the connection, it is verified before continuing.
The new connection is now successfully added to your account. It will be applied to the current source or destination app, if you created it within a flow. Otherwise, you may proceed to register the connection with an integration.