SQL Server and PostgreSQL Linked Server Configuration - Part 2

By:   |   Comments (25)   |   Related: 1 | 2 | 3 | > PostgreSQL


We need to have PostgreSQL and SQL Server database platforms communicate in our environment. We need to access the PostgreSQL data from SQL Server in an efficient manner. Based on the steps from your first tip, how can we take the next steps to setup the data access?  Can we create a Linked Server from SQL Server to PostgreSQL to access the data?


In part 1 of this series, we rolled out a simple database infrastructure with a PostgreSQL instance and a SQL Server instance. We have seen how both the servers could communicate with each other at network level.  Then we restored a sample database in PostgreSQL and created two of its table structures in SQL Server.

In this tip, we will show how SQL Server can access Postgres data and populate those tables.

Install PostgreSQL ODBC Driver

Although the de-facto data access library for any modern database should be based on OLE DB, PostgreSQL's official site doesn't list any freely available x64 bit OLE DB providers. As mentioned in part 1, there's a 64-bit OLE DB provider available from a third-party vendor. However, that driver comes with a price tag. The free OLE DB version available from Postgres site is for 32-bit only.

However, PostgreSQL also provides a 64-bit ODBC driver that's downloadable from its official site. In our example, we will download and install this 64-bit ODBC driver (psqlODBC) on our SQL Server.

Step 1: Remote desktop to SQL Server

Step 2: Browse to PostgreSQL's official download site for psqlODBC and download the zip file containing the x64 bit .msi installer. The file we will download is called psqlodbc_09_03_0300-x64-1.zip. We can see the driver is for PostgreSQL 9.3 and it's meant for 64-bit Windows. Once the download completes, unzip the file. The extracted content looks like this:

Extracted files of the x64 bit ODBC driver

Step 3: Double-click to start the psqlodbc_x64.msi installer. The next few images show the straightforward installation process.

Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3
Installing the 64-bit ODBC driver for PostgreSQL 9.3

Create ODBC Data Source

Once the driver has been installed, it's time to create a System DSN from it. So let's start the ODBC Data Source (64 bit) application from the Server Manager applet (see below).

Starting ODBC Data Source (64 bit) applet

In the next few screenshots, we can see how an ODBC data source is created.

Step 1: First let's choose the System DSN tab and then click Add...

Creating a System DSN

Step 2: Next we choose the PostgreSQL Unicode (x64) version and click Finish.

Selecting PostgreSQL Unicode (x64) version

Step 3: 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.

Specifying PostgreSQL new data source details

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

Step 1: Start SQL Server Management Studio and connect to the SQL Server instance as "sa" or a sysdmin role member. Expand the Server Objects folder, right click on the Linked Servers node, and then choose "New Linked Server..." option from the pop-up menu.

On the General tab of the New Linked Server dialog box, choose the "Other data source" option, select "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 world_db_postgres.

Creating linked server against the PostgreSQL instance

Step 2: In the Security tab, 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 super user account to keep things simple.

Security option for linked server to PostgreSQL

Step 3: In the Server Options tab, choose the following options:

Specifying linked server data access options

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.

Accessing PostgreSQL tables using linked server

Access PostgreSQL Data from SQL Server

Now that we can see the remote data, let's fetch it into SQL Server. Open a new query window in Management Studio, select the world database and execute the following commands:

INSERT INTO city SELECT * FROM WORLD_SAMPLE.world.[public].city

The result should show 4079 rows have been copied. Next, execute this command:

INSERT INTO country SELECT * FROM WORLD_SAMPLE.world.[public].country

This should show 239 rows have been copied.

To be sure, you can count the number of rows in the local tables.


So now we have it. We have created an ODBC connection against the remote PostgreSQL instance, created a linked server on top of it and then executed two commands to copy across the data. There was no need to export the source data into text files and importing them using BCP or BULK INSERT.

This process can obviously be automated via scripts and stored procedures that are called by SQL Server Integration Services Packages or SQL Server Agent Jobs. SQL Server doesn't give us any option to create push or pull replication subscription against PostgreSQL databases.

We haven't discussed data access speed via ODBC, nor have we discussed any migration pitfalls like data type mismatches. The idea was to show how SQL Server can access PostgreSQL data seamlessly. Executing any PostgreSQL functions or stored procedures from SQL Server is another area your data migration team may have to consider.

Next Steps
  • Stay tuned for the final part of this series
  • Download and install the PostgreSQL ODBC driver and configure a data source and linked server to access PostgreSQL data
  • Visit PostgreSQL official website for more information

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Sadequl Hussain Sadequl Hussain has been working with SQL Server since version 6.5 and his life as a DBA has seen him managing mission critical systems.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, March 27, 2024 - 3:31:49 AM - Juanpe Back To Top (92129)
Hello, I have managed to work perfectly following this guide between postgre and sql server, but when the volume of information to consult/update from sql server to postgre is high my sql server database crashes (the service and the agent stops) and I forces you to restart it. solution?

Thursday, June 22, 2023 - 9:18:51 AM - teja59 Back To Top (91326)
Sir how to solve this ?

Msg 7347, Level 16, State 1, Line 23
OLE DB provider 'MSDASQL' for linked server '<ServerName>' returned data that does not match expected data length for column '[ServerName].[test].[batch].[v_team].Column1'. The (maximum) expected data length is 510, while the returned data length is 548.

Friday, March 25, 2022 - 3:15:47 AM - Alicia Back To Top (89924)
This was the most helpful article I could find on the internet! Thank you so much for your effort.

Monday, November 22, 2021 - 5:27:14 PM - Raphael Ferreira Back To Top (89485)
I have the same exact problem that Manuel Flores has. I can confirm that the ODBC connection is properly configured in the ODBC Data Source Administrator. In fact, when I test the connection in the ODBC Data Source Administrator the connection works perfectly. It is only in SSMS, when creating the linked server, that I get the same error message that Manuel gets. I understand this is a stale page (old), but I would very much appreciate anyone with a resolution to this problem. The link in the error message is a dead end. It ends in a 404, page not found message. Error message:

"TITLE: Microsoft SQL Server Management Studio

The test connection to the linked server failed.


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "BB_SaaS_Migration".
OLE DB provider "MSDASQL" for linked server "BB_SaaS_Migration" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". (Microsoft SQL Server, Error: 7303)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7303-database-engine-error



Thank you in advance for anyone seeing this with something to contribute. Best, Raphael

Wednesday, August 4, 2021 - 11:11:52 PM - Anton Sukhanov Back To Top (89092)
Thats great instruction! Thanks

Wednesday, May 6, 2020 - 9:24:41 AM - Debbie Back To Top (85582)

I've followed the steps of this articles which were clear and simple.

I got pretty far but ran into issues at the end.

1 - I see the tables in the object explorer of my SQl Server under linked server but I cannot select them unless I use openquery. When I try 'select * from linkedserver.databse.schema.table it gives me the error:

The OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" supplied inconsistent metadata for a column. The column "Title" (compile-time ordinal 2) of object ""Riverside"."public"."Contact"" was reported to have a "DBCOLUMNFLAGS_ISLONG" of 128 at compile time and 0 at run time.

and when I right click the linked table and press script to new query... I get the error: Enumerate columns failed for linkedserver

2 - how can I do select top 100 * with a linked server table?

Thursday, November 21, 2019 - 1:04:59 PM - Eddy Jawed Back To Top (83161)

Actaully bro, 

I figured out the problem. I must use OpenQuery to bring data from this linked server. Maybe you could add that into your already brilliant article :) 



Thursday, November 21, 2019 - 11:13:16 AM - Eddy Jawed Back To Top (83160)

Hi Sadequl,

Thank you bro for such an excellent written post. I have successfully connected to pgsql from sql server, and the linked server is showing all the views from the remote database. However when I query them directly like 'SELECT top 100 * FROM [Linked Server Name].[DB Name].[View Schema].[View Name]' the query is taking ages to run, 10 minutes so far and nothing. 

Wednesday, October 9, 2019 - 11:19:11 AM - rodrigo Back To Top (82714)

Hi Is it possible to make this connection to a SQL Server Express running on docker linux?

Is there any article about this?

Friday, October 4, 2019 - 10:16:47 AM - Sadequl Hussain Back To Top (82670)

Hi Bill,

So sorry for not seeing your comment before. I think you can find the workaround for the ODBC error message from these links:



Monday, July 15, 2019 - 1:34:40 PM - Bill Back To Top (81751)

I am trying to setup a linked server from AWS EC2 SQL server to AWS RDS Postgres and followed your procedures step by step, but it does not work, the error message is:

OLE DB provider "MSDASQL" for linked server "RDSDEVPOSTGRES" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "RDSDEVPOSTGRES" returned message "FATAL:  password authentication failed for user "xxxxxx".

The ODBC system DSN was setup and tested successfully, I also tried to connect the AWS Postgres inatance use pgAdmin, it worked fine as well.

I am not sure what I missed, any help is great appreciated.

Thursday, June 21, 2018 - 8:27:14 AM - Shobha Back To Top (76268)

Very easy to understand article specially if you are doing the PostgreSQL migration to SQL Server the first time. Looking forward to next article in series.

I have been working with PostgreSQL to SQL Server migration for a couple of months. Still stumbling with all the various data type conversions, specially arrays, bytea, etc. . Will be nice to have an article on that. We have also purchased the third party OLE DB provider to help with big tables (over 5 million rows).   

Tuesday, April 24, 2018 - 11:49:33 AM - bipin Back To Top (75770)

could not connect to server unknown host. where is server address

Tuesday, December 19, 2017 - 9:33:21 AM - m32pl Back To Top (74242)


 Thanks for a great article.

I finally get it working, just odbc configuration must be performed on the computer where ms-sql is running (sql error 7303)



Friday, March 31, 2017 - 7:11:03 AM - Pawan Back To Top (54012)


Can you please confirm if a Server reboot is required after installing PostgreSQL ODBC driver.



Tuesday, December 13, 2016 - 7:00:27 PM - CJ Back To Top (44964)

Thanks for the post.    

Im trying to connect to PostgreSQL Database which is SSL allow . i manage to create and connect successfully with ODBC Dat Source .

but  im getting below  *Error with SQL Link server  configuration. 

My Server Version details 

SQL 11.0.3156 

PostgreSQL 9.5.4 



Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "PostgreSQL".

OLE DB provider "MSDASQL" for linked server "PostgreSQL" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)


For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.3156&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476



Friday, July 29, 2016 - 9:13:36 PM - Javier Back To Top (43011)

 Gracias! muy completa y precisa la informacion.

Thanks! very torough and accurate information. I was struggling to perform a database lookup to a PostgresSQL DB from a modern application that only supports .NET System.Data.OleDb class data providers. Instead of needlesly spending money on buying a commercial component (I couldn´t put to work the free available ones...) I followed the detailed instructions on linking a Postgres 9 DB in MSSMStudio 2014 and it, after a few tweaks, simply worked. Great! 


Thursday, June 30, 2016 - 1:33:12 PM - Manuel Flores Back To Top (41799)


 Hi, I try to connect SQL Server 2008 R2 Enterprise with Postgre, Created and tested ODBC success, but  when I want to create the Linked Server using the ODBC show me one error, i teked the screen, next

"Cannot initailize the data source object of OLEDB provider "MSDASQL" for Linked Server


OLE DB provider "MSDASQL" for linked server "POSTGRE_CC_REPORTING" returned message 

"[Microsoft][ODBC driver manager] Data source name not found and no default driver specified".

(Microsoft SQL Server, Error:7303)


And I Found the error code, but I can't to resolve this isue.


In my Developer envoriontment It works without problem. but In Production environment Not.


Can you Help me?

Best regards


Tuesday, February 23, 2016 - 4:41:51 PM - Sadequl Hussain Back To Top (40758)

Hi Abouzar,


You can find commercial OLEDB provider for PostgreSQL from the PGNP site:



Tuesday, February 23, 2016 - 12:34:37 AM - abouzar Back To Top (40751)



tanks for your education

i need to to create a link server to postgres via oledb provider instead odbc connection

please help me?

tanks alot

Wednesday, July 29, 2015 - 8:22:05 AM - Sadequl Hussain Back To Top (38311)

Hi Graves,


Not sure why you need to use OPENQUERY instead of directly calling the linked server, and also what is the error message youa re geting.

However, I noticed in your query you have used "workbooks" in the OPENQUERY statement and "workbook" when using the linked server. Do you receive any error message for this? Also, I would recommend using a linked server without any special chacraters like hyphens embedded (for example, BI_PORTAL instead of BI-Portal. If that can be implemented, you can use a command like this:

SELECT * FROM BI_PORTAL.workgroup.[public].domains.

Hope this helps

Tuesday, July 28, 2015 - 3:32:07 AM - Graves Kilsgaard Back To Top (38295)

Hi Sadequl

Thanks for a great article.

I have tried for sometime to integrate a progreSQL database with an MS SQL 2012 database.

I have to write an OPENQUERY: SELECT * FROM OPENQUERY ([BI-Portal], ' SELECT * FROM workbooks ') to get data from ProgreSQL to MS SQL through SSMS.

I cannot write a query like: select * from [Bi-Portal].[workgroup].[public].[domains]

Is that something you have had any experience with?

I have used the ODBC driver as you described :-)

Thanks on advance.

Tuesday, June 30, 2015 - 7:06:28 AM - Sadequl Hussain Back To Top (38077)

Hi Sabir, yes, once you create a linked server to a remote data source, you can access its tables as if they were local. You need to use the <linked_server_name><database_name>.<schema_name>.<table_name> notation to access the remote tables.

Tuesday, June 30, 2015 - 5:18:21 AM - Sabir Hazratzai Back To Top (38073)

Can we use the linked postgre tables directly in our views and stored procedures?

Monday, June 29, 2015 - 7:02:52 PM - Ekbal Back To Top (38066)

Once again excellet Sadeq...  Good piece of informaitons. Cheers

get free sql tips
agree to terms