DBMigrator fails to read accesstoken in ef6 codefirst - SQL Connection

user_2432690 0
Viewed 431

I have a WebAPI with ef6 code first setup and working. In app start we have dbmigrator.Update() which applies any pending migrations to database.

After changing the connection string to remove username and password and provide access token instead, dbmigrator.Update() fails with an error:

Login failed for user ''

How to ensure that dbmigrator works with Azure SQL access token instead of username/password in connection string?

Edit 1:

The change done to dbcontext constructor is to change it from 

DbContext() : base("nameofConnString"){}

to

DbContext() : base(GetSQLConn(), true) 
{
     Database.SetInitializer<DbContext>(null);
}

With GetSQLConn(), I am retrieving a connection without uname/pwd and attaching accesstoken to it and returning the connection!

Edit 2:

    private static SqlConnection GetSQLConn()
    {
        var accessToken = TokenFactory.AcquireToken();
        var connString = ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString;

        var conn = new SqlConnection(connString)
        {
            AccessToken = accessToken,
        };

        return conn;
    }
azure asp.net-web-api entity-framework sql c#
1 Answers

How to ensure that dbmigrator works with Azure SQL access token instead of username/password in connection string?

actually dbcontext works for CRUD operations on all my tables, just this migrator won't work!

According to your comment, it seems that you have no permission to alert table. If you don't grant corresponding permission for the created user. Please have a try to grant permission to the created user. More details about db role please refer to the Database-Level Roles.

EXEC sp_addrolemember N'db_owner', N'your-user-name'

I also following the SO thread you mentioned.If I add the user to do_owner, I test it with Alter Table, it works correctly on my side. The following is my detail steps.

1.Registry an Azure AD Application

2.Provision an Azure Active Directory administrator for your Azure SQL server, more details please refer to the this tutorials

enter image description here

3.Create user for the Azure SQL and grant corresponding permission.

CREATE USER [RegistryAppName] FROM  EXTERNAL PROVIDER 

EXEC sp_addrolemember N'db_owner', 'RegistryAppName'

enter image description here

4.Change the demo code and run it as expected.

 SqlConnectionStringBuilder builder =
         new SqlConnectionStringBuilder
             {
               ["Data Source"] = "azureServername.database.windows.net",
                    ["Initial Catalog"] = "databaseName",
                    ["Connect Timeout"] = 30
             };
    // replace with your server name
    // replace with your database name

    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;
               connection.Open();

               var commentText = "ALTER TABLE AccountRec ADD newColumn varchar(10) ";
               SqlCommand sqlCommand = new SqlCommand(commentText, connection);
                   
               Console.WriteLine("Executed Result:" + sqlCommand.ExecuteNonQuery());
           }
         catch (Exception ex)
         {
                Console.WriteLine(ex.Message);
         }
      }
      Console.WriteLine("Please press any key to stop");
      Console.ReadKey();
user_70051590
Related