Enabling user authentication to connect Access front end to Azure SQL Database

Question

I need to connect/link the tables in Access to an Azure SQL database using ODBC Driver 18 for SQL Server. I am trying to use New Data Source->From Database->From Azure Database to authenticate with either Azure AD Password or Interactive authentication.
However, only the Entra Admin is able to authenticate, and not any other users in the tenant. How can I enable user authentication for other users in the organization to connect to the database? I have read several articles about this, but I must be missing something.

Thanks in advance.

Answer 1

@George Dimitrov Thank you for reaching out.

You can create the user in the DB by following - Create contained database users in your database mapped to Azure AD identities

• CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
• CREATE USER [bob@contoso.com] FROM EXTERNAL PROVIDER;
• CREATE USER [alice@fabrikam.onmicrosoft.com] FROM EXTERNAL PROVIDER;
• CREATE USER [ICU Nurses] FROM EXTERNAL PROVIDER;
• CREATE USER [appName] FROM EXTERNAL PROVIDER;

To create new users, you must have the ALTER ANY USER permission in the database. The ALTER ANY USER permission can be granted to any database user. The ALTER ANY USER permission is also held by the server administrator accounts, and database users with the CONTROL ON DATABASE or ALTER ON DATABASE permission for that database, and by members of the db_owner database role.

To create a contained database user in Azure SQL Database, Azure SQL Managed Instance, or Azure Synapse, you must connect to the database or instance using a Microsoft Entra identity. To create the first contained database user, you must connect to the database by using a Microsoft Entra administrator (who is the owner of the database). This is demonstrated in Configure and manage Microsoft Entra authentication with SQL Database or Azure Synapse.

see also this article provides an overview of using Microsoft Entra ID (formerly Azure Active Directory) to authenticate to Azure SQL Database. https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview?view=azuresql