xp_cmdshell fails - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

xp_cmdshell fails

Author

Topic

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-07-26 : 22:06:01

Dear all,

I think this is a typical problem but I have followed the solutions from BOL yet the error is still there:

In my stored procedure, I included a call to xp_cmdshell to execute a DTS package using DTSRUN (the DTS package performs some processing in Analysis Server), an an OS command. From my application, I used a non-sysadmin SQL Server login called APP_USER to connect to SQL Server, and it maps to database user APP_USER in databases master and APP (the application database).

I granted execute permission on xp_cmdshell to the user APP_USER in master database, and then run the xp_sqlagent_proxy_account as follows:

EXEC master.dbo.xp_sqlagent_proxy_account N'SET',
N'APP-SERVER',
N'administrator',
N'password'

The 3 parameters are the SQL Server machine name, administrator user name and its password, respectively.

So I run the stored procedure using the APP_USER account in query analyzer, yet at the line the xp_cmdshell is called, the following error occurs:

Msg 50001, Level 1, State 50001
xpsql.cpp: Error 87 from GetProxyAccount on line 604

Did I do anything wrong? Or what else must I do to let APP_USER successfully run the stored procedure?

Many thanks,
delpiero

dragan
Starting Member

17 Posts

Posted - 2005-07-27 : 03:36:05

I had a problem much like yours a few months ago. These are the procedures I created in case it happens again. It's very step-by-step, see if it helps you

On the database server

1.) Start - Control Panel – Administrative Tools – Services
Ensure that the SQLSERVERAGENT service is set to start with the Service Account under the Logon tab.
2.) Start – Programs - Microsoft SQL Server – Enterprise Manager
3.) When the Enterprise Manager opens, expand the Management folder and R-click on the SQL Server Agent. Select Properties and click on the Connection tab.
Ensure that the SQL server connection is set to Use Windows Authentication.

Creation of the Proxy Account on the Database Server

1.) Start – Programs - Microsoft SQL Server – Enterprise Manager.
2.) In Enterprise Manager expand the Security folder.
3.) R-click Logons and select New Login.
4.) To the right of the Name field click the block with the 3 dots on it.
5.) In the Add Name field type the name of the Service Account that was created by the Domain Administrators, and click OK.
6.) On the SQL server Login Properties box that is left open behind, click the Server Roles tab and check the System Administrators box.
7.) Ensure that on the general tab the default database is Master.
8.) Click OK.
9.) R-click on the right-hand pane of the Security, Logins window and click Refresh.
10.) Your added Service account will appear.

11.) Back on the left-hand pane Tree, expand the Databases folder.
12.) Expand the Master database and click on Users.
13.) On the right-hand pane R-click anywhere in the white area and select New Database User.
14.) Next to the Login Name field click the drop-down list and select your Service account

15.) On the same Master database click on Extended stored Procedures in the left-hand pane.
16.) In the right-hand pane select double click XP_CMDSHELL.
17.) On the Permissions tab tick the EXEC box for the Service Account that you have added earlier. A green tick should appear.

18.) Once you have done that click on the Management folder in the left-hand pane again.
19.) R-click on SQL Server Agent and select Properties.
20.) Click on the Job System tab.
21.) At the bottom of the open box un-check Non-SysAdmin job step proxy account.
22.) As soon as you do that a box pops up prompting you for a User Name, Password and Domain.
23.) Insert the name of the Service Account, its password and the domain name .
24.) Click OK.

At this point you would have created an account that will give non-Administrative accounts access to execute Extended Stored Procedures.

Later

delpiero
Yak Posting Veteran

98 Posts

Posted - 2005-07-28 : 05:35:58

Thanks dragan for your detailed steps.

I think what I missed out is the step 21) from your list ... I didn't uncheck that box. Now, my application works fine with that application user.

Just think that this application user does not necessarily need to have the system administrator role in the SQL Server.

Thanks,
delpiero

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources