Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Linked Server to File Folder Security Issue Expand / Collapse
Author
Message
Posted Sunday, December 8, 2013 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:36 PM
Points: 3, Visits: 9
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
Post #1520877
Posted Sunday, December 8, 2013 12:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Who owns the job?

CEWII
Post #1520879
Posted Sunday, December 8, 2013 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:36 PM
Points: 3, Visits: 9
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.
Post #1520884
Posted Sunday, December 8, 2013 1:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:36 PM
Points: 3, Visits: 9
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.
Post #1520885
Posted Monday, December 9, 2013 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 12,880, Visits: 31,796
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1521051
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse