setting up a linked server to excel sheet

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

setting up a linked server to excel sheet

Author

Topic

MCCCLXXXV
Starting Member

10 Posts

Posted - 2008-08-24 : 17:22:25

HI,

I'm new to SQL and I'm having trouble connecting to a linked server from a remote computer. this is what I have done so far:

On my machine (the server), I set up access to a normal SQL server (MSSMLBIZ) by allowing Windows and SQL authentication. On this machine I also set up a linked server to Z:\APH.xls via SQL Server Mgmt Studio Express. (Product name=Excel, data source = Z:\APH.xls, provider string = Excel 8.0, Provider = Jet 4.0). I have set up a SQL login called ClearAccess to the MSSMLBIZ server.

When I login from my machine to MSSMLBIZ with this login, it works fine. I can do SELECT * FROM APH...Sheet1$ and it shows me the data. If I go over to another computer in the office and login to MSSMLBIZ with the same login, i AM able to login to the server, but when I try to execute the same command, i get this:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "APH" 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 "APH" 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 "APH".

What am i doing wrong?

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-24 : 17:25:40

quote:
Originally posted by MCCCLXXXV

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

Sounds to me that the excel file is already open or being used by another application. Try closing it then try again.

MCCCLXXXV
Starting Member

10 Posts

Posted - 2008-08-24 : 18:05:36

The file is closed. and I closed anything that would possibly be accessing it. If I go to my computer (the server) and do the same command, i dont get an error. its only when i'm connected from a different computer. do i have to do something with mapping logins?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-24 : 22:46:41

When you created the ClearAccess SQL login, did you use "Windows Authentication? And if yes, what windows security does the ClearAccess account have on Z:\APH.xls?

MCCCLXXXV
Starting Member

10 Posts

Posted - 2008-08-25 : 00:36:52

no, that is a SQL Auth

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-25 : 03:24:04

Also check your firewall settings.

MCCCLXXXV
Starting Member

10 Posts

Posted - 2008-08-25 : 05:16:50

well i can login into the server and execute commands on the regular (non-linked) server, so im assuming the firewall is fine.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-25 : 06:02:08

Can you change this account to windows authentication & give it permissions on the file/folder?

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-25 : 07:16:39

Before changing the login, I'd suggest having a look at the account which is running your SQL Server engine- go to Control Panel -> Administrative Tools -> Services, open the SQL Server service & check the Log On tab. Try giving this account security on your folder & file.

MCCCLXXXV
Starting Member

10 Posts

Posted - 2008-08-25 : 09:04:18

I went to Services...SQL Server...Log On and the account that is there is NT AUTHORITY\NetworkService.

I right clicked on Z:\ and went to properties, clicked on Security tab and clicked Add.. to add this user (its not in the list already). I typed in NT AUTHORITY\NetworkService under "Enter the object names to select" and it can't find it. I tried all combinations of NT AUTHORITY and NetworkService. I can't change "From this location" either. Note that Z:\ is a mapped shared drive that I connect to.

So, I copied the file to C:\test\APH.xls and created a second linked server called APH2 in the exact same way I made the first one. (I also tried adding security permissions to the C:\test folder for NT AUTHORITY\NetworkService but I still couldn't do it.) When I go over to the other computer and log in with the SQL Auth 'ClearAccess' and execute SELECT * FROM APH2...INTL$, I get this error instead:

Msg 7416, Level 16, State 2, Line 1
Access to the remote server is denied because no login-mapping exists.

When I execute SELECT * FROM APH...INTL$ in the same session, I still get:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "APH" 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 "APH" 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 "APH".

I tried adding a Windows Auth to the server, but I don't know how to add a user from a different computer. The windows login on the computer that I am trying to connect from is User12 and the computer name is INTERN. Any ideas? Thanks for your help so far.

MCCCLXXXV
Starting Member

10 Posts

Posted - 2008-08-25 : 09:11:19

wait...I changed the login mapping on APH2 to "Be made using the login's current security context" (which is how APH is set up) and I get:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "APH" 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 "APH" 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 "APH".

tochaduro
Starting Member

2 Posts

Posted - 2009-03-31 : 20:33:33

I was having the same problem. This is what I did:

1. SQL2005
2. Server Objects
3. Linked Servers
4. <database name>
5. Properties
6. Select a Page> Security
7. Select * Be made without using security context.

I play a little with it and found that if I have a anything under "local login ... mappings", it will not work.

Also, I play with changing other settings and didn't work.

This is the only setting that has to change.

Good luck.

Tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources