Step By Step Guide to resolve SQL server connection issues when connecting from R Studio

Yesterday I tried using a couple of  RevolveScaleR functions -- RxSqlServerData and RxImport.in Rstudio.  These are my learnings as part of this exercise.

The following are the steps that need to be taken for successful connection to the SQLExpress database

1. Ensure that the SQL Server database has permission for the user that you are using as shown below.



2. Ensure that the NamedPipes and TCP/IP protocols are Enabled using the SQL Server Configuration Manager. 






3. Otherwise you will receive an error as -- 
• [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.
4. When you are enabling the TCP/IP protocol ensure that you specify the port 1433 at the appropriate location.  Otherwise you will receive an error as -- 
• [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied. 
• [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).

5. Restart the SQL SERVER service after configuring the above step.
6. Ensure that the firewall is open to the port 1433.
7. Ensure that you can run a Telnet session to your sql server IP address.
8. There is a default user group created named SQLRUserGroupSQLEXPRESS when you install SQLEXPRESS with R   Ensure that the user connecting to the SQL server from R Studio belongs to this group.  Otherwise you will receive an error as --  Error in doTryCatch(return(expr), name, parentenv, handler) :   Could not open data source.

Now coming to the code I have used in R Studio to import the data from the SQL Server Database into R is as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

#Connection string to connect to SQL Express connStr <- font=""> paste("Driver=SQL Server; Server=", "SQLEXPRESS", ";Database=", "TestDB", ";Trusted_Connection=true;", sep = ""); #Get the data from the Table SQL_testdata <- font=""> RxSqlServerData(table = "dbo.rental_data", connectionString = connStr, returnDataFrame = TRUE); #Import the data into a data frame testdata <- font=""> rxImport(SQL_testdata); #See the structure of the data head(testdata); #See the top rows str(testdata);

Hope this is useful for those who would like to connect to sql server using RStudio.