SQL2008R2 - sp_xp_cmdshell_proxy_account - Setting breaking daily

Question

I am using this feature for the intended purpose, to allow xp_cmdshell execution for a non-admin user.  After running the command, and setting the proxy account, everything works as expected.  The commands are executed as expected through this user account.

The really strange thing is the setting is getting "broken" overnight every day on a client site. I have had this setup on other sites, for months, without encountering this.

I can query the sys.credentials view and see the account still setup with the user I specified.  The create date reflects the date I first set this up, and the update date reflects the last time I ran exec sp_xp_cmdshell_proxy_account command after it was "broken."  This is what makes it really odd, is the update date and create date aren't modified, but an error is thrown when the user attempts to run a xp_cmdshell command.

You can fix the issue by rerunning the exec sp_xp_cmdshell_proxy_account command, and everything works as expected.  I can temporarily "band-aid" the issue by setting up a daily job to run each morning.

It appears that a nightly process is altering something, but not at the level where the sys.credentials view reflects an update.

The sys.credentials view doesn't contain the password, so I was thinking that maybe the system table containing the password is getting hit with an update/delete statement by a nightly process, but I am unsure which system table this is even in.  I tried profiling the exec sp_xp_cmdshell_proxy_account to find out what the sproc is doing, but it doesn't give any information. 

Does anyone have any ideas on where to look or what could be altering the setting without reflecting a change in the sys.credentials view?  Any additional information/ideas would be greatly appreciated.  Thanks in advance!

Answer 1

Please include SQL Server version ( + any cumulative updates ) you are using

Thanks

Sethu Srinivasan[MSFT]

SQL Server

http://blogs.msdn.com/sqlagent

Answer 2

Thanks for the response.. I am awaiting reply from our client and will update with this info ASAP.

Answer 3

Hi Enjoydo,

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.
 
When IDENTITY is a Windows user, the secret can be the password. The secret is encrypted using the service master key. If the service master key is regenerated, the secret is re-encrypted using the new service master key.

In the Password and Confirm password boxes, type the password of the account specified in the Identity box. If Identity is a Windows user account, this is the Windows password. The Password can be blank, if no password is required.

You can try to use: Audit App Role Change Password Event Class

It occurs whenever a password is changed for an application role.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

Thanks for the information Iric, I will see if that helps and post results. 

In the meantime, the version is:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit)

So it looks like SP1 w/o any CU's. 

Answer 5

Hi Iric, I tried profiling with the audit app role change password event and the event was not triggered when executing sp_xp_cmdshell_proxy_account.  I tried numerous other audit events that seemed like they might be triggered when executing this sproc, but nothing is showing up in the profiler. 

Edit: I want to make it clear, I was only profiling certain audit events when reporting these results.  I can definitely profile different types of events and see trace results when the sproc is executed.  However, these traces don't pick up anything when left on overnight...yet the setting is still broken by morning.

Answer 6

enjaydo,

I have a test in SQL Server Profiler, the sp_xp_cmdshell_proxy_account could be traced in SQL Server 2008 R2, and it shows the application name. Please do some filter for your profiler.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Iric,

That is a little different than your original suggestion.  You suggested profiling the App Role Change Password Event, which does not yield results in profiler when the sp_xp_cmdshell_proxy_account sproc is run.

I can surely profile this sproc with different criteria than the App Role Change Password Event and see the AppName when the command is executed.  However, I have profiled with the following filters overnight: %xp_cmdshell_proxy_account% and %alter credential%.

This did not yield any results beyond my initial test to ensure the filters worked as expected.

The setting is still being broken without either of these being triggered in profiler, and that makes sense considering the modify_date isn't being updated in the credential view. 

Answer 8

I have additional information.

After the setting is broken, running sp_xp_cmdshell_proxy_account again fixes it. 

However, alter credential ##xp_cmdshell_proxy_account## does not. 

This is an interesting new piece to the puzzle.  It does not appear to be related to the password, and would explain why the modify_date is not updated after the setting is broken. 

Something else in the sp_xp_cmdshell_proxy_account, that isn't related to updating user/pass, is correcting the issue.  However, I am not sure what else the command is doing.  Does anyone have any ideas?  Thanks!

Answer 9

Hi

I have the same issue using SQL 2008 R2 - Version 10.50.1600.1. It only happens with one client but not with others that have the same setup.

To isolate the issue, I ran a xp_cmdshell command every half hour to do a directory listing, ie

exec master..xp_cmdshell 'dir'

It works OK from the sa account but when I try and run it from an operating system account that doesn't have sysadmin privileges it works OK until 6:30pm then it fails with the following error.

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.

There are no SQL or Server jobs that run around that time so I assume there is some other job that is casuing the issue or there is more to this than meets the eye.

If I change the account to have sysadmin privelege it works but I don't want to do this. 

Similarly re-executing sp_xp_cmdshell_proxy_account works.

However, I am looking for a longer term fix.

Any feedback would be greatly appreciated. 

Thanks

  

Answer 10

NET HELPMSG 1385 says

Logon failure: the user has not been granted the requested logon type at this computer.

Since the error occurs from with a certain point in time, I would assume that this a local configuration. I would look in the AD how the account is set up.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

On our systems - we have a group policy that runs every night and resets the local administrators group.  That means, if the account was added manually it will be removed.

Could it be possible that a group policy is being executed that resets the password on that account?

---

Jeff Williams

Answer 12

Check if the xp_cmdshell proxy account is added to "Logon as Batch Job" local security policy

Control Panel -> Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Assignment -> Log on as a batch job