CODEWITHSUNDEEP
sql-serverpostgresqlodbc
Raphael_S
Raphael_S

Reputation: 180

Error connecting PostgreSQL 9.5 (odbc) as Linked Server to SQL Server 2012

I'm getting an error by connecting a PostgreSQL Database as an Linkd Server on my SQL Server 2012.

First I tried a local connection out of MS Access which worked fine. So I tried to do the same on my SQL Server 2012.

I set up the ODBCx64 Driver from PostgreSQL ODBC Driver Repo and the connection test was succesfull https://i.sstatic.net/xNEwD.jpg

But if I want to add it as a linked server in my SQL Server Mgmt Studio it fails to connect https://i.sstatic.net/wxso9.jpg

Any tips why it won't connect without errors?

I'm using:

Upvotes: 0

Views: 1648

Answers (1)

Raphael_S
Raphael_S

Reputation: 180

I found a solution that works for me.

The wizard in the Microsoft SQL Server Management Studio isn't helpful, I set up the connection by myself. I've tested it with selects and it works

Here's the code I used:

EXEC master.dbo.sp_addlinkedserver 
@server = N'NameShownInMSSQLSrvMgmtStudio', 
@srvproduct=N'PostgreSQL Unicode(x64)', 
@provider=N'MSDASQL', 
@provstr=N'Driver=PostgreSQL Unicode(x64);uid=postgres;Server=SERVERNAME;database=DBNAME;pwd=MyPWD;SSLmode=disable;PORT=5432'

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

Upvotes: 1

Related Questions