1

I have always used the following code to successfully connect to sql server in R

dbhandle=odbcDriverConnect("Driver={SQL Server};Server=servername;Database=databasename;Trusted_Connection=Yes")

However, after sql server was upgraded to be compatible with TLS 1.2 it has the following error

Warning messages:
1: In odbcDriverConnect("Driver={SQL Server};Server=MHPOPMMSS100;Database=REZNKWB01;Trusted_Connection=Yes") :
  [RODBC] ERROR: state 08001, code 18, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SSL Security error
2: In odbcDriverConnect("Driver={SQL Server};Server=MHPOPMMSS100;Database=REZNKWB01;Trusted_Connection=Yes") :
  [RODBC] ERROR: state 01000, code 1, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (SECCreateCredentials()).
3: In odbcDriverConnect("Driver={SQL Server};Server=MHPOPMMSS100;Database=REZNKWB01;Trusted_Connection=Yes") :
  ODBC connection failed

I have never needed a uid, but I have tried putting in the same userid I use to connect to sql server directly, I have tried trustedconnection=yes and no, I have tried adding the port after the server, basically anything I have read online I have tried.

I am wondering if there is something I am missing or if anyone has input. Thanks!

Adam Sanders
  • 39
  • 5
  • The `SQL Server` ODBC driver that ships with Windows is a legacy one provided for backwards compatibility. Try installing the latest version: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server and changing you connection string accordingly. – Dan Guzman May 29 '18 at 14:33

1 Answers1

2
library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=Server_Name; Database=DB_Name;Uid=; Pwd=; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
odbcClose(channel)

Also, see the links below.

http://stackoverflow.com/questions/15420999/rodbc-odbcdriverconnect-connection-error

https://andersspur.wordpress.com/2013/11/26/connect-r-to-sql-server-2012-and-14/

Finally, make sure your setup is 32bit or 64bit compatible. In SQL Server, do this: Select @@Version

That will tell you if you are using 32bit or 64bit. Make sure your R Studio is compatible with whatever version of SQL Server you are running.

Note that by holding down the Control key during the launch of RStudio you can cause the R version selection dialog to display at startup.

enter image description here

ASH
  • 20,759
  • 19
  • 87
  • 200