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

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

  • Comments 4

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.

Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post
Page 1 of 1 (4 items)