sp_OACreate returns Access is Denied
-
Hello everybody
What i am trying to do is instanciate a com object and call a mothod from id.
So i've written the following code in vb (ActiveX DLL):
The project name is TestCom and the classname is cTestComClass
Public Function DoSomething() as int
DoSomething=1
end function
And the TSQL code is
Create Procedure spTestCom
AS
DECLARE @HR INT
DECLARE @RETVAL INT
EXEC @HR=sp_OACreate TestCom.cTestComClass', @retval output
IF @hr <> 0
BEGIN
EXEC @hr = sp_OAGetErrorInfo @retval, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @out = ' Source: ' + @source
PRINT @out
SELECT @out = ' Description: ' + @description
PRINT @out
END
@hr allways returns -2147024891 (access is denied)
it works fine with WORD or EXCEL or SQLDMO (@hr=0).
Can somebody help me?
-
1) Did you recently install SP3?
2) Did you disable "Cross Database Ownership Chaining" during the SP3 install?
3) Is your proc created with the owner as dbo?
4) Is the owner of your database sa?
If the answers to all the above questions is yes, then you have been caught
by a new security fix of SP3. It is a good fix, you need to be aware of it.
It disables cross database ownership chaining (by default). "Ownership
Chaining" is too lengthy a subject for me to get into here, please look it
up in BOL. To fix your problem you can turn on "Cross Database Ownership
Chaining" at either the server level OR teh database level. To see this new
option open the server or database properties, and go to the security tab,
and you will see a new checkbox on it. I suggest reading up on the subject
before deciding what to do. As I see it, there are several options for you. Here
are a couple :
1) Turn on chaining at the server level (not recommended)
2) Turn on chaining at the database level, easiest solution
3) Grant public the right to execute the procedures in master that you need to
run
4) Create the procedures as a dummy user, and explicitly grant him the rights to
execute master stored procedures that needed.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
This isn't related to cross-database ownership chaining. The system stored procedures are first looked for in master and executed there if found. Take for instance, sp_help. The stored procedure sp_help is located only in master, but by default, the public role can execute it. And this means in any database. It's one of the behaviors of the sp_ stored procedures (see my article on stored procedure and caching or Antares' article on sp_ for more explanation).
Dimitris, you said sp_OACreate works fine with the Word and Excel objects, so it looks like the issue at hand is your COM object. Do you receive the same error when you instantiate the object through another VB program (not in the same project)?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
When i instanciate the com from another vb project works fine.Either early bound or late bound.I am checking now if i can do it throught .net and when i build the dll i receive the message COM Interop registration failed. Access to the registry key is denied.
Thanks for your support.
-
By default public is not granted rights to execute sp_OACreate, and if
cross database ownership chaining is turned off, you may get access denied when
executing database procs not in your current database with SP3 installed, even
when the owner of your proc is aliased to sa. You are right, if this proc can
be executed from other apps then this is not the issue. But, are the other apps
connecting as sa? and is the VB one as well? If the VB one is not and the others
are, then it may indeed be cross database ownership chaining.
Try this in QA as sa :
--impersonate a user who is only member of public
SETUSER <some user who is only member of public>
--with cross db ownership turned off this will FAIL
EXEC spTestCom
--revert back to sa
SETUSER
--will work
EXEC spTestCom
--turn on the SERVER 'Cross DB Ownership Chaining'
EXEC master..sp_configure 'Cross DB Ownership Chaining', 1
RECONFIGURE
--impersonate a user who is only member of public
SETUSER <some user who is only member of public>
--with cross db ownership turned on this will PASS
EXEC spTestCom
--set it back to off
EXEC master..sp_configure 'Cross DB Ownership Chaining', 0
RECONFIGURE
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Have you looked at the security settings using DCOMCNFG to ensure that Everyone can access your newly created object? I'm not sure the implications of trying this with .net because that doesn't use COM...I have successfully built and called COM objects using VFP, so I think this is a configuration issue. I assume you're building on the same server that SQL is installed on?
Regards
Simon
-
From the SP3 readme :
quote:
5.1.11 Cross-Database Ownership ChainingIntroduced in SP3
This service pack provides new options for turning cross-database ownership chaining on and off. During Setup, the Backward Compatibility Checklist dialog displays an option for configuring cross-database ownership chaining. By default, Setup turns off cross-database ownership chaining for all user databases. You can enable cross-database ownership chaining for all databases. For more information, see Backward Compatibility Checklist dialog box
Note Enabling cross-database ownership chaining for all databases is not recommended.
After installation, you can use the following methods to turn cross-database ownership chaining on and off for all databases in the instance:
Use the new Cross DB Ownership Chaining argument of the sp_configure system stored procedure.
Use the Allow Cross-Database Ownership Chaining option on the Security tab of the SQL Server Properties dialog box in Enterprise Manager.
Use the SQLServer.Configuration.ConfigValues Database Management Objects (DMO) collection. When the SQLServer object references an instance of SQL Server 2000 SP3 or later, this collection contains a ConfigValue object named Cross DB Ownership Chaining.
If cross-database ownership chaining is turned off for the instance, you can configure it for individual databases. Use the following methods to turn cross-database ownership chaining on and off for a database:
Use the new db chaining option of the sp_dboption system stored procedure.
Use the Allow Cross-Database Ownership Chaining option on the Options tab of the Database Properties dialog box in Enterprise Manager.
Use the DBChaining property of the DBOption2 DMO object.
Note If you previously enabled cross-database ownership chaining on a pre-release version of SP3 (earlier than build 8.00.760), you must enable it again after installing the release version of SP3.
For more information, click the Help button on the Backwards Compatibility Checklist page when you run Setup, download the updated edition of SQL Server 2000 Books Online, or see Knowledge Base article 810474.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Tim, the reason I didn't delve into cross-database ownership chains is because he's apparently calling the stored procedure using an account with privilege (since he's able to instantiate Word, Excel, and DMO objects). I didn't want to take the troubleshooting down a rabbit hole.
Also, should it be a permissions problem within SQL Server, he'd have gotten the following error message back:
Server: Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
EXECUTE permission denied on object 'sp_OACreate', database 'master', owner 'dbo'.The root of the problem seems to be security on the COM object itself and I was trying to help Dimitris determine if the object was behaving this way in all cases or just within SQL Server. Hopefully Simon has hit upon the solution.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
Edited by - bkelley on 01/31/2003 09:48:30 AM
K. Brian Kelley
@kbriankelley -
sjcsystems was right. I ran DCOMCNFG at the applications list i select my com component then i click properties and at the security tab i choose use custom access permissions,use custome launch permissions and finally at the identity tab i choose "the interactive user".
Thanks a lot everybody.
-
Lesson learned : always read the fine print. Misunderstood the very end of his first post. K, you said access to a registry key was denied? Take a look at SysInternals Regmon to help you determine what key. Sometimes key security can get changed causing problems creating classes as the SCM is not able to look up the progid. Not sure if this helps either, but I apologize if I have you confused you, or led you down a wrong path Dimitris.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
You don't have to apologize.You did not confuse me at all.It was very clear that the "error" did not came from sql but from the registration. I didn't know where to search. Now i have learned.
Thanks again.
-
Makes a change to be right!! (Don't refer to previous posts for proof of that). I'm interested in this cross db ownership chaining. Does that mean that if I call a procedure in a different db using a 4 part name that it won't work unless this is turned on? I can see an issue with something like an ISP wanting to stop cross db chaining, but isn't this going to break a lot of multi-db systems?
Regards
Simon
-
Congrats, Simon!
Though I think you are more right than you give yourself credit for. I'm hoping to put together an article on cross-database ownership chaining in the next week or two and explore it in some detail.K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
What it does is force you to be more careful with your security. As most
databases are created as dbo, and the owner is typically sa, then the default
rights that MSFT has placed upon all of the procedures in MASTER are
ineffective. You can still emulate the old functionality by turning it on at the
server level (in master). If the option is turned off in master you can turn it
on in an individual basis from db to db.
See below urls for more info as it is a really long subject with many implications :
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b301299
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp
http://support.microsoft.com/?kbid=810474
http://www.microsoft.com/sql/downloads/2000/SP3readme.htm
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_4nxu.asp
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food -
Oops, posted my last post before I saw brians reply. Look forward to your article as this is going to have many design implications on databases, more than I can possibly forsee as of right now.
Tim C.
//Will write code for food
One Windows to rule them all, One Windows to find them,
One Windows to bring them all and in the darkness bind them
In the Land of Microsoft where the Shadows lie.
Tim C //Will code for food
Though I think you are more right than you give yourself credit for. I'm hoping to put together an article on cross-database ownership chaining in the next week or two and explore it in some detail.Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply