[SOLVED] Excel VBA connection to SQL Server with Microsoft Entra MFA authentication

Excel VBA connection to SQL Server with Microsoft Entra MFA authentication

I've been asked to fix a broken macro (that I didn't create) which has started returning an error.

Essentially the macro opens a connection to our SQL Server, runs a stored procedure, and writes the output to a table; pretty simple.

However, our server has recently changed to require Microsoft Entra MFA authentication (whereas previously we could use SQL Server authentication) so the login fails. I'm struggling to work out how to structure the connection string to use this type of authentication.

The previous connection string was:

Provider=SQLOLEDB;Data Source=[SQL Server];User ID=user.name;Password=P4$$word;Initial Catalog=db_name

Any pointers gratefully received.

Solution

That's the legacy OleDb provider that ships with Windows. It doesn't support Entra ID auth. You'll need to install the new MSOLEDBSQL driver to connect. It supports interactive, MFA auth, or Service Principal auth. EG

    conn.ConnectionString = "Provider=MSOLEDBSQL;Server=myserver.database.windows.net;Database=WH;Authentication=ActiveDirectoryInteractive;User ID=SomeUser@SomeCompany.com"
    conn.Open