1

I'm working on creating a Synapse pipeline in Azure, and I'm facing an issue while setting up a linked service to connect to a self-hosted MSSQL server. Here are the details of my setup:

Source: Self-hosted MSSQL server. Destination: Azure Synapse workspace. Authentication: System-assigned managed identity. Permissions: I have owner permissions at both the subscription level and the Synapse workspace level.

When I try to add the linked service for the SQL server, I receive the following error messages:

Error Code: SqlFailedToConnect Details: "Cannot connect to SQL Database. Login failed for user ."

I also encountered another error while adding the SQL server:

Error Code: Unauthorized Details: "The principal does not have the required Synapse RBAC permission to perform this action. Required permission: Microsoft.Synapse/workspaces/read."

I tried adding roles, but I'm not able to find Synapse-related roles in the IAM role assignment section. I've double-checked my permissions and ensured that I have owner-level access to both the subscription and the Synapse workspace. Verified that the Azure Synapse SQL pool firewall rules allow Azure services to connect.

Why am I unable to see Synapse-related roles while assigning roles in the IAM settings? How can I resolve these connection issues to successfully set up the linked service using managed identity authentication?

This is the error I am getting

Synapse related roles are not coming somehow

Improve this question

1 Answer 1

Reset to default
1

According to the MS document

Authentication types values which are allowed are SQL (default), Windows and UserAssignedManagedIdentity (only for SQL Server on Azure VMs).

So, you can't connect to SQL server using Synapse managed identity authentication that may be the reason to get error while connecting SQL server through managed identity authentication. You should use SQL or Windows authentication types.

Error Code: SqlFailedToConnect Details: "Cannot connect to SQL Database. Login failed for user ."

Ensure you have provided correct credentials and the user has access to the database, otherwise, you may get login failed error. To get username for windows authentication, open command prompt run whoami command then you will get it as shown below:

enter image description here

Configure linked service as shown below:

enter image description here

It will test successfully as shown above.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks a lot for this. actually same error is also coming when I am trying to add Synapse Analytics SQL Pool into ADF. Although I have given owner access to azure pipeline in IAM of synapse workspace. And, what can be the reason of synapse related roles not visible to me even while having Owner access at both subscription and synapse workspace level
Could you please provide your requirement? Which you want to connect on- premises SQL or serverless SQL pool or dedicated SQL pool from adf or synapse itself?
I want to connect dedicated sql pool as sink and windows authenticated MS SQL as source.
Do you want to connect with ADF or Synapse. And for authentication method for Synapse dedicated SQL pool?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.