Communication between PostgreSQL and SQL Server

Question

It's been three days that I've been looking for a solution for this scenario I'm in: I need to access to a SQL Server 2000 hosted in a Windows Server 2000, from a PostgreSQL 9 x hosted on Debian 7. This is because me and my team will mount a web server and we need to extract some info from the database (the one in SQL Server 2000).

And no matter how many pages I have visited, I can't find a solution, it seems that there isn't much documentation about the topic. I've read some things like pgadmin, foreign data wrappers, dbi.links... But none of the documentation that I have seen exposes a viable solution since there is not a single and straightforward tutorial.

I just want to make a simple select or update query from PostgreSQL to SQL Server 2000. I really need help.

Can you talk ODBC to your SQL server 2000 install? If so try github.com/ZhengYang/odbc_fdw or github.com/CartoDB/odbc_fdw — David-SkyMesh
– David-SkyMesh, Commented Sep 1, 2016 at 4:10
A few hours ago i made it work with foreing data wrappers. I'm new to all this tools and i think that's why it was so hard for me to understand. Thanks for your response! — Andres Gabauquer
– Andres Gabauquer, Commented Sep 2, 2016 at 6:51

Anton · Accepted Answer · 2016-09-01 04:23:49Z

2

https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql-foreign-data-wrapper-configuration--part-3/

Get TDS Foreign data wrapper from here http://pgxn.org/dist/tds_fdw/

Also you need to open the ports

answered Sep 1, 2016 at 4:23

Anton

2,8821 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andres Gabauquer Over a year ago

Actually, right now i'm using that guide and i was able to create the FDW, but i still don't have i connection. But you said something key right there, are you talking about opening the 1433 port on SQLServer?

Anton Over a year ago

We had similar issue, and after changing firewall settings, it was resolved. I'm not sure what exactly should be done in your case as all systems are different, but here is the link which shows how other people managed it. mssqltips.com/sqlservertip/3661/… The main idea is that there are multiple levels of security controls. First is on SQL Server side (enabled remote connections, enabled port, enabled protocols), the second is on Postgres server. Hardware may also have own firewall rules.

Andres Gabauquer Over a year ago

Thank you Anton. I'm glad to say that a few hours ago i managed to make it work! I'm planning to make a long post explaining the steps in a very comprehensible way, in case someone finds himself struggling in a similar scenario.

Collectives™ on Stack Overflow

Communication between PostgreSQL and SQL Server

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related