SQL Server Blocked Access to Procedure 'Sys.Sp_Oacreate' of Component 'Ole Automation Procedures'

How do I use a SP in SQL Server 2014 to call a external XML source?

OK Fully tested the following SP which pulls back your XML Data:

DROP PROCEDURE [dbo].[proc_CallWebService]
GO
CREATE PROCEDURE [dbo].[proc_CallWebService]
AS
    IF OBJECT_ID('tempdb..#xml') IS NOT NULL DROP TABLE #xml
    CREATE TABLE #xml ( Data xml )

    DECLARE @obj INT
    DECLARE @sURL VARCHAR(200)
    DECLARE @response varchar(max)

    SET @sURL = 'http://www.floatrates.com/daily/usd.xml'

    DECLARE @XML xml
    DECLARE @Result int 
    DECLARE @HTTPStatus int 
    DECLARE @ErrorMsg varchar(MAX)

    EXEC @Result = sp_OACreate 'MSXML2.XMLHttp', @Obj OUT 
    EXEC @Result = sp_OAMethod @Obj, 'open', NULL, 'GET', @sURL, false
    EXEC @Result = sp_OAMethod @Obj, 'setRequestHeader', NULL, 'Content-Type', 'text/html'
    EXEC @Result = sp_OAMethod @Obj, send, NULL, ''
    EXEC @Result = sp_OAGetProperty @Obj, 'status', @HTTPStatus OUT 
    --EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml', --@Response OUT

    INSERT #xml ( Data )
   EXEC @Result = sp_OAGetProperty @Obj, 'responseXML.xml'--, @Response OUT 
    SELECT * from #xml
RETURN  

The way i got around it, was to use the #xml hash temp table to retrieve the response, and to select the response into it.

This is because of the way that MIME types and the downloading of the file contents is actually completed, originally it was only bringing back a binary value, with no response header, and that was because it wasnt actually completeting the full action of doing the conversion to the XML

-- For anyone that needs the below, im keeping it as part of this comment

When i try and run your SP i get the following Error:
But it does return me back a response of NULL.

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', search for 'Ole Automation Procedures' in SQL Server Books Online.

To Fix this issue,

Right click on your
Server Instance -> then click Facets
Then select "Server Configuration" under Facets
and in the Facet Properties change OleAutomationEnabled = true

OR if that doesnt work use this in a Query Window inside SQL Server Management Tools.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

The documentation for the above statement is here: https://msdn.microsoft.com/en-US/library/ms191188.aspx

SQL Server 2014 - Error 15281 on Linked Excel File

The most likely answer is Ole Automation Procedures is not turned on. You can check by running:

EXEC sp_configure 'Ole Automation Procedures';  

This will return something along the lines of:

name                        minimum maximum config_value    run_value
Ole Automation Procedures   0       1       0               0

If the config_value is 0 you need to enable it.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'Ole Automation Procedures', 1;  
GO  
RECONFIGURE;  
GO 

Source.

Sending SMS from SQL Server 2014 Issu Identification

You didn't supply the error message returned by SQL server, but this sure sounds like a security issue. DBCC FREEPROCCACHE requires Alter server state permission. Additionally the sp_oa... methods need to be enabled and the users must have access to run them (SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures'). Lastly, I'm not sure why you are running the DBCC commands in the stored procedure. FREEPROCCACHE has limitations but you can call it with options (https://msdn.microsoft.com/en-us/library/ms174283.aspx). DROPCLEANBUFFERS is really meant to test query performance. If your DBCC commands are there just for testing, remove them from the stored procedure and put them right after the call to the stored procedure.

Why doesn't xp_cmdshell work in SQL Server 2012?

This has been disabled out of the box starting with SQL Server 2005, when they introduced the Surface Area Configuration Tool, in an effort to make SQL Server more secure by default. That tool has since been retired, but you can still control the behavior using sp_configure. An example is shown on MSDN:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

(I also blogged about this many years ago.)

The reason is that this is a potential security hole. If you allow SQL Server to execute xp_cmdshell, then they can theoretically send any operating system command there, bypassing any and all security you thought you had. This is especially problematic when the SQL Server service account and/or the proxy account have been elevated to sysadmin or other levels because that's easier than explicitly defining only the exact things they should be able to do.

Rather than enable it and disable it to support command-line interaction, a popular way to expose operating system functionality while still having some control over security is to implement the OS-level functionality you need using SQL-CLR. Here is a good starting point for accessing the file system with CLR (however if you search around you will find much more modern and exhaustive approaches).

---

Related Topics