Linked Server to File Folder Security Issue

  • 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

  • Who owns the job?

    CEWII

  • 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.

  • 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.

  • 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!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply