Connection to SQL Server from Julia on Ubuntu Linux using ODBC.jl won’t connect

I have tried every permutation I can find on the web but still no luck. I know the connection is possible because it works easily from R, dbeaver, and command line sqlcmd, but I can not get it to work from Julia using ODBC.jl

$ sqlcmd -S 66.66.66.66,1433 -U my.name -P mypasswd -d database_name -Q "SELECT TOP 5 stuff_id FROM table_name;"

stuff_id
-----------
A81064     
A82027     
A82046     
A82055     
A83011     

(5 rows affected)

However in Julia 1.7.0 I just get this error:

julia> using ODBC

julia> using DBInterface

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

julia> 

con = ODBC.Connection("Driver={ODBC Driver 17 for SQL Server};Server=66.66.66.66:1433,Database=database_name,UID=my.name,PWD=mypasswd")


ERROR: HYT00: [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired08001: [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Connection string is not valid [87]. 08001: [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Stacktrace:
 [1] error(s::String)
   @ Base ./error.jl:33
 [2] driverconnect(connstr::String)
   @ ODBC.API ~/.julia/packages/ODBC/qhwMX/src/API.jl:114
 [3] connect
   @ ~/.julia/packages/ODBC/qhwMX/src/API.jl:351 [inlined]
 [4] ODBC.Connection(dsn::String; user::Nothing, password::Nothing, extraauth::Nothing)
   @ ODBC ~/.julia/packages/ODBC/qhwMX/src/dbinterface.jl:57
 [5] ODBC.Connection(dsn::String)
   @ ODBC ~/.julia/packages/ODBC/qhwMX/src/dbinterface.jl:55
 [6] top-level scope
   @ REPL[5]:1

I think it might have something to do with the odbcinst.ini setup but don’t know how to debug/fix it. Any suggestions most welcome. Thx. J

Answer

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.2UsageCount=1"
  "ODBC Drivers"                      => ""
  "ODBC Driver 17 for SQL Server"     => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1UsageCount=6"
  "unixODBC/usr/lib/x86_64-linux-gnu" => "Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.8.so.1.1UsageCount=1"

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>