Set up a connection to Microsoft Azure Synapse Analytics

Anitha Abraham

Updated September 27, 2024 16:44

A. Set up a Microsoft Azure Synapse Analytics connection

Start establishing a connection to Microsoft Azure Synapse Analytics 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 Azure Synapse Analytics.

B. Supply general Microsoft Azure Synapse Analytics settings

At this point, you’re presented with initial options for providing the new Microsoft Azure Synapse Analytics connection.

Name (required): How you want to refer to this connection in integrator.io.

Application: Default value is Microsoft Azure Synapse Analytics.

C. Provide Microsoft Azure Synapse Analytics authentication

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.

sidebar. How to get authentication details

Sign in to the Azure portal: https://portal.azure.com.
In the Microsoft Azure page that opens, under Resources, click the Synapse workspace that you want to connect to. For example, celigo01.
In the Synapse workspace page that opens,
- For Host: Copy the value in Serverless SQLendpoint and paste it in the connection field.
- For Database name: Click Open Synapse Studio > Open.
  
  Then, in the left pane of the page that opens, click Data > SQL database. The databases are shown below. Copy the required database name and paste it in the connection field.
- For Username: Copy the value in SQL admin username and paste it in the connection field. (See image in the first bullet point above.)

D. Edit advanced Microsoft Azure Synapse Analytics settings

Before continuing, you have the opportunity to provide additional configuration information, if needed, for the Microsoft Azure Synapse Analytics 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 the 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, an 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 rows 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 rows are received, enabling this option could result in excessive memory usage.
tdsVersion	The version of TDS to use. If the 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).

Borrow concurrency from (optional): Select another connection from the list. By default, all data flowing through a connection record is submitted to the respective endpoint application at the concurrency level configured for that connection record. There are cases, however, where multiple integrator.io connections need to share the same concurrency level, and this field allows you to specify which connection you would like to borrow from. With borrowed concurrency, the data flowing through both connections will be submitted to the endpoint application together, via a shared concurrency model.
Auto-recover rate limit errors: This setting is enabled by default with a predefined value for Target concurrency level. You can disable the setting if required. To set or make changes in any of these settings, you must have admin or manage permissions.
- Target concurrency level: This field is shown only if Auto-recover rate limit errors is enabled. Change the predefined value as required to limit the number of concurrent API requests allowed by the connection resource. See also, Setting currency level on a connection.
- Concurrency level: When Auto-recover rate limit errors is checked, you can't modify the concurrency level because it matches the value set by the connected platform's runtime. When Auto-recover rate limit errors is not checked, you can enter a value to limit the number of concurrent API requests allowed by the connection resource. See also, Setting currency level on a connection.

Tip

Click to open the Build SQL query editor and ask Celigo AI to generate a SQL query for you. Enter a description of your SQL query in plain English in the space provided, and Celigo AI will attempt to generate a SQL query based on your prompt.

E. Test the connection

Once you have configured the Microsoft Azure Synapse Analytics connection, you have a few options for continuing:

Save – click this button to test the connection, and 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. If you create it within a flow, it will be applied to the current source or destination app. Otherwise, you may proceed to register the connection with an integration.