R: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

Question

setwd("/mnt/mountpoint/abc/")
sqlServerConnString <- "SERVER=server;DATABASE=sqldwdb;UID=xyz;PWD=abc;"
sqlServerDataDS <- RxSqlServerData(sqlQuery = "SELECT * FROM xyz",
                               connectionString = sqlServerConnString)
sqlServerDataDF <- rxImport(sqlServerDataDS)

This is my code. I am getting the followin error in R

[unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

[unixODBC][Driver Manager]Connnection does not exist ODBC Error in SQLDisconnect Could not open data source.Error in doTryCatch(return(expr), name, parentenv, handler) : Could not open data source.

I have installed MSSQL and unixODBC driver on my linux machine and it is getting refelected in /etc/odbc.ini file too

Can someone please help me in this?

You seem to be missing `DRIVER=SQL Server;` from the beginning of your connection string. — nrussell, Apr 25 '17 at 10:35
This error is passed through Microsoft R Server directly from the ODBC driver, have you successful made an ODBC connection from outside of Microsoft R Server to your SQL Server? Could you please include the version information for Microsoft R Server? — Kirill Glushko - Microsoft, Apr 26 '17 at 00:39
@KirillGlushko-Microsoft: Thanks for your help. I checked my SQL connection through Linux Terminal. It was giving error before because the location of the file was wrong in `/etc/odbcinst.ini` file. I corrected that and it worked for me. Thanks a lot. — Sonal, Apr 26 '17 at 10:51
@Sonal: How and what did you corrected about the file location in `/etc/odbcinst.ini`? — Slimshadddyyy, Apr 04 '18 at 06:50
I get the same problem in mac, while trying to run on Rstudio with odbc library. Any clues how to solve this? — tavalendo, Jun 15 '18 at 08:09
The library(odbc) [readme](https://github.com/r-dbi/odbc/blob/master/README.md#connecting-to-a-database) provides a detailed guide concerning the necessary steps to connect to MSSQL. — ismirsehregal, Nov 28 '18 at 09:00

score 36 · Answer 1 · answered Jul 10 '18 at 13:22

36

I got the same error when i put below code for connection to MSSQLSERVER

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=SQL Server;Server=192.168.76.60; Database=kaggle;Uid=sa; Pwd=1234")

It throws to me

[unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

why this Error thrown? Answer: when we fail to put proper ODBC version name on Driver value.

From where we can get Driver ODBC version name

inside "/etc" folder you will find "odbcinst.ini" file open it and check the version name

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

so i got ODBC Driver name from here , it will be "ODBC Driver 17 for SQL Server" Then i modify my connection string

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 17 for SQL Server;Server=192.168.76.60; Database=kaggle;Uid=sa; Pwd=1234")

And it works fine

answered Jul 10 '18 at 13:22

Zaynul Abadin Tuhin

31,407
5
33
63

9

Where does the information inside /etc/odbcinst.ini come from? On my machine that file is blank. Did you do anything to configure your odbc driver prior to looking inside that file? – Patrick Conwell Nov 14 '18 at 23:01
@PatrickConwell after installing odbd driver you will get odbcinst.ini inside etc – Zaynul Abadin Tuhin Nov 15 '18 at 02:25
6

I used instructions from here (https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15) to install driver – Thomas Luechtefeld Nov 12 '19 at 03:57
Nothing on server, no `locate odbcinst.ini` – Peter Krauss Jun 25 '20 at 18:59
1

@ThomasLuechtefeld +1, you should submit your comment as an answer, because it was the way to make ODBC work for me, instead of this answer above – Marcelo Ventura Jan 08 '21 at 13:57

Wagner Bertolini Junior · Answer 2 · 2019-01-21T13:50:42.677

In my case Zaynul answer didn't worked, unfortunately. But it helped me to find another way. I am using sqlalchemy and I could sucessfully create a connection passing the driver path:

engine = create_engine('mssql+pyodbc://sa:******@localhost:1433/dbCVServer?driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1', echo=True)

engine.execute('select 1')

EDIT: I was not satisfied with that solution, since the file name of driver could change, so I managed to change the library used to connect do pymssql.

engine = create_engine('mssql+pymssql://sa:******@localhost:1433/dbCVServer')

and it worked well too. =]

EDIT 2: Another discovery, connecting to a named instance, for those using sql express, for instance.

engine = create_engine('mssql+pymssql://mssql+pymssql://localhost\SQLEXPRESS/dbCVServer')

Instead of putting installation path to libmsodbcsql library you can reference it in the sqlalchemy URL like `engine = create_engine('mssql+pyodbc://sa:******@localhost:1433/dbCVServer?driver=ODBC+Driver+17+for+SQL+Server` — Tom Close, Dec 03 '19 at 00:16

score -2 · Answer 3 · answered May 17 '22 at 10:19

-2

This works for me:

con <- DBI::dbConnect(
  odbc::odbc(),
  Driver   = "SQL Server",
  Server   = "server",
  Database = "database",
  UID      = "uid",
  PWD      = "pwd"
)

answered May 17 '22 at 10:19

Dmitry Ishutin

396
2
13

R: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

3 Answers3

Linked