Home Forums SQL Server 2016 SQL Server 2016 - Administration EXEC OPENDATASOURCE - security from within a job step RE: EXEC OPENDATASOURCE - security from within a job step

  • March 27, 2017 at 2:14 pm

    #1935163

    Adam Bean - Monday, March 27, 2017 1:51 PM

    Thanks for the response Sue. A couple things.
    1) To maintain what I'm attempting to do, I'd greatly prefer to push out custom procs on these environments.
    2) You can't have a proxy on a T-SQL job step, but you're saying to have a job call another job using a proxy? 
    2a) I guess I could just do a CmdExec and call sqlcmd ... dirty, but might work.

    As for the execute as, I did try to do an execute as within an execute opendatasource, and it's the craziest thing.

    First, this works, just fine:
    EXEC
    ('
    EXEC OPENDATASOURCE(''SQLNCLI'',
        ''Data Source=Server\Instance;Integrated Security=SSPI'')
        .<DBName>.<Schema>.<Prod>
    ')

    Yet when I add AS LOGIN:
    EXEC
    ('
    EXEC OPENDATASOURCE(''SQLNCLI'',
        ''Data Source=Server\Instance;Integrated Security=SSPI'')
        .<DBName>.<Schema>.<Prod>
    ')
    AS LOGIN = 'ETC\SVC_SQL_ADMIN'

    I get:SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

    There's got to be a way to do this!

    I'm guessing it's a t-sql step so change the owner of the second job that is called by the first job. Have a non-sysadmin own the job then it runs under that owners credentials.
    On that second error, the execute as needs to be in the stored procedure. And this depends on what its doing but create the stored procedure on the remote server and then call it in the opendatasource .

    Sue