Babelfish for PostgreSQL supports linked servers from the PostgreSQL 15.2 (Babelfish 3.1.0) release by using the tds_fdw (TDS Foreign Data Wrapper) PG extension. To work with linked servers, you will need to install the tds_fdw PostgreSQL extension. For more information about the tds_fdw extension please see https://github.com/tds-fdw/tds_fdw.

To use linked servers you will need to build the babelfishpg_tsql extension with certain flags and create the tds_fdw extension in the babelfish database as mentioned in the build instructions: https://github.com/babelfish-for-postgresql/babelfish_extensions/blob/BABEL_3_X_DEV/contrib/README.md#how-to-build-the-babelfishpg_tsql-extension-with-linked-servers-enabled

Babelfish supports adding remote SQL Server or Babelfish endpoints as a linked server. You can then use OPENQUERY() to retrieve data from these linked servers. Starting from Babelfish version 3.2.0, four-part names are also supported.

In order to use linked servers, support for the following stored procedures and views have been added:

• Procedures:

  • sp_addlinkedserver

    • Babelfish does not support the @provstr parameter.

  • sp_addlinkedsrvlogin

    • You will need to provide an explicit remote username and password that can be used to connect to the remote data source. Connection using user’s self credentials is not supported. Babelfish currently supports only @useself = true.
    • Since configuring remote server access specific to local login is not supported, Babelfish does not currently support the @locallogin parameter.

  • sp_linkedservers

  • sp_helplinkedsrvlogin

  • sp_dropserver

  • sp_droplinkedsrvlogin

    • Since configuring remote server access specific to local login is not supported, Babelfish does not currently support the @locallogin parameter.

  • sp_serveroption Babelfish supports following server options:

    • query timeout (from Babelfish version 3.2.0)
    • connect timeout (from Babelfish version 3.3.0)

  • sp_testlinkedserver (from Babelfish version 3.3.0)

  • sp_enum_oledb_providers (from Babelfish version 3.3.0)

• Views:

  • sys.servers
  • sys.linked_logins

The connection from the source Babelfish for PostgreSQL server to the target remote server uses encryption in transit (TLS/SSL) depending on the remote server’s database configuration. If the remote server isn't configured for encryption, the source Babelfish for PostgreSQL server making the request to the remote database falls back to unencrypted.

If the target linked server is a Babelfish for PostgreSQL instance, set babelfishpg_tsql.tds_ssl_encrypt = on and ssl = on for the target server. For more information about SSL/TLS configuration for Babelfish for PostgreSQL, see Babelfish Security - SSL/TLS support for client connections.

Babelfish for PostgreSQL can be added as a linked server from a SQL Server. On a SQL Server database, you can add Babelfish as a linked server using Microsoft OLE DB provider for ODBC : MSDASQL.

There are two ways to configure Babelfish as a linked server from SQL Server using MSDASQL provider:

• Providing ODBC connection string as the provider string.
• Provide the System DSN of ODBC data source while adding the linked server.

1. OPENQUERY() works only for SELECT and doesn't work for DML.
2. Four-part object names work only for reading and doesn't work for modifying the remote table. An UPDATE can reference a remote table in the FROM clause without modifying it.
3. Executing stored procedures against Babelfish linked servers isn't supported.
4. Babelfish major version upgrade might not work if there are objects dependent on OPENQUERY() or objects referenced through four-part names. You must ensure that any objects referencing OPENQUERY() or four-part names are dropped before a major version upgrade.
5. The following datatypes don't work as expected against remote Babelfish server: nvarchar(max), varchar(max), varbinary(max), binary(max) and time. We recommend using the CAST function to convert these to the supported datatypes.