Configuring an ADF Linked Service Connection for ODBC

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 ODBC from the Linked Service Type dropdown, the form required for creating an ODBC Linked Service will appear. The ODBC option is offered on connections that use the ODBC System Type.

Source Only Connector

An ODBC connection is used as a source. It is not available as a target.

Each object is read through a dataset that carries the table name alone, from its TargetObjectName parameter, and maps it to the ADF tableName property. The copy source carries the query that BimlFlex composes from the object's columns and source filters, written to the ADF query property, and passes the TargetSchemaName and TargetObjectName parameters. BimlFlex quotes each identifier in the query with ANSI double quotes, for example "OrderId".

Choosing an Authentication Method

Method	Credentials	Additional Settings
Basic	User Name and Password	None
Anonymous	None	An optional Credential, the access credential portion of the connection string

Recommendation: Store the Connection String, the Password and the Credential in Azure Key Vault rather than entering them as plaintext.

Complete Example: Connection String with Azure Key Vault

The values below are made up for illustration.

Field	Example Value
Connect via Integration Runtime	bfx-selfhosted-ir
AKV Linked Service (Connection String, Azure Key Vault tab)	bfx-kv
Secret Name (Connection String, Azure Key Vault tab)	BFX-OdbcConnectionString

Required Fields

Common Required Fields

These fields are required for every ODBC Linked Service connection:

• Connect via Integration Runtime
• Connection String (or Azure Key Vault)
• Authentication Type, written by BimlFlex rather than entered in the form: Anonymous unless the connection's LS_AuthenticationType column holds Basic

Authentication Type Dependent Required Fields

Required fields for Basic:

• User Name
• Password (or Azure Key Vault)

Optional fields for Anonymous:

• Credential (or Azure Key Vault)

Optional fields are:

• Encrypted Credential

Connect via Integration Runtime

Connect via Integration Runtime names the integration runtime for an ODBC Linked Service connection. Azure Data Factory connects to an ODBC data store through a self-hosted integration runtime, so select a self-hosted integration runtime here. 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.

Connection String

The Connection String is the ODBC connection string without its credential portion, for example Driver={SQL Server};Server=sql.contoso.example;Database=Sales; or Dsn=SalesDsn; for a system DSN set up on the integration runtime machine. It is stored in the connection's LS_ConnectionString column and maps to the ADF connectionString property.

The Connection String tab of the form has no fields of its own. Enter the connection string in the Additional Connection String Properties field. When the connection is saved, the form writes that text to the LS_ConnectionString column, together with the Dsn key the form reads from the stored connection string, and adds the key AuthenticationMethod with the connection's Authentication Method, Windows Authentication when the connection stores none. When the connection stores no linked service connection string and no Azure Key Vault secret for it, the form reads the Dsn key from the connection string of the connection.

The Azure Key Vault tab beside the Connection String tab takes an Azure Key Vault secret that holds the whole connection string, named by the AKV Linked Service, the Secret Name and an optional Secret Version. The values are stored in the connection's LS_ConnectionStringKVS_ fields. BimlFlex writes the secret reference to the linked service as the ADF connectionString secret when both the AKV Linked Service and the Secret Name are filled. While the Azure Key Vault tab is selected, the form hides the Additional Connection String Properties field.

note

It is suggested that Azure Key Vault be used in place of manually entering Connection String details.

Authentication Type

Azure Data Factory accepts Basic and Anonymous as the authentication type of an ODBC linked service. BimlFlex writes the connection's LS_AuthenticationType column to the ADF authenticationType property. When the column is empty, the linked service is written with the authentication type Anonymous.

The ODBC linked service form has no Authentication Type field, and saving the connection in the form clears the LS_AuthenticationType column.

For Basic, a User Name and Password are required. For Anonymous, a Credential is optional.

Basic

Basic authentication signs in to the ODBC data store with a User Name and Password.

The User Name is stored in the connection's LS_Username column and maps to the ADF userName property. The Password is stored as plaintext in the connection's LS_Password column, or as an Azure Key Vault reference in the LS_PasswordKVS_ fields that resolves to the password at runtime. Either form maps to the ADF password property. BimlFlex writes the Azure Key Vault reference when both its store and its Secret Name are filled.

The ODBC linked service form has no User Name or Password field.

note

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

Anonymous

The Credential is the access credential portion of the connection string, in the driver's property-value format (for example, RefreshToken=<secret refresh token>;). It is stored as plaintext in the connection's LS_Credential column, or as an Azure Key Vault reference in the LS_CredentialKVS_ fields. Either form maps to the ADF credential property. BimlFlex writes the Azure Key Vault reference when both its store and its Secret Name are filled.

The ODBC linked service form has no Credential field.

Encrypted Credential

When the connection's LS_EncryptedCredential column holds an Encrypted Credential value, it is also written to the linked service as the ADF encryptedCredential property. The form has no field for it.

Azure Data Factory Linked Service Additional Information

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