MSSQL and PostgreSQL Linked Server Configuration –DB LINK

Install PostgreSQL ODBC Driver

Browse to PostgreSQL’s official download site for psqlODBC and download the zip file containing the x64 bit .msi installer and you can installation the process.

Create ODBC Data Source

Let’s start the ODBC Data Source (64 bit) application from the Server Manager applet (see below).

First, let’s choose the System DSN tab and then click Add.

Next, we choose the PostgreSQL Unicode (x64) version and click Finish.

In the dialog box that pops up, provide a name and description for the data source, specify the database name, server’s IP address, port, user name and password as connection parameters. Once done, test the details by clicking on the Test button. If the test is successful, click Save and then click OK in the ODBC Data Source Administrator.

Create a SQL Server Linked Server to PostgreSQL

On the General tab of the New Linked Server dialog box, choose the “Other data source” option, select the “Microsoft OLE DB Provide for ODBC Drivers” option from the Provider drop-down list, provide a name for the Product and specify the Data Source name. The data source should be the one we just created: in this case it’s POSTGRESQL

Choose the fourth option (“Be made using this security context”) and provide a login name and password to connect to the remote PostgreSQL instance. In this case, we have used the built-in Postgres superuser account to keep things simple

Click OK. If the connection is successful, the Linked Server will be created without any error.

Expanding the Linked Server node in SQL Server Management Studio would show us the tables in the world database in PostgreSQL.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store