Connection string for SQL Server in Julia ODBC

Question

In Julia, I use this code to connect to a Sql Server database, with no credentials All good!

ODBC.adddsn("SQL_Server_DSN", "SQL Server"; SERVER="x", DATABASE ="x", Trusted_Connection="True")
conn = DBInterface.connect(ODBC.Connection, "SQL_Server_DSN")
cursor = DBInterface.execute(conn, "SELECT * FROM dbo.users")|> DataFrame

But when I try to use credentials, somehow I am not able to get how to adapt the connection string. Any sugestions?

Answer 1

I finally got a working solution to this problem but it was quite difficult, primarily due to a lack of documentation for most of this. I'm not convinced that the ODBC drivers are set up correctly but it works! Thx for all suggestions.

julia> using ODBC

julia> using DBInterface

julia> using DataFrames

julia> ODBC.drivers()
Dict{String, String} with 4 entries:
  "unixODBC"                          => "Driver=/usr/lib/x86_64-linux-gnu/libodbc.so.2\0UsageCount=1\0"
  "ODBC Drivers"                      => ""
  "ODBC Driver 17 for SQL Server"     => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1\0UsageCount=6\0"
  "unixODBC/usr/lib/x86_64-linux-gnu" => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1\0UsageCount=1\0"

julia> conn2 = ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=ip#;DATABASE=DBName;UID=UserName;PWD=Passwd")
ODBC.Connection(Driver=ODBC Driver 17 for SQL Server;SERVER=ip#;DATABASE=DBName;UID=UserName;PWD=Passwd)

julia> results=DBInterface.execute(conn2, "SELECT TOP 15 variable FROM table")|> DataFrame
15×1 DataFrame
 Row ? variable 
     ? String      
???????????????????
   1 ? A81064
   2 ? A82027
   3 ? A82046
   4 ? A82055
   5 ? A83011
  ?  ?      ?
  12 ? A84027
  13 ? A84030
  14 ? A84032
  15 ? A84033
     6 rows omitted

julia>