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.

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.

• sql-server
• excel
• vba
• entra

• Check learn.microsoft/en-us/sql/connect/oledb/features/… perhaps? – siggemannen Commented Nov 19, 2024 at 12:14

1 Answer 1

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;Database=WH;Authentication=ActiveDirectoryInteractive;User [email protected]"
    conn.Open