SQL R Library RODBC Script to Connect to SQL Server
One of the methods for R developers on SQL Server to connect to SQL Server databases in order to execute SQL commands and query data is using RODBC R library. RODBC package simply provides an ODBC database connectivity from R scripts to data developers.
Please refer to RODBC package for official documentation and more details about the R library and provided R functions.
In this guide, I want to share an interesting case I experienced during my developments on my new machine configuration.
I have R Studio installed on my development computer and then I install RODBC package on R Studio.
Before you install RODBC package, you may check it among all installed R packages, too.
Then I executed following script step by step on R Studio to connect to a SQL Server instance and query a database table by executing a SQL Select statement as follows.
Please note that I'm using a named instance for target SQL Server
library(RODBC)
sqlConn <- odbcDriverConnect('driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
fix(ds)
As R Studio developers can see I could connect to SQL Server instance using SQL login name and password using RODBC R package odbcDriverConnect function.
Then by executing sqlQuery function I can pass a SQL statement to run on connected SQL Server database
When I tried to execute the same R script on SQL Server 2019 instance where R is installed as a component of the current SQL Server 2019 instance, I got errors.
If you have not installed R (In-Database Machine Learning Services) on your SQL Server please check SQL Server 2019 installation steps.
To enable database programmers to build and run R Scripts on SQL Server, database administrators have to enable execution of external scripts on that instance.
Please follow instructions given at Enable sp_execute_external_script to Run Python or R Script on SQL Server to complete this step.
After all these steps are completed, you can also execute R scripts on your SQL Server database.
Here is the SQL script I created to execute external R script to connect to an other SQL Server instance. (I exeute R script on SQL Server 2019 database instance to connect to a database which is created on SQL Server 2016 instance). This script is created for test purposes. So instead of creating a linked server, I use RODBC library for remote connection.
EXEC sp_execute_external_script
@language = N'R',
@script = N'
Sys.setenv(LANG = "en");
library(RODBC)
sqlConn <- odbcDriverConnect(''driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test'')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
OutputDataSet <- ds
';
Please note that in above ODBC connection settings for named SQL Server instance I used double "\". The first one is used as escape character. So instead of "kodyaz\sql2016" I used "kodyaz\\sql2016"
Maybe it is worth to mention. uid is for SQL login name and pwd is for the password of the SQL login. Here I created "test" user with password as "test" for test purposes :)
Error message displayed on SQL Server Management Studio is:
Msg 39004, Level 16, State 20, Line 374
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 2, Line 374
An external script error occurred:
Error in sqlQuery(sqlConn, "SELECT * FROM dbo.Country;") :
first argument is not an open RODBC channel
Calls: source -> withVisible -> eval -> eval -> sqlQuery
In addition: Warning messages:
1: In odbcDriverConnect("driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test") :
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBMSLPCN]SQL Server yok veya eriþim engellendi.
2: In odbcDriverConnect("driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test") :
[RODBC] ERROR: state 01000, code 10061, message [Microsoft][ODBC SQL Server Driver][DBMSLPCN]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver={SQL Server};server=kodyaz\\sql2016;database=testdata;uid=test;pwd=test") :
ODBC connection failed
Error in execution. Check the output for more information.
Error in eval(ei, envir) :
Msg 39019, Level 16, State 2, Line 374
An external script error occurred:
Error in execution. Check the output for more information.
Calls: runScriptFile -> source -> withVisible -> eval -> eval -> .Call
Execution halted
I could connect to the target SQL Server named instance using SQL Server Management Studio SSMS or using R-Studio even with the same R script.
Then I decided to check the port assigned for the SQL Server instance.
So I launched SQL Server Configuration Manager
While checking configuration data on SQL Server Configuration Manager tool, I realized I did not enable TCP/IP protocol for the target server.
I was trying to connect SQL2016 SQL Server instance from SQL2019CTP21 database instance. So I checked protocols for SQL2016 named instance.
As seen in above screenshot from Configuration Manager tool, TCP/IP protocol for SQL2016 SQL Server instance is disabled (by default). So I enable this protocol (TCP/IP) by right click on the protocol name and choose Enable option.
To make the changes take affect the related SQL Server service should be restarted.
Again using the same tool, SQL Server Configuration Manager you can restart the service. Please note that user connected to the database instance will not be able to reach the server at that time. That will be a downtime that will probably take less than a minute.
As seen in following screenshot, drill-down SQL Server Services node find the target database instance. Open the context menu by right clicking on the SQL Server name and choose Restart
Now R developers or data science developers using SQL Server R Services can execute the R script once more.
EXEC sp_execute_external_script
@language = N'R',
@script = N'
Sys.setenv(LANG = "en");
library(RODBC)
sqlConn <- odbcDriverConnect(''driver={SQL Server};
server=kodyaz\\SQL2016;
database=testdata;
uid=test;pwd =test'')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
OutputDataSet <- ds
';
GO
When the TCP/IP protocol is enabled or activated for the server, it is also possible to use "servername\instancename,port" or "servername,port" form for target server identifier.
It is possible to determine the port number using TCP/IP protocol Properties screen IP Addresses tab.
In IPAll section, if you see a port number other than 0 for TCP Dynamic Ports, you can use it in above forms, too.
If the SQL login does not exist or failed with authentication, the error message is different and indicating the login problem as "Login failed for user"
For example,
[RODBC] ERROR: state 28000, code 18456, message [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test'.
If you want to use trusted_connection and Windows user to authenticate on the target SQL Server instance, then first you have to add current SQL Server instance R user group (where you execute the R script) as a Windows login on target SQL Server instance.
So I added "KODYAZ\SQLRUserGroupSQL2019CTP21" as login user for the server and for the database on target SQL Server instance "KODYAZ\SQL2016"
You can simply define the SQL Server login properties as follows for the target database (testdata db). Please note that I marked "db owner" to query database objects without dealing with additional security settings at object level.
Mapping the SQL Server login to testdata database will create a database user on testdata automatically.
The second step will be modifying the R-Script RODBC connection string.
It is enough to replace "uid=test;pwd=test" with "trusted_connection=yes" as seen below.
EXEC sp_execute_external_script
@language = N'R',
@script = N'
Sys.setenv(LANG = "en");
library(RODBC)
sqlConn <- odbcDriverConnect(''driver={SQL Server};
server=kodyaz\\SQL2016;
database=testdata;trusted_connection =yes'')
ds <- sqlQuery(sqlConn, "SELECT * FROM dbo.Country;")
odbcClose(sqlConn)
OutputDataSet <- ds
';