The 'WITH Object_ID' syntax for creating SQL logins and users for nonunique Microsoft Entra service principals and groups is now in public preview.
Microsoft Entra ID is a cloud-based identity and access management service that allows you to manage users, service principals, and groups in a unified directory. Microsoft Entra ID also enables you to grant access to resources, like Azure SQL database and Azure SQL Managed Instance, by creating logins and users for Microsoft Entra identities.
When a principal or group is created, a unique Object ID GUID is assigned and a custom display name is defined by the user. With the Object ID acting as the unique identifier, Entra does not require a principal display name to be unique making it possible for more than one service principal or group to have the same display name. However, in Azure SQL unique login and user names are a requirement.
When a new SQL login or user is being created for a Microsoft Entra principal and the Transact-SQL (T-SQL) CREATE LOGIN or CREATE USER query is run, a lookup is done for that principal name. If there is more than one Microsoft Entra principal that exists with the same name, or in other words has a duplicate display name, SQL returns an error message.
For example, if I have two Microsoft Entra service principals named “myapp,” executing:
CREATE USER [myapp] FROM EXTERNAL PROVIDER
returns
Message 33131: Principal 'myapp' has a duplicate display name. Make the display name unique in Azure Active Directory and execute this statement again.
Solution using the Object ID syntax (public preview)
The Object ID (OID) is a 36-character hexadecimal string that uniquely identifies the Microsoft Entra principal.
The 'WITH Object_ID' syntax is an optional clause that can be added to the CREATE USER or CREATE LOGIN statement, as shown below:
CREATE LOGIN [login_name] FROM EXTERNAL PROVIDER WITH Object_ID = '<object_id>'
CREATE USER [user_name] FROM EXTERNAL PROVIDER WITH Object_ID = '<object_id>'
The introduction of the Object_ID syntax resolves the naming conflict between Entra allowing nonunique or duplicate display names for principals and groups, and SQL requiring unique names for logins and users by enabling SQL to perform the Microsoft Entra principal lookup by Object ID when the CREATE query is run.
This syntax also allows the user to define a custom alias by adding a suffix to the original principal or group name for the new login or user.
You can locate the Object ID of any principal in the Azure portal.
Finding the right Object ID
There are two Object IDs available in the Azure Portal UI of the Microsoft Entra ID account for a principal or group. To find the correct one, navigate to Enterprise applications located under Manage on the menu blade of the Entra account and search for your application or group.
The correct Object ID GUID is listed next to the identity under the Object ID column. However, to avoid a manual copy error, select the principal or group to open the Enterprise applications > Overview page. Under Properties, you will find the correct principal Object ID. Copy it.
Note: The Object ID property located on the App registrations > Overview page of the Entra service principal or group is not the correct Object ID for the ‘WITH OBJECT_ID’ clause. However, you can navigate to the correct OID from this view by selecting the Managed application in local directory principal name.
Using the OBJECT_ID syntax
For the nonunique service principal ‘testobjid’ in the screenshots from the previous section, we can run the CREATE USER query with the OBJECT_ID clause, which must begin with the original principal name optionally followed by any custom suffix appended to form an alias:
CREATE USER [testobjid1551] FROM EXTERNAL PROVIDER WITH OBJECT_ID = '1551f436-25de-4c1a-91f5-xxxxxxxxxxxx'
Let’s review our extended CREATE USER query. We created a custom user name alias by adding the first four characters of the Object ID as a suffix on the original Microsoft Entra principal ‘testobjid,’ included the new <WITH OBJECT_ID> option in the statement and supplied the full Object ID GUID as the value. Running the above query would return successfully.
Verifying the alias
You can verify that the SQL login or user alias is tied to the correct Microsoft Entra principal by looking up the Application ID of the service principal (or Object ID of the group):
SELECT CAST(sid as uniqueidentifier) ApplicationID, create_date FROM sys.server_principals WHERE NAME = 'login_alias'
SELECT CAST(sid as uniqueidentifier) ApplicationID, createdate FROM sys.sysusers WHERE NAME = 'user_alias'
Best practices and recommendations
We’ve discussed the naming conflict that can occur when creating new SQL logins and users for nonunique Microsoft Entra principals and group names, how the <OBJECT_ID> syntax resolves this issue, and how to find and use the correct Object ID GUID. Now we’ll go over best practices and recommendations for using this syntax.
When using the WITH OBJECT_ID option:
- Do not change the principal name in the CREATE statement unless there is a naming conflict caused by duplicate display names. The Object_ID syntax is a repair item. If no conflict exists use the CREATE statement without the OID syntax option.
- Check the current name of the service principal or group and its Object ID in Entra before running the CREATE query to avoid a manual error or confusion.
- When defining a login/user alias, use the original principal name and append the first few characters of the Object ID GUID as the suffix (ex. Principal_name00000). Though it is not required to use the OID as the suffix and you can define your own, using a portion of the OID as the suffix easily associates the new login or user alias with its corresponding principal OID.
Conclusion
By using the Object ID syntax, you can avoid Error Message 33131 and create SQL logins and users for principals with duplicate display names in Azure SQL Database and Azure SQL Managed Instance without any issues.
Related resources
For more information, see: