How to create a Linked Server for SQL Server to PostgreSQL

29 May,2018 by Jack Vamvas

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.

Postgresql_DSN

 

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 - exapand Linked Servers from the SSMS GUI. You can execute some SELECT and INSERT statements using the 4 part naming syntax  convention for


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to create a Linked Server for SQL Server to PostgreSQL


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer