Entity Framework connection to SQL Azure DB using access token

Question

We have web application which connects to SQL Azure DB. I have configured my application with Application ID and certificate. We would like to make use of Access Token Approach for connecting to SQL Server, As per below link, connecting through token approach to SQL Server is not a reliable approach. Any recommended way of connecting instead of User ID and Password.

Connect to Azure SQL using Azure Active Directory from an Azure Website?

Can anyone let me know if they have implemented SQL Azure DB AAD token based authentication using entity framework and is it right way for connecting.

Bruce Chen Bruce Chen · Accepted Answer · 2017-03-31T06:14:15

According to your description, I followed the tutorial about using AAD Authentication for Azure SQL Database.

As this tutorial mentioned about Azure AD token authentication:

This authentication method allows middle-tier services to connect to Azure SQL Database or Azure SQL Data Warehouse by obtaining a token from Azure Active Directory (AAD). It enables sophisticated scenarios including certificate-based authentication.You must complete four basic steps to use Azure AD token authentication:

Register your application with Azure Active Directory and get the client id for your code.

Create a database user representing the application. (Completed earlier in step 6.)

Create a certificate on the client computer runs the application.

Add the certificate as a key for your application.

Then I followed the code sample in this blog for getting started with this feature, and it works as expected.

Can anyone let me know if they have implemented SQL Azure DB AAD token based authentication using entity framework and is it right way for connecting.

Based on the above code sample, I added EntityFramework 6.1.3 for implementing SQL Azure DB AAD token based authentication using entity framework. After some trials, I could make it work as expected. Here are some details, you could refer to them.

DbContext

public class BruceDbContext : DbContext
{
    public BruceDbContext()
        : base("name=defaultConnectionString")
    { }

    public BruceDbContext(SqlConnection con) : base(con, true)
    {
        Database.SetInitializer<BruceDbContext>(null);
    }

    public virtual DbSet<User> Users { get; set; }
}

DataModel

[Table("Users")]
public class User
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; }
    [StringLength(50)]
    public string UserName { get; set; }
    public DateTime CreateTime { get; set; }
}

Program.cs

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();
    }
}

Result

Note: The contained database user for your application principal via CREATE USER [mytokentest] FROM EXTERNAL PROVIDER does not has any permissions to access your database. You need to grant privileges for this user, for more details you could refer to this issue.

Additionally, when you construct the DbContextinstance, you need to implement the SqlConnection instance with a valid AccessToken. AFAIK, you need to handle the token refreshing when the token is expired.

Entity Framework connection to SQL Azure DB using access token

1 Answers