Blog Post

Azure Database Support Blog
2 MIN READ

Principle 'XYZ' could not be found or this principal type is not supported - Azure SQL DB and MI

Sabrin_Alsahsah's avatar
Sabrin_Alsahsah
Icon for Microsoft rankMicrosoft
Sep 01, 2022

We received some support cases when customers encounter the error below while trying to add an AAD security group to their Azure SQL Database or Azure SQL managed instance. In this blog article, we will be listing a few points to be checked to troubleshoot this error and can help you to identify the cause.

 

Msg 33130, Level 16, State 1, Line 1

Principal 'XYZ' could not be found or this principal type is not supported.

 

 

Quick notes before we start, please make sure you have an AAD admin set to your Azure SQL. This can be checked from Azure portal -> Azure SQL server or Managed instance -> Azure Active Directory Admin (as below):

 

 

In case you faced this error, you can check the below points to help you to identify the cause and resolve it:

 

1) Validate that the User\security group you are trying to add in the associated Azure Active directory, you can check from Azure portal -> Azure Active Directory -> Users\Groups.  

 

2) Having extra white spaces within the AAD group name and this can be checked by following the below steps:

 

  1. Access your Azure Active Directory on Azure Portal -> Users or Groups -> Select the users\groups and click on Download Groups.    
  2. This action will export the selected groups to a CSV file.                                                                         
  3. Open the excel check and verify if you have any white spaces at the beginning or the end of the User\group name.
  4. If this was the cause, you can alter the group name by following this article.

 

3) Make sure that your AAD user or security group name does not have any reserved characters that are used by SQL, as this can affect the syntax of your query. For example, the characters [] as per the below:

Group name : [Azure] GroupName

In this scenario you need to take into consideration altering the script to allow having these characters as below:

 

CREATE USER [[Azure]] GroupName] FROM EXTERNAL PROVIDER

 

 

4) In case you are trying to add a service principle, you can use the objectID to add it to your Azure SQL database, using the script below:

 

CREATE USER [Azure_AD_Object] FROM EXTERNAL PROVIDER

 

 

You can get the object ID from Azure Portal à Azure Active Directory -> Enterprise Applications.

 

For more information: Create Azure AD users using service principals - Azure SQL Database | Microsoft Docs.

 

More information

Azure Active Directory service principal with Azure SQL - Azure SQL Database | Microsoft Docs

CREATE USER (Transact-SQL) - SQL Server | Microsoft Docs

 

I hope this article was helpful for you, please feel free to share your feedback in the comments section. 

 

Updated Sep 18, 2022
Version 4.0
AAD
Azure Active Directory
azure sql
Azure SQL DB
Azure SQL MI
  • Johannes_Vink's avatar
    Johannes_Vink
    Brass Contributor
    Sep 01, 2022

    I ran into this recently: if you do not have rights to view users/groups in AAD, then you cannot add principals in Azure SQL DB. Check 1 listed in the document will show that the user/group is not visible to you.

  • sam-mfb's avatar
    sam-mfb
    Copper Contributor
    Jan 12, 2023

    If you are trying to do this in Powershell (or via any other mechanism where the SQL cmd executes quickly after the AAD object creation), you may find this fails on the first few tries because of a lag in AAD syncing, as documented in this old, but still open, issue.  In other words, it takes some time for the creation of the object in AAD to propagate to the point where it is visible to SQL server resources.  

    As far as I can tell, retrying is the only way to solve that issue.  In the github link, I've provided a Powershell retry solution.

  • platar's avatar
    platar
    Brass Contributor
    Aug 23, 2023

    I receive this error when invoking session context.  I would like to use the session context across Azure Data Studio SQL code blocks.

     

    EXEC sp_set_session_context N'Principal', 'users id ';
    SELECT SESSION_CONTEXT(N'Principal');
    DECLARE @Principal varchar(50);
    SELECT @Principal = CAST(SESSION_CONTEXT(N'Principal') AS varchar(50));
    SELECT @Principal;
    CREATE LOGIN [@Principal] FROM EXTERNAL PROVIDER

     

    output:

    (1 row affected)

    (1 row affected)
    Msg 33130, Level 16, State 1, Line 6
    Principal '@Principal' could not be found or this principal type is not supported.

     

    What am I missing?  

    Thanks.  Rob