How do I connect to Azure SQL DB from VBA with Authentication = ActiveDirectoryInteractive?

Question

Set cnSQL = New ADODB.Connection 'cnSQL.Open "ODBC;DRIVER=SQL Server; Authentication = ActiveDirectoryInteractive; Database=XXXXX; Data Source=XXXX.database.windows.net"

Answer 1

This should give you what you need.

You'll have to download the ODBC Driver 17 for SQL Server and depending on your connection type you can update Authentication option

Sub AdoOdbcExample()    
    Dim con As Object    
    Set con = CreateObject("ADODB.Connection") 
    con.Open _
            "Driver={ODBC Driver 17 for SQL Server};" & _
            "Server=tcp:yourserver.database.windows.net,1433;" & _
            "Database=yourdb;" & _
            "Trusted_Connection=no;" & _
            "Authentication=ActiveDirectoryInteractive;" & _
            "UID=youremail;"
    con.Execute "UPDATE Clients SET FirstName='Gord' WHERE ID=5;"    
    con.Close    
    Set con = Nothing   
    'Authentication=ActiveDirectoryIntegrated
    'Authentication=ActiveDirectoryInteractive
    'ActiveDirectoryPassword  

End Sub

Answer 2

If you login to https://portal.azure.com/ and then open up your azure sql database - not the server, you can find pre configured connection strings under "Settings" for various database connections like odbc:

ODBC:

• ODBC (Includes Node.js) (SQL authentication)
• Driver={ODBC Driver 17 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Uid=yoursqluser;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;
• ODBC (Includes Node.js) (Azure Active Directory password authentication)
• Driver={ODBC Driver 17 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Uid={your_user_name};Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword
• ODBC (Includes Node.js) (Azure Active Directory integrated authentication)
• Driver={ODBC Driver 17 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Uid={your_user_name};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated

Please also note that for this scenario you also need the following drivers:

• Download ODBC driver 17 for SQL server

So in my case i used the following vba code with the copy paste odbc connection string (SQL authentication) from the azure portal.

con.ConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:yourserver.database.windows.net,1433;Database=yourdb;Uid=yoursqluser;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
con.Open

Answer 3

Just to add, this is the connection string that worked for me (Azure AD User + PW auth):

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryPassword;User ID=myUserName;Password=myPassword;Use Encryption for Data=true;