Every data store your ADF pipeline touches — source or sink — starts with a linked service. Get the linked service right and the rest of the pipeline follows. Get it wrong and you spend an afternoon staring at authentication errors. Let's cover what's available in 2014 and how to handle the credentials problem properly.
Available Linked Service Types in 2014
The connector library is limited but covers the most common Azure and on-premises scenarios:
- Azure Storage — Blob containers and Table storage. The most common source and staging area.
- Azure SQL Database — Azure-hosted SQL databases. Direct connection, no gateway required.
- Azure SQL Data Warehouse — Separate type from Azure SQL, supports PolyBase for high-throughput loads.
- SQL Server (on-premises) — Via Data Management Gateway. SQL auth or Windows auth.
- Oracle (on-premises) — Via DMG. Requires Oracle Data Provider for .NET on the gateway machine.
- MySQL (on-premises) — Via DMG. Requires MySQL Connector/Net on the gateway machine.
- Azure DocumentDB — Microsoft's NoSQL offering. Limited activity support.
- HDInsight — Both existing cluster and on-demand. Not a data store per se — a compute linked service for HDInsight activities.
FTP, SFTP, Salesforce, ADLS — these are not here yet. If you need them, the answer in 2014 is a Custom Activity that handles the connection itself. Painful, but it works.
Cloud Linked Service: Azure Storage
{
"name": "AzureStorageLinkedService",
"properties": {
"type": "AzureStorage",
"description": "Primary storage - staging and landing zone",
"typeProperties": {
"connectionString": "DefaultEndpointsProtocol=https;AccountName=myaccount;AccountKey=base64encodedkey=="
}
}
}Straightforward. The account key is the storage account's primary or secondary key from the Azure portal. This goes into the JSON as plaintext. That's the credentials problem, and we'll address it below.
Cloud Linked Service: Azure SQL Database
{
"name": "AzureSQLLinkedService",
"properties": {
"type": "AzureSqlDatabase",
"typeProperties": {
"connectionString": "Server=tcp:myserver.database.windows.net,1433;Database=MyDB;User ID=adfuser@myserver;Password=mypassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
}
}
}SQL authentication only in 2014 — no Azure AD integrated auth yet for ADF. Create a dedicated ADF SQL user with least-privilege access: SELECT on sources, INSERT/UPDATE/TRUNCATE on staging and target tables, EXECUTE on transform stored procedures. Do not use sa or an admin account.
On-Premises Linked Service: SQL Server via DMG
{
"name": "OnPremSQLServerLinkedService",
"properties": {
"type": "OnPremisesSqlServer",
"typeProperties": {
"connectionString": "Data Source=MYSERVER\MYINSTANCE;Initial Catalog=SourceDB;Integrated Security=False;User ID=adfuser;Password=mypassword",
"gatewayName": "my-data-gateway"
}
}
}The gatewayName must match the gateway name registered in your factory — not the machine name. If the gateway is offline or the registration key has expired, activities using this linked service will fail with a gateway connectivity error. Build gateway monitoring into your ops runbook from day one.
Windows authentication is also supported. Set Integrated Security=True in the connection string and add a username and password field in the typeProperties for the Windows account the gateway service should impersonate.
On-Premises Linked Service: Oracle
{
"name": "OracleLinkedService",
"properties": {
"type": "OnPremisesOracle",
"typeProperties": {
"connectionString": "data source=MYORACLESERVER/MYDB;User Id=oracleuser;Password=oraclepassword",
"gatewayName": "my-data-gateway"
}
}
}Before this works, install Oracle Data Provider for .NET (ODP.NET) on the gateway machine. The 32-bit version — the gateway is a 32-bit process. The 64-bit ODP.NET will not be found by the gateway even on a 64-bit server. This is one of those things that costs an afternoon if you don't know it upfront. Trust me on this one.
The Credentials Problem
All of these linked services contain credentials as plaintext in the JSON. Connection strings, passwords, account keys — all there in clear text. This is a genuine security concern that ADF does not address in 2014. Azure Key Vault integration is not available for ADF yet.
The pattern I use: never commit credential values to git. Keep a separate config file (not committed) that holds environment-specific values. The PowerShell deployment script reads from this config and substitutes values before deploying the linked service JSON.
# Deployment script snippet
$linkedServiceJson = Get-Content "linkedservicesAzureStorageLinkedService.template.json" -Raw
$linkedServiceJson = $linkedServiceJson.Replace("{{STORAGE_KEY}}", $StorageAccountKey)
$linkedServiceJson | Set-Content "linkedservicesAzureStorageLinkedService.json"
New-AzureRmDataFactoryLinkedService `
-ResourceGroupName $ResourceGroup `
-DataFactoryName $DataFactoryName `
-File "linkedservices\AzureStorageLinkedService.json" `
-Force
Remove-Item "linkedservices\AzureStorageLinkedService.json"The template file has placeholders, not real values. The deployment script injects real values at runtime and deletes the populated file immediately after deploy. Not elegant, but it keeps credentials out of source control until ADF gets proper secrets management.
What Is Coming
The connector list will expand significantly through 2014 and 2015. Based on the ADF roadmap, expect FTP, SFTP, ADLS, and eventually Salesforce. Each new connector follows the same pattern — linked service JSON, dataset, activity support. Once you understand the pattern, adding a new source type is straightforward even if the connector itself is new.
Key Vault integration is on the roadmap. When it arrives, the credentials-in-JSON problem gets a proper solution. Until then, the template-and-inject pattern is what we have.
Next post: Copy Activity — the full deep dive. If you're wrestling with a linked service that won't authenticate, I'm here to help.