Azure Active Directory authentication with SQL Database

Question

I have been trying to make Azure Active Directory authentication with SQL Database work for our Web API according to this and this Microsoft resource. However I have not been able to connect from our application to the Azure SQL.

I have the Active Directory admin set on Azure.

Then I created a group "dbusers" in Active Directory on Azure. The Active Directory admin is also assigned to this group.

I set it on the database with following command.

CREATE USER [dbusers] FROM EXTERNAL PROVIDER;

Everything up to here successfully completes.

I updated the web.config in my application to following:

Data Source=[my azure db]; Authentication=Active Directory Integrated; Initial Catalog=[my db name];

When I try to connect to the database from the Web API I get following exception:

Exception message: This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection., Exception stacktrace: at System.Data.Entity.SqlServer.SqlProviderServices.UsingMasterConnection(DbConnection sqlConnection, Action`1 act)

To which I found a workaround using following line:

Database.SetInitializer<MyDbContext>(null);

Which gets me to another good old known execption:

Exception message: The underlying provider failed on Open., Exception stacktrace: at System.Data.Entity.Core.EntityClient.EntityConnection.d__8.MoveNext()

I am starting to get crazy about how to configure this.

I am not sure if my configuration on Azure is faulty or if there is something wrong with my connection string or if there is something completely different to this issue.

EDIT:

One of the requirements is:

To use integrated Windows authentication, your domain’s Active Directory must be federated with Azure Active Directory. Your client application (or a service) connecting to the database must be running on a domain-joined machine under a user’s domain credentials

We are using Azure Web App & Azure SQL. How do I find this information or is this even possible without running dedicated VMs?

MirekS MirekS · Accepted Answer · 2018-04-17T18:57:03

Since you are running all under Active Directory Integrated, did you try to use SSMS and connect to your database using the Windows credentials? Was the connection successful as a specific Windows user The alternative is to use AAD user/password Also based on your string Data Source=[my azure db]; Authentication=Active Directory Integrated; Initial Catalog=[my db name]; the data source represents a server name in a format myazureserver.database.windows.net and the database mydbname In addition you have created the user "dbusers" in mydbname - correct?

Azure Active Directory authentication with SQL Database

1 Answers