Set up a connection to Microsoft Azure Synapse Analytics

Microsoft Azure Synapse Analytics is Microsoft’s cloud analytics service for data warehousing and big data workloads. Use a Microsoft Azure Synapse Analytics connection to export data from, and import data into, tables in your SQL pool. This article explains how to create the connection, choose how integrator.io signs in (Database user or Azure service principal), and add the optional Azure Data Lake Storage Gen2 staging settings that optimized bulk load needs.

A. Set up a Microsoft Azure Synapse Analytics connection

Go to Resources > Connections, select + Create connection, and then select Microsoft Azure Synapse Analytics.

B. Supply general Microsoft Azure Synapse Analytics settings

• 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 either SQL endpoint from your Synapse workspace’s Overview page in the Azure portal: the Dedicated SQL endpoint (<workspace>.sql.azuresynapse.net) or the Serverless SQL endpoint (<workspace>-ondemand.sql.azuresynapse.net). Serverless SQL pools are read-only, so use the Dedicated SQL endpoint if this connection will be used for imports. For where to find the endpoints, see Connect to Synapse SQL.
• Database name (required): Enter the name of your SQL pool database. For a dedicated SQL pool, enter the pool name. Synapse Studio lists your databases under Data.

• Auth type (required): Select how integrator.io signs in to your SQL pool.

  • Database user (default): Authenticate with SQL credentials (username and password).
  • Azure service principal: Authenticate with a Microsoft Entra ID application identity stored in an Azure service principal iClient.

  Existing connections continue to authenticate as Database user with no migration or action required.

Database user

• Username (required): Enter the username created by your Azure Synapse administrator for this account. For the workspace admin account, use the SQL admin username shown on the workspace’s Overview page in the Azure portal.
• Password (required): Enter the password created by your Azure Synapse administrator for this account.

Database user connections don’t show the Storage account, Container, and Folder path settings and can’t use optimized bulk load.

Azure service principal

Service principal authentication connects to Azure Synapse Analytics using a Microsoft Entra ID application identity stored in an Azure service principal iClient. Before you begin, you need an app registration in Microsoft Entra ID with a client secret. A Microsoft Entra administrator must be set on the Synapse workspace, and the service principal must be added as a user in the target database with the roles it needs. Otherwise, Test connection fails with an ELOGIN error. For the steps, see Microsoft Entra authentication for Azure Synapse.

Note: For a serverless SQL pool, if you read external tables that use a database-scoped credential, also grant the service principal REFERENCES on that credential.

When you select Azure service principal, the Username and Password settings no longer appear.

Service principal client (required): Select the iClient that stores your Microsoft Entra ID app registration credentials (Tenant ID, Client ID, and Client secret).

• If your account has no Azure service principal iClient, select Create service principal client to create one. The iClient form opens with Type set to Azure service principal.
• If you already have one, select it from the list.

You can reuse an existing Azure service principal iClient, including one created for a Microsoft SQL connection. For setup steps, see Create an Azure Service Principal iClient.

To add the service principal, a Microsoft Entra administrator connects to the pool’s database, not master, and runs these statements. Replace <service_principal_display_name> with the display name of your service principal.

CREATE USER [<service_principal_display_name>] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_datareader', '<service_principal_display_name>';
EXEC sp_addrolemember 'db_datawriter', '<service_principal_display_name>';
GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<service_principal_display_name>];

• Storage account (optional): Enter the Azure Data Lake Storage Gen2 (ADLS Gen2) storage account name, not a URL. The name must be 3–24 lowercase letters or numbers. To retrieve it, go to the Azure portal > Storage accounts and select your account. The storage account must have hierarchical namespace enabled. Keep the storage account in the same region as the dedicated SQL pool. Other regions aren’t supported. The storage account, the service principal, and the Synapse workspace must belong to the same Microsoft Entra tenant. The storage account must allow public network access. Storage behind a firewall or virtual network isn’t supported for optimized bulk load.
• Container (optional): Enter the container name that is in the provided storage account. The name must be 3–63 lowercase letters, numbers, or single hyphens. To retrieve it, open that storage account in the Azure portal and go to Data storage > Containers. Grant the connection’s service principal the Storage Blob Data Contributor role on this container. This role applies to the whole container, so use the container only for Celigo staging.
• Folder path (optional): Enter a folder inside the container, not the container root. You choose this folder. In the Azure portal, open the container to browse or create folders. Use this folder only for Celigo staging, because integrator.io deletes old folders inside it.

Note: To use optimized bulk load, enter all three storage settings. Optimized bulk load requires a dedicated SQL pool. Serverless SQL pools aren’t supported for bulk load.

integrator.io deletes staged files after each run and sets a 24-hour expiration on each file as a safety net.

Note: Azure Synapse Analytics service principal connections authenticate with a client secret. Managed identity authentication isn’t supported. See Microsoft’s documentation on registering an application with Microsoft Entra ID.

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 settings to provide additional name-value pairs to be sent to Azure Synapse Analytics 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 metadata 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_COMMITTED).
  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_COMMITTED).
  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 setting lets you specify which connection 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 turned on by default with a predefined value for Target concurrency level. You can turn it off if required. To set or make changes in any of these settings, you must have admin or manage permissions.
  • Target concurrency level: This setting is shown only if Auto-recover rate limit errors is turned on. Change the predefined value as required to limit the number of concurrent API requests allowed by the connection resource. See also, Setting concurrency level on a connection.
  • Concurrency level: When Auto-recover rate limit errors is turned on, 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 turned off, you can enter a value to limit the number of concurrent API requests allowed by the connection resource. See also, Setting concurrency level on a connection.

E. Test the connection

Select Test connection to verify the connection without saving it. Save and Save & close test the connection and then save it, so it’s available to all integrations in your account.

When you test an Azure service principal connection, integrator.io validates the full authentication chain: the iClient credentials, the Microsoft Entra ID token, and the Synapse connection. If the test fails, verify that the Client ID, Client secret, and Tenant ID in your iClient are correct and that the secret hasn’t expired.

After you save, you can register the connection with an integration.

When you fill in Storage account, Container, and Folder path, Test connection checks the whole bulk load path:

1. Connects to the SQL pool as the service principal and confirms that it’s an online dedicated SQL pool, not a serverless SQL pool.
2. Writes a small test file to the staging folder in your storage account.
3. Loads the test file into a temporary table with COPY INTO, using the service principal to read the file.
4. Confirms that one row loaded, then deletes the test file.

If a check fails, Test connection shows one of these errors.

Related articles

• Create an Azure Service Principal iClient
• Export data from Microsoft Azure Synapse Analytics
• Import data into Microsoft Azure Synapse Analytics
• Set up a connection to Microsoft SQL