0

I am not able to access the SQL Server database hosted on Azure Windows VM from the RStudio Connect server. The SQL Server is configured to allow remote connections. I tried the following R code in vain. I also reviewed other posts on this forum. But, none of them addressed this specific situation.

odbc.ini file content:

[dbname]
Driver = /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
Server = azure_wvm_name.corp.mycompany.com
ServerSPN = MSSQLSvc/azure_wvm_name.corp.mycompany.com:1433
Database = sqldb
Port = 1433
UID =  mycompany\rkahn
PWD = myPassword
Trusted_Connection = yes
Encrypt = yes
TrustServerCertificate = yes
Kerberos = Yes

telnet ip_address 1433, as suggested by 'AlwaysLearning' works.

Can someone let me know what am I missing in my code?

library(odbc)
library(DBI)

UserId <- 'mycompany\rkahn'
UserPassword <- 'myPassword'
dbname <- 'sqldb'

sqlsrvcon <- dbConnect(odbc(),
                 Driver = "ODBC Driver 18 for SQL Server",
                 Server = "azure_wvm_name.corp.mycompany.com",
                 Database = dbname,
                 UID = UserId,
                 PWD = UserPassword,
                 Port = 1433)

I get this error message: Error: nanodbc/nanodbc.cpp:1021: 00000: [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired [Microsoft][ODBC Driver 18 for SQL Server]MAX_PROVS: Connection string is not valid [87]. [Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server.

I am able to connect to SQL Server through ODBC DSN

ubuntu@ip-xxx-xx-xx-xx:~$ isql -v dsnName

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

But when I try connecting using R

con <- DBI::dbConnect(odbc::odbc(), "dsnName")

I get this error: Error: nanodbc/nanodbc.cpp:1021: 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

RanonKahn
  • 853
  • 10
  • 34
  • Can you `telnet azure_wvm_name.com 1433` from the AWS Ubuntu server? If telnet can't connect then neither can R/ODBC. – AlwaysLearning Apr 23 '22 at 10:53
  • Yes, I am able to connect to azure_wvm_name.com 1433 using telnet. – RanonKahn Apr 25 '22 at 00:53
  • Instead of specifying the file system path to the library have you tried `Driver="ODBC Driver 18 for SQL Server"` yet? If you continue to have problems please [Edit](https://stackoverflow.com/posts/71978607/edit) your question to include the full and complete error message - as text, not screen shot(s). – AlwaysLearning Apr 25 '22 at 04:32
  • I just updated my question as you advised. – RanonKahn Apr 25 '22 at 08:16
  • @AlwaysLearning, if possible, can you please help? – RanonKahn Apr 26 '22 at 19:04
  • Have you read through [ODBC Driver 18.0 for SQL Server Released](https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228) yet? Looks like you're connecting to a non-Azure install of SQL Server. By default SQL Server installs with a self-signed certificate, i.e.: one that's not trusted by a certificate authority in the Trusted Root Certificates Program. As of ODBC Driver 18 for SQL Server the `Encrypt=False/No` option default has changed to `Encrypt=True/Yes`, so you need to add `TrustServerCertificate=True/Yes` to your connection string. – AlwaysLearning Apr 26 '22 at 22:11
  • I very much appreciate your advice. I have included the odbc.ini which already reflects your advice. – RanonKahn Apr 27 '22 at 01:00

1 Answers1

1

At last this worked for me:

library(odbc)
library(DBI)

UserId <- 'mycompany\rkahn'
UserPassword <- 'myPassword'
dbname <- 'sqldb'

sqlsrvcon <- dbConnect(odbc(),
                 Driver = "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1",
                 Server = "azure_wvm_name.corp.mycompany.com",
                 Database = dbname,
                 UID = UserId,
                 PWD = UserPassword,
                 Encrypt="yes",
                 TrustServerCertificate="yes",
                 Port = 1433)
RanonKahn
  • 853
  • 10
  • 34