1

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.

  • 1
    As far as I know you can never use Windows authentication if the client is not a Windows host either. For example: Microsoft only ships Windows DLLs for their JDBC driver to enable Windows authentication. Your Postgres server is the client (from SQL Server's point of view) and is connecting from a non-Windows OS. I think you will have a lot less headaches if you switch SQL Server to mixed authentication and use a SQL Server user for the FDW. – a_horse_with_no_name Apr 2 '15 at 13:04
  • Yeah I'll think the same but switching authentication its not really my call, I thought there might be a way around this... – Nelz Apr 2 '15 at 13:06
  • 1
    It'd be quite tricky to support this I think. We'd probably have to have a way to tunnel the credentials request from the Windows server through the FDW and to the client, or we'd need some kind of proxy/delegation rights given to the PostgreSQL server so it can "act as" any user. – Craig Ringer Apr 2 '15 at 15:06
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:postgres@10.0.0.0/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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.