locked
Linked Server to Access mdb problem RRS feed

  • Question

  • I've set up a linked server to an Access database and read from the linked database in a stored procedure.  No problems when I execute the stored procedure from management studio and the Access database resides on a local drive.

     

    However, when I move the database to a network share drive and run the proc through a scheduled job using SQL Server Agent I get the following error --

     

    "Executed as user: C-SGROUP\actprod. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)."

     

    I've spent days trying to resolve this problem and tried many different setting to fix the problem, but no luck yet.

     

    Under the Security setting for the linked server I have "Be made using this security context" selected with a valid id/password.  I am able to login to the server where the database resides using the id and password and successfully open the database.

     

    Don't know what to try next.  Any suggestions?

     

    I'm using SQL Server 2005 Developers edition.

    Thanks.

    Tuesday, May 22, 2007 1:34 PM

Answers

  • Thank you, Sue.

     

    After much trial-and-error I finally got the linked server to work -- but I don't know for sure what I did to make it work.  You see, SQL Server is new to me.  The problem I was having with the security context for the mdb on the share drive may have been related to the "for a login not defined in the above, connection will:" property of the linked server.  I had the remote login and password set.  I removed the id/password and set the property to "to be made without using a security context".

     

    Thanks again for your help, Sue.

    Wednesday, May 23, 2007 4:14 PM

All replies

  • What if the mdb is local and it's run as a job?

    What if the mdb is on the share - can you still execute the stored proc in SSMS? Then can you log into the server using the service account for SQL Server agent and execute the stored proc from SSMS?

     

    -Sue

    Wednesday, May 23, 2007 2:45 AM
  • Thank you, Sue.

     

    After much trial-and-error I finally got the linked server to work -- but I don't know for sure what I did to make it work.  You see, SQL Server is new to me.  The problem I was having with the security context for the mdb on the share drive may have been related to the "for a login not defined in the above, connection will:" property of the linked server.  I had the remote login and password set.  I removed the id/password and set the property to "to be made without using a security context".

     

    Thanks again for your help, Sue.

    Wednesday, May 23, 2007 4:14 PM