Microsoft Entra logins and users with nonunique display names (preview)

Applies to: Azure SQL Database Azure SQL Managed Instance

This article teaches you how to use the T-SQL Object_ID syntax to create Microsoft Entra logins and users with nonunique display names in Azure SQL Database and Azure SQL Managed Instance.

Overview

Microsoft Entra ID supports authentication for service principals. However, using a service principal with a display name that is not unique in Microsoft Entra ID leads to errors when creating the login or user in Azure SQL.

For example, if the application myapp isn't unique, you might run into the following error:

Msg 33131, Level 16, State 1, Line 4 
Principal 'myapp' has a duplicate display name. Make the display name unique in Azure Active Directory and execute this statement again. 

When trying to run the following T-SQL statement:

CREATE LOGIN [myapp] FROM EXTERNAL PROVIDER 

The WITH OBJECT_ID extension

The duplicate display name error occurs because Microsoft Entra ID allows duplicate display names for Microsoft Entra application (service principal), while Azure SQL requires unique names to create Microsoft Entra logins and users. To mitigate this problem, the Data Definition Language (DDL) statement to create logins and users has been extended to include the Object ID of the Azure resource with the WITH OBJECT_ID clause.

T-SQL create login/user syntax for nonunique display names

CREATE LOGIN [login_name] FROM EXTERNAL PROVIDER 
  WITH OBJECT_ID = 'objectid'

CREATE USER [user_name] FROM EXTERNAL PROVIDER 
  WITH OBJECT_ID = 'objectid'

With the T-SQL DDL supportability extension to create logins or users with the Object ID, you can avoid error 33131 and also specify an alias for the login or user created with the Object ID. For example, the following will create a login myapp4466e using the application Object ID 4466e2f8-0fea-4c61-a470-xxxxxxxxxxxx.

CREATE LOGIN [myapp4466e] FROM EXTERNAL PROVIDER 
  WITH OBJECT_ID = '4466e2f8-0fea-4c61-a470-xxxxxxxxxxxx' 

• To execute the above query, the specified Object ID must exist in the Microsoft Entra tenant where the Azure SQL resource resides. Otherwise, the CREATE command will fail with the error message: Msg 37545, Level 16, State 1, Line 1 '' is not a valid object id for '' or you do not have permission.
• The login or user name must contain the original service principal name extended by a user-defined suffix when using the CREATE LOGIN or CREATE USER statement. As a best practice, the suffix can include an initial part of its Object ID. For example, myapp2ba6c for the Object ID 2ba6c0a3-cda4-4878-a5ca-xxxxxxxxxxxx. However, you can also define a custom suffix. Forming the suffix from the Object ID is not required.

This naming convention is recommended to explicitly associate the database user or login back to its object in Microsoft Entra ID.

Identify the user created for the application

For nonunique service principals, it's important to verify the Microsoft Entra alias is tied to the correct application. To check that the user was created for the correct service principal (application):

1. Get the Application ID of the application, or Object ID of the Microsoft Entra group from the user created in SQL Database. See the following queries:

   • To get the Application ID of the service principal from the user created, execute the following query:

     SELECT CAST(sid as uniqueidentifier) ApplicationID, create_date FROM sys.server_principals WHERE NAME = 'myapp2ba6c' 
     

     Example output:

     

     The Application ID is converted from the security identification number (SID) for the specified login or user name, which we can confirm by executing the below query and comparing the last several digits and create dates:

     SELECT SID, create_date FROM sys.server_principals WHERE NAME = 'myapp2ba6c' 
     

     Example output:

     

   • To get the Object ID of the Microsoft Entra group from the user created, execute the following query:

     SELECT CAST(sid as uniqueidentifier) ObjectID, createdate FROM sys.sysusers WHERE NAME = 'myappgroupd3451b' 
     

     Example output:

     

     To check the SID of the Microsoft Entra group from the user created, execute the following query:

     SELECT SID, createdate FROM sys.sysusers WHERE NAME = 'myappgroupd3451b' 
     

     Example output:

     

   • To get the Object ID and Application ID of the application using PowerShell execute the following command:

     Get-AzADApplication -DisplayName "myapp2ba6c"
     

2. Go to the Azure portal, and in your Enterprise application or Microsoft Entra group resource, check the Application ID or Object ID respectively. See if it matches the one obtained from the above query.

Finding the right Object ID

For information on the Object ID of a service principal, see Service principal object. You can locate the Object ID of the service principal listed next to the application name in the Azure portal under Enterprise applications.

Related content

• Microsoft Entra server principals
• CREATE LOGIN (Transact-SQL)
• CREATE USER (Transact-SQL)