实体框架使用访问令牌连接到SQL Azure DB [英] Entity Framework connection to SQL Azure DB using access token

问题描述

我们有连接到SQL Azure DB的Web应用程序。我已经为我的应用程序配置了应用程序ID和证书。我们想利用访问令牌方法来连接到SQL Server，按照下面的链接，通过令牌方法连接到SQL Server并不是一种可靠的方法。任何建议的连接方式，而不是用户ID和密码。

注意：通过通过外部提供者创建用户[mytokentest]的应用程序主体的数据库用户没有任何访问数据库的权限。您需要为此用户授予特权，有关更多详细信息，请参考此问题。

另外，当您构造 DbContext 实例时，您需要使用有效的 AccessToken 来实现 SqlConnection 实例。 AFAIK，您需要在令牌过期时处理令牌刷新。

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.

解决方案

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.

这篇关于实体框架使用访问令牌连接到SQL Azure DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！