Set up a connection to Microsoft SQL

Tom Santiago

Updated March 19, 2024 11:02

Microsoft SQL Server is a database management system that helps to store and retrieve data used by other applications.

Microsoft SQL links: API guide, Authentication

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.

Note: integrator.io uses the tedious npm module to automatically encrypt the connection. No further encryption steps are necessary.

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:

Azure
SQL Server 2008 R2 (no longer supported by Microsoft)
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017 (also applies to SQL Server 2019 connections)

Important: Celigo strongly recommends not selecting MSSQL server version 2008 R2 since it’s no longer supported by Microsoft. If you are unable to connect or run into trouble while integrating to this version, you won’t get any support from Microsoft, 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. You can use any of the following advanced configuration options:

Name	Value
`abortTransactionOnError`	A boolean determining whether to rollback a transaction automatically if any error is encountered during the given transaction's execution. This sets the value for SET XACT_ABORT during the initial SQL phase of a connection (documentation).
`appName`	Application name used for identifying a specific application in profiling, logging or tracing tools of SQL Server. (default: Tedious)
`camelCaseColumns`	A boolean, controlling whether the column names returned will have the first letter converted to lower case (true) or not. This value is ignored if you provide a columnNameReplacer. (default: false).
`cancelTimeout`	The number of milliseconds before the cancel (abort) of a request is considered failed (default: 5000).
`columnNameReplacer`	A function with parameters (columnName, index, columnMetaData) and returning a string. If provided, this will be called once per column per result-set. The returned value will be used instead of the SQL-provided column name on row and meta data objects. This allows you to dynamically convert between naming conventions. (default: null).
`connectionRetryInterval`	Number of milliseconds before retrying to establish connection, in case of transient failure. (default: 500)
`connectTimeout`	The number of milliseconds before the attempt to connect is considered failed (default: 15000).
`connectionIsolationLevel`	The default isolation level for new connections. All out-of-transaction queries are executed with this setting. The isolation levels are available from require('tedious').ISOLATION_LEVEL . READ_UNCOMMITTED READ_COMMITTED REPEATABLE_READ SERIALIZABLE SNAPSHOT (default: READ_COMMITED).
`cryptoCredentialsDetails`	When encryption is used, an object may be supplied that will be used for the first argument when calling tls.createSecurePair (default: {}).
`database`	Database to connect to (default: dependent on server configuration). `datefirst` Sets the first day of the week to a number from 1 through 7.
`dateFormat`	A string representing position of month, day and year in temporal datatypes. (default: mdy)
`debug.packet`	A boolean, controlling whether debug events will be emitted with text describing packet details (default: false).
`debug.data`	A boolean, controlling whether debug events will be emitted with text describing packet data details (default: false).
`debug.payload`	A boolean, controlling whether debug events will be emitted with text describing packet payload details (default: false).
`debug.token`	A boolean, controlling whether debug events will be emitted with text describing token stream tokens (default: false).
`enableAnsiNull`	A boolean, controls the way null values should be used during comparison operation. (default: true)
`enableAnsiNullDefault`	If true, SET ANSI_NULL_DFLT_ON ON will be set in the initial sql. This means new columns will be nullable by default. See the T-SQL documentation for more details. (Default: true).
`enableAnsiPadding`	A boolean, controls if padding should be applied for values shorter than the size of defined column. (default: true)
`enableAnsiWarnings`	If true, SQL Server will follow ISO standard behavior during various error conditions. For details, see documentation. (default: true)
`enableArithAbort`	Ends a query when an overflow or divide-by-zero error occurs during query execution. See documentation for more details. (default: true)
`enableConcatNullYieldsNull`	A boolean, determines if concatenation with NULL should result in NULL or empty string value, more details in documentation. (default: true)
`enableCursorCloseOnCommit`	A boolean, controls whether cursor should be closed, if the transaction opening it gets committed or rolled back. (default: null)
`enableImplicitTransactions`	A boolean, sets the connection to either implicit or autocommit transaction mode. (default: false)
`enableNumericRoundabort`	If false, error is not generated during loss of precession. (default: false)
`enableQuotedIdentifier`	If true, characters enclosed in single quotes are treated as literals and those enclosed double quotes are treated as identifiers. (default: true)
`encrypt`	A boolean determining whether or not the connection will be encrypted. Set to true if you're on Windows Azure. (default: true).
`fallbackToDefaultDb`	By default, if the database requested by `database` cannot be accessed, the connection will fail with an error. However, if `fallbackToDefaultDb` is set to true, then the default database will be used instead (Default: false).
`instanceName`	The instance name to connect to. The SQL Server Browser service must be running on the database server, and UDP port 1434 on the database server must be reachable. (no default)
`isolationLevel` (Mutually exclusive with `port`)	The default isolation level that transactions will be run with. The isolation levels are available from require('tedious').ISOLATION_LEVEL. READ_UNCOMMITTED READ_COMMITTED REPEATABLE_READ SERIALIZABLE SNAPSHOT (default: READ_COMMITED).
`language`	Specifies the language environment for the session. The session language determines the datetime formats and system messages. (default: us_english).
`localAddress`	A string indicating which network interface (ip address) to use when connecting to SQL Server.
`maxRetriesOnTransientErrors`	The maximum number of connection retries for transient errors. (default: 3).
`multiSubnetFailover`	Sets the MultiSubnetFailover = True parameter, which can help minimize the client recovery latency when failovers occur. (default: false).
`packetSize`	The size of TDS packets (subject to negotiation with the server). Should be a power of 2. (default: 4096).
`port`	Port to connect to (default: 1433). Mutually exclusive with `instanceName`.
`readOnlyIntent`	A boolean, determining whether the connection will request read only access from a SQL Server Availability Group. For more information, see here. (default: false).
`requestTimeout`	The number of milliseconds before a request is considered failed, or 0 for no timeout (default: 15000).
`rowCollectionOnDone`	A boolean, that when true will expose received rows in Requests' done* events. See done, doneInProc and doneProc. (default: false) Caution: If many row are received, enabling this option could result in excessive memory usage.
`rowCollectionOnRequestCompletion`	A boolean, that when true will expose received rows in Requests' completion callback. See new Request. (default: false) Caution: If many row are received, enabling this option could result in excessive memory usage.
`tdsVersion`	The version of TDS to use. If server doesn't support the specified version, a negotiated version is used instead. The versions are available from require('tedious').TDS_VERSION. 7_1 (use for SQL Server 2000) 7_2 7_3_A 7_3_B 7_4 (default: 7_4).
`textsize`	Specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement. (default: 2147483647) (Textsize is set by a numeric value.)
`trustServerCertificate`	If "true", the SQL Server SSL certificate is automatically trusted when the communication layer is encrypted using SSL. If "false", the SQL Server validates the server SSL certificate. If the server certificate validation fails, the driver raises an error and terminates the connection. Make sure the value passed to serverName exactly matches the Common Name (CN) or DNS name in the Subject Alternate Name in the server certificate for an SSL connection to succeed. (default: true).
`useColumnNames`	A boolean determining whether to return rows as arrays or key-value collections. (default: false).
`useUTC`	A boolean determining whether to pass time values in UTC or local time. (default: true).

NOTE: Selecting another app changes form...

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.