Monday 13 February 2017

Resolving "Cannot create an instance of OLE DB provider "OraOLE.DB.Oracle" after changing the SQL Server Agent account.

After an SQL Server Agent service account change, scheduled jobs that call an Oracle linked server began failing with the error

Executed as user: <username>. Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name>. [SQLSTATE 42000] (Error 7302).  The step failed.

The stored procedures called by those jobs executed as expected when I executed them from my account.

No ACCESS DENIED issues came up in Process Monitor while attempting to make the connection.

After some websurfing, this MSDN blog suggested modifying the MSDAINITIALIZE security settings to give the agent account all permissions on both Launch and Activation Permissions, Access Permissions and Configuration Permissions.

After doing that, the error changed to

Executed as user: <username>. The OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name> reported an error. Access denied. [SQLSTATE 42000] (Error 7399)  Cannot get the column information from OLE DB provider "OraOLEDB.Oracle" for linked server <linked server name>. [SQLSTATE 42000] (Error 7350).  The step failed.

Progress! Searching for that error message took me to this SQL Server Central post, where a user suggested ticking "Allow inprocesses" for the Oracle linked server provider.


Success! The job now completed as expected.


No comments:

Post a Comment