SQL Server 2012:将链接的服务器添加到PostgreSQL [英] SQL Server 2012: Add a linked server to 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?
谢谢.
Jerome
[2013年2月8日更新] 我用今天实现的测试结果来完成这篇文章.
[Update 2/08/2013] I complete this post with the result of a test I realize today.
在使用数据包嗅探器时，我检查了通过ODBS数据源管理器(64位)和SMSS下的链接服务器在测试连接时发送的数据包.
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 SMSS.
两个系统之间的数据相同:
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.
对于SMSS，我收到一条错误消息(如上所述).
For SMSS, I receive an error message (as describe upper).
推荐答案
好，我找到了解决方法.
Ok, I found the solution.
在pg_hba.conf文件中，我将从MD5发送密码的方法更改为Trust.重新加载服务器后，我的链接服务器连接正常.
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
希望对其他民族有所帮助.
In hope that help others peoples.
这篇关于SQL Server 2012:将链接的服务器添加到PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
