Unable to connect to MSSQL instance.

[dbackowski]

I have MSSQL server running with instance name 'OPTIMA' on 10.0.0.2.

Already tried:

Setting servername and port as instance name

CREATE SERVER mssql_server
                FOREIGN DATA WRAPPER tds_fdw
                OPTIONS (servername '10.0.0.2', port 'OPTIMA'); 

error message:

ERROR:  DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (10.0.0.2), OS #: 110, OS Msg: Connection timed out, Level: 9

Setting servename as ip address with instance name

CREATE SERVER mssql_server
                FOREIGN DATA WRAPPER tds_fdw
                OPTIONS (servername '10.0.0.2\OPTIMA'); 

error message:

ERROR:  DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (10.0.0.2\OPTIMA), OS #: 0, OS Msg: Success, Level: 9

Create server configuration in freetds.conf:

[myserver]
  host = 10.0.0.20
  instance = OPTIMA

and connect by server name

CREATE SERVER mssql_server
                FOREIGN DATA WRAPPER tds_fdw
                OPTIONS (servername 'myserver'); 

error message:

ERROR:  DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: (null), Level: 15

Connection by tsql works:

tsql -S myserver

result:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>

[dbackowski]

Please close this issue, had typo in configuration, now everything works.

[GeoffMontee]

Hi @dbackowski,

I'm glad that you were able to figure out the cause of your problem. I'll close this. Please let me know if you run into any more trouble.

[thedumbtechguy]

Sorry if I have to bump this, but I am facing the exact same issue.
Connection via isql works but no matter what I do, I can't seem to connect from Postgres.

@dbackowski where did you notice the typo?

[dbackowski]

@frostymarvelous i don't remember

[thedumbtechguy]

I finally got it working. It wasnt a typo, but I still don't know for sure.

I removed the port option and added the tds-version and it seemed to work
after.

But I kept tinkering so much I can't be certain.

On Fri, Jun 24, 2016, 6:27 AM Damian Baćkowski notifications@github.com
wrote:

@frostymarvelous https://github.com/frostymarvelous i don't remember

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
#62 (comment), or mute
the thread
https://github.com/notifications/unsubscribe/AA4XZflMa3hK7MrvrEa-cpjstYyPZJwtks5qO3jegaJpZM4HocXe
.

[lleirborras]

Hey @frostymarvelous can you show me your configuration? I'm facing the same issues but with error code 14 .
Thanks

[thedumbtechguy]

Sorry, I left the job last month. I don't have the source.

[GeoffMontee]

Hi @lleirborras,

Is your tds_fdw built from the latest master? If so, you might be able to get more information about the problem by:

1.) Adding msg_handler for your foreign server:

ALTER SERVER mssql_server
   OPTIONS (ADD msg_handler 'notice');

2.) Set client_min_messages to DEBUG3 in the session in which you are querying the table.

SET client_min_messages=DEBUG3;

[wchmei]

start SQL Server Browser
vim /etc/freetds.conf
[dbbak]
host = 192.168.49.1
instance = db2--instance_name
tsql -S dbbak -U sa -P password -D sqlserver_db

[salvaguardia]

I had the same issue until I used the 'database' parameter when defining the foreign server (an Azure SQL Server with multiple databases)

[gxclark]

Hello @GeoffMontee ,
Thanks for your great work on this, and answering questions. I seem to have a similar issue, and I've followed the advice in a number of ways, but alas, I still cannot query my mssql server from postgres.

military=# select * from pg_foreign_server;
  oid   |   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                          srvoptions                                           
--------+-------------+----------+--------+---------+------------+--------+-----------------------------------------------------------------------------------------------
 163845 | mssql_win7b |       10 | 163844 |         |            |        | {servername=192.168.10.101,port=1433,database=vgaf_navies,tds_version=7.4,msg_handler=notice}
(1 row)

military=# SET client_min_messages=DEBUG3;

military=# CREATE FOREIGN TABLE vgaf_navies.iso639_lanuages_ft ( language_a3_cd varchar(255)) SERVER mssql_win7b OPTIONS (query 'select language_a3_cd from dbo.iso639_lanuages', row_estimate_method 'showplan_all');
CREATE FOREIGN TABLE
military=# select * from vgaf_navies.iso639_lanuages_ft;
DEBUG:  tds_fdw: Using remote estimate
DEBUG:  tds_fdw: Getting query
DEBUG:  tds_fdw: Query is explicitly set
DEBUG:  tds_fdw: Value of query is select language_a3_cd from dbo.iso639_lanuages
DEBUG:  tds_fdw: Initiating DB-Library
DEBUG:  tds_fdw: Getting login structure
DEBUG:  tds_fdw: Setting login user to sa
DEBUG:  tds_fdw: Setting login password to Klingon$98007
DEBUG:  tds_fdw: Setting login tds version to 7.4
DEBUG:  tds_fdw: Setting login database to vgaf_navies
DEBUG:  tds_fdw: Connection string is 192.168.10.101:1433
DEBUG:  tds_fdw: Connecting to server
DEBUG:  Failed to connect using connection string 192.168.10.101:1433 with user sa
ERROR:  DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (192.168.10.101), OS #: 0, OS Msg: Success, Level: 9

I know I can connect to the server:
[postgres@pgsql-mochida ~]$ netcat -w3 -4 -v 192.168.10.101 1433 Connection to 192.168.10.101 1433 port [tcp/ms-sql-s] succeeded!

What is strange is that I appear to be able to connect from another remote server as user "sa" using JDBC.

jdbc:sqlserver://192.168.10.101:1433;databaseName=vgaf_navies;selectMethod=cursor;trustServerCertificate=true
select * from dbo.iso639_languages;

This returns data ... so I removed the user mapping for "sa", added another for "analyst1", made a new JDBC connection and was able to get data ...

military=# select * from vgaf_navies.iso639_lanuages_ft;
DEBUG:  tds_fdw: Using remote estimate
DEBUG:  tds_fdw: Getting query
DEBUG:  tds_fdw: Query is explicitly set
DEBUG:  tds_fdw: Value of query is select language_a3_cd from dbo.iso639_lanuages
DEBUG:  tds_fdw: Initiating DB-Library
DEBUG:  tds_fdw: Getting login structure
DEBUG:  tds_fdw: Setting login user to analyst1
DEBUG:  tds_fdw: Setting login password to rom4ulan
DEBUG:  tds_fdw: Setting login tds version to 7.4
DEBUG:  tds_fdw: Setting login database to vgaf_navies
DEBUG:  tds_fdw: Connection string is 192.168.10.101:1433
DEBUG:  tds_fdw: Connecting to server
DEBUG:  Failed to connect using connection string 192.168.10.101:1433 with user analyst1
ERROR:  DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (192.168.10.101), OS #: 0, OS Msg: Success, Level: 9

Would you please advise about what else I can try?

[gxclark]

Well, just to clarify what I think the problem is above, it seems to be an issue with the inability to disable TLS/SSL using the version 18 driver of MS SQL ODBC.

grafana/grafana#24589