Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Methods For Converting a Stored Procedure Expand / Collapse
Author
Message
Posted Thursday, October 29, 2009 9:39 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 4:02 AM
Points: 648, Visits: 1,874
Ramon-218872 (10/29/2009)
Hi to all,

I'm trying to use the code, because it's that I need, but I get the error:

Msg 7357, Level 16, State 2, Procedure sp_ConvProc2View, Line 27
Cannot process the object "SET FMTONLY OFF EXEC sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.


Some body can help me?

Thanks in advance...


Do you have exec permssions on the stored procedure with the name sp_ControlStock?

i.e. if you type 'EXEC sp_ControlStock' (with no parameters) does that return you some rows?
Post #810909
Posted Thursday, October 29, 2009 10:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:42 AM
Points: 10, Visits: 75
Yes I can exec sp_ControlStock without problems...

I make some tests:

exec SGANPS.dbo.sp_ControlStock

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'SELECT *
FROM SGANPS.dbo.ARTICULOS');

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec SGANPS.dbo.sp_ControlStock');

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec SGANPS.dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Some suggestion?

Thanks in advance
Post #810949
Posted Thursday, October 29, 2009 10:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
I would avoid such approaches as overly complex. I don't even want to think if this is secure or not. It is so very much easier to just reuse code wrapped in UDFs, so why would anyone want to go for a much more complex approach?
Post #810973
Posted Thursday, October 29, 2009 10:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Ramon-218872 (10/29/2009)
Yes I can exec sp_ControlStock without problems...

I make some tests:

exec SGANPS.dbo.sp_ControlStock

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'SELECT *
FROM SGANPS.dbo.ARTICULOS');

It works

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec SGANPS.dbo.sp_ControlStock');

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec SGANPS.dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Some suggestion?

Thanks in advance


I think I'm beginning to confuse myself. However while your credentials are passed along to the '.' linked version of your server, will the linked server acknowledge them for that procedure? Try -
SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec .dbo.sp_who');



Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #810979
Posted Thursday, October 29, 2009 10:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:42 AM
Points: 10, Visits: 75
Hi,

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec .dbo.sp_who');

It works

But:

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec .dbo.sp_ControlStock');

Gets:

Msg 7357, Level 16, State 2, Line 1
Cannot process the object "exec .dbo.sp_ControlStock". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Post #810989
Posted Thursday, October 29, 2009 11:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
Ramon,
I told you I was confusing myself.
USE TEST
GO

CREATE PROCEDURE dbo.sp_ControlStock AS SELECT 'ONE', 'TWO', 'THREE'
GO

SELECT *
FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;',
'exec TEST.dbo.sp_ControlStock');
GO

That works fine for me. Maybe you should start over, or go another route.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #811015
Posted Thursday, October 29, 2009 11:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:42 AM
Points: 10, Visits: 75
I'm not sure but perpahs the problem it's that in sp_ControlStock I'm using temporal tables or OPENQUERY statments because if I use a "simple" store procedure, now it works...

Post #811037
Posted Thursday, October 29, 2009 11:30 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
That explains a lot. Like
linked server "(null)"

It sounds like your OPENROWSET statement within the procedure isn't passing suitable credentials.

Check BOL for Linked Server Security and Delegates regarding double hops.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #811046
Posted Thursday, October 29, 2009 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 7:15 AM
Points: 7, Visits: 43
The security implications of this and lack of discussion and warning on them really bothers me.
Post #811058
Posted Thursday, October 29, 2009 3:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 9, 2012 2:48 PM
Points: 493, Visits: 636
It's the double hop issue that always prevents me from using the

Trusted_Connection=yes

However, if the account that runs your SQL Server instance has the ability to register its own SPN as is explained at

http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

then you should find that the connection you have open is using Kerberos authentication with the following query:

select auth_scheme from sys.dm_exec_connections
where session_id = @@Spid

If it is NTLM then the service account of SQL probably doesn't have the permission to register it's own SPN which causes security to collapse to NTLM which has the affect of disallowing the SQL instance to trust a security token passed to it. The double hop then becomes impossible and the security context is always lost on the second hop. If you are having trouble with the openrowset across servers with error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

then check out http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

This only applies to trusted because there is no security token passed with SQL authentication.
Post #811240
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse