Connection string for SQL Server database RRS feed

  • Question

  • I have SQL Server 2016 SQLEXPRESS with Windows authentication.

    Using RODBC I can access the database fine by specifying a connection string like this:

    > connStr<- "Driver={SQL Server};Server=SHARRISON01\\SQLEXPRESS;Database=X_IM"
    > locDS <- RxOdbcData(sqlQuery=sampleDataQuery, connectionString=connStr)

    But using RxInSqlServer I get the following

    cc <- RxInSqlServer(connectionString = connStr, shareDir = sqlShareDir, + wait = sqlWait, consoleOutput = sqlConsoleOutput) Error in .rxEnsureOdbcCredentials(connectionString, server, databaseName, : Trusted_Connection or User must be specified either in the connection string with the uid keyword or as a named parameter.

    If I do specify the uid and password in the connection string (which I must avoid on security grounds) then it fails to connect anyway:

    rxGetVarInfo(data = inDataSource) [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'XELOCITY\Simon.Harrison'. Could not open data source.

    What is the trick to getting access using the In-Database Compute Context?

    Monday, June 20, 2016 12:43 AM


  • I have found the answer using Trusted_Connection={Yes} in the connection string.

    connStr <- "Driver=SQL Server;Server=SHARRISON01\\SQLEXPRESS;Database=X_IM;Trusted_Connection={Yes}"

    Monday, June 20, 2016 4:25 AM