• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11309
  • Last Modified:

Why not enable 'OLE Automation Procedures'?

I've installed Crystal Reports XI and attempting to reach my SQL 2008 db on a Windows 2008 Server .  I am getting errors with 'Ole Automation Procedures' being turned off.  I think I understand how to run sp_configure to turn it on, but would like to know what ramifications there are to setting this to enabled or the advanced options that is required to 1.
0
Still Learning
Asked:
Still Learning
1 Solution
 
David ToddSenior DBACommented:
Hi,

Firstly I don't understand where the error is occurring. Is the error in Crystal reports and that environment, or in SQL? Are you trying to connect from crystal to sql, or from sql to crystal? If from sql to crystal then I can understand why you might need ole automation procedures.

Otherwise, is the o9le automation needed on the server where Crystal is, or inside SQL?

I was told that some ole automation procs have memory leaks in them, but I never saw that myself.

You are testing this on a test system, and not deploying against production, right?

HTH
  David
0
 
Gideon7Commented:
MS SQL Server allows an SQL script to create and manipulate external OLE Automation objects via the special stored procedures sp_OACreate, sp_OAMethod, and sp_OAProperty.  See http://msdn.microsoft.com/en-us/library/ms190501.aspx.

Before you can invoke OLE Automation objects you must enable access (http://msdn.microsoft.com/en-us/library/ms191188.aspx).

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

Open in new window


The above script temporarily turns on Show Advanced Options, enables OLE Automation, and turns it back off.  

Warning: By default access to the OLE Automation stored procedures is disabled.  If enabled it allows any SQL script to invoke any OLE Automation object on the computer (such as the Windows Shell).  It is a significant security risk and should not be done lightly.  For example you need to be extra careful to protect against SQL-injection attacks.   It is basically the same as allowing xp_cmdshell.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now