SQL Server Agent with Proxy - running under incorrect credentials

  • Hi,
    I've got a batch file which simply echos the username (%USERNAME%), and I'm calling this from a SQL Server Agent job which is using CMDExec under a proxy linked to a local windows account.
    (This is part of a bigger project, but this is the part that is throwing a problem)

    The process generally works fine and the batch file returns the name of local windows account which is linked to my proxy

    However, if I launch an application (such as cmd.exe) as that user (using "run as different user") then the batch file will return the user as SQLSERVERAGENT
    It appears that if the user account linked to the proxy is already in use, then the SQL Server Agent job is unable to use it.
    Is this by design, or is it a problem with the config or a bug within Windows or SQL?

    This is the simple batch file:

    The SQL Server Agent Job Step is setup as:

    If i run this, the job succeeds and writes the Windows Username (TC_DR) that is linked to my proxy to the job history

    If, however, I launch cmd.exe (or any other application) by right-clicking and selecting "run as different user", and then enter the details of my TC_DR user, then run the job again, the following gets written to the job history.

    This is causing me a problem because my project will be reading files from the profile of the account linked to my SQL proxy.
    If I don't get the correct profile, then I won't read the files that I need.

    I'm testing this on SQL Server 2016 SP1 on Windows 10

    Thank you for your help
    Steve

  • Steve Smith-163358 - Thursday, December 28, 2017 3:08 PM

    Hi,
    I've got a batch file which simply echos the username (%USERNAME%), and I'm calling this from a SQL Server Agent job which is using CMDExec under a proxy linked to a local windows account.
    (This is part of a bigger project, but this is the part that is throwing a problem)

    The process generally works fine and the batch file returns the name of local windows account which is linked to my proxy

    However, if I launch an application (such as cmd.exe) as that user (using "run as different user") then the batch file will return the user as SQLSERVERAGENT
    It appears that if the user account linked to the proxy is already in use, then the SQL Server Agent job is unable to use it.
    Is this by design, or is it a problem with the config or a bug within Windows or SQL?

    This is the simple batch file:

    The SQL Server Agent Job Step is setup as:

    If i run this, the job succeeds and writes the Windows Username (TC_DR) that is linked to my proxy to the job history

    If, however, I launch cmd.exe (or any other application) by right-clicking and selecting "run as different user", and then enter the details of my TC_DR user, then run the job again, the following gets written to the job history.

    This is causing me a problem because my project will be reading files from the profile of the account linked to my SQL proxy.
    If I don't get the correct profile, then I won't read the files that I need.

    I'm testing this on SQL Server 2016 SP1 on Windows 10

    Thank you for your help
    Steve

    I just tested the same and didn't have any problems with the proxy executing - always executed as the credential for the proxy account and I opened several different apps using runas a different user <credential login>
    I tested this on a laptop that isn't a member of a domain. Mentioned that as maybe you have number of logins limited by policy? Did you check the security log for any entries when you execute this on the Windows 10 computer where you are testing this?
    One other thing is you probably don't want a process dependent on a profile - are you doing that for mapped drive purposes? If that's the case, The files should be in a share, referenced using a UNC path.

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

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