Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

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?
0
Vachik
Asked:
Vachik
  • 6
  • 5
  • 4
  • +1
5 Solutions
 
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
 
VachikAuthor 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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

IT MUST BE CLOSED
0
 
VachikAuthor 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
 
VachikAuthor 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
 
VachikAuthor 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
 
VachikAuthor 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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
VachikAuthor 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
 
QlemoC++ DeveloperCommented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now