Julia, ODBC connection to a remote MS SQL Server

I have been using R to connect to a remote MS SQL Server database, from Ubuntu Linux 21.10 and it works quite well. I’d like to do the same thing from Julia but can not make the connection using the ODBC package. Does anyone have a script that does make a “con”, and would share it with me? I have the “R” equivalent of what I’m trying to achieve. Thx. J.

con <- dbConnect(odbc::odbc(),
                 Driver="ODBC Driver 17 for SQL Server",
                 Server="ip_number",
                 UID="User.Name",
                 PWD=decrypt_gk_pw("key1 word1 key2 word2"),
                 Database="DBName")

Ok, this is what I’m trying, and I know generally that the variables are correct.

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

julia> ODBC.Connection(Driver={ODBC Driver 17 for SQL Server};Server="server_ip#";Database="DBaseName";UID="User.Name";PWD="passwor")
ERROR: syntax: invalid comprehension syntax
Stacktrace:
 [1] top-level scope
   @ none:1

So it seems the driver is there, what is “comprehension syntax” ? Should I not be using double quotes ? What about commas vs semicolons? Any working example would be greatly appreciated. Thx.

The compiler thinks you are trying to specify a comprehension here. Single- and multi-dimensional Arrays · The Julia Language. Perhaps you want a string, delimited with quotes instead of braces?

Thanks Jeff, getting closer! It still won’t connect, although these same settings work from R!

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

julia> con = ODBC.Connection("Driver=ODBC Driver 17 for SQL Server;SERVER=ip#,DATABASE=dbname,UID=username,PWD=passwrod")
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[27]:1

From the connection string example at Connect to an ODBC Data Source (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Docs, it looks like you need the braces around the driver name within the connection string.

Thanks again, that does get closer but still getting wrong string errors:

`julia> con = ODBC.Connection(“Driver={ODBC Driver 17 for SQL Server};Server=ip#,Database=dbname,UID=username,PWD=pwd”)
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[23]:1

How might I debug this ?  Thx. J

Look at the “SQL Server Import and Export” window in the link I sent earlier. It displays a connection string. Maybe you can connect through that tool (or some other MS-Sql tool) and copy and paste the connection string from there. Probably a red herring, but server in that connection string is lowercase, and yours is uppercase, but somewhere else on that page it is all uppercase.

Basically this Connect to an ODBC Data Source (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Learn

I finally got a working solution by cobbling pieces together. There is not much documentation on any of this. If anyone knows a more elegant solution I’d be happy to try it.

julia> using ODBC

julia> using DBInterface

julia> using DataFrames
ODBC.drivers()

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> 
8 Likes

What about Windows Authentication? is it able to handle it?

Just tried it, and the connection worked but now having troubles with Nulls values.

Error: Argument error: embedded NULs are not allow in C strings…

For anyone else who lands here, it seems like Windows Auth works if your database driver supports it.

For me, I had the following already installed on my Windows Machine:

julia> ODBC.drivers()
Dict{String, String} with 4 entries:
  "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)" => "UsageCount=3\0APILevel=1\0ConnectFunctions=YYN\0DriverODBCVer=02.50\…
  "Microsoft Access Text Driver (*.txt, *.csv)"            => "UsageCount=3\0APILevel=1\0ConnectFunctions=YYN\0DriverODBCVer=02.50\…
  "Microsoft Access Driver (*.mdb, *.accdb)"               => "UsageCount=3\0APILevel=1\0ConnectFunctions=YYN\0DriverODBCVer=02.50\…
  "SQL Server"                                             => "APILevel=2\0ConnectFunctions=YYY\0CPTimeout=60\0DriverODBCVer=03.50\…

And a complete example that worked for me of getting data into a dataframe (replace with your server, database, table…):

using ODBC
using DBInterface
using DataFramesMeta

ODBC.drivers()

conn = ODBC.Connection("Driver=SQL Server;SERVER=MY.SERVER.COM")

q = """
SELECT .... FROM [database_name].[dbo].[table_name]
"""

DBInterface.execute(conn,q) |> DataFrame

1 Like