How to create a Linked Server for SQL Server to PostgreSQL

Question: I need to create a SQL Server Linked Server connection from a SQL Server to a PostgreSQL database on Linux.  The purpose is to check through a basic test from INSERT and SELECT statements for data insert and retrieval .

I've checked through a few sites and there are different approaches - do you have a standardized method which you utilize for this purpose.?

Answer: Increasingly I'm getting more requests within my own projects to create connectivity between different database server systems. It's not uncommon to link SQL Server with all other systems such as DB2 LUW, Oracle, MariaDB etc.

The increase use of the 12 factor app and database services  development methodology  and more on-site\Cloud hybrid architectures has resulted in more database server types connecting and exchanging data

Creating a Linked Server from SQL Server 2016 to a PostgreSQL db  requires a few steps. For the purposes of this post , I'll assume there is a PostgreSQL server set up , with a database and port configured for external access

Step 1 -  Download the relevant drivers from the official PostgreSQL driver download site and install on your server hosting SQL Server

Step 2 - Configure a System DSN to the PostgreSQL database.You'll need a valid :server, username\password,database, port.

Once you've confirmed you can "Test" OK - move to step 3 . If you're having problems connect with your PostgreSQL DBA.

Step 3 - In your SQL Server - log in with suitable permissions to create a Linked Server. This is the code I use

EXEC master.dbo.sp_addlinkedserver @server = N'MY_LINKED_SERVER_NAME', @srvproduct=N'PostgreSQL35W', 
 @datasrc='PostgreSQL35W', @provider=N'MSDASQL', @provstr=N'Driver=PostgreSQL 64-bit ODBC Drivers;uid=my_user_ID;Server=my_server_name;database=my_db;pwd=my_pwd'

 /* For security reasons the linked server remote logins password is changed with ######## */
 EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MY_LINKED_SERVER_NAME',
 @useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL 

Step 4 - To confirm this works - expand Linked Servers from the SSMS GUI. You can execute some SELECT and INSERT statements using the 4 part naming syntax  convention.