excel - 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