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
That's the legacy OleDb provider that ships with Windows. It doesn't support Entra ID authentication. You'll need to install the new MSOLEDBSQL driver to connect. It supports interactive authentication, MFA authentication, or Service Principal authentication. E.g.,
conn.ConnectionString = "Provider=MSOLEDBSQL;Server=myserver.database.windows.net;Database=WH;Authentication=ActiveDirectoryInteractive;User ID=SomeUser@SomeCompany.com"
conn.Open