Siddharth Chauhan
asked on
Not able to execute OLE Automation SPs. Execute permission denied.
Not sure what has went wrong but I'm not able to execute OLE Automation Procs like "sp_OACreate", "sp_OAMethod" etc. which was working fine for past week.
The account\login I'm using has "SYSADMIN" privilege. Also, Configuration values for "Ole Automation Procedures" has already been set to 1.
Tried with several SQL as well as Windows Login with "sysadmin" permissions but to no avail. Even with sysadmin account getting the following error:
Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1
The EXECUTE permission was denied on the object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'.
Even tried with adding the "sysadmin" accounts as DBO on master and giving "execute" permissions to all above Extended SPs (knowing it won't going to help much).
Command to Rebuild these Ex-SPs is also failing.
exec sp_addextendedproc N'sp_OACreate', 'odsole70.dll'
exec sp_addextendedproc sp_OADestroy, 'odsole70.dll'
exec sp_addextendedproc sp_OAGetErrorInfo, 'odsole70.dll'
exec sp_addextendedproc sp_OAGetProperty, 'odsole70.dll'
exec sp_addextendedproc sp_OAMethod, 'odsole70.dll'
exec sp_addextendedproc sp_OASetProperty, 'odsole70.dll'
exec sp_addextendedproc sp_OAStop, 'odsole70.dll'
Error:
Msg 208, Level 16, State 77, Procedure sp_addextendedproc, Line 42
Invalid object name ' '.
No changes were made on SQL Level however few changes were made on OS level like, Creating and deleting SPN for the server in domain (for SQL Service Account), adding and removing SQL Service Account from following local windows account group.
Currently there is no SPN exists for the server and the SQL Services are running with windows domain account which is administrator on server but not part of any other group.
Kindly assist me as I'm neither able to find anything related to it on web nor any steps in this regard are working.
The account\login I'm using has "SYSADMIN" privilege. Also, Configuration values for "Ole Automation Procedures" has already been set to 1.
Tried with several SQL as well as Windows Login with "sysadmin" permissions but to no avail. Even with sysadmin account getting the following error:
Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAMethod, Line 1
The EXECUTE permission was denied on the object 'sp_OAMethod', database 'mssqlsystemresource', schema 'sys'.
Msg 229, Level 14, State 5, Procedure sp_OAGetProperty, Line 1
The EXECUTE permission was denied on the object 'sp_OAGetProperty', database 'mssqlsystemresource', schema 'sys'.
Even tried with adding the "sysadmin" accounts as DBO on master and giving "execute" permissions to all above Extended SPs (knowing it won't going to help much).
Command to Rebuild these Ex-SPs is also failing.
exec sp_addextendedproc N'sp_OACreate', 'odsole70.dll'
exec sp_addextendedproc sp_OADestroy, 'odsole70.dll'
exec sp_addextendedproc sp_OAGetErrorInfo, 'odsole70.dll'
exec sp_addextendedproc sp_OAGetProperty, 'odsole70.dll'
exec sp_addextendedproc sp_OAMethod, 'odsole70.dll'
exec sp_addextendedproc sp_OASetProperty, 'odsole70.dll'
exec sp_addextendedproc sp_OAStop, 'odsole70.dll'
Error:
Msg 208, Level 16, State 77, Procedure sp_addextendedproc, Line 42
Invalid object name ' '.
No changes were made on SQL Level however few changes were made on OS level like, Creating and deleting SPN for the server in domain (for SQL Service Account), adding and removing SQL Service Account from following local windows account group.
Currently there is no SPN exists for the server and the SQL Services are running with windows domain account which is administrator on server but not part of any other group.
Kindly assist me as I'm neither able to find anything related to it on web nor any steps in this regard are working.
Vitor Montalvão
By the error message the SP's aren't in the master database but in 'mssqlsystemresource'.
ASKER
Thanks Vitor for your response. But if I'm not mistaken, "mssqlsystemresource" refers to MS SQL's resource database cause I can't find it in the database list.
I'm afraid you may have a corruption in your master database.
Can you find those SP's in the master database?
Can you find those SP's in the master database?
ASKER
Though I can still see all these SPs in master but I can't ignore your point that they may've got corrupted somehow. Anyway you think to ensure this or rebuild them as I've already tried rebuilding as well with help of sp_addextendedproc SP (even with DAC connection) but it dint helped.
If your master database is corrupted then you only have two options:
1. Restore last known good backup
2. Rebuild master database
ASKER
I'm not sure if master is actually corrupt cause the instance is accessible and all other functionality and I also was able to recycle the instance twice without any hiccups. Is there anything else I should do before going in for this extreme step as this is a PROD box.
Not corruption as you can't connect to it but as you can't find some objects.
You could also run a DBCC CHECKDB on master database. I don't have more ideas on this :(
You could also run a DBCC CHECKDB on master database. I don't have more ideas on this :(
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Seems that there are more information missing.
Who runs the SQL Server service? A local account?
The EXECUTES AS command is set to a domain user or a SQL Server user?
If it is possible post here the code.
Who runs the SQL Server service? A local account?
The EXECUTES AS command is set to a domain user or a SQL Server user?
If it is possible post here the code.
ASKER
Sorry Vitor to miss those details. All the required details are as follows:
SQL Server is running with a dedicated SQL Service Domain Account.
Created a new Domain Service Account for this purpose and the same was use in "execute as" clause of the SP.
The slightly obfuscated code is below:
=============
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_UserWrittenSP]
(
@var1 VARCHAR(MAX),
@var2 VARCHAR(MAX),
@var3 VARCHAR(4000) OUT
)
WITH EXECUTE AS 'OurDomain\SpecialServiceA
AS
BEGIN
DECLARE @XMLResponse XML
DECLARE @obj INT
DECLARE @sUrl NVARCHAR(200)
SET @sUrl = 'Custom URL Goes Here;Parameter1='+@var1+';
EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'POST', @sUrl, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ReturnParam', @XMLResponse OUT
SELECT @var3 = CONVERT(VARCHAR(4000), @XMLResponse, 1)
END
GO
========
The service account mentioned in SP has "sysadmin" and "DBO" on both the user database where this SP was created and on "master" DB as well.
SQL Server is running with a dedicated SQL Service Domain Account.
Created a new Domain Service Account for this purpose and the same was use in "execute as" clause of the SP.
The slightly obfuscated code is below:
=============
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_UserWrittenSP]
(
@var1 VARCHAR(MAX),
@var2 VARCHAR(MAX),
@var3 VARCHAR(4000) OUT
)
WITH EXECUTE AS 'OurDomain\SpecialServiceA
AS
BEGIN
DECLARE @XMLResponse XML
DECLARE @obj INT
DECLARE @sUrl NVARCHAR(200)
SET @sUrl = 'Custom URL Goes Here;Parameter1='+@var1+';
EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
EXEC sp_OAMethod @obj, 'Open', NULL, 'POST', @sUrl, false
EXEC sp_OAMethod @obj, 'send'
EXEC sp_OAGetProperty @obj, 'ReturnParam', @XMLResponse OUT
SELECT @var3 = CONVERT(VARCHAR(4000), @XMLResponse, 1)
END
GO
========
The service account mentioned in SP has "sysadmin" and "DBO" on both the user database where this SP was created and on "master" DB as well.
The service account mentioned in SP has "sysadmin" and "DBO" on both the user database where this SP was created and on "master" DB as well.Can you test without the sysadmin role?
ASKER
I get this solution accidentally when I was trying to implement a work-around for the same. When I tried the combination of permissions mentioned above I was able to overcome the situation and things started working again. All other permission combinations which I tried earlier failed to work.