How to create a Linked Server to an Access DB in SQL Server

This tutorial will cover how to create and reference a Linked Server in SQL Server that allows you to query an Access database. So what is a “Linked Server”? A Linked Server is simply a reference in SQL Server to an outside data source (usually another instance of SQL Server, but in this case…an Access DB).

In this walkthrough I’m using SQL Server 2008R2 and the Access Database was created in Office 2010.

The first thing you need to do in order to reference an Access database is ensure that the proper drivers are in place, so SQL Server and Access know how to talk to each other. You’ll want to download the “Microsoft Access Database Engine 2010 Redistributable” and install it. The Download and installation instructions are located at http://www.microsoft.com/download/en/details.aspx?id=13255 . This will need to be installed on the computer that SQL Server is installed on. I found the install to be very straightforward, consisting of something like NextàNextà Nextà J. Make sure you get the correct version though (x86 vs. 64 bit).

Once installed, Open up SQL Server Management Studio (SSMS) and connect to your instance of SQL Server that you want to create the Linked Server on. Once there, navigate to Server Objects à Linked Servers à Providers à Microsoft.ACE.OLEDB.12.0 (as shown highlighted below)

clip_image001[6]

Right click on the “Microsoft.ACE.OLEDB.12.0” provider and select “Properties” to open up the properties window. Check the box for “Allow inprocess” as shown below and then click OK.

clip_image003[6]

Now all we have left to do is create our linked server. Right click on the Linked Servers folder in the Object Explorer in SSMS and select “New Linked Server”. This should open up a wizard to help us along. Enter the same information shown in the image below, making sure to select the “Microsoft Office 12.0 Access..blah blah blah” as your provider. You’ll also want to change the value of “Data Source” to be the location of your Access db.

clip_image005[6]

Once finished just click ok and if all has gone according to plan, you should see your linked server in the “Linked Servers” folder in SSMS (you might need to refresh the folder).

Now…how do we query our Access DB using T-SQL? As shown above, I named my Linked Server “TEST” and in the Access database that TEST references, there is a table called “People”. If I wanted to query that table I could do so like this:

SELECT * FROM OPENQUERY(TEST, 'SELECT * FROM People')

Now if I wanted to make this available to something like PerformancePoint, I could simply make the above query into a View and reference it like I would any other SQL Server table.

Well, I think that about does it. Hope this helps.

 

How to configure Windows Firewall to allow access to SQL Server

Having trouble connecting to SQL Server from a different PC? Read on.

This scenario assumes SQL Server 2008 R2 and Windows Server 2008. I ran into this problem for the umpteenth time the other day while trying to connect to a SQL Instance, so here’s a quick write up on how you might fix it. Now there are all kinds of reasons you might not be able to connect, but if you’re able to do things like

· Remote Desktop into the server that SQL Server is installed on just fine

· Have no problems connecting to the SQL Instance through SSMS when logged into the server that SQL Server is installed on

And if you’re getting an error somewhat similar to the following:

“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 1326)”

You might want to take a look at your windows firewall settings in Windows Server and make sure that the ports that SQL Server talks over are open (port 1433 by default).

To open up the ports, you could just turn the firewall off all together, depending on your environment, this may or may not be an option….either way, it’s not something I suggest. It’s better to set up an exception in your firewall to allow communication over that port…..and this is how you can do it:

1. Log into the server that SQL Server is installed on and click on “Start” and then type Firewall.cpl in the search/run box. This should bring up the screen shown below. (Alternatively you could go to startàcontrol panel and then “check firewall status” under the “System and Security” section.)

clip_image002[4]

2. Click on “Advanced Settings” (highlighted in yellow above). This should bring up a new screen (shown below). Click on the “Inbound Rules” link highlighted in yellow.

clip_image004[4]

3. Next we’ll want to create a new inbound rule for our firewall by clicking “New Rule…” (highlighted in yellow below)

clip_image006[4]

4. The rest of the steps will be handled by a wizard. There are screen shots of each step. Important areas are highlighted in yellow.

clip_image008[4]

Port below is 1433 (if it's hard to read)

clip_image010[4]

clip_image012[4]

clip_image014[4]

clip_image016[4]

5. Once you’ve hit finish you should see your new rule in the “Inbound Rules” window as shown below.

clip_image018[4]

Depending on how you have your firewall setup, you may also have to configure an outgoing rule as well. To do so just follow the same steps above starting at step 2, only click “Outbound Rules” instead of “Inbound Rules”. Now this is not the end all fix for connection problems....and this assumes a few other things are in place, but it's an excellent starting point, and more often than not this is the problem from my experience. Hope this helps.