Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server to File Folder Security Issue


Linked Server to File Folder Security Issue

Author
Message
hornak.john
hornak.john
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
Here is my Problem:

1. I have sql 2008 R2 running on my LocalHost.
2. Created Data Base [Customer].
3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:\Data which has DBF files in it.
4. Create dbo.Customer_Upload Table.
5. INSERT INTO [Customer].[dbo].[Customer_UpLoad]
([Name],[Email])
SELECT
   NAME,EMAIL
FROM [CUSTOMERLINK]...[CUS]

All this works fine. I can even put it in to an After Insert Trigger on another table and it works.

My problem is that I need this to work in a scheduled job.

F:\Data is just a folder with files in it.
This info is from a Restaurant POS system and I need to update it every night.

I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.

Anyone have an answer to this. I'm out of trys.

John
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
Who owns the job?

CEWII
hornak.john
hornak.john
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
I've tried everyone and no one.

There is no security on the file side of the linked server so I don't know how to set up the security on the Job.
hornak.john
hornak.john
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
12/08/2013 14:53:04,Daily Data Upload,Error,1,WORKPC\PCAFE,Daily Data Upload,Customer Data,,Executed as user: NT SERVICE\SQLAgent$PCAFE.

Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PTCAFE". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "PTCAFE" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412).

The step failed.,00:00:00,16,7412,,,,0 12/08/2013 14:49:18,Daily Data Upload,Error,,WORKPC\PCAFE,Daily Data Upload,,,The job failed.

Here is what I am getting with no owner.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14933 Visits: 38925
hornak.john (12/8/2013)
I've tried everyone and no one.

There is no security on the file side of the linked server so I don't know how to set up the security on the Job.


my first knee jerk reaction was that the JET driver cannot be working in the first place, assuming that you are using SQL2014CTE, which is only 64 bit; but you already said it was working, so my next guess is:

from your two posts, you can see that the user context whent he job was running is NT SERVICE\SQLAgent$PCAFE

THAT user, apparently does not have access to the F:\Data , or at least the proxy account user (if it is set up?) doesn't have access to that drive; is that a mapped drive ont he network, or a local drive to the server?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search