Connect azure sql use pyodbc failed

Question

We are try to load some data from azure sql to pandas, here are the code:

conn = pyodbc.connect("DRIVER={SQL Server};Server=tcp:xxx.database.windows.net,1433;Initial Catalog=Database;UID=******@xxx.onmicrosoft.com;PWD=xxx;Authentication=ActiveDirectoryPassword;MultipleActiveResultSets=False;")

Then get such error: [HY000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open server "xxx.onmicrosoft.com" requested by the login. The login failed. (40532) (SQLDriverConnect);

But if I remove the "Authentication=ActiveDirectoryPassword" and use a SQL account it will be success, maybe you will say "Hey, why don't you use SQL authoration, dude." well, considering about some security facts we gonna enable the "Azure AD-only authentication" and the SQL authoration will no longer be available soon.

Answer 1

Fixed, need to install the ODBC ver 18 (ver 17 is work or not I'm not sure), and modify the connection str like this:

conn = pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};Server=tcp:xxx.database.windows.net,1433;DATABASE=xxx;UID=******@xxx.onmicrosoft.com;PWD=xxx;Authentication=ActiveDirectoryPassword;")  
  

Answer 2

@William Wang
I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "the question author cannot accept their own answer. They can only accept answers by others", I'll repost your solution in case you'd like to "Accept" the answer.

Solution:
You'll need to install the ODBC ver 18, and modify the connection str like this:

conn = pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};Server=tcp:xxx.database.windows.net,1433;DATABASE=xxx;UID=******@xxx.onmicrosoft.com;PWD=xxx;Authentication=ActiveDirectoryPassword;")

Thank you for your time and patience throughout this issue!