Search code examples
rsql-serverodbcunixodbc

R odbc::odbcListDrivers() does not list dirver in /opt/homebrew/etc/odbcinst.ini

I am trying to connect to a Microsoft SQL Server database from R studio.

I installed the unixODBC driver manager using homebrew:

brew install unixodbc

I then tried to install the freetds driver as recommended on https://db.rstudio.com/best-practices/drivers/

# SQL Server ODBC Drivers (Free TDS)
  brew install freetds --with-unixodbc

I received the error:

Error: invalid option: --with-unixodbc

I found a source saying the option is no longer available because it is default now. Thus, I ran:

brew install freetds

In R Studio (and R) I then attempted to list the installed driver, but it was not found:

> odbc::odbcListDrivers()
[1] name      attribute value    
<0 rows> (or 0-length row.names)

Next I tried to install the Microsoft ODBC driver for SQL Server (macOS) according to https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver15 and as recommended on https://db.rstudio.com/databases/microsoft-sql-server/

I ran:

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_NO_ENV_FILTERING=1 ACCEPT_EULA=Y brew install msodbcsql17 mssql-tools

To check that this driver was registered in an odbcinst.ini file I first ran:

odbcinst -j

with result:

unixODBC 2.3.9
DRIVERS............: /opt/homebrew/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/homebrew/etc/odbc.ini
FILE DATA SOURCES..: /opt/homebrew/etc/ODBCDataSources
USER DATA SOURCES..: /Users/Gina/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The driver was already registered in /opt/homebrew/etc/odbcinst.ini

view /opt/homebrew/etc/odbcinst.ini

file contents (note the freetds driver does not appear to be present):

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/homebrew/lib/libmsodbcsql.17.dylib
UsageCount=1

In Rstudio, I tried again to list the drivers and none were found:

> odbc::odbcListDrivers()
[1] name      attribute value    
<0 rows> (or 0-length row.names)

I have tried many more things, including:

  1. copying the contents of /opt/homebrew/etc/odbcinst.ini to the user level file /Users/Gina/.odbcinst.ini

  2. editing the /opt/homebrew/etc/odbc.ini file (which was previously empty) to include connection details (though I was planning to just specify these in the DBI::dbConnect command). New redacted file contents:

     [ODBC Driver 17 for SQL Server]
 Driver   = ODBC Driver 17 for SQL Server
 Servername   = servername.net
 Database = databasename
 UserName     = rstudioapi::askForPassword("Database user")
 Password      = rstudioapi::askForPassword("Database password")
 Port     = 1433

  3. copying the contents of the /opt/homebrew/etc/odbc.ini to the user level file /Gina/.odbc.ini

  4. setting an environmental variable:

     export ODBCSYSINI=/opt/homebrew/etc

Note that when I run:

odbcinst -q -s

The driver is found:

[ODBC Driver 17 for SQL Server]

Thus, it appears that odbc::odbcListDrivers() should be able to find the driver. Any thoughts or suggestions would be very much appreciated! Thank you!

EDIT: I also tried using the New Connection button and interestingly, my driver is listed there! New Connection Window

However, when I click on it and click Test, I get an error:

Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Data source name not found and no default driver specified

Driver Connection Window with Error

I get the same error when I try:

isql -v ODBC Driver 17 for SQL Server myusername mypassword
[IM002][unixODBC][Driver Manager]Data source name not found and no     
default driver specified

EDIT 2: I changed the Data Source Name (top line in odbc.ini file) to "SQLSever" and changed the Servername argument to Server (I did this in both the system level file and the user level file). Now when I run the below command it tries to connect (though hits a new error).

isql -v SQLServer myusername mypassword

[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSL   
Provider: [OpenSSL library could not be loaded, make sure OpenSSL   
1.0 or 1.1 is installed]
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Client    
unable to establish connection
[ISQL]ERROR: Could not SQLConnect

However, unfortunately, I still can not connect in R. I get the same errors as before.

Note that it appears that the isql command uses the system level files and RStudio uses the user level files. The files are identical in both places.

Solution

  • With M1 Mac I was able to connect to SQL Server through terminal by changing the openssl folder to an older version. The driver installs openssl@3 (opt/homebrew/opt/openssl@3) but you actually need to use [email protected] instead.

    Here are the steps i followed in terminal:

    brew install [email protected]
rm /opt/homebrew/opt/openssl
ln -s /opt/homebrew/Cellar/[email protected]/1.1.1l_1 /opt/homebrew/opt/openssl

    This will create a symlink "openssl" and point it to the correct file (1.1.1l_1) inside the opt/homebrew/Cellar/[email protected] folder. Before creating the symlink verify the file name 1.1.1l_1 has not changed with a newer version in opt/homebrew/Cellar/[email protected]/1.1.1l_1