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


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 OurLinkedServerSheet1$


 


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 (4)

  1. Rashmita says:

    It helped me to resolve the issue.. Thanks..

  2. Ramesh says:

    It helped me to resolve the issue.. Thanks..

  3. Ihsan says:

    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