Cannot obtain the required interface ("IID_IDBSchemaRowset")

_{Wednesday, March 18, 2009 3:49 PM}

I hope some oone can help with this.
I have a sql 2005 32 bit sp3 STD Edi running on Windows 2003 sp2 Std Edi
I am using a domain account and am local admin on the box. The SQL and the SQL agent are both running under the same account. The account has full to the directory where the file sits and to the temp directory.

Im trying to import data from an xlsx into the database using the link server below

EXEC master.dbo.sp_addlinkedserver @server = N'ReturnTrackingIdsXLSImport', @srvproduct=N'Microsoft Excel Workbook', @provider=N'Microsoft.ACE.OLEDB.12.0', @datasrc=N'G:\BakTemp\QATestFileForCR9216.xlsx', @provstr=N'Excel 12.0 Xml;HDR=YES'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ReturnTrackingIdsXLSImport'
,@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

The test connection works, But when I try to run it from the SSMS i get the following error:

Msg 50000, Level 16, State 2, Procedure ImportReturnTrackingIds_forEXCEL2007, Line 256

Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "ReturnTrackingIdsXLSImport".

Thanks in advanced for any help

_{Friday, May 22, 2009 9:41 PM | 4 votes}

1. Start --> Run --> Dcomcnfg

2. Component services --> My computer --> DCOM config --> MSDAINITIALIZE

3. Right click on MSDAINITIALIZE --> properties -->security

4. Add the SQL Start-up account ( you may have to go to services in administrative tools under control panel to check log on account) under "launch and activation permission", "Access

permission" and "Configuration permission".

5. Give full rights.

_{Wednesday, July 8, 2009 5:14 PM}

Runeet,

on Windows Server 2008, all the properties for MSDAINITIALIZE are grayed out. Any idea how to set the permissions? RegEdit?

Best wishes
Michael

_{Thursday, July 16, 2009 9:04 PM | 2 votes}

I had this same error on Windows 2008.  In my case, I simply changed the SQL Server service account to run under a user account that had rights to the Excel file (which was buried in another users Doc & Settings folder).  I didn't need to touch DCOMCNFG.

_{Thursday, July 8, 2010 5:53 PM}

Yes! Yes!Yes! this worked and we are back to business on or 64 bit servers since MSDORA does work on 64 Bit SQL 2005.

Thank You for your post!

_{Thursday, October 24, 2013 3:21 PM | 11 votes}

It may be worked but I'd try first as SQL Server's security recommends use of a service account vs. local account:

http://www.sqlservercentral.com/Forums/Topic635800-1042-1.aspx#bm1257273

Some people has fixed the issue by unchecking the
"Allow inprocess" option

On SQL Server Management Studio
Server
Objects --> Linked Servers --> Providers --> MSDAORA, right click select properties

Turn "Allow inprocess" on

_{Sunday, July 6, 2014 4:02 AM}

MSDAINITIALIZE

All fields in  Security is disabled. What is the fix?

Other helpful link:

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/08/19/permissions-needed-to-set-up-linked-server-with-out-of-process-provider.aspx?CommentPosted=true#commentmessage

mysorian

_{Friday, October 23, 2015 10:11 AM}

Super, your solution works! THANKS A LOT!!!

_{Tuesday, December 29, 2015 2:56 PM}

done, also restarted server, but still the same :(

any other idea?

W2014 Web x64 on W2012R2

thanks