How to assign SPN access to a Azure SQL Database using T-SQL

Question

I already have a SPN and I want to give it access to Azure SQL Database using T-SQL. How to do this.

Answer 1

@Ayush Shrivastava Thank you for reaching out.

UserPrincipalName of the Microsoft Entra object for Microsoft Entra Users.

• CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
• CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;

When creating the user in the Azure SQL database, the login_name must correspond to an existing Microsoft Entra login, or else using the FROM EXTERNAL PROVIDER clause will only create a Microsoft Entra user without a login in the master database.

Microsoft Entra server principals (logins) introduces creating users that are mapped to Microsoft Entra logins in the virtual master database. CREATE USER [bob@contoso.com] FROM LOGIN [bob@contoso.com]

Answer 2

You need to login via an AD server admin account on the database and post that execute the below queries :

CREATE USER [<<SPName>>] FROM EXTERNAL PROVIDER

EXEC sp_addrolemember '<<rolename>>', [<<SPName>>]

Below link provides the details :

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql