Configuring an ADF Linked Service Connection for Amazon RDS for SQL Server
For information on how to enable a connection for use with linked services, see Configuring a Linked Service Connection.
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 Amazon RDS for SQL Server from the Linked Service Type dropdown, the form required for creating an Amazon RDS for SQL Server Linked Service will appear.
BimlFlex writes the Amazon RDS for SQL Server linked service with separate server, database, authentication and user properties. The form edits each of these values in its own field.
Source Only Connector
An Amazon RDS for SQL Server connection is used as a source. It is not available as a target.
The connector is built for Azure Data Factory projects.
The Amazon RDS for SQL Server Linked Service Type is offered on connections that use the Amazon RDS for SQL Server System Type.
Each object is read through a dataset parameterized on schema and table. A table object is read with a query on the copy source, and a stored procedure object is read by calling the stored procedure on the copy source, which is written with StoredProcedure set to True and carries a StoredProcedureParameters element for the procedure's parameters.
Choosing an Authentication Method
| Method | Authentication Type | Credentials |
|---|---|---|
| SQL Authentication | SQL | User Name and Password |
| Windows Authentication | Windows | User Name (a Windows account, for example EXAMPLE\bfx-reader) and Password |
Recommendation: Store the Password in Azure Key Vault rather than entering it as plaintext.
Complete Example: SQL Authentication with Azure Key Vault
The values below are made up for illustration.
| Field | Example Value |
|---|---|
| Connect via Integration Runtime | AutoResolveIntegrationRuntime |
| Server Name | example-sql01.rds.amazonaws.com |
| Database Name | BFX_SRC |
| Authentication | SQL Authentication |
| User Name | bfx_reader |
| AKV Linked Service (Password, Azure Key Vault tab) | bfx-kv |
| Secret Name (Password, Azure Key Vault tab) | BFX-SRC-RdsSqlPassword |
Required Fields
Common Required Fields
These fields are required for every Amazon RDS for SQL Server Linked Service connection:
Authentication Dependent Required Fields
Required for SQL Authentication and Windows Authentication:
Optional fields are:
- Encrypt
- Trust Server Certificate
- Host Name In Certificate
- Additional Connection String Properties
- Connection string in Azure Key Vault, a toggle in the Advanced section of the form
Connect via Integration Runtime
Connect via Integration Runtime names the integration runtime for an Amazon RDS for SQL Server 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.
When the Amazon RDS for SQL Server instance is located inside an Amazon Virtual Private Cloud, Azure Data Factory connects to it through a self-hosted integration runtime.
Server Name
The Server Name is the name or network address of the Amazon RDS for SQL Server instance (for example, example-sql01.rds.amazonaws.com).
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 and Windows Authentication.
The selection is stored in two connection columns that always agree: LS_AuthenticationType holds the linked service value (SQL or Windows) and LS_AuthenticationMethod holds the label (SQL Authentication or Windows Authentication).
The LS_AuthenticationType value maps to the ADF authenticationType property.
User Name
The User Name is the SQL login for SQL Authentication, or the Windows account for Windows 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 stored 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.
It is suggested that Azure Key Vault be used in place of manually entering the Password.
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 Setting | ADF Property |
|---|---|
Encrypt | encrypt |
TrustServerCertificate | trustServerCertificate |
HostNameInCertificate | hostNameInCertificate |
ApplicationIntent | applicationIntent |
Failover Partner | failoverPartner |
MultiSubnetFailover | multiSubnetFailover |
MultipleActiveResultSets | multipleActiveResultSets |
Integrated Security or Trusted_Connection | integratedSecurity |
Pooling | pooling |
Max Pool Size | maxPoolSize |
Min Pool Size | minPoolSize |
ConnectRetryCount | connectRetryCount |
ConnectRetryInterval | connectRetryInterval |
Connect Timeout, Connection Timeout or Timeout | connectTimeout |
Load Balance Timeout | loadBalanceTimeout |
Command Timeout | commandTimeout |
Packet Size | packetSize |
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 stored as one Azure Key Vault secret, named in the LS_ConnectionStringKVS_ fields, and the Server Name, Database Name, Authentication, User Name, Password, Encrypt, Trust Server Certificate, Host Name In Certificate and Additional Connection String Properties fields are hidden.
A connection string stored as one Azure Key Vault secret is written as the connection string form of the linked service, which the editor calls version 1.0. To write the separate properties, turn the toggle off, enter the Server 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 Amazon RDS for SQL Server Linked Service and its connection requirements see the Azure Data Factory Amazon RDS for SQL Server Connector documentation.