SQL Server 2012: Add a linked server to PostgreSQL

Question

I try to connect a PostgreSQL to SQL Server 2012 server as linked server

I found some advices on different forum and follow it. But I'm stuck with an authentication problem.

I explain:

On the SQL Server, I have install the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a specific database on the PostgreSQL. This DSN work correctly.

Using SSMS, I run this command to add the linked server:

EXEC master.dbo.sp_addlinkedserver 
@server = N'lnk_test', 
@srvproduct=N'PostgreSQL Unicode(x64)', 
@provider=N'MSDASQL', 
@provstr=N'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=test1;database=accueil;pwd=MyPassword;SSLmode=disable;PORT=5432'

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'lnk_test',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL

After running, I have a new Linked Server called "lnk_test". When I receive the following error message:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "pgTest1_accueil".
OLE DB provider "MSDASQL" for linked server "pgTest1_accueil" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "pgTest1_accueil" returned message "FATAL: authentification par mot de passe échouée pour l'utilisateur  « postgres »". (Microsoft SQL Server, Error: 7303)

The error message is in french, the traduction is: "authentication by password failed for user "postgres". I find the same error message in the log of the PostgreSQL server.

Is someone have an idea for resolving this problem?

Thanks in advance.

Jerome

[Update 2/08/2013] I complete this post with the result of a test I realize today.

WHen using a packet sniffer, I check the packets sended when testing the connection through the ODBS Data Source Administrator (64-bit) and through the Linked Server under SSMS.

The data are the same between the 2 system for:

• Opening the connection to the PostgreSQL

• Sending the connection string (all parameters are the same)

• the response of PostgreSQL asking for password (only different is the salt value, but it's normal)

• the password are sended in the same way (crypted with MD5)

But after, the response of the server differ:

For the ODBC Data Source, all works correctly. I receive the authentication and can send test query.

For SSMS, I receive an error message (as describe upper).

Solution 1

Ok, I found the solution.

In the pg_hba.conf file, I change the method for sending passwords from MD5 to trust. After reloading the server, my linked server connection works.

the entry is now:

Type Database User Address   Method

host all      all  x.x.x.x/x trust

In hope that help others peoples.

Solution 2

I think I found the answer; I was still getting the error with a recent version of the ODBC driver and Postgres 10.x.

The connection will work when tested in the Windows ODBC diaglog, but will fail in SQL Server. When setting the password for the remote user, place the password in curly braces.

{password}

This will keep the ODBC connection string from getting screwed up if you have certain symbols in your user's password.

Comments

[Basil Musa]

But doesn't this introduce a security issue with the "trust" method?

[ps2goat]

I'm not sure this even addresses the actual problem. I have two postgres users, one of which an odbc connection works and one which I get errors (including password failed) when trying to connect in SQL Server via a linked server.

[Alexander Goida]

It works {password}. But how come we should have knew about this feature? Where in documentation is it described?

[ps2goat]

@AlexanderGoida I found it here -- It took a lot of searching before I found that thread