Search code examples
sql-serverpostgresqlazurelinked-server

Is there a way to create a linked server from an Azure SQL database to PostgreSQL?

I have been doing some research and I am yet to see a solution regarding this question. Does anyone know if it's actually possible to create a linked server from an Azure SQL database in order to connect it to a PostgreSQL database?

I noticed the folder server objects is present in the local database, however, on Azure that folder is non-existent. Am I right to assume linked server is not supported on the Azure SQL database?

Solution

  • Yes, you're right.

    Azure SQL database(single database) doesn't support linked server. We can not create the linked server to any other Server.

    enter image description here

    I found an another way may can help you connect to Postgresql.

    Please reference this tutorial: Connect to PostgreSQL as an External Data Source using PolyBase:

    Use the CData ODBC Driver for PostgreSQL and PolyBase to create an external data source in SQL Server 2019 with access to live PostgreSQL data.

    Creating a Master Encryption Key

    Execute the following SQL command to create a new master key, 'ENCRYPTION,' to encrypt the credentials for the external data source.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

    Creating a Credential Database

    Execute the following SQL command to create credentials for the external data source connected to PostgreSQL data.

    CREATE DATABASE SCOPED CREDENTIAL postgresql_creds
WITH IDENTITY = 'username', SECRET = 'password';

    Create an External Data Source for PostgreSQL

    Execute the following SQL command to create an external data source for PostgreSQL with PolyBase, using the DSN and credentials configured earlier.

    CREATE EXTERNAL DATA SOURCE cdata_postgresql_source
WITH ( 
  LOCATION = 'odbc://SERVERNAME[:PORT]',
  CONNECTION_OPTIONS = 'DSN=CData PostgreSQL Sys',
  -- PUSHDOWN = ON | OFF,
  CREDENTIAL = postgresql_creds
);

    Create External Tables for PostgreSQL

    The statement to create an external table based on a PostgreSQL Orders would look similar to the following:

    CREATE EXTERNAL TABLE Orders(
  ShipName [nvarchar](255) NULL,
  ShipCity [nvarchar](255) NULL,
  ...
) WITH ( 
  LOCATION='Orders',
  DATA_SOURCE=cdata_postgresql_source
);

    I couldn't test this for you because I don't have PostgreSQL.

    Hope this helps.