Granting xp_cmdshell permission to SQL Login

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Allison - 27 Nov 2007 13:16 GMT

Hi database geeks and MVPs!

Using SQL Server 2005 SP2

I have a stored procedure in my database which calls xp_cmdshell to run a
little task. I have done the following to allow this proc to be executed by a
non-privileged user:

USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO

CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin

CREATE DATABASE mytestdb
GO

USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO

CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:\'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin

EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert

I get the following error:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.

Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
through via a stored procedure?

Thanks,
Mark.

Reply to this Message

Uri Dimant - 27 Nov 2007 13:45 GMT

Mark
Did you restart MSSQLSERVICE after granting permissions?

> Hi database geeks and MVPs!
>
[quoted text clipped - 49 lines]
> Thanks,
> Mark.

Reply to this Message

Mark Allison - 27 Nov 2007 13:59 GMT

Yes I did. No effect.

> Mark
> Did you restart MSSQLSERVICE after granting permissions?

Reply to this Message

Uri Dimant - 27 Nov 2007 14:07 GMT

Mark
xp_cmdshell requires CONTROL SERVER permission.

Does the user have it?

> Yes I did. No effect.
>
>> Mark
>> Did you restart MSSQLSERVICE after granting permissions?

Reply to this Message

Mark Allison - 27 Nov 2007 14:24 GMT

Hi Uri,

I have changed my script to incorporate that permission, but I still get the
same error. xp_cmdshell works fine when run as a sysadmin.

USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO

CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
--EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
doesn't work either
GRANT CONTROL SERVER TO mysqllogin

CREATE DATABASE mytestdb
GO

USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO

CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:\'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin

EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
go

/*
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
*/

> Mark
> xp_cmdshell requires CONTROL SERVER permission.
[quoted text clipped - 5 lines]
> >> Mark
> >> Did you restart MSSQLSERVICE after granting permissions?

Reply to this Message

Dan Guzman - 28 Nov 2007 02:15 GMT

When I type "NET HELPMSG 1329" from the command prompt, I get message "Logon
failure: user not allowed to log on to this computer."

Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
has permissions to login locally.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Hi Uri,
>
[quoted text clipped - 55 lines]
>> >> Mark
>> >> Did you restart MSSQLSERVICE after granting permissions?

Reply to this Message

Mark Allison - 28 Nov 2007 12:20 GMT

Hi Dan,

That's the point. I cannot grant access to a SQL Login. It works fine with a
Windows login, but I have a SQL Login.

From BOL:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]

Arguments
NULL
Specifies that the proxy credential should be deleted.

account_name
Specifies a Windows login that will be the proxy.

Mark.

> When I type "NET HELPMSG 1329" from the command prompt, I get message "Logon
> failure: user not allowed to log on to this computer."
>
> Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
> has permissions to login locally.

Reply to this Message

Dan Guzman - 29 Nov 2007 04:26 GMT

> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.

xp_cmdshell needs an OS security context when it runs. That security
context is the Windows xp_cmdshell proxy account when it's executed by a
non-sysadmin user. The 1329 error isn't related to the SQL login executing
xp_cmdshell but is rather the Windows error code returned because the
xp_cmdshell proxy account doesn't have the needed Windows permissions

My guess is that the Windows login you mentioned is a sysadmin role member.
Xp_cmdshell runs under the context of the SQL Server service account when
executed by a sysadmin role member and that account probably has different
permissions than the proxy account

I successfully ran a modified of your original script on my test system. My
xp_cmdshell proxy account is a minimally privileged domain user account and
. I didn't grant CONTROL SERVER permission..

Signature

Hope this helps.

Dan Guzman
SQL Server MVP

> Hi Dan,
>
[quoted text clipped - 21 lines]
>> sp_xp_cmdshell_proxy_account)
>> has permissions to login locally.

Reply to this Message

Ekrem Önsoy - 27 Nov 2007 14:02 GMT

Hello Mark,

You can't use xp_cmdshell because you do not enable it. Because of the
'allow updates' option.

Your 'allow updates' server setting must be enabled. If you want to leave it
enabled then you'll need to run your code as the following to enable
xp_cmdshell

USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO

If you disable your 'allow updates' option using the following code

EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE

Then you'll be able to run your code as it is (without with override
thing...)

P.S.
I learned this solution from Jasper Smith, thanks to him.

Signature

Ekrem Önsoy

> Hi database geeks and MVPs!
>
[quoted text clipped - 49 lines]
> Thanks,
> Mark.

Reply to this Message