0

I have a utility application that is deployed to our test environments to help our developers assess the state of the platform; to do this it needs to access our Azure SQL db.

To connect to the database I'm using Authentication=Active Directory Interactive; in the connection string; under this method when the user opens the application and begins the connection process they are presented with an Azure AD authentication dialog and they can log in with their AD credentials via two factor auth.

This all works fine except for the fact that, if the user leaves the application for a few minutes (or otherwise doesn't do anything that causes a SQL query to be performed), they will then be prompted for another authentication dialog where they have to go through the entire login process again, 2FA and all. It's like the authentication 'expires'.

At the moment I'm leaving the application deployed as it is because it does work, but its users (myself included) are being driven mad by the need to constantly log in.

In an attempt to stop the authentication from expiring I've tried putting a simple select 1 query on a loop in the background (see the following code snippet) but this didn't work.


        public SqlConnection GetSqlConnection()
        {
            if (_connectionString is null)
            {
                throw new InvalidOperationException(
                    "The connection string has not been set");
            }

            if (_sqlConnection is not null)
            {
                return _sqlConnection;
            }

            _sqlConnection = new SqlConnection(_connectionString);

            if (_keepAlive)
            {
                _keepAliveTimer =
                    new Timer(_keepAliveTickLength) { AutoReset = true, Enabled = true };

                _keepAliveTimer.Elapsed += (_, _) =>
                {
                    _sqlConnection?.Query("select 1");
                };
            }

            return _sqlConnection;
        }

I have to admit my understanding of how AD authentication works in a .NET app is not very deep, so I welcome any pointers. If anyone has any ideas on how I can stop this app from needing to reauthenticate every few minutes I'd be very grateful!

In case it matters this is a .NET 5 WPF app.

Richiban
  • 5,569
  • 3
  • 30
  • 42
  • Once you login you could have your on class for the `user` and control the logoff... do you need to make more calls to the AD?. Is the Project Windows Form or Web? Please elaborate more what are you doing. – Rui Caramalho Mar 29 '21 at 16:55
  • As stated, it's a WPF app. I don't know what you mean by "Once you login you could have your on class for the user and control the logoff". Please let me know what I can do to make my question clearer. – Richiban Mar 29 '21 at 19:30
  • Sry I never worked with WPF. I've seen this link for you see if it helps: https://stackoverflow.com/questions/1483892/how-to-bind-to-a-passwordbox-in-mvvm – Rui Caramalho Mar 29 '21 at 19:42
  • Maybe you could [configure AAD token lifetime](https://learn.microsoft.com/en-us/azure/active-directory/develop/configure-token-lifetimes) to alleviate this issue. – Allen Wu Mar 30 '21 at 02:11

1 Answers1

0

This may be the reason for the short lifetime of the token.

If you are using Azure AD for login authentication, then you can parse your token to check the lifetime of the token. If the token has a lifetime of only a few minutes, this may cause this problem.

So, as Allen said, you can try to configure the AAD token lifetime to alleviate this problem, you need to use powershell to create a token lifetime policy, and then assign the policy to the service principal to set the token lifetime.

Carl Zhao
  • 8,543
  • 2
  • 11
  • 19
  • But I'm not authenticating manually so I never actually see the token. I'm just authenticating by putting `ActiveDirectoryInteractive` as the authentication method in the connection string (as per these docs: https://learn.microsoft.com/en-us/azure/azure-sql/database/active-directory-interactive-connect-azure-sql-db#c-code-example) so it's something that's supported by the default SqlConnection of .NET. I get the feeling I need to configure token auto-renewal somehow? – Richiban Apr 06 '21 at 18:34