Granting xp_cmdshell permission to SQL Login

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.Mark
Did you restart MSSQLSERVICE after granting permissions?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> 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.|||Yes I did. No effect.
"Uri Dimant" wrote:

> Mark
> Did you restart MSSQLSERVICE after granting permissions?
>|||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.
Ekrem ?nsoy
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> 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.|||Mark
xp_cmdshell requires CONTROL SERVER permission.
Does the user have it?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
> Yes I did. No effect.
> "Uri Dimant" wrote:
>
>|||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'.
*/
"Uri Dimant" wrote:

> Mark
> xp_cmdshell requires CONTROL SERVER permission.
> Does the user have it?
>
>
>
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
>
>|||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.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:FF048394-0029-4E1B-931F-E6B9E2F9CFF3@.microsoft.com...[vbcol=seagreen]
> 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'.
> */
>
> "Uri Dimant" wrote:
>|||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.
"Dan Guzman" wrote:

> When I type "NET HELPMSG 1329" from the command prompt, I get message "Log
on
> 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.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||> 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..
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:61EA5CCA-4C65-410C-959B-7B67195F2DFD@.microsoft.com...[vbcol=seagreen]
> 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' , 'passwor
d' } ]
> Arguments
> NULL
> Specifies that the proxy credential should be deleted.
> account_name
> Specifies a Windows login that will be the proxy.
>
> Mark.
> "Dan Guzman" wrote:
>

Graceful Exit...

I need to exit from an entire package if a data pump task fails in a sequence container.

I thought I'd use a script task to do this but I'd be glad to learn of any way to do this.

Thanks!

Enantiodrom

Hi Enantiodrom: I had to do exactly this same thing in my packages. I first set up a boolean variable named Go_NoGo and used it as a ReadWriteVariable in a Script Task. Then, within the Script Task I set the variable to either True or False, depending on conditions determined by the Script Task.

Then, on the constraint (the line connecting between tasks) between the Script Task and the next task, I put a "Precedence Constraint" that allows execution to continue to the next task only when Go_NoGo is True.

Double-click on the line connecting your Script Task and the next task in the flow. You should see the "Precedence Constraint Editor" open up. I set "Evaluation operation" to "Expression", and set the Expression to @.[User::Go_NoGo]==True

It took a long time to figure out this syntax. Don't know why the "==" is needed, but it works -- a single "=" won't work.

Also, the reference to the variable ("Go_NoGo") appears to be case-sensitive. Type it into the expression exactly as it appears in the Variables dialog. Also, make sure that the variable is global, and not locally-scoped to some task within the project.

When you set up the constraint this way, execution proceeds to the next task only if Go_NoGo is True. Otherwise, the package just ends without generating an error.

Does this make sense? Once I figured out how to create precedence constraints, I use 'em all the time! There just aren't many good examples in books online. Microsoft really needs to catch up on this one!

|||

Try to look into using failPackageOnFailure property. More details at:

http://msdn2.microsoft.com/zh-cn/library/microsoft.sqlserver.dts.runtime.dtscontainer.failpackageonfailure.aspx

Thanks,
Ovidiu Burlacu

|||

Thank you both very much; this is just what I needed.

Jim/Enantiodrom

global variables in dtspump task

I have two connections, an oracle and sqlserver. the dtspump task is pulling data from oracle to sqlserver. I am trying to automate this package by substituting the where clause values to variables since they are based on year and month. My question is - how does one use global variables in a dtspump task? How can I use variables to do this? Can it be done or do I need to use an Active X script for it?

thanks for any help.
novice userMay check with SQL DTS (http://www.sqldts.com) website.

Global Variables And the Script Component in DataFlow

I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component.

I did add it to the readwrite variable list but I haven't been able to access it.

Type "Me.Variables." and upon typing the second period, intellisense will kick in and give you a alist of all the vars that you have put into ReadOnlyVariables/ReadWriteVariables.

-Jamie

Global Variable

Hi All,

I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an error

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I've seen some examples online to get global varaibles in task script and all of them display the same code

Any idea

Franck

To access variable this way it has to be included in the list of ReadOnlyVariables or ReadWriteVariables on your script task properties SCRIPT properties section. Remember also that variables names are case sensitive.

Global function for different different task

Hi,

I've a function which I'm using in one of my script task. Then I do some operations and in other script task I'm using same function. If I change a function in one task then I need to do in all other tasks. Is there a way where we can write this function at package level and be able to use in any of the task.

Regards

Munafbhai

You cannot do this with scripts in packages. You can however create a managed dll library (in C-sharp or Vb) and make a reference to this dll in your script package.

Sometimes when you use SQL server or another relational DB, you could use stored procedure, to avoid cutting and pasting code in several package scripts.