Simple Connection from Data Factory

nl908 1 Reputation point
2022-10-19T03:56:20.493+00:00

When trying to create a linked service from Data Factory to a serverless Azure SQL database, I test the connection and get error:

Cannot connect to SQL Database. Please contact SQL server team for further support. Server: 'XXXXXX.database.windows.net', Database: 'XXXXXX', User: ''. Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.
Login failed for user '<token-identified principal>'., SqlErrorNumber=18456,Class=14,State=1

I'm trying to connect using a System Assigned Managed Identity. I enabled it in Data Factory.

I've granted that same System Assigned Managed Identity to the Azure SQL server in the "Access Control (IAM)" section. I added the System Assigned Managed Identity as an Owner and as a Contributer role.

I continue to get an error. I cannot figure out how to simply connect to the Azure SQL database from Data Factory.

Is using a System Assigned Managed Identity the most normal way to connect from Data Factory? Or is SQL Authentication a better way to go?

Any ideas at what I'm doing wrong? I saw some instructions about having to do more grant commands using a cli thing - but I just want to use the portal.

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,923 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Nandan Hegde 30,091 Reputation points MVP
    2022-10-19T04:04:32.843+00:00

    Hey,
    You need to provide the access within DB and not via RBAC :

    CREATE USER [<<DFname>>] FROM EXTERNAL PROVIDER
    exec sp_addrolemember 'db_owner', '<<DFName>>'

    0 comments No comments