Persistent Browser Session / Keep me signed in (KMSI) - for ODBC connections in Excel

Christoffer Struve Sømod 1 Reputation point
2021-04-09T09:12:27.8+00:00

Disclaimer: I've also made this post here on Stackoverflow (I'm a bit uncertain where it's best to ask).

Is anyone aware of whether it's possible to enable the "Persistent Browser Sessions"/"Keep me signed in" feature for ODBC connections made through Excel VBA (or other applications for that matter, as long as it's an ODBC connection)?

I know that it's possible to stay signed in when fetching data from a SQL Database using PowerQuery from within Excel - or simply the "Get Data --> From Database --> From SQL Server-database" function in Excel. This I've verified myself, where I'm not prompted to login in again when refreshing the data in a table (Excel QueryTable).

Background info of my setup:

  • ODBC Driver: ODBC Driver 17 for SQL Server
  • Connection string:
    "Driver={ODBC Driver 17 for SQL Server};Server=tcp:{my_SQL_server},1433;Database={my_database};TrustServerCertificate=no;Authentication=ActiveDirectoryInteractive;Uid=;Encrypt=yes;Connection Timeout=30;"
  • Office 365
  • Windows 10
  • Using an ADODB connection via Excel VBA - code example:
    Set cn = New ADODB.Connection
  • Repeated connections from Excel/VBA to the SQL Database doesn't require reauthentication immediately - however if you wait some time (haven't tested exactly how long), but maybe an hour or so, you will be to reauthenticate. Also, whenever you close the workbook and reopen it, reauthentication is required. Below an image of the login screen for reference sake, in case it's a help for someone/something:

Lyb5m.png

My considerations and what I've tried so far:

  • I'm wondering whether it's not possible to set the required cookie in the first place - and was almost certain this also was the reason why it isn't working, but when I checked the sign-ins overview it got me a little confused, as both the ODBC connecion and the connection through Power Query showed as being from an IE11 browser (interactive logins - authentication required):
    cVeSZ.png

And none-interactive logins (logins without reauthentication (personal info redacted) - note that the ODBC logins is connections established shortly after each other as mentioned earlier)DWDVz.png

I've tried the following settings in Azure to "keep me signed in":

Important:
My goal is to remove the need to input my credentials every time the workbook is opened without the need of being at a specific network/IP. I believe this rules out the option of using Seamless SSO which might have been another option. However, registering a device in Azure is not an issue. I also believe this rules out the option of using the "Integrated" authentication method(?), which could remove the need of reauthenticating often

Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,380 questions