Share via

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

George Dimitrov 0 Reputation points
2024-07-12T16:24:59.9766667+00:00

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.

Azure SQL Database
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
858 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Oury Ba-MSFT 18,021 Reputation points Microsoft Employee
    2024-07-12T21:51:23.34+00:00

    @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

    0 comments