Re: xp_cmdshell issue, local system

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

Date: Wed, 23 Nov 2005 13:02:13 -0600

> 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
    ... SQL Server MVP ... > you change startup account ... >> Server and SQL Agent service accounts. ...
    (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)