EXEC OPENDATASOURCE - security from within a job step

Question

EXEC OPENDATASOURCE - security from within a job step

Adam Bean

One Orange Chip

Points: 26631
More actions
March 27, 2017 at 11:53 am

#322035

Hey all, is this working as expected?
I have a job step performing an EXEC OPENDATASOURCE to remote instances that is attempting to use the calling Agent's service account, even though the job owner is running as a different account. The goal here is to have a different account performing these actions and being that a proxy can't run a T-SQL step, I'm at a bit of a loss here.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adam Bean One Orange Chip Points: 26631 More actions · Answer 1

So I attempted to use the runas within advanced of a job and that blows up on me with "Access to the remote server is denied because the current security context is not trusted", even though this account in question has sysadmin on both sides.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

What you can do depends somewhat on what is being done in those job steps and what requirements work in your environment. But as long as your willing to try different things, a couple of options:
If the steps you need running under a different account can be put into stored procedures, you could try creating the stored procedures with Execute AS but there are different caveats with that approach depending on what is doable in your environment. Different issues and requirements are documented in this article:
EXECUTE AS (Transact-SQL)
Another option that may work for your situation is creating another job that does the step or steps and use the proxy with the jobs. When you get to the step you need to run under the other account, try doing sp_start_job to run that job.

Sue

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Thanks for the response Sue. A couple things.
1) To maintain what I'm attempting to do, I'd greatly prefer NOT 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>.<Proc> ')

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

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

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 4

For the time being, If I don't change the runas under advanced or change the job owner and allow the agent to make the call + grant permissions on the destination to the calling agent account, it works fine. Not a desirable state, but it works.

Would love to figure out the proper and clean way to use a separate account for this.

Thanks

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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