Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. Token is expired

Question

I am using Entity Framework to connect to an Azure SQL database and I'm connecting using an access token (obtained via the Managed Identities).

I am not using user name and password in the SQL connection string and using managed identity and keeping SQL connection string in Azure keyvault.

I'm connecting to the database with a token like shown below, but after an hour it expires, so I'm getting "SQL login failed - " errors.

I have a long running process once connected the the database, and it collects some config data from some tables, and after more than 45 minutes, I need to update data in the database. When trying to save changes using dbcontext, the code is throwing this timeout issue.

How to get a new refreshed token and set to connection?

public SqlConnection GetDBConnection()
{
    SqlConnection conn = new SqlConnection();
    conn.ConnectionString = Environment.GetEnvironmentVariable("SqlConnectionString");

    var credential = new DefaultAzureCredential();
    var accessToken = credential.GetToken(new TokenRequestContext(new[] { "https://database.windows.net/.default" }));

    conn.AccessToken = accessToken.Token;

    return conn;
}

Are you disposing your connection as soon as each command is finished? In other words, you should not cache the connection object. Create when needed, execute commands, dispose immediately with `using`. See [C# Data Connections Best Practice?](https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice) — Charlieface, Sep 30 '21 at 14:52

score 0 · Answer 1 · answered Oct 01 '21 at 12:03

We need to make sure that the connection is not closed until the job is done, and coming to the token part it might expire when we have any connection issue when we run long processes.

So as we got some insights in the comments section to use “USING”. This can be done.

Below is how we use it for connections, mostly for SQL:

using (SqlConnection conn = new SqlConnection(...))
{
    using(SqlCommand cmd = new SqlCommand(..., conn))
    {
        conn.Open();
        using(DataReader dr = cmd.ExecuteReader())  // or load a DataTable, ExecuteScalar, etc.    
        {
             ...
        {
    }
}

Also check for AAD Authentication with Azure SQL Database as we have seen token-identified-principal.

Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user ''. Token is expired

1 Answers1