TDS FDW

The TDS_FDW enables integration from PostgreSQL to SQL Server and Sybase (which both use the TDS protocol). TDS_FDW is available in the BigSQL distribution on Linux, OS X and Windows.

Pre Requisites

On Linux, you need to install FreeTDS:

  yum install epel-release
  yum install freetds

Ensure that your SQL Server is properly configured to allow remote connections, is running the TCP/IP protocol and is listening on a static port. Ensure that the user in SQL Server is configured properly and has rights to the database and table you are trying to remotely access. For the sake of the example below, we assume you can succesfully connect to Microsoft SQL or Sybase from the user that runs Postgres using the tsql command line util that is part of FreeTDS:

  tsql -S 10.30.3.147 -U tdstest

Our sample table in TDS is defined like this:


SELECT * FROM TestDB.INFORMATION_SCHEMA.Tables SELECT * FROM t1
     Name    Null?      Type
     ------  ---------  -----------
     A       NOT NULL   NUMBER(38)
     B       NOT NULL   NUMBER(38)

Installation

First install the tds_fdw using BigSQL Manager or the PGC command line:

  ./pgc install tds_fdw1-pg95

Then configure PostgreSQL, from psql as the superuser, as follows:

postgres=# create extension tds_fdw;
CREATE EXTENSION
postgres=# create server TDS_TestDB foreign data wrapper tds_fdw options (servername '192.168.1.101', port '1433', database 'TestDB', tds_version '4.2', character_set 'UTF-8');
CREATE SERVER
postgres=# grant usage on foreign server TDS_TestDB to postgres;
GRANT
postgres=# create user mapping for postgres server TDS_TestDB options (username 'tdstest', password 'tdspassword');
CREATE USER MAPPING
postgres=# create foreign table t1 (a integer, b integer) server TDS_TestDB options (table 'T1');
CREATE FOREIGN TABLE

Now your remote table t1 can be used in SQL statements like any normal postgres table or view.


Notes

FreeTDS supports logging to a text file by setting the TDSDUMP environment variable. This log is especially helpful for debugging connection and security problems. Some error messages returned from SQL Server are not returned to psql (or another PostgreSQL client), but are logged in this file. Remember to restart your Postgres server after changing environment variables.


Learn More

Learn more about using TDS FDW perusing its documentation.