SQL Server 2012:向 PostgreSQL 添加链接服务器

SQL Server 2012: Add a linked server to PostgreSQL(SQL Server 2012:向 PostgreSQL 添加链接服务器)

问题描述

我尝试将 PostgreSQL 作为链接服务器连接到 SQL Server 2012 服务器

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.

我解释一下:

在 SQL Server 上，我安装了 PostgreSQL 的 ODBC 驱动程序 (psqlodbc_09_02_0100-x64).我为 PostgreSQL 上的特定数据库创建了一个系统 DSN.此 DSN 工作正常.

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.

使用 SSMS，我运行此命令以添加链接服务器:

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

运行后，我有一个名为lnk_test"的新链接服务器.当我收到以下错误消息时:

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)

错误信息是法语，翻译过来就是:用户postgres"的密码验证失败.我在PostgreSQL服务器的日志中发现了同样的错误信息.

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?

提前致谢.

杰罗姆

[更新 2/08/2013]我用我今天意识到的测试结果完成了这篇文章.

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

使用数据包嗅探器时，我会检查通过 ODBS 数据源管理器(64 位)和 SSMS 下的链接服务器测试连接时发送的数据包.

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:

• 打开与 PostgreSQL 的连接

• Opening the connection to the PostgreSQL

发送连接字符串(所有参数都一样)

Sending the connection string (all parameters are the same)

PostgreSQL 要求输入密码的响应(只是salt 值不同，但很正常)

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

密码以同样的方式发送(用MD5加密)

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

但是之后，服务器的响应不同:

But after, the response of the server differ:

对于 ODBC 数据源，一切正常.我收到身份验证并可以发送测试查询.

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

对于 SSMS，我收到一条错误消息(如上文所述).

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

推荐答案

好的，我找到了解决方案.

Ok, I found the solution.

在 pg_hba.conf 文件中，我将发送密码的方法从 MD5 更改为信任.重新加载服务器后，我的链接服务器连接正常.

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.

条目现在是:

Type Database User Address   Method

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

希望能帮助到其他人.

这篇关于SQL Server 2012:向 PostgreSQL 添加链接服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！