Connect RStudio on Linux to SQL Server

General
,
1

Hello, I am trying to connect RStudio to SQL Server. I have installed the Microsoft ODBC 18 driver for SQL Server (Linux) and the "mssql-tools18", and am able to see "ODBC Driver 18 for SQL Server " on the odbcListDriver(). I also installed "unixODBC-devel" on Linux and the odbc & DBI package in RStudio. I am able to run system("isql") and got results for UnixODBC -isql. Below is the result of odbcinst -j


unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/username/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Below is the command I am trying in RStudio and the error I am getting. Please advise.


con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "ODBC Driver 18 for SQL Server",
                      Server   = "SQLServerName",
                      Database = "DatabaseName",
                      UID      = rstudioapi::askForPassword("Database user"),
                      PWD      = rstudioapi::askForPassword("Database password"),
                      TrustedServerCertificate = "yes")

Error: nanodbc/nanodbc.cpp:1118: 00000: [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute

2

I found the issue and was able to resolve it. The odbc connection in RStudio is working now.

3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.