Re: xp_cmdshell issue, local system

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 11/24/05


Date: Wed, 23 Nov 2005 17:59:52 -0600

I'm glad you were able to get it working.

I haven't run into the EM hang problem myself and a KB search didn't turn up
anything obvious. A sporadic problem like this is difficult to diagnose
unless we can correlate it with an environmental setting of some sort.

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"yodarules" <yodarules@discussions.microsoft.com> wrote in message 
news:20B09211-FAB7-4044-9974-B6B2F77C5BD8@microsoft.com...
> You the man, Dan.  It worked.  So any idea why sometimes the EM hangs when
> you change startup account
>
> "Dan Guzman" wrote:
>
>> > How is that it worked for one (sql server startup account) but not for
>> > agent.  I double checked, the user exists.
>>
>> My guess is that EM uses a different technique for maintaining the SQL
>> Server and SQL Agent service accounts.  Rather than using the '.' 
>> shorthand,
>> try specifying the actual computer name ('ComputerName\sqluser').
>>
>> -- 
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "yodarules" <yodarules@discussions.microsoft.com> wrote in message
>> news:ECB606DB-1805-45C4-A2E3-B0670DF85C45@microsoft.com...
>> > Hey Dan,
>> >
>> > Thanks for the reply.  So initially I tried to change the login using 
>> > EM,
>> > but when ever I tried to do that it always hung and I had to EndTask 
>> > EM,
>> > so
>> > thats the reason why I changed the login using services screen.  So 
>> > what I
>> > did now was stopped the agent service, then went and changed the 
>> > startup
>> > account to the localsystem for SQL Server and the same for agent which
>> > worked
>> > surprisingly.  Then I went and reverted back to my original login for 
>> > sql
>> > server which is .\sqluser (a local user in the administrators group) it
>> > worked.  Now when I go and try the same for the agent startup account 
>> > it
>> > errors out.
>> >
>> > Error 15401 : Windows NT User or group '.\sqluser' not found. Check the
>> > name
>> > again
>> >
>> > Ok on this
>> >
>> > Error 15007 : The login '.\sqluser' does not exist
>> >
>> > I have windows 2003 Std Edition with SP1, SQL Server Enterprise with 
>> > SP4
>> >
>> > How is that it worked for one (sql server startup account) but not for
>> > agent.  I double checked, the user exists.
>> >
>> > Thanks.
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> > Msg 50001, Level 1, State 50001
>> >> > xp_cmdshell failed to execute because CreateProcessAsUserW returns
>> >> > error
>> >> > 1314. please make sure the service account SQL Server running under 
>> >> > has
>> >> > appropriate privilege.
>> >>
>> >> As the message indicates, this error may be because the SQL Server
>> >> service
>> >> account doesn't have the rights necessary to change security context 
>> >> to
>> >> the
>> >> proxy account.  Specifically, 'act as part of operating system' and
>> >> 'replace
>> >> a process level token' are needed.  These rights are set automatically
>> >> during SQL Server installation and when the account is changed using
>> >> Enterprise Manager but not when you change the account directly from
>> >> Windows.
>> >>
>> >> The easiest way to assign the rights is to use EM to change the SQL
>> >> Server
>> >> account to local system and then back to the desired domain account.
>> >>
>> >> -- 
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "yodarules" <yodarules@discussions.microsoft.com> wrote in message
>> >> news:8BD28577-F4B6-4F84-B116-F9930A32F8AC@microsoft.com...
>> >> >I want to give access to a regular user to execute xp_cmdshell.  To 
>> >> >do
>> >> >so,
>> >> >I
>> >> > followed all KB articles and did the following
>> >> >
>> >> > EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
>> >> >             N'Domain', -- agent_domain_name
>> >> >             N'name', -- agent_username domain
>> >> >             N'password' -- agent password
>> >> >
>> >> > -- Enable non-system administrators to run the job and to execute
>> >> > xp_cmdshell.
>> >> > EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0
>> >> >
>> >> > grant execute on xp_cmdshell to name -- Enter the user name again
>> >> > without
>> >> > quotes
>> >> >
>> >> > If I log in as this user 'name' and execute master..xp_cmdshell 
>> >> > 'dir'.
>> >> > Its
>> >> > fine.  My problem is, the system that I want this to work in is not
>> >> > part
>> >> > of a
>> >> > domain.  Its a stand alone SQL Server box.  So I set these
>> >> > EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
>> >> >             N'computer-name', -- agent_domain_name
>> >> >             N'localuser', -- agent_username domain
>> >> >             N'password' -- agent password
>> >> >
>> >> >
>> >> > When I set as local user and computer name I get no errors.  But 
>> >> > when I
>> >> > execute xp_cmdshell I doesn't work.  The localuser is part of
>> >> > administrators
>> >> > group as well.  The error that I get is
>> >> >
>> >> > Msg 50001, Level 1, State 50001
>> >> > xp_cmdshell failed to execute because CreateProcessAsUserW returns
>> >> > error
>> >> > 1314. please make sure the service account SQL Server running under 
>> >> > has
>> >> > appropriate privilege. For more information, search Book Online for
>> >> > topic
>> >> > related to xp_sqlagent_proxy_accoun
>> >> >
>> >> > Has anyone seen this before and any ideas to resolve it.  Thanks.
>> >>
>> >>
>> >>
>>
>>
>> 


Relevant Pages

  • Re: xp_cmdshell issue, local system
    ... > How is that it worked for one (sql server startup account) but not for ... Server and SQL Agent service accounts. ... SQL Server MVP ... Now when I go and try the same for the agent startup account it ...
    (microsoft.public.sqlserver.security)
  • Re: Failed to Create Directory
    ... ensure that the sql server agent or the account your merge agent is running ... Looking for a SQL Server replication book? ... I have seen posting regarding the setting up of the IUSR_Machine account ...
    (microsoft.public.sqlserver.replication)
  • Re: xp_cmdshell issue, local system
    ... So initially I tried to change the login using EM, ... account to the localsystem for SQL Server and the same for agent which worked ... Now when I go and try the same for the agent startup account it ...
    (microsoft.public.sqlserver.security)
  • Re: Error 15401 using sp_grantlogin (not addressed by current KB articles)
    ... Restarting Windows 2000 resolved the problem for this particular account, ... confused when it sees a duplicate SID. ... > One way to get SQL Server to agree with the renamed NT ... > Preview (to ensure the script was created), ...
    (microsoft.public.sqlserver.security)
  • Re: Cannot Restart SQL Server Agent
    ... Try to change the start up account for SQL Agent to ... "Local system account" and try starting the service. ... I even stopped the SQL Server and restarted it, ...
    (microsoft.public.sqlserver.server)