且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

实体框架使用访问令牌连接到SQL Azure DB

更新时间:2023-01-15 12:52:16

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.