SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Agent with Proxy - running under incorrect credentials


SQL Server Agent with Proxy - running under incorrect credentials

Author
Message
Steve Smith-163358
Steve Smith-163358
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1349 Visits: 373
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
Sue_H
Sue_H
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77404 Visits: 15517
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search