Setting up PostgreSQL as a Linked Server in Microsoft SQL Server 64-bit

We would like to thank Jeff Crumbley of IILogistics for providing many of these steps and informing us that Microsoft has finally released a 64-bit OLEDB for ODBC driver.

For those who have not experienced the torture of this situation - let me start with a little background. First if you are running SQL Server 2005 32-bit and wished to create a linked server to a PostgreSQL server, everything is hunky dory. If however you had a SQL Server 2005 64-bit server, you ran into 2 very annoying obstacles.

  1. Obstacle 1: There for a long-time was no 64-bit ODBC driver nor native driver for PostgreSQL. This obstacle was somewhat alleviated when Fuurin Kazanbai made experimental compiled 64-bit PostgreSQL ODBC drivers available which work for AMD and Intel based processors.
  2. Obstacle 2: All looked good in the world until you tried this in SQL Server 2005 64-bit and low and behold - you needed a 64-bit OLEDB provider for ODBC to use it in SQL Server 2005 64-bit. Yes we waited patiently for years for this piece to be available. We still love you Microsoft. Then as Jeff Crumbley pointed out - Microsoft released an OLEDB 64-bit provider for ODBC in early April 2008.

UPDATE: Since we wrote this article, PostgreSQL now comes with a 64-bit ODBC driver you can download from http://www.postgresql.org/ftp/odbc/versions/msi/. To use these make sure to use MSDASQL.1 instead of MSDASQL. In fact you should probably be using MSDASQL.1 anyway. refer to our newer article SQL Server 64-bit Linked Server woes

Below are the steps to get a PostgreSQL linked server working in SQL Server 2005 64-bit.

  1. Run WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe - (Available as of 4/4/2008 from: http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en) (If you are running Vista 64-bit or Windows 2008 64-bit these are included already (or possibly in SP1))
  2. Make the folder C:\Program Files\PostgreSQL\8.1\AMD64bin (seems to also work fine against 8.3/8.4 if you are running that) and place the dlls from psqlodbc_AMD64 available from http://www.geocities.jp/inocchichichi/psqlodbc/index.html There is a newer compiled 64-bit ODBC driver at http://code.google.com/p/visionmap/wiki/psqlODBC If you are using this newer driver the use PostgreSQL 64-bit ODBC Drivers for the driver name instead of what we have below. The newere driver doesn't seem to handle data type conversion quite as well as the older.
  3. Run the psqlodbcwAMD64.reg file
  4. Create a System DSN in the 64-bit Data Source (ODBC) - alternatively you can skip this and use and embedded file DSN in SQL Server 2005 that we will outline in the next step.
  5. Create a Linked Server in SQL Server - below is a sample script that creates a PostgreSQL Linked Server in Microsoft SQL Server 2005 64-bit. 
    
EXEC master.dbo.sp_addlinkedserver @server = N'NAMEOFLINKEDSERVERHERE', @srvproduct=N'PostgreSQL AMD64A', 
    @provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL AMD64A;uid=pguser;Server=pghost;database=pgdatabase;pwd=somepassword'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'NAMEOFLINKEDSERVERHERE',
        @useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
    After that you should see the linked server in SQL Server 2005 Management ->Server Objects ->Linked Server and from there you can fiddle further with the settings. You should also be able to expand the PostgreSQL linked server and see the tables and views.
  6. To test out the linked server - you can run the sample query below in SQL Server: 
    
SELECT * 
    FROM 
        OpenQuery(NAMEOFLINKEDSERVERHERE, 
            'SELECT * From information_schema.tables')

Keep in mind that the PostgreSQL 64-bit ODBC is marked as experimental, but we have had good success with it on an Intel processor based 64-bit Windows 2003 running SQL Server 2005 64-bit.