Login fails for user '' -- using an ASP.NET SqlDataSource in Azure using a managed identit-CodePudding

I'm working on an old webpage which the client is trying to migrate to Azure. The app has worked forever, so it's not a question of "maybe the code is broken". Instead, it's the migration to Azure that's been throwing up issues. The differences between a localhost build and the Azure build are significant, since I don't have a Managed Identity local. But the differences are primarily configuration, other than bits of code to add an Access Token to SQL connections.

The app uses dozens of asp:SqlDataSource objects defined in .aspx pages to do all sorts of work. But that's fine, they use a standard connection string, pulled from the web.config, to talk to the database:

<add name="MyConnectionString"
connectionString="Server=tcp:MyDatabaseServer.database.windows.net,1433;
Initial Catalog=MyDatabaseName;
Persist Security; Info=False; MultipleActiveResultSets=False;
Encrypt=True; TrustServerCertificate=False;
Connection Timeout=30" providerName="System.Data.SqlClient"/>

For security reasons, the client now has to use a Managed Identity to talk to the database, rather than our previous service account model.

I added an OnSelecting event to the SqlDataSource where I set an access token on the connection. I'm not getting any exceptions or complaints about that code, so either it's running fine or it's not running at all. The managed identity is created, and it has reader, writer, and executor roles on the database, but this bit of code is the first time that the managed identity is being put through its paces.

What I get is an error

[SqlException (0x80131904): Login failed for user ''.]

Is there some other permissions or flags I need to set on the managed identity, or some missing configuration? Should I be using a different event than the OnSelecting event? Is the URL for an access token different in this situation? Is there a place to find more detailed error logs?

CodePudding user response：

First note that I haven't seen any possible way to use SQL-based session state with a managed identity. Switch your Session storage to InProc and see if your login problem goes away.

Second, I've found that making a web request to 169.254.169.254 to obtain a token doesn't work in a sovereign Azure environment. Instead, do this:

var credential = new Azure.Identity.DefaultAzureCredential(); 
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
connection.AccessToken = token.Token;

Using the OnSelecting event to assign the access token to the connection will work fine for a SqlDataSource. Instead of creating credentials and getting a token every time a stored procedure is called, store the token in the app and just re-use it; and make sure to purge the token when the user logs out.