Adding Azure Data Factory Managed Identity to Azure SQL DB Failes - Error Msg 33132

Question

When trying to grant a Data Factory instance access to an Azure SQL DB I get the following error.

"Msg 33132, Level 16, State 1, Line 1

This principal type is not supported in Azure SQL Database."

Using this command - CREATE USER [##-prod-datafactory] FROM EXTERNAL PROVIDER;

I am the Azure Active Directory admin in Azure SQL DB. I can add the Dev instance of the Data Factory to the DB without issue. Prod instance is up and running. I can also view the Managed Identity within Enterprise applications.

Answer 1

Hello @Scott Cooke (Agile Analytics)

Thanks for the question and using MS Q&A platform.

Make sure you are using the correct managed identity name.

To use system-assigned managed identity authentication, specify the generic properties that are described in the preceding section, and follow these steps mentioned in the document: System-assigned managed identity authentication

As per the repo - I tried to use system-assingned managed identity authentication to connect Azure SQL Database.

Step1: Create Azure SQL Database linked service with Authentication type: System Assigned Managed Identity
Note: Tried to connect without granting permission, where result is connection failed.

Step2: To grant permission to the managed identity name copy the managed identity name chepraadfdemo as shown in step1.

Step3: Go to Azure SQL Database => Query Editor => login as Active Directoty authentication as shown below:

Step4: Grant permission to the managed identity by using the below SQL query:

CREATE USER [your Data Factory name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your Data Factory name];

Step5: Now test connection of the Azure SQL database linked service.

By following the above steps, we had successfully connected Azure SQL Database using System-assigned managed identity authentication.

Hope this helps. Do let us know if you any further queries.

---

Please don’t forget to Accept Answer wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Could you review the troubleshooting steps provided on this support article? It may help you solve the issue.

Answer 3

perfect solution :)