xp_cmdshell proxy account gets "lost"

  • I've created a procedure as a work around for our testers to be able to start and stop a perfmon trace when they run load tests. 
    Yes, I know, Powershell would have been a better choice.  But I am a PS novice, and it was far easier to give them a proc to run as opposed to granting permissions, RDP access, and so forth.

    I created a domain account, and added it to the proper groups on the server.
    I added this account to SQL, made it sysadmin, and disabled the login.
    I created the proc with the EXECUTE AS this user. 
    Everything worked.  

    The problem is that the proxy credentials seem to stop working.  You can run this proc successfully, but if you attempt to run it again, it fails with this error:
    Procedure: xp_cmdshell Message: An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'. Line Number: 1

    I then drop and re-add the proxy account with this code

    EXEC sp_xp_cmdshell_proxy_account NULL;
    GO
    EXEC sp_xp_cmdshell_proxy_account 'MyDomain\TheUser', 'TheUsersPassword';
    GO

    It then will run successfully.
    There is no pattern to when it fails.  

    I'm stumped.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • The error number translates to: Logon failure: the user has not been granted the requested logon type at this computer.
    That's usually related to rights for the account on the computer itself so probably similar to rights the SQL accounts themselves use. The first one I would try is adding the account to Log On as batch job since most of the others rights some of the SQL accounts use would be related more to running as a service or starting a service.

    Sue

  • An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1385'.
    In order to fix this you need to open the Local Security Settings on the host machine.
    Navigate to Security Settings -> Local Policies -> User Rights Assignment.
    Now open "Log on as a batch job" and add the user you assigned to the xp_cmdshell proxy account - and you should find it runs fine now.
    Hope that helps!

  • Well, that seems to have done the trick.  Thanks!
    As soon as I read it, I realized that I never added this to the GPO.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John - Thursday, June 22, 2017 9:27 AM

    I created a domain account, and added it to the proper groups on the server.
    I added this account to SQL, made it sysadmin, and disabled the login.
    I created the proc with the EXECUTE AS this user.

    Really not the right way to do this.  The proxy user should be a rather mindless AD user with virtually no privs.  Period.  Then you don't have to disable the login.

    The database that you're using should be owned by the disabled "sa" user.  Of course, the SQL Server login should be able to "see" where it is that you want it to "see".

    The only privs the users should need is "public", "connect", and privs to execute the proc.  For sure, you must NEVER give the privs to execute xp_CmdShell. PERIOD!.  The proc should have EXECUTE AS OWNER. 

    Heh... maybe I should submit my presentation on how to setup and use xp_CmdShell for the Pittsburgh SQL Saturday coming up?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Jeff Moden - Friday, June 23, 2017 8:21 PM

    Michael L John - Thursday, June 22, 2017 9:27 AM

    I created a domain account, and added it to the proper groups on the server.
    I added this account to SQL, made it sysadmin, and disabled the login.
    I created the proc with the EXECUTE AS this user.

    Really not the right way to do this.  The proxy user should be a rather mindless AD user with virtually no privs.  Period.  Then you don't have to disable the login.

    The database that you're using should be owned by the disabled "sa" user.  Of course, the SQL Server login should be able to "see" where it is that you want it to "see".

    The only privs the users should need is "public", "connect", and privs to execute the proc.  For sure, you must NEVER give the privs to execute xp_CmdShell. PERIOD!.  The proc should have EXECUTE AS OWNER. 

    Heh... maybe I should submit my presentation on how to setup and use xp_CmdShell for the Pittsburgh SQL Saturday coming up?

    Actually, after I looked at this whole thing again, the account is not sysadmin,  It has only been granted rights to xp_cmdshell, and read\write access to the logging table in the utility database.  

    What about a presentation of "a bunch of SQL Spackle like" things to make life easier?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Michael L John - Saturday, June 24, 2017 5:45 AM

    Jeff Moden - Friday, June 23, 2017 8:21 PM

    Michael L John - Thursday, June 22, 2017 9:27 AM

    I created a domain account, and added it to the proper groups on the server.
    I added this account to SQL, made it sysadmin, and disabled the login.
    I created the proc with the EXECUTE AS this user.

    Really not the right way to do this.  The proxy user should be a rather mindless AD user with virtually no privs.  Period.  Then you don't have to disable the login.

    The database that you're using should be owned by the disabled "sa" user.  Of course, the SQL Server login should be able to "see" where it is that you want it to "see".

    The only privs the users should need is "public", "connect", and privs to execute the proc.  For sure, you must NEVER give the privs to execute xp_CmdShell. PERIOD!.  The proc should have EXECUTE AS OWNER. 

    Heh... maybe I should submit my presentation on how to setup and use xp_CmdShell for the Pittsburgh SQL Saturday coming up?

    Actually, after I looked at this whole thing again, the account is not sysadmin,  It has only been granted rights to xp_cmdshell, and read\write access to the logging table in the utility database.  

    What about a presentation of "a bunch of SQL Spackle like" things to make life easier?

    Ya know... that sounds like it might be a good idea.  I've done that in the past for my local chapter.  I called it "Tool Time" but we could call it whatever you folks suggest.  It had things like a couple of automated reports (Morning "Jobs" report, and Enterprise Wide disk report/warning system and it does NOT require SSRS!), a mail system that DOESN'T require you to setup mail in the database (if you're in a hurry), a killer blocking monitor system that captures and list any blocking in a very meaningful hierarchical order along with the code that's involved, backup code that auto-magically does backups even on new databases, a clickable PerfMon button that would display the last half hour of various performance indicators in real time, how to correctly setup xp_CmdShell for safe usage, and a couple of useful procs (sp_WhatsRunning and sp_ShowWorst) that help you quickly find your worst performing queries, which may not actually be your longest running queries.  Might be good for the "So you wanna be a DBA track" that you have.  Could easily cover 2 sessions if you want some useful details.  Lemme know and I'll put the pieces together in a presentation along with an abstract.

    I don't know if I'll have it ready in time but I'm working on one about why you don't ever have to rebuild/reorganize indexes and a great way to find used stats, which also lets you find and drop unused stats, which is all that you really need to do instead of a bunch of index maintenance that may actually cause slowdowns and blocking when it's done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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