How to run xp_cmdshell from my app in ASP.NET MVC

Question

In my app I'm reading data to database and then I want to start Integration Serivces to import the data into the cube. This is the command, which I use to start it from SSMS:

execute master..xp_cmdshell 'dtexec /DTS "\MSDB\B_Costs" /SERVER . /CHECKPOINTING OFF /REPORTING V'

But how use it from my app? I've got this error at the begining:
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

And after use such command:

GRANT EXECUTE ON xp_cmdshell TO [IIS APPPOOL\DefaultAppPool]

I've got another error:
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

And as I checked I have no credentials in Database Engine -> Security -> Credentials.

I wanted use such command as:

EXEC sp_xp_cmdshell_proxy_account 'IIS APPPOOL\DefaultAppPool', 'password'

But I don't know what should I write in 'password'. Any suggestions?

This is my CubeController:

public ActionResult Index()
{            
    string sql = "execute master..xp_cmdshell 'dtexec /DTS \"\\MSDB\\B_Costs\" /SERVER . /CHECKPOINTING OFF /REPORTING V'";
    try
    {
        MyEntity.Database.ExecuteSqlCommand(sql);
    }
    catch (Exception e)
    {
        string error = "There was an error: <br />" + e.Message;
        TempData["Message"] = error;
    }

    return RedirectToAction("Index","Home");
}

Hatjhie · Answer 1 · 2014-09-29T15:11:32.070

0

Hope this link below would be able to assist you. Please let me know if it is working.

http://www.databasejournal.com/features/mssql/xpcmdshell-for-non-system-admin-individuals.html

After you set up a proxy account your non-sysadmin logins might still not be able to use xp_cmdshell.    If you have not granted your non-sysadmin user EXECUTE permissions on xp_cmdshell you will get this error:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
To overcome this error all you have to do is make sure the non-sysadmin user has a database user in the master database and then GRANT your non-sysadmin user the rights to execute xp_cmdshell.  In order to do that all you have to do is run the following TSL code:

USE master;
GO
CREATE USER [MyDomain\Dorothy] FOR LOGIN [MyDomain\Dorothy];
GRANT EXECUTE ON xp_cmdshell TO [MyDomain\Dorothy];

In my case, I tried:

GRANT EXECUTE ON xp_cmdshell TO [IIS APPPOOL\DefaultAppPool]

I am granting it for

DefaultAppPool

See the screenshot for your reference: http://postimg.org/image/yqcf3vya1/

The result is successful. I used either my sa or administrator account to login to my database through SQL Server Management Studio to do the GRANT EXECUTE. Please try and let me know the result. Thanks

edited Sep 29 '14 at 15:11

answered Sep 28 '14 at 09:11

Hatjhie

1,366
2
13
27

I wrote: `CREATE USER [IIS APPPOOL\DefaultAppPool] FOR LOGIN [IIS APPPOOL\DefaultAppPool];` and I have error: *User, group, or role 'IIS APPPOOL\DefaultAppPool' already exists in the current database.* – Monic Sep 28 '14 at 09:19
Then just run the GRANT EXECUTE ON xp_cmdshell TO [IIS APPPOOL\DefaultAppPool]; What is the result? – Hatjhie Sep 28 '14 at 09:29
Oh sorry, I used in my question revoke instead of grant. The error is as above, in question. – Monic Sep 28 '14 at 09:32
I know about it, just look at my question. "And after use such command: `GRANT EXECUTE ON xp_cmdshell TO [IIS APPPOOL\DefaultAppPool]` I've got another error: *The xp_cmdshell proxy account information cannot be retrieved or is invalid...*" You see? I've done this, but then I've got error associated with proxy. – Monic Sep 30 '14 at 07:13
Yes, which account did you use to execute that command? SA account or administrator account? – Hatjhie Sep 30 '14 at 07:15
Always administrator account with Windows Authentication, because I can't login as SA (I've tried reset password, I have enabled SA login and SQL Server and Windows authentication mode). – Monic Sep 30 '14 at 07:44

How to run xp_cmdshell from my app in ASP.NET MVC

1 Answers1