Foreign Data Wrappers & Windows Authentication

Question

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:[email protected]/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.

Answer 1

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:

• Ubuntu 14.04 <-> PostgreSQL 9.3
• Windows Server 2003 <-> SQL Server 2005

Additional components:

• SQL Alchemy 0.9.9
• Multicorn 1.1.0

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 defined SELECT permissions for user postgres in the table I wanted to get the data from tblTest.

Back in my PostgreSQL I created a new database (pgTest) and setup multicorn:

CREATE EXTENSION multicorn;

Created the server for our FOREIGN DATA WRAPPER:

CREATE SERVER alchemy_srv FOREIGN DATA WRAPPER multicorn options (
    wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
);

And then (what it was supposed to be the last step) create the table that is going to get data from MS SQL Server:

CREATE FOREIGN TABLE pgTable (LogTime varchar, 
                              LogUser varchar, 
                              LogData varchar) 
server alchemy_srv OPTIONS   (tablename 'tblTest', 
                           db_url 'mssql+pymssql://postgres:[email protected]/RemoteDBName');

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!