Search code examples
azureazure-sql-database

Azure Granted Permission to SQL Server not authenticating

I have granted an Azure user within our organization with SQL Contributor access to our SQL database:

enter image description here

But when the user attempts access the database from Azure's Query editor, they get the message

Login failed for user

enter image description here

Can someone let me know if there are any additional permissions that I need to grant the user?

Solution

  • Azure SQL has its own users, roles, and permissions inside it. Assigning a role from Azure Portal with Azure's RBAC won't give the user rights to connect to the database.

    You must instead connect to the database with an Entra ID (Azure AD) user and run e.g.:

    CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_datareader','[email protected]';

    This would create the user and grant them read access to everything. You should consider what access they need.

    • Role granted through Azure Portal -> Management access to Azure resource, edit settings, view metrics etc.
    • Role/permissions granted through SQL -> Connect, run queries

    If the user only needs to connect and run queries, you don't need the Azure RBAC role.