is it possible to access an excel file on a shared drive using linked server where sql server run on local system account?

Is it possible to access an excel file on a shared drive using linked server where sql server run on local system account?
Claudia CarlottiIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EmesCommented:
yes


Dir tt1 is shared

EXEC sp_addlinkedserver 'ExcelSource21q',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:\tt1\xx.xls',
   NULL,
   'Excel 5.0'
GO


-- option 1 to get a list
EXECUTE sp_tables_ex ExcelSource21q
0
Claudia CarlottiIT DirectorAuthor Commented:
Emes, The one you recommended gave me an error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LaborExcel" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LaborExcel" reported an error. Authentication failed

You need to remember that the shared file is on a different server and SQL Server runs on Local System Account (cannot change since being used by another important application) so the shared excel file can't be given a security permission of the SQL Server.  Will proxy work?  
0
EmesCommented:
may be I have something different

When I used

EXEC sp_addlinkedserver 'ExcelSource21qA',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   '\\Shared\TTX\xx.xls',
   NULL,
   'Excel 5.0'
GO


-- option 1 to get a list
EXECUTE sp_tables_ex ExcelSource21qA

It works.

Do you have the  'Microsoft.Jet.OLEDB.4.0', installed ??

0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

EmesCommented:
Wait make sure that the app is CLOSED !!!!!!!!!!!!!!!!!

IT MUST BE CLOSED
0
Claudia CarlottiIT DirectorAuthor Commented:
Emes, it's definite that it was closed.  By the way, I already have this one working on a different sql server but again as I've emphasized, this sql server runs on a local system account.  The other sql server I ran this one is under a domain account.

So that's my main problem, it's not running under local system account.  I'm looking for some workaround.
0
EmesCommented:
Let try something simple,

Can you make a local xls file work on the local box?

then try to open the  shared excel file from your local machine

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Claudia CarlottiIT DirectorAuthor Commented:
Emes,

Yes I was able to make it work locally.  But I need to run sp_addlinkedsrvlogin to make it work under sa.

0
Claudia CarlottiIT DirectorAuthor Commented:
I would appreciate other suggestions.  I'm still tied to the same problem:  I cannot access my excel file through linked server since my SQL Server is running in local system account.
0
Mark WillsTopic AdvisorCommented:
This is one good reason not to make SQL Server and SQL Server Agent (et al) not use the local system account.

When I started making a bonafide Domain User / Account for the SQL services, then most of this type of issue disappears. Reason being you can then have that user with the same network mappings and it will be OK. In fact being a Domain user you can assign all kinds of network type privileges, and deny them as well. Just make sure that account is secure and used ONLY for the SQL services.

To verify, you could use your own domain account to start the services (well, depends a bit on your circumstances), and make sure that works.

You can isolate your linked server issues by first copying to a local drive / folder on the server and test there.

If you cannot create a new account for the services, then, can you create a mapping to the sql server box and place the files there ?

Does that make sense ?
0
Claudia CarlottiIT DirectorAuthor Commented:
Mark.

Thank you for the response.  Actually, the SQL Server is currently being used for Citrix and Backup reporting purposes and asked the network if I can change the service account and said cannot guarantee that it's not going to break something if we change it so we're not risking it since it's production Citrix plus Backup.

I'm curious about what you said on the 2nd to the last line:
"If you cannot create a new account for the services, then, can you create a mapping to the sql server box and place the files there ?"

I'm trying to avoid manual work since this will be needed monthly, so hopefully when you say copying means I should copy the files automatically to the server (since shared folder still doesn't seem to work due to permissions)?  I tried to place the file locally to the server and it worked.  Now I'm trying to run a xp_cmdshell to automatically copy the files and it's giving me errors again (still permission errors).

I'm just wondering if there's any workaround like proxies, run as domain account or any other SQL related techniques rather than resorting to vbscript or scheduled task, etc.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If you don't care for inexact time schedule, I would use a workaround scheduled task to copy the file to local. The scheduled task can run under an arbitrary account with network access. You could even use the trick to kick that task with xp_cmdshell calling schtasks /run.
0
Mark WillsTopic AdvisorCommented:
Well, you will not be able to do much as it stands right now simply because the "user" is regarded as the local system account. So, xp_cmdshell will have the same problems, as you have found.

I have never had much joy in tricking SQL Server to be anyone else, especially (now) that my "default" installation includes setting up a domain account for services.

If the remote location is a share set up explictly for the purpose of storing those files, then it could be remapped to the SQL Server. If it is a shared location, for ither purposes, then there is not so many options.

If it is a batch style operation, then arguably the best method would be to have a batch or windows command as a Windows Scheduled Task where you can nominate a user to run the job, and simply get that to copy the files from the remote location.

That scheduled task could be either on the SQL Server "pulling" files across, or, remote server "pushing" them over to the SQL Server. It does have the advantage of renaming, archiving etc along the way.

0
Mark WillsTopic AdvisorCommented:
Sorry Qlemo, didnt see your posting, and yes, good idea, you can call that scheduled task as needed, gets around the "predertimined" batch type requirement.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Can't remember exactly, but couldn't you use a proxy account with xp_cmdshell to get around the service account limitations?
0
Mark WillsTopic AdvisorCommented:
In fact, you could have that batch job, copy the files, and run osql script to load the files and that way have it all controlled from the one spot ...
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That sounds best to me. If you need to trigger it, use ONLY xp_cmdshell schtasks /run, and do everything in the batch file.
0
Mark WillsTopic AdvisorCommented:
Yes you can use a proxy account with xp_cmdshell, but that also exposes xp_cmdshell and is not the most secure of environments.

generally an area that should be avoided...


The following example shows how to create a proxy credential for a Windows account called ADVWKS\Max04 with password ds35efg##65.

EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'ds35efg##65";
GO
 

The following example removes the proxy credential from the credential store.

EXEC sp_xp_cmdshell_proxy_account NULL;
GO
 
0
Claudia CarlottiIT DirectorAuthor Commented:
Well, I think I'll just going to have to use different combinations just to make this thing work.

Thank you very much to both of you.  

Can I just split the scores to share credit? How?
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Press "Accept Multiple Solutions", and assign points as you like. You can accept more than one answer per poster, the points will be summed up. You should accept answers which could be important for other people finding this solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.