Linking SQL Server to an Access database

Even if importing an old Access database to SQL Server is pretty easy using available tools, this might not always be the optimal solution. Linking an old Access mdp file to your SQL Server enables you to gradually move old legacy databases and systems to leverage the power and stability of a server database as you can leave the old dependencies untouch while implementing new ones to use the server.

Setting up an Access database as a linked server on a SQL Server requires a number of steps. For those in a hurry and not wanting to read the whole article, this is what you need to do:

1. Install the 64 bit database driver
2. Configure the Access Database Engien provider settings
3. Allow users full access to the temp directory of the SQL Server service account
4. Setup the linked server

First thing to do is to get the Jet database drivers installed on the server. As Access first did not support 64 bit operating the drivers are not available out of the box. Don’t worry, you will not need to restart after installing this.

Then go to Server Objects — Linked Servers — Providers and you should see the newly installed “Microsoft.ACE.OLEDB.120.0” driver. Open it’s properties and check both “Dynamic parameters” and “Allow inprocess”.  

Then complete this  first step by enabling the ‘Ad Hoc Distributed Queries’ setting with the the following command:

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

 Now you will avoid getting errors when trying to browse or access items in the database.

SQL Server needs to use the temp directory of the SQL Server service account for handling the linked server. For things to work and you to avoid any “Unspecified errors” you need to make sure also the account accessing the linked server has access to that same temp directory (the security settings of the link seem to have no affect on this). The quick and dirty solution to this is to give the account(s) or all users access to the temp directory under the application data folder under the account profile. The proper solution is to:

1. create a new temp folder for the service account (or use c:\temp)
2. change the TMP and TEMP environment variables of the service account to point to the new folder — this will require restaring the server for the changes to take effect
3.  setup a group with the accounts that need access to the database to give it full control over the folder.

As a final touch before setting up the actual linked server you might need to take the time to define a SPN for the service account in cases when the database file is not on the same server as the SQL Server.

Finally go to Server Objects — Linked Servers and add a new linked server. Give it any name you like to identify it and select Office Access Database Engine as the driver. The define the connection parameters as follows:n

• Product name can be anything you like, usually the type of the database e.g. ACCESS
• Data source defines the path to the database file
• Provider string can be used to define additional connection string parameters

Make sure to check that the settings on the Security page are has you want to have it. Then click on OK.

Now to make sure the linked server actually works, first open the context menu of the new linked server and select Test Connection. Then browse through the structure down to the tables to see if SQL Server is able to display you the objects in the database. If no errors are displayed the link is now ready and you can go and get your self your well deserved hot beverage.