Monday, September 10, 2012

SQL Error 7302 Cannot Create linked server for OleDb Provider

Symptoms:


You Create a linked server for an excel or Access file. The following procedure relates to Excel 2003 (Microsoft.Jet.Oledb.4.0) and Excel 2007. (Ace driver – Microsoft Office 12 Access OleDb Provider.
You cannot make the connection and receive the error message “cannot load Microsoft Jet oledb provider for linked server null – SQL Error 7302”.

Note: Do not install MS Office 2007 on a server to connect to Excel or Access 2007 files. Only connectivity tools are needed (ACE Drivers).
          
If you try to run a select on a worksheet you can see the following message in the Event viewer (Applications):
The machine-default permission settings do not grant Local Activation permission for the COM Server application with CLSID
{2206CDB0-19C1-11D1-89E0-00C04FD7A829}
to the user <user> SID (sid).  This security permission can be modified using the Component Services administrative tool.

RESOLUTION

Grant the user permissions to start the COM component. To do this, follow
these steps:

1. Click Start, click Run, type regedit in the Open box, and
then click OK.

2. Locate and then click the following registry subkey:
HKEY_CLASSES_ROOT\CLSID\CLSID value
Note In this subkey, "CLSID value" is a placeholder for the CLSID
information that appears in the message.

3. In the right pane, double-click AppID.
The Edit String dialog box appears. Leave this dialog box open and continue
to the next step.

4. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.

If a Windows Security Alert message prompts you to keep blocking the
Microsoft Management Console program, click to unblock the program.
5. In Component Services, double-click Component Services, double-click
Computers, double-click My Computer, and then click DCOM Config.

6. In the details pane, locate the program by using the friendly name.
If the AppGUID identifier is listed instead of the friendly name, locate the
program by using this identifier.

7. Right-click the program, and then click Properties.

8. Click the Security tab.

9. In the Launch and Activation Permissions area, click Customize, and then
click Edit.

10. Click Add, type the user's account name, and then click OK.

11. While the user is selected, click to select the Allow check boxes for
the following items: 
.. Local Launch
.. Remote Launch
.. Local Activation
.. Remote Activation

12. Click OK two times.

13. Quit Registry Editor.