Simple Connection from Data Factory

Question

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.

Answer 1

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>>'