Solved

Problem with xp_cmdshell in a SQL Server Agent Job

Posted on 2011-09-02
6
1,814 Views
Last Modified: 2012-08-14
I have an SQL script that includes an xp_cmdshell statement. If I run the script in Management Studio whilst logged in as a particular user everything works.
The user (let's call him userA)  is a member of sysadmin.

If I include the script in a job the job fails. SQL Server agent runs under a domain service account and I'm using Run as user UserA because the script needs to query a database that UserA has access and the domain service account does not.

The error is:
Msg 229, Sev 14, State 5, Line 1 : The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'. [SQLSTATE 42000]

The server is SQL Server 2008 R2. xp_cmdshell is enabled. I am not using a proxy (userA is a member of sysadmin role)

Any guidance would be appreciated.
0
Comment
Question by:Feridun Kadir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Expert Comment

by:markterry
ID: 36475001
Add that domain user to the SQL Server logins, or set the job to execute using UserA. if you are using SQL Jobs Agent, this is a section in there. If you are using sqlcmd and task manager, then you need to include it as a parameter.
0
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 100 total points
ID: 36475004
does this apply to you?:

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running.

When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail.

This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server

have a look at this forum:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45799
0
 
LVL 29

Author Comment

by:Feridun Kadir
ID: 36475179
Thanks for your posts. UserA is windows login and has been added to SQL Server and made a member of the sysadmin role so it should run under the context of the SQL Server service.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 6

Expert Comment

by:markterry
ID: 36475416
Run the job with that user...
0
 
LVL 29

Accepted Solution

by:
Feridun Kadir earned 0 total points
ID: 36492119
I had to treat UserA as if he wasn't a member of sysadmins and grant him access to xp_cmdshell explicitly. I wonder whether there is a bug or a change in SQL 2008 R2 because this was a not a problem for me in SQL 2005.
0
 
LVL 29

Author Closing Comment

by:Feridun Kadir
ID: 36518446
HainKurt provided information that I already knew, but it was helpful to have it confirmed.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 45
Need to replicate a Log table 4 41
ms sql + help with query 2 44
Http Post send by Store Procedure Help 5 29
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question