Solved

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

Posted on 2009-05-18
21
262 Views
Last Modified: 2012-05-07
Is it possible to access an excel file on a shared drive using linked server where sql server run on local system account?
0
Comment
Question by:Vachik
  • 6
  • 5
  • 4
  • +1
21 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 24415693
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
 

Author Comment

by:Vachik
ID: 24415858
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
 
LVL 14

Expert Comment

by:Emes
ID: 24415909
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
 
LVL 14

Expert Comment

by:Emes
ID: 24415917
Wait make sure that the app is CLOSED !!!!!!!!!!!!!!!!!

IT MUST BE CLOSED
0
 

Author Comment

by:Vachik
ID: 24416202
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
 
LVL 14

Accepted Solution

by:
Emes earned 100 total points
ID: 24416278
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
 

Author Comment

by:Vachik
ID: 24416443
Emes,

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

0
 

Author Comment

by:Vachik
ID: 24417493
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
ID: 24438943
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Vachik
ID: 24443510
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
 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 100 total points
ID: 24444282
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
ID: 24444387
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24444400
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 24444436
Can't remember exactly, but couldn't you use a proxy account with xp_cmdshell to get around the service account limitations?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24444483
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 24444501
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
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
ID: 24444537
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
 

Author Comment

by:Vachik
ID: 24445634
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 24445738
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now