(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Rami » Sun, 17 Nov 2002 17:55:12



Hello!

Can someone tell me why ADO.NET cannot find an Encrypted Stored Procedure?
The scenario is, that SP calls another encrypted SP.  Also it executes a
master..XP_CMDSHELL.  But when I try to call a different encrypted SP that
does not call any other SPs, ADO.NET does not return any errors and the SP
executed perfectly.  Do I need to set something in ADO.NET for this?

Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Mary Chipma » Sun, 17 Nov 2002 23:52:36


The problem probably isn't with encryption, but with calling
xp_cmdshell, which defaults to sysadmins-only. You'd need to
explicitly grant permission to less-privileged accounts, which is not
a good idea security-wise.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"


>Hello!

>Can someone tell me why ADO.NET cannot find an Encrypted Stored Procedure?
>The scenario is, that SP calls another encrypted SP.  Also it executes a
>master..XP_CMDSHELL.  But when I try to call a different encrypted SP that
>does not call any other SPs, ADO.NET does not return any errors and the SP
>executed perfectly.  Do I need to set something in ADO.NET for this?

>Any help is very much appreciated.  Thanks!


 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Benoit » Tue, 19 Nov 2002 17:36:25


hi !

having a stored proc calling another stored proc itself calling cmdshell
should be possible, but there's some traps:
- your first Stored proc (say: SP_A) must belong to 'dbo'
- your second Stored proc (say: SP_B) must also belong to 'dbo'
- you don't have to grant special permissions to the 'cmdshell' system SP,
dbo already has the right to exec it ...
- BUT: when a user call SP_A, itself calling SP_B, itself calling cmdshell,
sql will first check if the user have the permission to do so ... if it
doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be used,
and cmdshell can be called without compromising security ...
- the only thing to know is that the owner of your DB must be 'dbo' (the
same as the owner of 'Master' - where cmdshell reside ...) otherwise the
calling chain doesn't work ...

so:

- YOUR_DB -> owner 'dbo'
    - SP_A -> owner 'dbo'   (exec SP_B)
    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

- MASTER -> owner 'dbo'
    - xp_cmshell -> 'dbo' has 'exec permission' already !

It means that a user can't directly exec 'xp_cmdshell' , but it can exec
'SP_A' & 'SP_B' if you grant him the permission to di it ...

Hope it helps ...


> The problem probably isn't with encryption, but with calling
> xp_cmdshell, which defaults to sysadmins-only. You'd need to
> explicitly grant permission to less-privileged accounts, which is not
> a good idea security-wise.

> -- Mary
> MCW Technologies
> http://www.mcwtech.com

> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

> >Hello!

> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
Procedure?
> >The scenario is, that SP calls another encrypted SP.  Also it executes a
> >master..XP_CMDSHELL.  But when I try to call a different encrypted SP
that
> >does not call any other SPs, ADO.NET does not return any errors and the
SP
> >executed perfectly.  Do I need to set something in ADO.NET for this?

> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Mary Chipma » Wed, 20 Nov 2002 00:26:26


I'm sorry, but you are incorrect in your assumptions. Try this simple
repro scenario-- create the following stored procedure in Northwind:

Create proc CopyTest
AS
EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll c:\temp\test2.dll',
   NO_OUTPUT

Grant execute permissions to the public role on CopyTest.

Log on to QA as a non-dbo user and attempt to execute CopyTest.
Here's the error message you'll see:

Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
EXECUTE permission denied on object 'xp_cmdshell', database 'master',
owner 'dbo'.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"


>hi !

>having a stored proc calling another stored proc itself calling cmdshell
>should be possible, but there's some traps:
>- your first Stored proc (say: SP_A) must belong to 'dbo'
>- your second Stored proc (say: SP_B) must also belong to 'dbo'
>- you don't have to grant special permissions to the 'cmdshell' system SP,
>dbo already has the right to exec it ...
>- BUT: when a user call SP_A, itself calling SP_B, itself calling cmdshell,
>sql will first check if the user have the permission to do so ... if it
>doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be used,
>and cmdshell can be called without compromising security ...
>- the only thing to know is that the owner of your DB must be 'dbo' (the
>same as the owner of 'Master' - where cmdshell reside ...) otherwise the
>calling chain doesn't work ...

>so:

>- YOUR_DB -> owner 'dbo'
>    - SP_A -> owner 'dbo'   (exec SP_B)
>    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

>- MASTER -> owner 'dbo'
>    - xp_cmshell -> 'dbo' has 'exec permission' already !

>It means that a user can't directly exec 'xp_cmdshell' , but it can exec
>'SP_A' & 'SP_B' if you grant him the permission to di it ...

>Hope it helps ...



>> The problem probably isn't with encryption, but with calling
>> xp_cmdshell, which defaults to sysadmins-only. You'd need to
>> explicitly grant permission to less-privileged accounts, which is not
>> a good idea security-wise.

>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com

>> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

>> >Hello!

>> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
>Procedure?
>> >The scenario is, that SP calls another encrypted SP.  Also it executes a
>> >master..XP_CMDSHELL.  But when I try to call a different encrypted SP
>that
>> >does not call any other SPs, ADO.NET does not return any errors and the
>SP
>> >executed perfectly.  Do I need to set something in ADO.NET for this?

>> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Benoit » Wed, 20 Nov 2002 19:33:55


I'm sorry, but what i'm refering to is known as 'ownership chain', and if
you do *exactly* what i wrote in my post it should work ...    (it works for
me, and i use this often ...)
If you receive the 'permission denied' error, it's certainly because the
owner of 'master' is not the same as 'northwind', so the ownership chain is
broken ...   take a look at this :
http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

 Benoit


> I'm sorry, but you are incorrect in your assumptions. Try this simple
> repro scenario-- create the following stored procedure in Northwind:

> Create proc CopyTest
> AS
> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll c:\temp\test2.dll',
>    NO_OUTPUT

> Grant execute permissions to the public role on CopyTest.

> Log on to QA as a non-dbo user and attempt to execute CopyTest.
> Here's the error message you'll see:

> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
> EXECUTE permission denied on object 'xp_cmdshell', database 'master',
> owner 'dbo'.

> -- Mary
> MCW Technologies
> http://www.mcwtech.com

> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"

> >hi !

> >having a stored proc calling another stored proc itself calling cmdshell
> >should be possible, but there's some traps:
> >- your first Stored proc (say: SP_A) must belong to 'dbo'
> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
> >- you don't have to grant special permissions to the 'cmdshell' system
SP,
> >dbo already has the right to exec it ...
> >- BUT: when a user call SP_A, itself calling SP_B, itself calling
cmdshell,
> >sql will first check if the user have the permission to do so ... if it
> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be used,
> >and cmdshell can be called without compromising security ...
> >- the only thing to know is that the owner of your DB must be 'dbo' (the
> >same as the owner of 'Master' - where cmdshell reside ...) otherwise the
> >calling chain doesn't work ...

> >so:

> >- YOUR_DB -> owner 'dbo'
> >    - SP_A -> owner 'dbo'   (exec SP_B)
> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

> >- MASTER -> owner 'dbo'
> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

> >It means that a user can't directly exec 'xp_cmdshell' , but it can exec
> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

> >Hope it helps ...



> >> The problem probably isn't with encryption, but with calling
> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
> >> explicitly grant permission to less-privileged accounts, which is not
> >> a good idea security-wise.

> >> -- Mary
> >> MCW Technologies
> >> http://www.mcwtech.com

> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

> >> >Hello!

> >> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
> >Procedure?
> >> >The scenario is, that SP calls another encrypted SP.  Also it executes
a
> >> >master..XP_CMDSHELL.  But when I try to call a different encrypted SP
> >that
> >> >does not call any other SPs, ADO.NET does not return any errors and
the
> >SP
> >> >executed perfectly.  Do I need to set something in ADO.NET for this?

> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Mary Chipma » Thu, 21 Nov 2002 00:09:48


Ownership chains don't apply here, same way they don't apply for
dynamic SQL using Exec() in a sproc. My install is "out of the box"
and dbo owns everything. You have granted explicit permissions on
xp_cmdshell or are always running as dbo, one or the other.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 19 Nov 2002 11:33:55 +0100, "BenoitM"


>I'm sorry, but what i'm refering to is known as 'ownership chain', and if
>you do *exactly* what i wrote in my post it should work ...    (it works for
>me, and i use this often ...)
>If you receive the 'permission denied' error, it's certainly because the
>owner of 'master' is not the same as 'northwind', so the ownership chain is
>broken ...   take a look at this :
>http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

> Benoit



>> I'm sorry, but you are incorrect in your assumptions. Try this simple
>> repro scenario-- create the following stored procedure in Northwind:

>> Create proc CopyTest
>> AS
>> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll c:\temp\test2.dll',
>>    NO_OUTPUT

>> Grant execute permissions to the public role on CopyTest.

>> Log on to QA as a non-dbo user and attempt to execute CopyTest.
>> Here's the error message you'll see:

>> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
>> EXECUTE permission denied on object 'xp_cmdshell', database 'master',
>> owner 'dbo'.

>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com

>> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"

>> >hi !

>> >having a stored proc calling another stored proc itself calling cmdshell
>> >should be possible, but there's some traps:
>> >- your first Stored proc (say: SP_A) must belong to 'dbo'
>> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
>> >- you don't have to grant special permissions to the 'cmdshell' system
>SP,
>> >dbo already has the right to exec it ...
>> >- BUT: when a user call SP_A, itself calling SP_B, itself calling
>cmdshell,
>> >sql will first check if the user have the permission to do so ... if it
>> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be used,
>> >and cmdshell can be called without compromising security ...
>> >- the only thing to know is that the owner of your DB must be 'dbo' (the
>> >same as the owner of 'Master' - where cmdshell reside ...) otherwise the
>> >calling chain doesn't work ...

>> >so:

>> >- YOUR_DB -> owner 'dbo'
>> >    - SP_A -> owner 'dbo'   (exec SP_B)
>> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

>> >- MASTER -> owner 'dbo'
>> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

>> >It means that a user can't directly exec 'xp_cmdshell' , but it can exec
>> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

>> >Hope it helps ...



>> >> The problem probably isn't with encryption, but with calling
>> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
>> >> explicitly grant permission to less-privileged accounts, which is not
>> >> a good idea security-wise.

>> >> -- Mary
>> >> MCW Technologies
>> >> http://www.mcwtech.com

>> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

>> >> >Hello!

>> >> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
>> >Procedure?
>> >> >The scenario is, that SP calls another encrypted SP.  Also it executes
>a
>> >> >master..XP_CMDSHELL.  But when I try to call a different encrypted SP
>> >that
>> >> >does not call any other SPs, ADO.NET does not return any errors and
>the
>> >SP
>> >> >executed perfectly.  Do I need to set something in ADO.NET for this?

>> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Benoit » Thu, 21 Nov 2002 01:22:40


my install is a fresh one on a newly installed machine...
it is known that permission chain doesn't apply with dynamic SQL, but in my
post i only use static SQL , so it should work.


> Ownership chains don't apply here, same way they don't apply for
> dynamic SQL using Exec() in a sproc. My install is "out of the box"
> and dbo owns everything. You have granted explicit permissions on
> xp_cmdshell or are always running as dbo, one or the other.

> -- Mary
> MCW Technologies
> http://www.mcwtech.com

> On Tue, 19 Nov 2002 11:33:55 +0100, "BenoitM"

> >I'm sorry, but what i'm refering to is known as 'ownership chain', and if
> >you do *exactly* what i wrote in my post it should work ...    (it works
for
> >me, and i use this often ...)
> >If you receive the 'permission denied' error, it's certainly because the
> >owner of 'master' is not the same as 'northwind', so the ownership chain
is
> >broken ...   take a look at this :
> >http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

> > Benoit



> >> I'm sorry, but you are incorrect in your assumptions. Try this simple
> >> repro scenario-- create the following stored procedure in Northwind:

> >> Create proc CopyTest
> >> AS
> >> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll c:\temp\test2.dll',
> >>    NO_OUTPUT

> >> Grant execute permissions to the public role on CopyTest.

> >> Log on to QA as a non-dbo user and attempt to execute CopyTest.
> >> Here's the error message you'll see:

> >> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
> >> EXECUTE permission denied on object 'xp_cmdshell', database 'master',
> >> owner 'dbo'.

> >> -- Mary
> >> MCW Technologies
> >> http://www.mcwtech.com

> >> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"

> >> >hi !

> >> >having a stored proc calling another stored proc itself calling
cmdshell
> >> >should be possible, but there's some traps:
> >> >- your first Stored proc (say: SP_A) must belong to 'dbo'
> >> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
> >> >- you don't have to grant special permissions to the 'cmdshell' system
> >SP,
> >> >dbo already has the right to exec it ...
> >> >- BUT: when a user call SP_A, itself calling SP_B, itself calling
> >cmdshell,
> >> >sql will first check if the user have the permission to do so ... if
it
> >> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be
used,
> >> >and cmdshell can be called without compromising security ...
> >> >- the only thing to know is that the owner of your DB must be 'dbo'
(the
> >> >same as the owner of 'Master' - where cmdshell reside ...) otherwise
the
> >> >calling chain doesn't work ...

> >> >so:

> >> >- YOUR_DB -> owner 'dbo'
> >> >    - SP_A -> owner 'dbo'   (exec SP_B)
> >> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

> >> >- MASTER -> owner 'dbo'
> >> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

> >> >It means that a user can't directly exec 'xp_cmdshell' , but it can
exec
> >> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

> >> >Hope it helps ...



> >> >> The problem probably isn't with encryption, but with calling
> >> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
> >> >> explicitly grant permission to less-privileged accounts, which is
not
> >> >> a good idea security-wise.

> >> >> -- Mary
> >> >> MCW Technologies
> >> >> http://www.mcwtech.com

> >> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

> >> >> >Hello!

> >> >> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
> >> >Procedure?
> >> >> >The scenario is, that SP calls another encrypted SP.  Also it
executes
> >a
> >> >> >master..XP_CMDSHELL.  But when I try to call a different encrypted
SP
> >> >that
> >> >> >does not call any other SPs, ADO.NET does not return any errors and
> >the
> >> >SP
> >> >> >executed perfectly.  Do I need to set something in ADO.NET for
this?

> >> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Mary Chipma » Sat, 23 Nov 2002 11:24:56


Do me a favor and just read up on xp_cmdshell in BOL.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Tue, 19 Nov 2002 17:22:40 +0100, "BenoitM"


>my install is a fresh one on a newly installed machine...
>it is known that permission chain doesn't apply with dynamic SQL, but in my
>post i only use static SQL , so it should work.



>> Ownership chains don't apply here, same way they don't apply for
>> dynamic SQL using Exec() in a sproc. My install is "out of the box"
>> and dbo owns everything. You have granted explicit permissions on
>> xp_cmdshell or are always running as dbo, one or the other.

>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com

>> On Tue, 19 Nov 2002 11:33:55 +0100, "BenoitM"

>> >I'm sorry, but what i'm refering to is known as 'ownership chain', and if
>> >you do *exactly* what i wrote in my post it should work ...    (it works
>for
>> >me, and i use this often ...)
>> >If you receive the 'permission denied' error, it's certainly because the
>> >owner of 'master' is not the same as 'northwind', so the ownership chain
>is
>> >broken ...   take a look at this :
>> >http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

>> > Benoit



>> >> I'm sorry, but you are incorrect in your assumptions. Try this simple
>> >> repro scenario-- create the following stored procedure in Northwind:

>> >> Create proc CopyTest
>> >> AS
>> >> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll c:\temp\test2.dll',
>> >>    NO_OUTPUT

>> >> Grant execute permissions to the public role on CopyTest.

>> >> Log on to QA as a non-dbo user and attempt to execute CopyTest.
>> >> Here's the error message you'll see:

>> >> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
>> >> EXECUTE permission denied on object 'xp_cmdshell', database 'master',
>> >> owner 'dbo'.

>> >> -- Mary
>> >> MCW Technologies
>> >> http://www.mcwtech.com

>> >> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"

>> >> >hi !

>> >> >having a stored proc calling another stored proc itself calling
>cmdshell
>> >> >should be possible, but there's some traps:
>> >> >- your first Stored proc (say: SP_A) must belong to 'dbo'
>> >> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
>> >> >- you don't have to grant special permissions to the 'cmdshell' system
>> >SP,
>> >> >dbo already has the right to exec it ...
>> >> >- BUT: when a user call SP_A, itself calling SP_B, itself calling
>> >cmdshell,
>> >> >sql will first check if the user have the permission to do so ... if
>it
>> >> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be
>used,
>> >> >and cmdshell can be called without compromising security ...
>> >> >- the only thing to know is that the owner of your DB must be 'dbo'
>(the
>> >> >same as the owner of 'Master' - where cmdshell reside ...) otherwise
>the
>> >> >calling chain doesn't work ...

>> >> >so:

>> >> >- YOUR_DB -> owner 'dbo'
>> >> >    - SP_A -> owner 'dbo'   (exec SP_B)
>> >> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

>> >> >- MASTER -> owner 'dbo'
>> >> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

>> >> >It means that a user can't directly exec 'xp_cmdshell' , but it can
>exec
>> >> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

>> >> >Hope it helps ...



>> >> >> The problem probably isn't with encryption, but with calling
>> >> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
>> >> >> explicitly grant permission to less-privileged accounts, which is
>not
>> >> >> a good idea security-wise.

>> >> >> -- Mary
>> >> >> MCW Technologies
>> >> >> http://www.mcwtech.com

>> >> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

>> >> >> >Hello!

>> >> >> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
>> >> >Procedure?
>> >> >> >The scenario is, that SP calls another encrypted SP.  Also it
>executes
>> >a
>> >> >> >master..XP_CMDSHELL.  But when I try to call a different encrypted
>SP
>> >> >that
>> >> >> >does not call any other SPs, ADO.NET does not return any errors and
>> >the
>> >> >SP
>> >> >> >executed perfectly.  Do I need to set something in ADO.NET for
>this?

>> >> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Benoit » Sat, 23 Nov 2002 17:05:07


and what should i find regarding permission chain ??? i know that
xp_cmdshell can be dangerous, but calling it from a Stored proc is safe,
because a user don't have direct access to xp_cmdshell, but can access the
SP that will call xp_cmdshell in a specific way. Thus permission chain apply
here ...
(by the way what do you think about sp_OACreate ?!? it's evil :), no ? but
what a chance we have perm chain ! we can use safe stored proc owned by dbo
that itself call sp_OACreate in a safe way )

that's my point of view ...


> Do me a favor and just read up on xp_cmdshell in BOL.

> -- Mary
> MCW Technologies
> http://www.mcwtech.com

> On Tue, 19 Nov 2002 17:22:40 +0100, "BenoitM"

> >my install is a fresh one on a newly installed machine...
> >it is known that permission chain doesn't apply with dynamic SQL, but in
my
> >post i only use static SQL , so it should work.



> >> Ownership chains don't apply here, same way they don't apply for
> >> dynamic SQL using Exec() in a sproc. My install is "out of the box"
> >> and dbo owns everything. You have granted explicit permissions on
> >> xp_cmdshell or are always running as dbo, one or the other.

> >> -- Mary
> >> MCW Technologies
> >> http://www.mcwtech.com

> >> On Tue, 19 Nov 2002 11:33:55 +0100, "BenoitM"

> >> >I'm sorry, but what i'm refering to is known as 'ownership chain', and
if
> >> >you do *exactly* what i wrote in my post it should work ...    (it
works
> >for
> >> >me, and i use this often ...)
> >> >If you receive the 'permission denied' error, it's certainly because
the
> >> >owner of 'master' is not the same as 'northwind', so the ownership
chain
> >is
> >> >broken ...   take a look at this :
> >> >http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

> >> > Benoit



> >> >> I'm sorry, but you are incorrect in your assumptions. Try this
simple
> >> >> repro scenario-- create the following stored procedure in Northwind:

> >> >> Create proc CopyTest
> >> >> AS
> >> >> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll
c:\temp\test2.dll',
> >> >>    NO_OUTPUT

> >> >> Grant execute permissions to the public role on CopyTest.

> >> >> Log on to QA as a non-dbo user and attempt to execute CopyTest.
> >> >> Here's the error message you'll see:

> >> >> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
> >> >> EXECUTE permission denied on object 'xp_cmdshell', database
'master',
> >> >> owner 'dbo'.

> >> >> -- Mary
> >> >> MCW Technologies
> >> >> http://www.mcwtech.com

> >> >> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"

> >> >> >hi !

> >> >> >having a stored proc calling another stored proc itself calling
> >cmdshell
> >> >> >should be possible, but there's some traps:
> >> >> >- your first Stored proc (say: SP_A) must belong to 'dbo'
> >> >> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
> >> >> >- you don't have to grant special permissions to the 'cmdshell'
system
> >> >SP,
> >> >> >dbo already has the right to exec it ...
> >> >> >- BUT: when a user call SP_A, itself calling SP_B, itself calling
> >> >cmdshell,
> >> >> >sql will first check if the user have the permission to do so ...
if
> >it
> >> >> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be
> >used,
> >> >> >and cmdshell can be called without compromising security ...
> >> >> >- the only thing to know is that the owner of your DB must be 'dbo'
> >(the
> >> >> >same as the owner of 'Master' - where cmdshell reside ...)
otherwise
> >the
> >> >> >calling chain doesn't work ...

> >> >> >so:

> >> >> >- YOUR_DB -> owner 'dbo'
> >> >> >    - SP_A -> owner 'dbo'   (exec SP_B)
> >> >> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

> >> >> >- MASTER -> owner 'dbo'
> >> >> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

> >> >> >It means that a user can't directly exec 'xp_cmdshell' , but it can
> >exec
> >> >> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

> >> >> >Hope it helps ...



> >> >> >> The problem probably isn't with encryption, but with calling
> >> >> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
> >> >> >> explicitly grant permission to less-privileged accounts, which is
> >not
> >> >> >> a good idea security-wise.

> >> >> >> -- Mary
> >> >> >> MCW Technologies
> >> >> >> http://www.mcwtech.com

> >> >> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

> >> >> >> >Hello!

> >> >> >> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
> >> >> >Procedure?
> >> >> >> >The scenario is, that SP calls another encrypted SP.  Also it
> >executes
> >> >a
> >> >> >> >master..XP_CMDSHELL.  But when I try to call a different
encrypted
> >SP
> >> >> >that
> >> >> >> >does not call any other SPs, ADO.NET does not return any errors
and
> >> >the
> >> >> >SP
> >> >> >> >executed perfectly.  Do I need to set something in ADO.NET for
> >this?

> >> >> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Mary Chipma » Mon, 25 Nov 2002 02:52:38


my point was, which you seem to have missed entirely, that permission
chains don't apply with xp_cmdshell unless you are executing the code
as a dbo or have granted explicit permissions on it to users who are
not sysadmins.

-- Mary
MCW Technologies
http://www.mcwtech.com

On Fri, 22 Nov 2002 09:05:07 +0100, "BenoitM"


>and what should i find regarding permission chain ??? i know that
>xp_cmdshell can be dangerous, but calling it from a Stored proc is safe,
>because a user don't have direct access to xp_cmdshell, but can access the
>SP that will call xp_cmdshell in a specific way. Thus permission chain apply
>here ...
>(by the way what do you think about sp_OACreate ?!? it's evil :), no ? but
>what a chance we have perm chain ! we can use safe stored proc owned by dbo
>that itself call sp_OACreate in a safe way )

>that's my point of view ...



>> Do me a favor and just read up on xp_cmdshell in BOL.

>> -- Mary
>> MCW Technologies
>> http://www.mcwtech.com

>> On Tue, 19 Nov 2002 17:22:40 +0100, "BenoitM"

>> >my install is a fresh one on a newly installed machine...
>> >it is known that permission chain doesn't apply with dynamic SQL, but in
>my
>> >post i only use static SQL , so it should work.



>> >> Ownership chains don't apply here, same way they don't apply for
>> >> dynamic SQL using Exec() in a sproc. My install is "out of the box"
>> >> and dbo owns everything. You have granted explicit permissions on
>> >> xp_cmdshell or are always running as dbo, one or the other.

>> >> -- Mary
>> >> MCW Technologies
>> >> http://www.mcwtech.com

>> >> On Tue, 19 Nov 2002 11:33:55 +0100, "BenoitM"

>> >> >I'm sorry, but what i'm refering to is known as 'ownership chain', and
>if
>> >> >you do *exactly* what i wrote in my post it should work ...    (it
>works
>> >for
>> >> >me, and i use this often ...)
>> >> >If you receive the 'permission denied' error, it's certainly because
>the
>> >> >owner of 'master' is not the same as 'northwind', so the ownership
>chain
>> >is
>> >> >broken ...   take a look at this :
>> >> >http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

>> >> > Benoit



>> >> >> I'm sorry, but you are incorrect in your assumptions. Try this
>simple
>> >> >> repro scenario-- create the following stored procedure in Northwind:

>> >> >> Create proc CopyTest
>> >> >> AS
>> >> >> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll
>c:\temp\test2.dll',
>> >> >>    NO_OUTPUT

>> >> >> Grant execute permissions to the public role on CopyTest.

>> >> >> Log on to QA as a non-dbo user and attempt to execute CopyTest.
>> >> >> Here's the error message you'll see:

>> >> >> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
>> >> >> EXECUTE permission denied on object 'xp_cmdshell', database
>'master',
>> >> >> owner 'dbo'.

>> >> >> -- Mary
>> >> >> MCW Technologies
>> >> >> http://www.mcwtech.com

>> >> >> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"

>> >> >> >hi !

>> >> >> >having a stored proc calling another stored proc itself calling
>> >cmdshell
>> >> >> >should be possible, but there's some traps:
>> >> >> >- your first Stored proc (say: SP_A) must belong to 'dbo'
>> >> >> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
>> >> >> >- you don't have to grant special permissions to the 'cmdshell'
>system
>> >> >SP,
>> >> >> >dbo already has the right to exec it ...
>> >> >> >- BUT: when a user call SP_A, itself calling SP_B, itself calling
>> >> >cmdshell,
>> >> >> >sql will first check if the user have the permission to do so ...
>if
>> >it
>> >> >> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will be
>> >used,
>> >> >> >and cmdshell can be called without compromising security ...
>> >> >> >- the only thing to know is that the owner of your DB must be 'dbo'
>> >(the
>> >> >> >same as the owner of 'Master' - where cmdshell reside ...)
>otherwise
>> >the
>> >> >> >calling chain doesn't work ...

>> >> >> >so:

>> >> >> >- YOUR_DB -> owner 'dbo'
>> >> >> >    - SP_A -> owner 'dbo'   (exec SP_B)
>> >> >> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

>> >> >> >- MASTER -> owner 'dbo'
>> >> >> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

>> >> >> >It means that a user can't directly exec 'xp_cmdshell' , but it can
>> >exec
>> >> >> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

>> >> >> >Hope it helps ...



>> >> >> >> The problem probably isn't with encryption, but with calling
>> >> >> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
>> >> >> >> explicitly grant permission to less-privileged accounts, which is
>> >not
>> >> >> >> a good idea security-wise.

>> >> >> >> -- Mary
>> >> >> >> MCW Technologies
>> >> >> >> http://www.mcwtech.com

>> >> >> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"

>> >> >> >> >Hello!

>> >> >> >> >Can someone tell me why ADO.NET cannot find an Encrypted Stored
>> >> >> >Procedure?
>> >> >> >> >The scenario is, that SP calls another encrypted SP.  Also it
>> >executes
>> >> >a
>> >> >> >> >master..XP_CMDSHELL.  But when I try to call a different
>encrypted
>> >SP
>> >> >> >that
>> >> >> >> >does not call any other SPs, ADO.NET does not return any errors
>and
>> >> >the
>> >> >> >SP
>> >> >> >> >executed perfectly.  Do I need to set something in ADO.NET for
>> >this?

>> >> >> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Benoit » Wed, 27 Nov 2002 00:31:34


i'm sorry, but i don't agree with you, why xp_cmdshell would be a special
case ??? I used this technique often, in different network config for my
customers, and it always worked as i expected: sysadmin are able to run
directly xp_cmdshell (and do whatever they want...), regular users can't
(it's the default, and i NEVER grant exec on xp_cmdshell to anyone except
sysadmin). If i create a SP as dbo, and that SP calls xp_cmdshell, and if i
grant execute permission on that SP to regular users, they can execute it
without any problem ...

Make a try on Northwind, you'll see it works as i say ...  If you receive a
permission error on xp_cmdshell in QA, it's certainly because the owner of
Northwind is not the same as the owner of 'Master'...
If i remember, Northwind is owned by the user that performed the SQL Server
install, Master is always owned by 'sa'... You'll have to do a
sp_changedbowner on Northwind...  Check it, and tell me if it works !

"Mary Chipman" <mc...@nomail.please> a crit dans le message news:
p0gvtuk4p9lr3kq3m3uoeqmjhrlo8ht...@4ax.com...

> my point was, which you seem to have missed entirely, that permission
> chains don't apply with xp_cmdshell unless you are executing the code
> as a dbo or have granted explicit permissions on it to users who are
> not sysadmins.

> -- Mary
> MCW Technologies
> http://www.mcwtech.com

> On Fri, 22 Nov 2002 09:05:07 +0100, "BenoitM"
> <Benoit_M...@hotmail.Com> wrote:

> >and what should i find regarding permission chain ??? i know that
> >xp_cmdshell can be dangerous, but calling it from a Stored proc is safe,
> >because a user don't have direct access to xp_cmdshell, but can access
the
> >SP that will call xp_cmdshell in a specific way. Thus permission chain
apply
> >here ...
> >(by the way what do you think about sp_OACreate ?!? it's evil :), no ?
but
> >what a chance we have perm chain ! we can use safe stored proc owned by
dbo
> >that itself call sp_OACreate in a safe way )

> >that's my point of view ...

> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >news:2a5rtu8hmcf4ndnampfffrimu33qqcppit@4ax.com...
> >> Do me a favor and just read up on xp_cmdshell in BOL.

> >> -- Mary
> >> MCW Technologies
> >> http://www.mcwtech.com

> >> On Tue, 19 Nov 2002 17:22:40 +0100, "BenoitM"
> >> <Benoit_M...@hotmail.Com> wrote:

> >> >my install is a fresh one on a newly installed machine...
> >> >it is known that permission chain doesn't apply with dynamic SQL, but
in
> >my
> >> >post i only use static SQL , so it should work.

> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >news:ntkktu4kvbrm56rsejrfj76ja5ee2ta7pd@4ax.com...
> >> >> Ownership chains don't apply here, same way they don't apply for
> >> >> dynamic SQL using Exec() in a sproc. My install is "out of the box"
> >> >> and dbo owns everything. You have granted explicit permissions on
> >> >> xp_cmdshell or are always running as dbo, one or the other.

> >> >> -- Mary
> >> >> MCW Technologies
> >> >> http://www.mcwtech.com

> >> >> On Tue, 19 Nov 2002 11:33:55 +0100, "BenoitM"
> >> >> <Benoit_M...@hotmail.Com> wrote:

> >> >> >I'm sorry, but what i'm refering to is known as 'ownership chain',
and
> >if
> >> >> >you do *exactly* what i wrote in my post it should work ...    (it
> >works
> >> >for
> >> >> >me, and i use this often ...)
> >> >> >If you receive the 'permission denied' error, it's certainly
because
> >the
> >> >> >owner of 'master' is not the same as 'northwind', so the ownership
> >chain
> >> >is
> >> >> >broken ...   take a look at this :
> >> >> >http://support.microsoft.com/default.aspx?scid=KB;en-us;q272424

> >> >> > Benoit

> >> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >> >news:o81ituknaqajrnrd5cc76mjj625c90u2em@4ax.com...
> >> >> >> I'm sorry, but you are incorrect in your assumptions. Try this
> >simple
> >> >> >> repro scenario-- create the following stored procedure in
Northwind:

> >> >> >> Create proc CopyTest
> >> >> >> AS
> >> >> >> EXEC master.dbo.xp_cmdshell 'copy c:\temp\test.dll
> >c:\temp\test2.dll',
> >> >> >>    NO_OUTPUT

> >> >> >> Grant execute permissions to the public role on CopyTest.

> >> >> >> Log on to QA as a non-dbo user and attempt to execute CopyTest.
> >> >> >> Here's the error message you'll see:

> >> >> >> Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 3
> >> >> >> EXECUTE permission denied on object 'xp_cmdshell', database
> >'master',
> >> >> >> owner 'dbo'.

> >> >> >> -- Mary
> >> >> >> MCW Technologies
> >> >> >> http://www.mcwtech.com

> >> >> >> On Mon, 18 Nov 2002 09:36:25 +0100, "BenoitM"
> >> >> >> <Benoit_M...@hotmail.Com> wrote:

> >> >> >> >hi !

> >> >> >> >having a stored proc calling another stored proc itself calling
> >> >cmdshell
> >> >> >> >should be possible, but there's some traps:
> >> >> >> >- your first Stored proc (say: SP_A) must belong to 'dbo'
> >> >> >> >- your second Stored proc (say: SP_B) must also belong to 'dbo'
> >> >> >> >- you don't have to grant special permissions to the 'cmdshell'
> >system
> >> >> >SP,
> >> >> >> >dbo already has the right to exec it ...
> >> >> >> >- BUT: when a user call SP_A, itself calling SP_B, itself
calling
> >> >> >cmdshell,
> >> >> >> >sql will first check if the user have the permission to do so
...
> >if
> >> >it
> >> >> >> >doesn't it will exec SP_B using the owner of SP_A. So 'dbo' will
be
> >> >used,
> >> >> >> >and cmdshell can be called without compromising security ...
> >> >> >> >- the only thing to know is that the owner of your DB must be
'dbo'
> >> >(the
> >> >> >> >same as the owner of 'Master' - where cmdshell reside ...)
> >otherwise
> >> >the
> >> >> >> >calling chain doesn't work ...

> >> >> >> >so:

> >> >> >> >- YOUR_DB -> owner 'dbo'
> >> >> >> >    - SP_A -> owner 'dbo'   (exec SP_B)
> >> >> >> >    - SP_B -> owner 'dbo'   (exec master..xp_cmdshell ...)

> >> >> >> >- MASTER -> owner 'dbo'
> >> >> >> >    - xp_cmshell -> 'dbo' has 'exec permission' already !

> >> >> >> >It means that a user can't directly exec 'xp_cmdshell' , but it
can
> >> >exec
> >> >> >> >'SP_A' & 'SP_B' if you grant him the permission to di it ...

> >> >> >> >Hope it helps ...

> >> >> >> >"Mary Chipman" <mc...@nomail.please> wrote in message
> >> >> >> >news:spmctukcvc79sur3bmfrc3p73nntuuglh1@4ax.com...
> >> >> >> >> The problem probably isn't with encryption, but with calling
> >> >> >> >> xp_cmdshell, which defaults to sysadmins-only. You'd need to
> >> >> >> >> explicitly grant permission to less-privileged accounts, which
is
> >> >not
> >> >> >> >> a good idea security-wise.

> >> >> >> >> -- Mary
> >> >> >> >> MCW Technologies
> >> >> >> >> http://www.mcwtech.com

> >> >> >> >> On Sat, 16 Nov 2002 16:55:12 +0800, "Ramil"
> >> >> >> >> <ram...@comtechsolutions.com> wrote:

> >> >> >> >> >Hello!

> >> >> >> >> >Can someone tell me why ADO.NET cannot find an Encrypted
Stored
> >> >> >> >Procedure?
> >> >> >> >> >The scenario is, that SP calls another encrypted SP.  Also it
> >> >executes
> >> >> >a
> >> >> >> >> >master..XP_CMDSHELL.  But when I try to call a different
> >encrypted
> >> >SP
> >> >> >> >that
> >> >> >> >> >does not call any other SPs, ADO.NET does not return any
errors
> >and
> >> >> >the
> >> >> >> >SP
> >> >> >> >> >executed perfectly.  Do I need to set something in ADO.NET
for
> >> >this?

> >> >> >> >> >Any help is very much appreciated.  Thanks!

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Mary Chipma » Wed, 27 Nov 2002 01:13:03


You know, this thread is getting to be really stupid. Go back and
re-read the repro steps I posted at the beginning. Signing out now for
good,

-- Mary
MCW Technologies
http://www.mcwtech.com

On Mon, 25 Nov 2002 16:31:34 +0100, "BenoitM"


>i'm sorry, but i don't agree with you, why xp_cmdshell would be a special
>case ??? I used this technique often, in different network config for my
>customers, and it always worked as i expected: sysadmin are able to run
>directly xp_cmdshell (and do whatever they want...), regular users can't
>(it's the default, and i NEVER grant exec on xp_cmdshell to anyone except
>sysadmin). If i create a SP as dbo, and that SP calls xp_cmdshell, and if i
>grant execute permission on that SP to regular users, they can execute it
>without any problem ...

>Make a try on Northwind, you'll see it works as i say ...  If you receive a
>permission error on xp_cmdshell in QA, it's certainly because the owner of
>Northwind is not the same as the owner of 'Master'...
>If i remember, Northwind is owned by the user that performed the SQL Server
>install, Master is always owned by 'sa'... You'll have to do a
>sp_changedbowner on Northwind...  Check it, and tell me if it works !

 
 
 

(Encrypted) "Stored Procedure does not exist" -- error returned when called by ADO.NET

Post by Benoit » Thu, 28 Nov 2002 20:09:25


i reread your repro steps, and done exactly what you said...

It worked ! i have no 'EXECUTE permission denied on object 'xp_cmdshell',
database 'master', owner 'dbo'.' error in QA when logged as a simple user...

BUT, if i change the owner of the Northwind database i have this error... (i
changed it to Machinename\Administrator, it was 'sa' before ...) so, we have
what i said previously: the permission chain was broken because the owner of
the Nortwhind DB was not the same as the owner of 'Master' ....

BTW: there's nothing stupid talking about different point of view, someone
is wrong on this one, i want to know if it me ... perhaps i missed
something, if it's the case i would like to know what ...


Quote:> You know, this thread is getting to be really stupid. Go back and
> re-read the repro steps I posted at the beginning. Signing out now for
> good,

> -- Mary
> MCW Technologies
> http://www.mcwtech.com