SQL server authentication mode from SQL AUTHENTICATION to AZURE AD AUTHENTICATION

Question

Is it possible to migrate the SQL server authentication mode from SQL AUTHENTICATION to AZURE AD AUTHENTICATION, in .Net framework running App service uses Entity framework to connect SQL server ?

Answer 1

By default, Azure SQL allows SQL Authentication and Azure AD authentication, but for Azure AD Authentication to work you need to provision an Azure Active Directory administrator for your Azure SQL logical server in the Azure portal or by using PowerShell. Here you will find how to do that in the Azure Portal.

Once you have done the previous step, you can use token-based authentication for Azure SQL Database using Azure Active Directory (Azure AD). Below you will find an EF sample code on how to do the token-based authentication and you will find more details on this StackOverflow forum thread.

class Program
{
    static void Main()
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder["Data Source"] = "brucesqlserver.database.windows.net";
        builder["Initial Catalog"] = "brucedb";
        builder["Connect Timeout"] = 30;

        string accessToken = TokenFactory.GetAccessToken();
        if (accessToken == null)
        {
            Console.WriteLine("Fail to acuire the token to the database.");
        }
        using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
        {
            try
            {   
                connection.AccessToken = accessToken;
                //working with EF
                using (var model = new BruceDbContext(connection))
                {
                   var users= model.Users.ToList();
                    Console.WriteLine($"Results:{Environment.NewLine}{JsonConvert.SerializeObject(users)}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        Console.WriteLine("Please press any key to stop");
        Console.ReadKey();
    }
}