Using trusted connection in RxSqlServerData connection string

Question

I have the following connection string and query that I would like to run using RxSqlServerData:

connString <- paste("Driver=SQL Server", paste("Server=", config$dwServer, sep = ""), paste("Database=", config$dwName, sep = ""), "trusted_connection=true", sep = ";")

rxSetComputeContext("local")


query <- "SELECT * FROM Table1"

RxSqlServerData(sqlQuery=query,connectionString=connString)

My syntax for specifying a trusted connection/window authentication seems to be wrong. Can anyone tell me how to use trusted connection correctly with the RevoScaleR package?

Answer 1

Here's how I got mine to work. Note that you have to set up an ODBC DSN in the ODBC Data Source Administrator, and then you need to make sure the driver name in your connection string matches what your DSN is using exactly. In my case, it's "SQL Server Native Client 11.0". The curly brackets seem to allow for spaces in the name of the driver. Also, I had to set the trusted_connection parameter to "yes" rather than "true".

instance_name <- "yourDsnName";
database_name <- "youDataBaseName";
connStr <- paste("Driver={SQL Server Native Client 11.0};Server=",instance_name, ";Database=",database_name,";Trusted_Connection=yes;",sep="");

# Set other variables used to define the compute context
sqlWait = TRUE;
sqlConsoleOutput = FALSE;
sqlShareDir <- paste("C:\\RShared\\",Sys.getenv("USERNAME"),sep="")

# Create and set the compute context to Dev SQL Server
sqlCC <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir,   
                   wait = sqlWait, consoleOutput = sqlConsoleOutput,
                   traceEnabled=TRUE,traceLevel=7)
rxSetComputeContext(sqlCC)

rxGetVarInfo(RxSqlServerData(sqlQuery = "select 1 as test",connectionString = connStr, colClasses = c(test = "integer"), rowsPerRead=500))