Excel VBA Connection String for Azure

[jay1983]

Hello – I am struggling to replicate a db connection that was previously sybase using DSN and now azure

my old connection string was:
Sub PullData_Click()
Dim SSN As String
SSN = Range("G7")
Application.ScreenUpdating = False
'Clear Contents
Range("S2:x5000").Select
Selection.ClearContents

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=ddd_new;DB=DDD1;UID=****;PWD=*****", Destination:=Range("S2"))
CommandText = "SELECT t_......

so i had a DSN set up that it was referencing --- i'm trying to keep the format of the query (written years and years ago so prob not efficient --"
here's my new connection string:

With ActiveSheet.QueryTables.Add(Connection: = "Driver={ODBC Driver 17 for SQL Server};Server=tcp:<server>,1433;database=dddprod;Uid=****;Pwd=****;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryPassword;ApplicationIntent=ReadOnly;", Destination:=Range("S2"))
.CommandText = "SELECT t_.......

i get a run time error 5 with invalid procedure call. I tried taking out the ODBC, tried using just 'connection' --- maybe it can't be replicated -- i dont want to go back to the DSN route if i dont have to, cant get it to connect and run the select query.

thanks