I have a need to enable to the Ole Automation Procedures Server Configuration Option on my SQL Server, but was wondering what specific kind of security risks do I incur by doing so?
What is an example of how the security can be exploited with this option enabled?
I don't think I would look at it in terms of exploitation. It's very similar to (if not the same security issue as) xp_cmdshell. These are wide open in terms of what you can execute if they are enabled, and for processes that reach outside of SQL Server, they use the security context of the service account running SQL Server (although xp_cmdshell does at least have the option to set up a proxy account for non-sysadmin users).
But, you need to be in the sysadmin instance-level role in order to execute the sp_OA* procs (or be given explicit EXECUTE permission), so you can at least keep all uses of it within the confines of stored procedures that use Module Signing to get the elevated permission (yes, specifying WITH EXECUTE AS N'dbo' also works, if the Login associated with the dbo User is in the sysadmin role, but Module Signing is better).
Looking at the CIS quote in @kevinsky's answer (i.e. "These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server."), unless I am missing something, that seems to be an overblown, or possibly uninformed, reaction. It would certainly be a huge issue if all users had access to it if it were enabled. But really, it's only users that already have the ability to enable "OLE Automation procedures" that are allowed to use the sp_OA* stored procs. I'm not saying that it should be enabled if it isn't being used, but this doesn't seem to be an inherent security risk.
I think the security "issue" is that it allows for code to be created by developers that either does things that you might not approve of on the network or file system or at the OS level, or perhaps there are unintended consequences.
That being said, if you are asking with regards to legacy code that you need to support, then while not ideal, it will probably work. But, if this is in regards to new development then you shouldn't be using OLE Automation; you should instead be using SQLCLR. SQLCLR has many benefits over OLE Automation, including (but not limited to):
SAFE) or able to access external resources but not alter the system or do asynchronous work, etc (EXTERNAL_ACCESS)
The CIS controls for SQL server 2008 and 2016 use this wording
The Ole Automation Procedures option controls whether OLE Automation objects can be instantiated within Transact-SQL batches. These are extended stored procedures that allow SQL Server users to execute functions external to SQL Server.
Rationale: Enabling this option will increase the attack surface of SQL Server and allow users to execute functions in the security context of SQL Server.
The risk is that the object you are calling from your stored procedure does not do what you think it does. As far as I know you cannot sign Ole Automation objects or provide any guarantee that the actions they are taking are what was intended.
To restate it again the ole object is an untrusted source that your stored procedure is executing with the permissions granted to it. Mitigation would be to only grant the minimum permissions to the user calling the procedure and setting the context of use when you create it to 4 which is described as
a local OLE server does not have access to any SQL Server resources, and it cannot damage SQL Server memory or resources.
See here for more details and here for an example. What the example won't show you is how this can go wrong. It might be better to rephrase your question as "What can happen if the data returned is not correct?"
As an aside SQL Server 2008 R2 goes out of support in July 2019. Perhaps an upgrade should be considered..
