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:firstname.lastname@example.org/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.