Set up PostgreSQL as a linked server in Microsoft SQL Server 32-bit on a Windows Server 2003 64-bit

Environment

• Machine 1
  IP: 10.2.29.183
  OS: Windows Vista Business SP1
  DB: PostgreSQL 8.3, port: 5432
  
  
• Machine 2
  IP: 10.2.29.18
  OS: Windows Server 2003 Enterprise x64 Edition SP2
  DB: Microsoft SQL Server X86 Standard 9.00.1399.06, port: 4900
  

Procedures

• Machine 1
  
  
  
  1. Make sure port 5432 is opened in the firewall.
     
  2. Make sure Machine 2 is granted access to PostgreSQL. The access config file is at C:\Program Files\PostgreSQL\8.3\data\pg_hba.conf in this case. host all all 10.2.29.18/32 md5 is added to the end of the file.
     
  
  
• Machine 2
  
  
  
  1. Install 64-Bit OLEDB Provider for ODBC (MSDASQL), the installer file is WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe in this case.
     
  2. Install 32-bit PostgreSQL ODBC driver, choose the appropriate PostgreSQL version matching the setup (in this case psqlodbc_08_03_xxxx.zip).
     
  3. In SQL Server Management Studio, create a linked server using the following command:
     
EXEC sp_addlinkedserver
@server = '10.2.29.183',
@srvproduct = 'PostgreSQL',
@provider = 'MSDASQL',
@provstr = 'Driver=PostgreSQL ANSI;uid=postgres;Server=10.2.29.183;Port=5432;database=my_db_name;pwd=my_password';

EXEC sp_addlinkedsrvlogin
@rmtsrvname = '10.2.29.183',
@useself = 'false',
@rmtuser = 'postgres',
@rmtpassword = 'my_password';

     
  4. To test the connection:
     
SELECT *
FROM OPENQUERY([10.2.29.183], 'SELECT * FROM information_schema.tables')

     
  
  

References

• "Using 32 bit ODBC driver in x64?". Available: http://forums.techarena.in/windows-x64-edition/698703.htm [Accessed: May 06, 2009].
  
• Postgres OnLine Journal, "SETTING UP POSTGRESQL AS A LINKED SERVER IN MICROSOFT SQL SERVER 64-BIT". Available: http://www.postgresonline.com/journal/index.php?/archives/46-Setting-up-PostgreSQL-as-a-Linked-Server-in-Microsoft-SQL-Server-64-bit.html [Accessed: May 06, 2009].