Cannot connect to SQL database

[ShuAiii]

While inside the a container with rocker/r-ubuntu base image,

I'm unable to use dbConnect and odbc to connect to my sql database.

Traceback:

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

Some extra information: 1) I authenticated my log-in information with Kerberos, and I'm able to connect to the database through pyodbc (python odbc) while inside the same rocker/r-ubuntu container. 2) I'm also able to connect via sql id/password with pyodbc, but not dbConnect.

[eddelbuettel]

R and Python likely used different way to configure the ODBC driver so the fact that one works and the other doesn't ... mostly says that you configure one and not the other.

We covered this here in issues before. Rocker is more or less a 'base layer' on top of which you can add packages and configurations to fit your needs. We cannot possibly cover all possible database connection.

Also, if you can, a 'direct' connections (say: RMariaDB to a MariaDB instance, or RPostgreSQL to Postgres, ...) will always outperform ODBC which needs extra drivers on both sides and config.

[ShuAiii]

@eddelbuettel I apologize for the prolong reply, for some reason I did not get a notification.

1) Can you refer me to the issue that was covered in the past?

2) I installed additional dependencies on top of the base-image, such as unixodbc-dev, unixodbc, and libpq-dev. When you say most likely because I configured one and not the other. In R console, I see this:

> odbc::odbcListDrivers()
                           name   attribute
1 ODBC Driver 17 for SQL Server Description
2 ODBC Driver 17 for SQL Server      Driver
3 ODBC Driver 17 for SQL Server  UsageCount
                                                       value
1                    Microsoft ODBC Driver 17 for SQL Server
2 /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
3                                                          1

And in my /etc/odbcinst.ini I see this:

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

and I'm attemping to connect to the DB like this:

database <- dbConnect(
      odbc::odbc(),
      dsn = "my-dsn",
      Database = "my-db",
      Driver = "ODBC Driver 17 for SQL Server",
      uid = "my-uid",
      pwd = "my-pw"
  )

I'm a novice at using R, any obvious configuration error you see?

[eddelbuettel]

I would recommend establishing a working use case outside the container first. Identify what the container needs, add it, and configure inside a (derived) container.

The last time I worked with SQL Server from R (over a decade ago) it did not have the linux drivers and one had to cheat vis the FreeTDS driver which had its own set of issues. I do not have a SQL Server sitting anywhere so I cannot help you with anything concrete.

[ShuAiii]

okay, thanks anyways. Closing this issue as the driver is out of the scope of r-ocker.