March 20, 2018 at 8:03 am
Hi,
I've intertied a failing process and have been asked to fix it. The process is a SQL job which has a single step to create and execute a xp_cmdshell statement. The xp_cmdshell statement calls an in-house .NET app (which I also have access to the source code). The app is designed to get a list of server names and execute a prepared TSQL statement on each server. My issue is that one of the connections is failing as the login used to connect has been disabled.
My understanding is that when the SQL Job runs it does so under the explicit 'Run as:' user specified on the job step. In my case this is empty so I think it goes back up a level to get the jobs 'Owner' and run under that principal. The job owner is set to an AD user which has historically been configured as a sysadmin on each of my SQL instances. Since the xp_cmdshell calls the .NET app I think that it does so under the guise of this AD user. The .NET app establishes connections with each of the servers in the list of server names using 'Integrated Security=True', thus connecting as the AD user. In an attempt to reduce the risk that the legacy policy of one AD user as a login on each instance places me in I have recently began a programme to ensure that each instance has it's own unique sysadmin login and so have disabled this particular AD user login on one of my servers in the list of server names. This is where my issue is.
Is my understanding of how the credentials are passed from SQL job to .NET app and ultimately back to a SQL connection correct?
Since I would like each SQL instance to have it's own unique logins, how do I amend the process so that the .NET app can connect to each server in the list?
As far as a I can tell I'll need to either need to accept that I need a common logon on each instance (maybe not a sysadmin though!) and set this as the SQL job steps 'Run as:' or I have to pass a username and password for each connection. Is this correct or are there any other options available to me which I'm missing?
Thanks,
Mike
March 22, 2018 at 9:41 am
MikeRen - Tuesday, March 20, 2018 8:03 AMHi,I've intertied a failing process and have been asked to fix it. The process is a SQL job which has a single step to create and execute a xp_cmdshell statement. The xp_cmdshell statement calls an in-house .NET app (which I also have access to the source code). The app is designed to get a list of server names and execute a prepared TSQL statement on each server. My issue is that one of the connections is failing as the login used to connect has been disabled.
My understanding is that when the SQL Job runs it does so under the explicit 'Run as:' user specified on the job step. In my case this is empty so I think it goes back up a level to get the jobs 'Owner' and run under that principal. The job owner is set to an AD user which has historically been configured as a sysadmin on each of my SQL instances. Since the xp_cmdshell calls the .NET app I think that it does so under the guise of this AD user. The .NET app establishes connections with each of the servers in the list of server names using 'Integrated Security=True', thus connecting as the AD user. In an attempt to reduce the risk that the legacy policy of one AD user as a login on each instance places me in I have recently began a programme to ensure that each instance has it's own unique sysadmin login and so have disabled this particular AD user login on one of my servers in the list of server names. This is where my issue is.
Is my understanding of how the credentials are passed from SQL job to .NET app and ultimately back to a SQL connection correct?
Since I would like each SQL instance to have it's own unique logins, how do I amend the process so that the .NET app can connect to each server in the list?
As far as a I can tell I'll need to either need to accept that I need a common logon on each instance (maybe not a sysadmin though!) and set this as the SQL job steps 'Run as:' or I have to pass a username and password for each connection. Is this correct or are there any other options available to me which I'm missing?Thanks,
Mike
I seem to remember that xp_cmdshell also uses specific logins, essentially either one system wide id (service account when called by sysadmins), or another one set up as a "proxy account" for lower privileged accounts, either of which could be different than the user id specified in the job spec.
This page seems to have a run down on this:
https://stackoverflow.com/questions/1092163/execute-xp-cmdshell-command-as-specific-user
Setting up a proxy account (Microsoft page):
Oftentimes I'll do the following and check the results for details regarding the environment xp_cmdshell spawns the shell into:
exec master..xp_cmdshell 'set'
March 22, 2018 at 10:41 am
You can have proxies at a few levels, for agent jobs and xp_cmdshell. If nothing is set, the agent job should run as the Agent service account, not owner.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply