none
use R to open connection to remote SQL Server in script executed by sp_execute_external_script RRS feed

  • Question

  • This simple script runs as expected from RGui in the same R environment that was installed with SQL Server 2017 in-database R services:

    print(Sys.info());
    library(RODBC);
    cn <- odbcDriverConnect("driver={SQL Server};server=local-default-inst;database=tempdb;uid=sa;pwd=sapwd")
    #cn <- odbcDriverConnect("driver={SQL Server};server=remote-default-inst;database=tempdb;uid=sa;pwd=sapwd;")
    ds <- sqlQuery(cn, "SELECT suser_name(), @@servername");
    odbcClose(cn);
    (OutputDataSet <- ds);

    It produces the expected results, indicating that the script was run under my user account and the appropriate user and server names are returned for local and remote SQL connections.

    In SSMS connected to the local default instance of SQL,

    EXECUTE sp_execute_external_script
    @parallel = 1,
    @language = N'R',
    @script = N'

    print(Sys.info());
    library(RODBC);
    cn <- odbcDriverConnect("driver={SQL Server};server=local-default-inst;database=tempdb;uid=sa;pwd=sapwd")
    #cn <- odbcDriverConnect("driver={SQL Server};server=remote-default-inst;database=tempdb;uid=sa;pwd=sapwd;")
    ds <- sqlQuery(cn, "SELECT suser_name(), @@servername");
    odbcClose(cn);
    OutputDataSet <- ds;

    '

    Indicates that the script was run under the local user account MSSQLSERVER01, with the connection to the local server returning suser_name() = 'sa'. Attempts against remote-default-instance fail with the errors

    [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

    [RODBC] ERROR: state 01000, code 1326, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

    I changed the password on MSSQLSERVER01, and as MSSQLSERVER01 executed telnet local-default-inst 1433 with correct results. Attempts on telnet remote-default-inst 1433 fail immediately with

    Connecting To remote-default-inst...Could not open connection to the host, on port 1433: Connect failed

    I deleted the local user MSSQLSERVER01 and created a new local user with the same name, assigning the same group membership and rights. telnet remote-default-inst 1433 connects correctly. Unfortunately, the Launchpad service is hosed at this point, so I can't test from sp_execute_external_script. Restart Launchpad, with the service option Reset External Users Password set to Yes. The same MSSQLSERVER01 I manually created has had its password reset, and the environment is reverted. sp_execute_external_script references to local-default-inst succeed, calls to remote-default-inst fail.

    I've compared everything I can see about MSSQLSERVER01 after I manually created it, and after Launchpad altered it. I can't find an attribute, right or group membership that's changed. Once Launchpad has altered the account, it won't work off the box until the account is deleted and recreated. Then it will work everywhere except from sp_execute_external_script until Launchpad is restarted and it loses the capacity to go off box.

    Any help is appreciated. Sorry for the long read. Thanks for getting this far!

    Friday, August 9, 2019 3:05 PM