Issue report

The following information is very important in order to help us to help you. Omission of the following details cause delays or could receive no attention at all.

Operating system

On recent GNU/Linux distributions, you can provide the content of the file /etc/os-release

NAME="Red Hat Enterprise Linux Server"
VERSION="7.9 (Maipo)"
ID="rhel"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Red Hat Enterprise Linux"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:7.9:GA:server"
HOME_URL="https://www.redhat.com/"
BUG_REPORT_URL="https://bugzilla.redhat.com/"

REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 7"
REDHAT_BUGZILLA_PRODUCT_VERSION=7.9
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="7.9"

Version of tds_fdw

From a psql session, paste the outputs of running \dx

If you built the package from Git sources, also paste the output of running git log --source -n 1 on your git clone from a console

                                                   List of installed extensions
       Name       |    Version    |   Schema   |                                    Description
------------------+---------------+------------+-----------------------------------------------------------------------------------
 edb_dblink_libpq | 1.0           | pg_catalog | EnterpriseDB Foreign Data Wrapper for PostgreSQL
 edb_dblink_oci   | 1.0           | pg_catalog | EnterpriseDB Foreign Data Wrapper for Oracle
 edbspl           | 1.0           | pg_catalog | EDB-SPL procedural language
 pldbgapi         | 1.0           | pg_catalog | server-side support for debugging PL/pgSQL functions
 plpgsql          | 1.0           | pg_catalog | PL/pgSQL procedural language
 tds_fdw          | 2.0.0-alpha.3 | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(6 rows)

Version of PostgreSQL

From a psql session, paste the output of running SELECT version();

                                                    version
---------------------------------------------------------------------------------------------------------------
 EnterpriseDB 10.17.26 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

Version of FreeTDS

How to get it will depend on your Operating System and how you installes FreeTDS

From a console:

On RPM based systems: rpm -qa|grep freetds
On Deb based systems: dpkg -l|grep freetds
_If you built your own binaries from source code, then go to the sources, and run: grep 'AC_INIT' configure.ac_

AC_INIT(FreeTDS, 1.1.15)

Logs

Please capture the logs when the error you are reporting is happening, as well as commands with their outputs if you are reporting a problem build or installing

_For problems using tdsfdw on PostgreSQL how to do it will depend on your system, but if your PostgreSQL is installed on GNU/Linux, you will want to use tail -f with the log of the PostgreSQL cluster

For MSSQL you will need to use the SQL Server Audit Log

Sharing the below logs getting in /tmp/freetds.log log file created on linux OS.

log.c:168:Starting log file for FreeTDS 1.1.15
        on 2021-12-14 17:31:30 with debug flags 0x4fff.
dblib.c:1254:tdsdbopen: Calling tds_connect_and_login(0x11642f0, 0x1164870)
iconv.c:326:tds_iconv_open(0x1163f30, UTF-8)
iconv.c:186:local name for ISO-8859-1 is ISO-8859-1
iconv.c:186:local name for UTF-8 is UTF-8
iconv.c:186:local name for UCS-2LE is UCS-2LE
iconv.c:186:local name for UCS-2BE is UCS-2BE
iconv.c:348:setting up conversions for client charset "UTF-8"
iconv.c:350:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:389:tds_iconv_open: done
net.c:391:Connecting with protocol version 7.3
net.c:318:Connecting to 10.183.73.7 port 1433
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:528:tds_open_socket() succeeded
packet.c:801:Sending packet
0000 12 01 00 3a 00 00 00 00-00 00 1a 00 06 01 00 20 |...:.... ....... |
0010 00 01 02 00 21 00 0c 03-00 2d 00 04 04 00 31 00 |....!... .-....1.|
0020 01 ff 09 00 00 00 00 00-02 4d 53 53 51 4c 53 65 |........ .MSSQLSe|
0030 72 76 65 72 00 27 96 01-00 00                   |rver.'.. ..|

packet.c:435:Received packet
0000 04 01 00 2b 00 00 01 00-00 00 1a 00 06 01 00 20 |...+.... ....... |
0010 00 01 02 00 21 00 01 03-00 22 00 00 04 00 22 00 |....!... ."....".|
0020 01 ff 0d 00 18 9c 00 00-03 00 00                |........ ...|

login.c:1254:detected flag 3
login.c:582:login packet rejected
query.c:3783:tds_disconnect()
util.c:165:Changed query state from IDLE to DEAD
util.c:319:tdserror(0x10a9380, 0x11642f0, 20002, 0)
dblib.c:8121:dbperror(0x10b14e0, 20002, 0)
dblib.c:8189:dbperror: Calling dblib_err_handler with msgno = 20002; msg->msgtext = "Adaptive Server connection failed (mssql2)"
dblib.c:8211:dbperror: dblib_err_handler for msgno = 20002; msg->msgtext = "Adaptive Server connection failed (mssql2)" -- returns 2 (INT_CANCEL)
util.c:349:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:372:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1258:tdsdbopen: tds_connect_and_login failed for "mssql2"!
dblib.c:1472:dbclose(0x10b14e0)
dblib.c:236:dblib_del_connection(0x7fba506a9780, 0x11642f0)
mem.c:656:tds_free_all_results()
dblib.c:283:dblib_release_tds_ctx(1)
dblib.c:6042:dbfreebuf(0x10b14e0)

Sentences, data structures, data

This will depend on the exact problem you are having and data privacy restrictions

However the more data you provide, the more likely we will be able to help

As a bare minimum, you should provide

The SQL sentence that is failing
The data structure on the PostgreSQL side and on the MSSQL side

Recently the MSSQL has encrypted the connection with TLS and since when we are unable to establish connection with MSSQL server from PostgreSQL DB with existing server to pull data from the foreign tables.

However, when we are connecting the MSSQL server with tsql command ( tsql -I /usr/local/etc/freetds.conf -S mssql2 -U CAPRY2CPDB) we are able to make connection and in that case logs are stating that its establishing connection using TLS. (Logs attached for reference)

Created server at PostgreSQL end is 

CREATE SERVER capry_mssql
    FOREIGN DATA WRAPPER tds_fdw
    OPTIONS (servername 'mssql2', database 'iteraplan', msg_handler 'notice');

freetds.conf configuration for server -

[mssql2]
[tsql_command_success_logs.txt](https://github.com/tds-fdw/tds_fdw/files/7713473/tsql_command_success_logs.txt)

        host = 10.183.73.7
        port = 1433
        database = iteraplan
        tds version = 7.3
        encryption = require

tsql -C output wherein openSSL is enabled -

[root@dbserver ~]# tsql -C
Compile-time settings (established with the "configure" script)
                            Version: freetds v1.1.15
             freetds.conf directory: /usr/local/etc
     MS db-lib source compatibility: no
        Sybase binary compatibility: no
                      Thread safety: yes
                      iconv library: yes
                        TDS version: auto
                              iODBC: no
                           unixodbc: no
              SSPI "trusted" logins: no
                           Kerberos: no
                            OpenSSL: yes
                             GnuTLS: no
                               MARS: yes

Please help how can this be overcome and do let us know if any further information is required.

tds-fdw / tds_fdw

Unable to establish TDS_FDW connection when MSSQL server is TLS encrypted #302