admin管理员组

文章数量:1023120

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
Share Improve this question edited Nov 19, 2024 at 12:23 marc_s 757k184 gold badges1.4k silver badges1.5k bronze badges asked Nov 19, 2024 at 11:52 CobaltZorchCobaltZorch 1551 silver badge5 bronze badges 1
  • Check learn.microsoft/en-us/sql/connect/oledb/features/… perhaps? – siggemannen Commented Nov 19, 2024 at 12:14
Add a comment  | 

1 Answer 1

Reset to default 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

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
Share Improve this question edited Nov 19, 2024 at 12:23 marc_s 757k184 gold badges1.4k silver badges1.5k bronze badges asked Nov 19, 2024 at 11:52 CobaltZorchCobaltZorch 1551 silver badge5 bronze badges 1
  • Check learn.microsoft/en-us/sql/connect/oledb/features/… perhaps? – siggemannen Commented Nov 19, 2024 at 12:14
Add a comment  | 

1 Answer 1

Reset to default 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

本文标签： Excel VBA connection to SQL Server with Microsoft Entra MFA authenticationStack Overflow

版权声明：本文标题：Excel VBA connection to SQL Server with Microsoft Entra MFA authentication - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745563907a2156333.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。