I been trying to get data from a SQL Server to use it on a PostgreSQL database. After a previous question I got into use FOREIGN DATA WRAPPERS, first I try with tds_fdw
but after a successfull install I couldn't make it work, possibly due to the same issue I detail below.
After this i went to Multicorn and things where a bit better since there actual documentation for the usage of it.
After attempting to add a table from SQL Server I got the message:
ERROR: Error in python: OperationalError DETAIL: (OperationalError)
SQL Server message 18456, severity 14, state 1, line 1: **Login failed
for user 'postgres'**. DB-Lib error message 18456, severity 14: General
SQL Server error: Check messages from the SQL Server DB-Lib error
message 20002, severity 9: Adaptive Server connection failed
So I got it that its an authentication error, the SQL Server works with Windows Authentication so my connection string (mssql+pymssql://postgres:postgres@10.0.0.10/ForeignDatabase
) its rendered useless. After some more search i got to this answer:
The check_mssql_server.py plugin will NOT work with Windows
Authentication and requires SQL authentication.As of this time I do not believe anyone has created a MSSQL plugin
that can use Windows Authentication.
Clearly not the same scenario but its also a Data wrapper operation giving a extremely similar error message.
So my question is: Is it true that there's no way of using a foreign data wrapper from PostgreSQL to SQL Server using Windows Authentication?. I did add a new user (postgres:postgres
) to the SQL Server.
I will also like to add that the PostgreSQL 9.3 Server lives on an Ubuntu Server 14.04 and the SQL Server 2005 on a Windows Server 2003.
Best Answer
Ok, thanks to the guidance of the community I manage to got it to work... I believe its important for me to mention a few things you should take in account when attempting this (at least under the same environment I been trying).
My setup:
Additional components:
Im not gonna go into details on how to install this components since there's plenty of documentation online.
Once this is setup I took a_horse_with_no_name advice on changing SQL Server authentication to mixed authentication and created a new account, for the sake of the example I use
postgres:postgres
. Additionaly still on SSMS I definedSELECT
permissions for userpostgres
in the table I wanted to get the data fromtblTest
.Back in my PostgreSQL I created a new database (
pgTest
) and setup multicorn:CREATE EXTENSION multicorn;
Created the server for our FOREIGN DATA WRAPPER:
And then (what it was supposed to be the last step) create the table that is going to get data from MS SQL Server:
This shows no error, but once you throw a simple
SELECT * FROM pgTable;
I was getting the error I OP up there.So I search a bit more and stumbled on this Google Groups posts labeled on a response as a BUG.
One of the offered solutions is getting rid of pymssql, as in:
apt-get uninstall python-pymssql; pip install pymssql
After this I tried again to create the table and getting data but nothing happened, i was just getting a blank table.
So I
pip install pymssql --upgrade
, once again create the table within PostgreSQL and voila! this time worked perfect.Hopefully this will help someone else in the future, thanks for your guidance!