Azure SQL Database v12 AD authentication, some users not able to login

Question

Hi I have configured Azure active directory authentication on Azure SQL Server. After that I have added contained DB users on a SQL Database by using the following queries

Created user

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

Assigned following permissions

EXEC sp_addrolemember 'db_datareader', '[email protected]';
GO
EXEC sp_addrolemember 'db_datawriter', '[email protected]';
GO
GRANT EXECUTE ON SCHEMA :: dbo TO [[email protected]];
GO
GRANT ALTER ON SCHEMA :: dbo TO [[email protected]]; 

GRANT CREATE TABLE  TO [[email protected]];
GRANT SELECT,DELETE,UPDATE,INSERT TO [[email protected]];
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION to [[email protected]];
GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION to [[email protected]];

Some users are able to login and access the DB but some users getting error message saying login failed for the user. Do I need to provide more permissions?

What is the user type which can't access the database ? Do you grant same permissions to all that users ? — Nan Yu
Yes, all users granted same permissions and User type is Member. Who are able to access they also have User type as Member. — narendramacha

Greg Grater Greg Grater · Accepted Answer · 2017-04-26T19:16:05

By default, connections to Azure SQL that do not specify a specific database will attempt to log into the master database. Because AAD uses group based membership, if a user is part of a group that can access the master database, they will be able to login. Users that get denied login are typically denied because they have not been granted rights to master and no specific database was specified in the connection string.

To connect through SSMS: On the "Connect to Database Engine" dialog select the "Options" button, the "Connection Properties" tab and specify the database name in the "Connect to database:" dropdown. You'll need to know it b/c the user does may not have access to master to read the list of databases on the virtual database server.

You can get a connection string for your tech stack from the Azure Portal under the "Overview" for the database.

Azure SQL Database v12 AD authentication, some users not able to login

1 Answers