Linked Server Access DB

After converting to SQL Server 2005 and VS2005 I am having a problem connecting to Linked Servers for Access databases. I have searched and read everything I can find, but the solution is still not evident. I have a lot of data integration and data conversion procedures to run and I am stuck at this point not understanding exactly what the problem is and how to resolve it. I believe it is somehow related to "double hop" and permissions with .NET. Many of the discussions are related to using .ASP but that is not involved in this situation.

The Linked Server is defined using the UNC path. The machines are not in a domain. They are in the same LAN segment. Connections will be made without using a security context is specified. A sample Linked Server definition:

EXEC master.dbo.sp_addlinkedserver @.server = N'ACCESS EDUCATION', @.srvproduct=N'OLE DB Provider for Jet', @.provider=N'Microsoft.Jet.OLEDB.4.0', @.datasrc=N'\\Tacir2k3\Infrastructure\Databases\July 2005 Databases\Education\EduDb2005_TR_Db.mdb'

The error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACCESS EDUCATION" returned message "The Microsoft Jet database engine cannot open the file '\\Tacir2k3\Infrastructure\Databases\July 2005 Databases\Education\EduDb2005_TR_Db.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".

Machine 1 (Developer Workstation): XP SP2 with latest security patches; SS Management Studio, VS2005

Machine 2 (SQL Server server): Windows 2003 Server with SS2005. The Linked Servers are defined on an SS2005 instance on this machine.

Machine 3 (File server): Windows 2003 Server that contains the Access DB files

When I run SS Management Studio on Machine 2 (the database server), I have no problem accessing the Linked Server pointing to an Access DB file on Machine 3 and can compile or run SPs referencing the Linked Server.

When I run SS Management Studio on Machine 1 to execute an SP that references the Linked Servers, I get the above error.

When I use Machine 1 to compile an SP that references the Linked Server, I get the above error.

I tried granting Everyone Full Control to the folder that contains all the Access database subfolders but I get the same error when running from Machine 1.

Yes in general it is not a good idea to use an Access file on a network share in this manner from a linked server. It complicates things in many ways. If you can move the Access file locally this would be the best solution. 2nd best solution is setup linked server to run as a specific account and grant this account rights to the network share.

|||How do you set a linked server to run as a specific account?|||

When you create the linked server look at the Security page/tab at the bottom.

Select "Be made using this security context" and you can enter the account.

|||

Can anyone explain why the linked server can be accessed by the database server but not by a workstation using Management Studio via the database server? What specifically is causing the issue?

|||

The security token of a local user can by default make a single hop off of a server.

A security token from a remote user by default cannot hop off of a server.

This is the default security design of Windows, it is designed this way to prevent a malicious user on a server from using a remote user's credentials to access domain resources. The server can only use the remote user's token to access resources on the server.

For example, suppose I setup a web site on Server1. You use IE to connect to my web site and secretly I take your credentials and log into your Exchange account and read all your private email. The default behavior of Windows is designed to prevent this scenario.

This is why a remote user logging into SQL Server cannot access files or other things on a machine other than the local SQL Server machine.

A local user on the SQL Server machine can make a single hop from the local machine, so they can access files off the SQL box.

You can reconfigure your domain to allow user accounts to make multiple hops but it is very complicated and in the end it makes your network less secure, so it's not recommended. If this is the path you want to take then read up on configuring Kerberos delegation. An easier solution is to configure the linked server to use a specific account.