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:
@server = N'OurLinkedServer',
And try to test it:
exec sp_testlinkedserver OurLinkedServer
-- Or select from it
select top 1 * from OurLinkedServer...Sheet1$
This should give:
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.
@locallogin=N'the sql account', -- Replace this with the SQL Authenticated account
Now you should successfully be able to query the linked server in question.
PingBack from http://www.anith.com/?p=35986
It helped me to resolve the issue.. Thanks..