How do I connect an Azure Synapse SQL server to an Azure Data Factory Linked Service?

Question

• Browser: Google Chrome, latest version
• OS: Windows 10

We have an Azure Synapse SQL server with an address like myserversql.azuresynapse.net. I can connect to it via Power BI just fine using an Active Directory login. But when I am trying to make a Linked Service to connect to it in an Azure Data Factory it doesn't work because 1) I do not have an SQL server username for this database and 2) There is no Active Directory option for the Azure Linked Service.

I'm not the admin for the database or for our Azure installation. I do not have permissions to manage identities, I have limited permissions. Under our subscription I can create resource groups, data factories and anything under a data factory like a linked service, dataset, pipeline, etc.

I tried a Google search and AI search on this forum for my answer and none of the pages have a workable solution, perhaps because I don't have admin permissions.

Is there something I can do to get this working or does my username need more permissions to get Azure DF to talk to our Synapse SQL server?

Thank you.

Answer 1

Hi Chuck Roberts,

Thanks for reaching out to Microsoft Q&A.

To connect Azure Synapse SQL to ADF using a Linked Service, you typically need either SQL authentication (username and password) or AAD integration. Since you are facing limitations with SQL auth & need to use AAD but it's not available in your ADF setup, there are a few steps you might consider:

1. ADF supports MI for Azure Synapse SQL auth. This doesn't require a username/password as it uses the identity of the ADF itself to authenticate to Synapse SQL.
2. Setting up MI:

• Go to your ADF --> "Managed Identity" in the azure portal and ensure its enabled.
• Assign the necessary permissions to this identity on your Synapse SQL server. This usually requires help from an admin who can grant your adf managed identity the necessary roles in Synapse, such as db_datareader and db_datawriter.

3. Configure the Linked Service:

• In ADF, create a new Linked Service for Azure Synapse.
• Choose "Azure SQL db" as the connection type (Synapse SQL pools are compatible with this service type).
• In the auth type, select "Managed Identity".
• Enter your Synapse SQL server name (myserversql.azuresynapse.net) and the database name.

4. Validate and Test the Connection:

• Once set up, use the test connection feature in ADF to ensure that your Data Factory can successfully connect to the Synapse SQL database using the configured MI.

If you dont have permissions to assign roles to the managed identity, youll need to coordinate with your Azure admin. They will need to provide the necessary permissions to the adf's MI or enable a configuration that supports AAD authentication for your scenario. This approach doesnt require managing SQL server usernames and passwords and adheres more closely to security best practices by leveraging Azures built-in identity management capabilities.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.