Principal ‘xyz’ could not be found or this principal type is not supported

Product:

Microsoft Azure SQL

Issue:

Error when try to create a user in AZURE SQL from a Managed Identity Object ID.

Principal ‘xyz’ could not be found or this principal type is not supported.

Cannot add the principal ‘xyz’, because it does not exist or you do not have permission.

Solution:

Use the managed resource name instead of the object id. Managed Identity can be replaced with the resource name, when referring to the object.

Background:

To make Azure Data Factory to connect to a Azure SQL resource with Managed Identity, you need to create a Managed identity for the ADF resource.

Then you can add the resource to AZURE SQL with below command to give it access to the database.

Login to the AZURE SQL server with SSMS, use the Azure SQL server name to connect.
Select the database and click New Query:

CREATE USER [adf_name] FROM EXTERNAL PROVIDER

ALTER ROLE [db_owner] ADD MEMBER [adf_name]

By adding a USER direct to the database, and not create a login in SQL, the user must provided the database name when it connects to AZURE SQL.

If you are Owner of the Azure database, then you do not need to be AAD Admin to be able to perform the change in SSMS.

“Azure Active Directory authentication allows you to centrally manage identity and access to your Azure SQL Database.”

More Information:

Connect Azure SQL from Data Factory using Managed Identity

CogKnowHow