Skip to main content

Configuring an ADF Linked Service Connection for Azure SQL Data Warehouse

note

For information on how to enable a connection for use with linked services, see Configuring a Linked Service Connection.

note

Field labels in this walkthrough follow the BimlFlex Connection Editor, with the connection column that carries each value and the Azure Data Factory property it maps to named alongside.

After selecting Azure Synapse Analytics (SQL DW) from the Linked Service Type dropdown, the form required for creating an Azure Synapse Analytics Linked Service will appear.

An Azure SQL Data Warehouse connection uses the Azure Synapse Analytics linked service.

BimlFlex writes the Azure Synapse Analytics linked service with separate server, database, authentication and user properties. The form edits each of these values in its own field.

Source and Target Connector​

An Azure Synapse Analytics connection is used as a source and as a target. The Azure Synapse Analytics (SQL DW) Linked Service Type is offered on connections that use the Azure Synapse System Type.

Choosing an Authentication Method​

MethodAuthentication TypeCredentials
SQL AuthenticationSQLUser Name and Password
Service PrincipalServicePrincipalTenant, Service Principal Id and Service Principal Key
Managed IdentitySystemAssignedManagedIdentityNone. The system assigned managed identity of the data factory signs in.

Recommendation: Store the Password and the Service Principal Key in Azure Key Vault rather than entering them as plaintext. Managed Identity needs no stored credential.

Complete Example: SQL Authentication with Azure Key Vault​

The values below are made up for illustration.

FieldExample Value
Connect via Integration RuntimeAutoResolveIntegrationRuntime
Fully Qualified Domain Nameexample-server.database.windows.net
Database NameBFX_DWH
AuthenticationSQL Authentication
User Namebfx-loader
AKV Linked Service (Password, Azure Key Vault tab)bfx-kv
Secret Name (Password, Azure Key Vault tab)BFX-DWH-Password

Required Fields​

Common Required Fields

These fields are required for every Azure Synapse Analytics Linked Service connection:

Authentication Dependent Required Fields

Required for SQL Authentication:

Required for Service Principal:

Managed Identity requires no further fields.

Optional fields are:

Connect via Integration Runtime​

Connect via Integration Runtime names the integration runtime for an Azure Synapse Analytics Linked Service connection. The default value is AutoResolveIntegrationRuntime. To use another runtime, select it from the dropdown or choose + New to add one. The custom values that appear in this dropdown can be maintained in Settings under Azure, in the AzureIntegrationRuntime setting. The value is stored in the connection's LS_ConnectVia_IntegrationRuntime column and maps to the ADF connectVia property. When the value is AutoResolveIntegrationRuntime, no connectVia property is written and the default Azure integration runtime is used.

Fully Qualified Domain Name​

The Fully Qualified Domain Name is the name or network address of the server (for example, example-server.database.windows.net). It is stored in the connection's LS_Server column and maps to the ADF server property.

Database Name​

The Database Name is the name of the database to connect to. It is stored in the connection's LS_Db column and maps to the ADF database property. When the Database Name is blank, the connection's Catalog is written as the database.

Authentication​

The Authentication dropdown offers SQL Authentication, Service Principal and Managed Identity. The selection is stored in two connection columns that always agree: LS_AuthenticationType holds the linked service value (SQL, ServicePrincipal or SystemAssignedManagedIdentity) and LS_AuthenticationMethod holds the label (SQL Authentication, Service Principal or Managed Identity). The LS_AuthenticationType value maps to the ADF authenticationType property.

When the Server Name or LS_AuthenticationType is blank and the connection holds a connection string, the build decides the authentication type from the connection:

  • SQL for a user name with a password or an Azure Key Vault password secret
  • ServicePrincipal when the Service Principal Id is filled and the connection string names no user

When no authentication type can be decided, the linked service keeps the connection string form and the build reports one warning that names the reason. A connection string that sets Integrated Security or Trusted_Connection to True, Yes or SSPI keeps the connection string form with the one warning, with or without a user name and password, because this connector offers no Windows authentication. When the connection holds no connection string and LS_AuthenticationType is blank, no authenticationType property is written.

User Name​

The User Name is the SQL login used with SQL Authentication. It is stored in the connection's LS_Username column and maps to the ADF userName property.

Password​

The Password field accepts the password as plaintext in the LS_Password column, or an Azure Key Vault reference held in the LS_PasswordKVS_ fields that resolves to the password at runtime. Either form maps to the ADF password property. When an Azure Key Vault secret is named, the plaintext password is not written.

note

It is suggested that Azure Key Vault be used in place of manually entering the Password.

Service Principal​

Service Principal authentication signs in with a Microsoft Entra application. The application must be registered in Azure and given access to the database as described in the Microsoft documentation for service principal authentication.

The Tenant is the tenant information, such as the domain name or tenant id, under which the application resides. It is stored in the connection's LS_Tenant column and maps to the ADF tenant property.

The Service Principal Id is the application's client id. It is stored in the connection's LS_ServicePrincipalId column and maps to the ADF servicePrincipalId property.

The Service Principal Key is the application's key, as plaintext in the LS_ServicePrincipalKey column, or as an Azure Key Vault reference held in the LS_ServicePrincipalKeyKVS_ fields that resolves to the key at runtime. Either form maps to the ADF servicePrincipalKey property.

note

It is suggested that Azure Key Vault be used in place of manually entering the Service Principal Key.

Managed Identity​

Managed Identity signs in with the system assigned managed identity of the data factory. The identity must be given access to the database as described in the Microsoft documentation for managed identity authentication. No credential is entered in the form.

Encrypt​

Encrypt sets whether TLS encryption is required for the data sent between the client and the server. The options are Not set, Mandatory, Optional and Strict. The selection is stored in the connection's LS_Encrypt column as mandatory, optional or strict, and maps to the ADF encrypt property. When Encrypt is Not set and no connection string sets it, no encrypt property is written and the Data Factory default applies.

Trust Server Certificate​

When checked, the channel is encrypted while the certificate chain is not validated. The value is stored in the connection's LS_TrustServerCertificate column and maps to the ADF trustServerCertificate property. When the box is clear and no connection string sets it, no trustServerCertificate property is written and the Data Factory default applies.

Host Name In Certificate​

The Host Name In Certificate is the host name that the server certificate is validated against. It is stored in the connection's LS_HostNameInCertificate column and maps to the ADF hostNameInCertificate property. When it is blank, Data Factory validates the certificate against the server name.

Additional Connection String Properties​

Additional Connection String Properties is in the Advanced section of the form. Enter settings the same way they appear in a connection string, as key=value pairs with a semicolon between settings, for example ApplicationIntent=ReadOnly.

Settings entered here are saved as a connection string beside the fields above, in the connection's LS_ConnectionString column. The build reads that connection string and writes each setting it recognises as its own linked service property. A value in a field always wins over the same setting in the connection string.

The build matches setting names without regard to spaces or letter case, so Connect Timeout and connecttimeout are the same setting. It recognises the server keys (Data Source, Server, Address, Addr and Network Address), the database keys (Initial Catalog and Database), the user keys (User ID, UID, User and User Name), the password keys (Password and PWD), and these settings with the property each one is written to:

Connection String SettingADF Property
Encryptencrypt
TrustServerCertificatetrustServerCertificate
HostNameInCertificatehostNameInCertificate
ApplicationIntentapplicationIntent
Failover PartnerfailoverPartner
MultiSubnetFailovermultiSubnetFailover
MultipleActiveResultSetsmultipleActiveResultSets
Integrated Security or Trusted_ConnectionintegratedSecurity
Poolingpooling
Max Pool SizemaxPoolSize
Min Pool SizeminPoolSize
ConnectRetryCountconnectRetryCount
ConnectRetryIntervalconnectRetryInterval
Connect Timeout, Connection Timeout or TimeoutconnectTimeout
Load Balance TimeoutloadBalanceTimeout
Command TimeoutcommandTimeout
Packet SizepacketSize

Persist Security Info, Application Name, App, Workstation ID and WSID are read and not written.

A setting the build does not recognise, or a value it cannot read, keeps the whole linked service in the connection string form (version 1.0 in the editor's words), and the build reports one warning that names the reason. When the connection string holds a password, the warning does not list the settings it does not recognise.

When Additional Connection String Properties is empty, the connection string is saved empty and the linked service is written from the fields alone.

Connection String in Azure Key Vault​

The Advanced section also holds the Connection string in Azure Key Vault (version 1.0) toggle. When it is on, the connection string is held as one Azure Key Vault secret, named in the LS_ConnectionStringKVS_ fields, and the server, database, authentication, certificate and Additional Connection String Properties fields are hidden.

A connection string held as one Azure Key Vault secret is written as the connection string form of the linked service, which the editor calls version 1.0. The build reports a warning for a linked service written in the connection string form, whether the string is entered or kept as an Azure Key Vault secret, because the Azure Data Factory service reads that form as version 1.0 of the connector, a version Microsoft lists as reaching end of support. The warning names the linked service, names the separate properties as the remedy and links Microsoft's connector lifecycle page for the date. To write the separate properties, turn the toggle off, enter the Fully Qualified Domain Name, Database Name and User Name, and keep the password in Azure Key Vault on the Password field's Azure Key Vault tab.

Azure Data Factory Linked Service Additional Information​

For additional information on ADF Azure Synapse Analytics Linked Service and its connection requirements see the Azure Data Factory Azure Synapse Analytics Connector documentation.