SQL DBA Blog

In my previous post, I created a Link Server to another SQL Server instance This time, it’s a link to a Postgres database!

This guide can be followed from start to finish using the links below.

# Install SQL Server (separate post).
# Install PostgreSQL (separate post).
# Test Connectivity to Remote Server (previous Linked Server post).
# Create Linked Server Login on Postgres.
# Install the Postgres ODBC Driver.
# Configure ODBC Data Source Settings.
# Create Linked Server to a Postgres Database.

Create Linked Server Login on Postgres

As always, this kind of task can be done using a management tool like pgAdmin 4 or via command (psql in this case).

Making this one a Superuser for quickness.

And for the post, here’s the above being created using psql.

Install Postgres ODBC Driver

The ODBC driver must be installed on both ends – This is a local set-up though, so I only need to install this once. You can use Stack Builder to install it, or download the package on its own (link here) which I’m doing below.

Click famous Next button.

Accept License stuff.

Specify directory & we don’t need the documentation.

And then install!

Configure ODBC Data Source Settings

We need to point the ODBC driver on the SQL Server to the Postgres database.

Run ODBC Data Sources as Administrator.

Click into the System DSN tab and click Add.

Select the PostgreSQL Unicode driver.

Enter all details required as shown. I created a ‘sales’ database for this test which I’m connecting to. 

Always worth it to test.

Create Linked Server to a Postgres Database

(Within SQL Server Management Studio’s Object Explorer)

Expand Server Objects, right-click Linked Servers and select New Linked Server.

The Linked server name at the top of the next window is what you’ll need to include in queries, so choose that wisely.

Then choose a provider for ODBC, enter any semi-relevant Product name, and then enter the Data Source name which you entered within the ODBC driver settings.

Enter the Postgres login details as created above within this post.

I’m ignoring the Server Options tab for now and rolling with the defaults.

When ready, take a breath, then hit OK!

Nothing will display if it’s successful, so have a go at testing the connection.

We’re now linked with the Sales database that’s in Postgres!