Azure Data Factory: LinkedService for AzureSql in failed state

Question

Azure Data Factory - getting started. (One similar question is unanswered.)

I am using the data factory to move data from Storage to a Azure SQL table. I can create the linked servers OK but by the time I try to reference it in a DataSet that operation fails with an error pointing at the LinkedService:

statusCode:BadRequest serviceRequestId:f1e0d752-6d10-42db-b445-6c59d8eaf074 statusMessage:{"message":"Linked service AzureSqlLinkedService is not ready. Current status: Failed. If AzureSqlLinkedService is in failed state, you can investigate by getting the object and inspecting the error message, then decide whether you need to update some of its properties or recreate the object.","code":"LinkedServiceNotReady"}

I've tried to investigate the AzureSqlLinkedService which is now showing an error:

ENTITY PROVISIONING FAILED: FAILED TO CONNECT TO LINKED SERVICE. ERRORCODE:-2146232060.

This code appears in widely differing contexts so not great but the error message seems to be valid. I have opened the firewall to allow connection to SQL Azure and see no errors about credentials or connectivity.

I tried to get more from PowerShell but no joy: "ProvisioningState : Failed" is all it adds.

Pointers please?

Answer 1

this error indicates that ADF is unable to connect to the Azure SQL database you are defining as a LinkedService. Have you enabled "Allow access to Azure services" setting on the Azure SQL Server?

Answer 2

Also, double check your connection string. It is definitely and issue reaching to or getting authenticated by the SQL Azure server.

Answer 3

Try this

"connectionString": "Server=tcp:.database.windows.net,1433;Database=;User ID=;Password=;Integrated Security=False;Encrypt=True;Connection Timeout=30"