Share via

Msg 7399 - The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Authentication failed.

  • Article

I’ve seen this one a few times lately.

In SQL Server there is a linked server to Access\Excel etc. Most of the times it works well, however, every now and then we get this:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<linked server>" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<linked server>" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<linked server>".

The most common reason I have seen for this is that the account executing the query against the linked server is not a Windows Authenticated account,

instead it is a SQL Server Authenticated account.

What I understand is happening is that some invalid login or security tokens are passed down to the file.

This works well when the account is a Windows account, but when a SQL Server account is used, the token is not valid and the error shows.

For example, create an Excel file called “MyLinkedXls.xls” in “C:\Temp”.

Start SSMS and login using a SQL Server authenticated account.

Then create a linked server to the Excel file, like so:

exec sp_addlinkedserver

@server = N'OurLinkedServer',

@srvproduct=N'Excel',

@provider=N'Microsoft.Jet.OLEDB.4.0',

@datasrc=N'C:\Temp\MyLinkedXls.xls',

@provstr=N'Excel 8.0;IMEX=1'

And try to test it:

exec sp_testlinkedserver OurLinkedServer

-- Or select from it

select top 1 * from OurLinkedServer...Sheet1$

This should give:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<linked server>" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<linked server>" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "<linked server>".

Now, logout from the server and login using a Windows authenticated account.

This can be done by simply right clicking somewhere in the query window in SSMS and select Connection -> Change Connection.

Rerun the query or sp_testlinkedserver procedure, this should work.

So, how to resolve this?

The simplest way is to add a linked server login for the SQL Authenticated account and set the remote user and password to NULL.

exec sp_addlinkedsrvlogin

@rmtsrvname=N'OurLinkedServer',

@useself=N'False',

@locallogin=N'the sql account', -- Replace this with the SQL Authenticated account

@rmtuser=NULL,

@rmtpassword=NULL

Now you should successfully be able to query the linked server in question.

Comments

  • Anonymous
    May 08, 2009
    PingBack from http://www.anith.com/?p=35986

  • Anonymous
    January 30, 2013
    It helped me to resolve the issue.. Thanks..

  • Anonymous
    March 18, 2014
    It helped me to resolve the issue.. Thanks..

  • Anonymous
    April 23, 2014
    Work, but I have a problem, I have a mdb database password. I do not know how to use a remote user and remote password. can you help me what should I do? Thank you