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

sg3maiej  于 6个月前  发布在  其他
关注(0)|答案(4)|浏览(33)

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
  1. copying the contents of the /opt/homebrew/etc/odbc.ini to the user level file /Gina/.odbc.ini
  2. 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.

sql-server

4条答案

按热度按时间
um6iljoc

um6iljoc1#

I read in a post on a similar issue that the M1 Macs have this problem but non-M1 Macs do not. Thus, I tried repeating the whole process on an older Macbook Pro and did not encounter the problem! odbc::odbcListDrivers() immediately listed the driver after installing the Microsoft ODBC driver for SQL Server (macOS). I did not need to edit or create any files.

赞(0）分享  回复(0）举报  6个月前
omqzjyyz

omqzjyyz2#

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(https://stackoverflow.com/cdn-cgi/l/email-protection) 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(https://stackoverflow.com/cdn-cgi/l/email-protection) 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(https://stackoverflow.com/cdn-cgi/l/email-protection) /1.1.1l_1

赞(0）分享  回复(0）举报  6个月前
qnzebej0

qnzebej03#

This solution worked for me:

Create two symlinks:

sudo mkdir -p /usr/local/etc
sudo ln -s /opt/homebrew/etc/odbc.ini /usr/local/etc/odbc.ini
sudo ln -s /opt/homebrew/etc/odbcinst.ini /usr/local/etc/odbcinst.ini
赞(0）分享  回复(0）举报  6个月前
j2qf4p5b

j2qf4p5b4#

For MSSQL driver install on Intel Mac (Ventura) and R 4.3.1, I created the symlinks mentioned under Troubleshooting, but odbc::odbcListDrivers() still returned an empty table.

My solution:

  1. Find installed paths.
    Run odbcinst -j to list the paths for installed drivers:
<username@machine> % odbcinst -j
unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/<username>/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
  1. Override R's ODBC Driver Path:
    Following the POSIT Instructions for MacOS aarch64 I edited my .Renviron file ( nano /Users/<username>/.Renviron ) to add the line: ODBCSYSINI=/etc I know that the /etc folder contains a symlink to /usr/local/etc/odbcinst.ini because I created the symlink following the Troubleshooting steps in the original article.
  2. Restart RStudio.
    Now odbc::odbcListDrivers() lists the same drivers as are found in /usr/local/etc/odbcinst.ini .

Just sub in your DRIVER path ( /opt/homebrew/etc/ ) instead of mine, and your symlink path: ODBCSYSINI=/opt/homebrew/etc/ ) and I suspect it should work without having to copy any files.

赞(0）分享  回复(0）举报  6个月前
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com