How to secure Azure SQL database used by Web API and Application (Azure AD)

Question

I am currently developing several applications, mostly hosted on Azure : an Excel add-in (Office Apps) that calls an API hosted on Azure, a web application hosted on Azure, and probably other applications later.

To authenticate users, I use Azure Active Directory (Azure AD), based on Token authentication for the web API, and OpenID for the web application. Every call to the API or web app is thus authenticated thanks to the Azure AD, so Role-Based Access Control can be implemented in the application layer.

These API/apps are often calling an Azure SQL database and my question is the following : is it possible to use the authenticated user (from Azure AD) to also authenticate the database access ?

Currently, database credentials are hardcoded in the connection string (the login is created for the application), but this does not allow strict DB access control rules.

Additional information : integrated authentication (using Windows user) is not possible, because Windows login and Azure login are different in my organization : Azure and Windows Active Directories are different.

Thanks a lot for your help.

yoape · Accepted Answer · 2017-01-25 11:54:23Z

1

Yes, Azure SQL Server supports Azure Active Directory authentication. This article describes how to set it up: https://learn.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication

Your database needs to be V12 for this feature. If it is not you can upgrade it to V12.

You can then connect to your database with a connectionstring like:

    string ConnectionString = @"Data Source=n9lxnyuzhv.database.windows.net; Authentication=Active Directory Integrated; Initial Catalog=testdb;";
    SqlConnection conn = new SqlConnection(ConnectionString);
    conn.Open();

answered Jan 25, 2017 at 11:54

yoape

3,3351 gold badge17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rimcanfly Over a year ago

Hi Yoape, thanks for your answer. I already tried Active Directory Integrated, and it doesn't work. I guess this is because the users are not logged onto the Azure AD on the computer, but I'm not totally sure.

Dan Rediske Over a year ago

Since your users are not AAD authenticated on the computer, you may have to opt for Username/Password AAD. The details are on the same link @yoape posted.

RudyCo Over a year ago

Can we use something similar to github.com/Microsoft/sql-server-samples/tree/master/samples/… ? But for an ASP.NET Web API

yoape Over a year ago

Depending on how you have done the token authentication for your Web/Web API, it might be possible to use token authentication for the connection, check out 7.3 Connecting with an Azure AD token in the same article.

Rimcanfly Over a year ago

Concerning the web API : the token is acquired for the website calling the web api (which has a delegated right to call the API), which means the token resource is "mycompany.onmicrosoft.com/myWebsiteUsingTheAPI", not "database.windows.net". Is it possible to get access to the database with such a token, by delegating the permission ? I could not get it to work, and all tutoriel point to getting a token for "database.windows.net", which means a token dedicated to database access.

Collectives™ on Stack Overflow

How to secure Azure SQL database used by Web API and Application (Azure AD)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related