dimitri
MSSQL "Connection to the database failed for an unknown reason" but other tools can connect
Hi Guys,
Not sure where else to post/ask this. I am trying to migrate from a MSSQL database to Postgres. The MSSQL server is at host 10.1.10.251, on port 49214. I can connect to this server from my Mac using both SQLPro and Azure Data Studio (just to try a couple of native Mac MSSQL clients), as in the screen shots.
But if I try to connect using pg_loader I get a failure for an "unknown reason." I've tried many (many) permutations of the connection string, including setting TDSPORT variable to 49124 and referencing the server by its Windows-y DOMAIN\SERVERNAME syntax, but always get this same error. I'm also attaching the TDS log. Am I missing something obvious?
$ pgloader --verbose --dry-run mssql://ghassett:[email protected]:49214/pps_sms pgsql://ghassett@localhost/pps_sms
2023-10-17T15:47:47.004825-04:00 NOTICE Starting pgloader, log system is ready.
2023-10-17T15:47:47.008264-04:00 LOG pgloader version "3.6.9"
2023-10-17T15:47:47.051337-04:00 LOG Migrating from #<MSSQL-CONNECTION mssql://[email protected]:49214/pps_sms {700B9CD9A3}>
2023-10-17T15:47:47.051459-04:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://ghassett@localhost:5432/pps_sms {700BB97803}>
2023-10-17T15:47:47.081077-04:00 ERROR mssql: Failed to connect to mssql at "10.1.10.251" (port 49214) as user "ghassett": Connection to the database failed for an unknown reason.
2023-10-17T15:47:47.081126-04:00 LOG report summary reset
table name errors read imported bytes total time read write
----------------- --------- --------- --------- --------- -------------- --------- ---------
fetch meta data 0 0 0 0.000s
----------------- --------- --------- --------- --------- -------------- --------- ---------
----------------- --------- --------- --------- --------- -------------- --------- ---------
The TDS Log:
$ cat /tmp/freetds.log
log.c:187:Starting log file for FreeTDS 1.4.3
on 2023-10-17 15:59:28 with debug flags 0x4fff.
dblib.c:1187:tdsdbopen(0x6000034fb780, 10.1.10.251, [microsoft])
dblib.c:1213:tdsdbopen: dbproc->dbopts = 0x10ff0b280
dblib.c:1224:tdsdbopen: tds_set_server(0x600000ae6000, "10.1.10.251")
dblib.c:251:dblib_get_tds_ctx(void)
locale.c:61:Attempting to read locales.conf file
config.c:513:Looking for section default.
config.c:572: Found section default.
config.c:575:Got a match.
config.c:572: Found section en_us.
config.c:572: Found section es_es.
config.c:572: Found section pt_br.
config.c:572: Found section it_it.
config.c:586: Reached EOF
config.c:513:Looking for section C.
config.c:572: Found section default.
config.c:572: Found section en_us.
config.c:572: Found section es_es.
config.c:572: Found section pt_br.
config.c:572: Found section it_it.
config.c:586: Reached EOF
dblib.c:1241:tdsdbopen: About to call tds_read_config_info...
config.c:170:Getting connection information for [10.1.10.251].
config.c:174:Attempting to read conf files.
config.c:362:... $FREETDSCONF not set. Trying $FREETDS/etc.
config.c:375:... $FREETDS not set. Trying $HOME.
config.c:301:Could not open '/Users/ghassett/.freetds.conf' ((.freetds.conf)).
config.c:305:Found conf file '/opt/homebrew/etc/freetds.conf' (default).
config.c:513:Looking for section global.
config.c:572: Found section global.
config.c:575:Got a match.
config.c:598: tds version = 'auto'
config.c:957:Setting tds version to auto (0x0).
config.c:572: Found section egserver50.
config.c:572: Found section egserver73.
config.c:586: Reached EOF
config.c:513:Looking for section 10.1.10.251.
config.c:572: Found section global.
config.c:572: Found section egserver50.
config.c:572: Found section egserver73.
config.c:586: Reached EOF
config.c:311:[10.1.10.251] not found.
config.c:197:Failed in reading conf file. Trying interface files.
config.c:1207:Looking for server 10.1.10.251....
config.c:1224:Looking for server in /Users/ghassett/.interfaces.
config.c:1089:Searching interfaces file //Users/ghassett/.interfaces.
config.c:1114:Couldn't open /Users/ghassett/.interfaces.
config.c:1244:Looking for server in /etc/freetds/interfaces.
config.c:1089:Searching interfaces file /etc/freetds/interfaces.
config.c:1114:Couldn't open /etc/freetds/interfaces.
config.c:1275:Setting 'ip_port' to 1433 from $TDSPORT.
config.c:199:Failed to find [10.1.10.251] in configuration files; trying 'SYBASE' instead.
config.c:839:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:851:Setting 'port' to 1433 from $TDSPORT.
config.c:839:Setting 'dump_file' to '/tmp/freetds.log' from $TDSDUMP.
config.c:851:Setting 'port' to 1433 from $TDSPORT.
dblib.c:1268:tdsdbopen: Calling tds_connect_and_login(0x10ff0c370, 0x600000af0a00)
iconv.c:371:tds_iconv_open(0x10ff040c0, ISO-8859-1, 1)
iconv.c:202:local name for ISO-8859-1 is ISO-8859-1
iconv.c:202:local name for UTF-8 is UTF-8
iconv.c:202:local name for UCS-2LE is UCS-2LE
iconv.c:202:local name for UCS-2BE is UCS-2BE
iconv.c:390:setting up conversions for client charset "ISO-8859-1"
iconv.c:392:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:431:tds_iconv_open: done
net.c:391:Connecting with protocol version 7.4
net.c:318:Connecting to 10.1.10.251 port 1433
net.c:340:tds_setup_socket: connect(2) returned "Operation now in progress"
net.c:231:getsockopt(2) reported: Connection refused
net.c:526:tds_open_socket() failed
util.c:333:tdserror(0x16aeee5d0, 0x10ff0c370, 20009, 61)
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:8167:dbperror(0x10ff0bdb0, 20009, 61)
dblib.c:8235:dbperror: Calling dblib_err_handler with msgno = 20009; msg->msgtext = "Unable to connect: Adaptive Server is unavailable or does not exist (10.1.10.251)"
dblib.c:5120:default_err_handler 0x10ff0bdb0, 9, 20009, 61, 0x6000018fc580, 0x18cb0f4e0dblib.c:5086:dbdead(0x10ff0bdb0) [dead]
dblib.c:8257:dbperror: dblib_err_handler for msgno = 20009; msg->msgtext = "Unable to connect: Adaptive Server is unavailable or does not exist (10.1.10.251)" -- returns 2 (INT_CANCEL)
util.c:333:tdserror(0x6000038d4030, 0x10ff0c370, 20009, 61)
dblib.c:8167:dbperror(0x10ff0bdb0, 20009, 61)
dblib.c:8235:dbperror: Calling dblib_err_handler with msgno = 20009; msg->msgtext = "Unable to connect: Adaptive Server is unavailable or does not exist (10.1.10.251)"
dblib.c:5120:default_err_handler 0x10ff0bdb0, 9, 20009, 61, 0x6000018fc580, 0x18cb0f4e0dblib.c:5086:dbdead(0x10ff0bdb0) [dead]
dblib.c:8257:dbperror: dblib_err_handler for msgno = 20009; msg->msgtext = "Unable to connect: Adaptive Server is unavailable or does not exist (10.1.10.251)" -- returns 2 (INT_CANCEL)
util.c:363:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:386:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1277:tdsdbopen: tds_connect_and_login failed for "10.1.10.251"!
dblib.c:1491:dbclose(0x10ff0bdb0)
dblib.c:236:dblib_del_connection(0x1050f0698, 0x10ff0c370)
mem.c:665:tds_free_all_results()
dblib.c:278:dblib_release_tds_ctx(1)
dblib.c:6088:dbfreebuf(0x10ff0bdb0)
dblib.c:739:dbloginfree(0x6000034fb780)
The screen shots from clients that successfully connect:
Hi @ghassett,
i face the same issue under wsl2. Under wsl2 with sqlcmd I can only connect with the option "-C".
e.g. sqlcmd -S localhost -U sa -P xxxx -C
This option is the same in the connection string "TrustServerCertificate=True".
I can only assume, I didn't had the time to dig in more deeply, it may not connect because the certficate which is installed on the SQL Server is not thrusted. I think we have to go with installing a thrusted certificate or (I'm only thinking loud...) adapt the usage of the sybase client lib
https://github.com/archimag/cl-mssql/blob/045602a19a32254108f2b75871049293f49731eb/src/mssql.lisp
to add in the communication protocoll the option "TrustServerCertificate=True" or change it with something like FreeTDS from http://www.freetds.org
