Problems connecting to SQLA15.newtekwebhosting.com (MSSQL2000)

[mcepl]

Trying to connect to the remote MSSQL2000 from openSUSE Linux and I am confused:

stitny~$ rpm -q libtdsodbc0 freetds-config unixODBC
libtdsodbc0-1.3.17-1.1.x86_64
freetds-config-1.3.17-1.1.x86_64
unixODBC-2.3.11-2.2.x86_64

stitny~$ sudo cat /etc/freetds.conf 
[global]
	tds version = auto

# A typical Microsoft server
[NewTekSQL]
	host = SQLA15.newtekwebhosting.com
	port = 1433
stitny~$ sudo cat /etc/unixODBC/odbcinst.ini
[SQLITE3]
Description=SQLite ODBC 3.X
Driver=/usr/lib64/libsqlite3odbc.so
Setup=/usr/lib64/libsqlite3odbc.so
Threading=2
FileUsage=1
UsageCount=1

[FreeTDS]
Description=FreeTDS unixODBC Driver
Driver=/usr/lib64/libtdsodbc.so.0
Setup=/usr/lib64/libtdsodbc.so.0
UsageCount=1
stitny~$ sudo cat /etc/unixODBC/odbc.ini
[SQLite3 Datasource]
Driver=SQLITE3

[MSSQLServerDatabase]
Driver=FreeTDS
Description=Connect to PCF old website database
Servername=NewTekSQL
UserName=fakelogin
Password=fakepass
stitny~$ tsql -v -S NewTekSQL -U fakelogin -P fakepass
locale is "cs_CZ.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Changed database context to 'dydotnetnuke'.
Changed language setting to us_english.
1> 
stitny~$ isql -v MSSQLServerDatabase
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

Why it works with tsql and not with isql?

[freddy77]

Hi, Il giorno dom 19 mar 2023 alle ore 09:26 Matěj Cepl < ***@***.***> ha scritto:

Trying to connect to the remote MSSQL2000 from openSUSE Linux and I am confused:

MSSQL 2000? Pretty old I would say. I suppose also an old the machine running it. Maybe setting the TDS version to 7.1 could be helpful. stitny~$ rpm -q libtdsodbc0 freetds-config unixODBC

libtdsodbc0-1.3.17-1.1.x86_64 freetds-config-1.3.17-1.1.x86_64 unixODBC-2.3.11-2.2.x86_64 stitny~$ sudo cat /etc/freetds.conf [global] tds version = auto # A typical Microsoft server [NewTekSQL] host = SQLA15.newtekwebhosting.com port = 1433

you could add a version for a specific server here.

stitny~$ sudo cat /etc/unixODBC/odbcinst.ini [SQLITE3] Description=SQLite ODBC 3.X Driver=/usr/lib64/libsqlite3odbc.so Setup=/usr/lib64/libsqlite3odbc.so Threading=2 FileUsage=1 UsageCount=1 [FreeTDS] Description=FreeTDS unixODBC Driver Driver=/usr/lib64/libtdsodbc.so.0 Setup=/usr/lib64/libtdsodbc.so.0 UsageCount=1 stitny~$ sudo cat /etc/unixODBC/odbc.ini [SQLite3 Datasource] Driver=SQLITE3 [MSSQLServerDatabase] Driver=FreeTDS Description=Connect to PCF old website database Servername=NewTekSQL UserName=fakelogin Password=fakepass stitny~$ tsql -v -S NewTekSQL -U fakelogin -P fakepass locale is "cs_CZ.UTF-8" locale charset is "UTF-8" using default charset "UTF-8" Changed database context to 'dydotnetnuke'. Changed language setting to us_english. 1> stitny~$ isql -v MSSQLServerDatabase [S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source [01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed [01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed [ISQL]ERROR: Could not SQLConnect Why it works with tsql and not with isql?

Maybe adding username and password? See also https://www.freetds.org/userguide/logging.html. Regards, Frediano

…

Message ID: ***@***.***>

[mcepl]

Hi, Il giorno dom 19 mar 2023 alle ore 09:26 Matěj Cepl < @.***> ha scritto:
MSSQL 2000? Pretty old I would say. I suppose also an old the machine running it.

It is at some web hosting company we are trying to terminate contract with, but before we do it, we would get our data from them. Binary backup (.bak file) doesn’t give me much hope for data recovery, especially considering that if I even manage to open that format (and I haven’t found any Linux tool for dealing with it, aside from installing whole MSSQL for Linux), so I would like to get SQL database dump at least.

OK, some progress. With this /etc/freetds.conf:

[global]
	tds version = auto
	dump file = /tmp/freetds.log
	debug flags = 0xffff

[NewTekSQL]
   host = SQLA15.newtekwebhosting.com
   database = dydotnetnuke
   port = 1433
   tds version = 7.1
;  enable gssapi delegation = off
;  use lanman = yes

(I tried those two commented out lines, but they didn’t seem to have any effect),

and this /etc/unixODBC/odbc.ini (/etc/unixODBC/odbcinst.ini remained the same):

[MSSQLServerDatabase]
Driver=FreeTDS
Description=Connect to PCF old website database
Servername=NewTekSQL
Trace           = Yes
TraceFile       = /tmp/sql.log
ForceTrace      = Yes
UserName        = fakelogin
Password        = fakepassword

I get this in /tmp/freetds.log:

stitny:/etc/unixODBC # tail -F /tmp/freetds.log /tmp/sql.log
==> /tmp/sql.log <==

==> /tmp/freetds.log <==
14:00:40.333513 10151 (log.c:187):Starting log file for FreeTDS 1.3.17
	on 2023-03-19 14:00:40 with debug flags 0xffff.
14:00:40.333526 10151 (iconv.c:367):tds_iconv_open(0x55d8de03cda0, UTF-8)
14:00:40.333638 10151 (iconv.c:198):local name for ISO-8859-1 is ISO-8859-1
14:00:40.333645 10151 (iconv.c:198):local name for UTF-8 is UTF-8
14:00:40.333649 10151 (iconv.c:198):local name for UCS-2LE is UCS-2LE
14:00:40.333652 10151 (iconv.c:198):local name for UCS-2BE is UCS-2BE
14:00:40.333689 10151 (iconv.c:389):setting up conversions for client charset "UTF-8"
14:00:40.333694 10151 (iconv.c:391):preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
14:00:40.333727 10151 (iconv.c:430):tds_iconv_open: done
14:00:40.333732 10151 (net.c:391):Connecting with protocol version 7.1
14:00:40.333757 10151 (net.c:318):Connecting to 216.197.96.16 port 1433
14:00:40.333830 10151 (net.c:340):tds_setup_socket: connect(2) returned "Operation now in progress"
14:00:40.496268 10151 (net.c:528):tds_open_socket() succeeded
14:00:40.496390 10151 (packet.c:852):Sending packet
0000 12 01 00 34 00 00 00 00-00 00 15 00 06 01 00 1b |...4.... ........|
0010 00 01 02 00 1c 00 0c 03-00 28 00 04 ff 08 00 01 |........ .(......|
0020 55 00 00 00 4d 53 53 51-4c 53 65 72 76 65 72 00 |U...MSSQ LServer.|
0030 a7 27 00 00            -                        |.'..|

14:00:40.658726 10151 (packet.c:410):Received packet
0000 04 01 00 25 00 00 01 00-00 00 15 00 06 01 00 1b |...%.... ........|
0010 00 01 02 00 1c 00 01 03-00 1d 00 00 ff 08 00 07 |........ ........|
0020 f7 00 00 02 00         -                        |.....|

14:00:40.658828 10151 (login.c:1342):detected crypt flag 2
14:00:40.658847 10151 (login.c:971):using GSS authentication
14:00:40.658866 10151 (gssapi.c:299):using kerberos name MSSQLSvc/SQLA15.newtekwebhosting.com:1433
14:00:40.658883 10151 (gssapi.c:311):gss_import_name: GSS_S_COMPLETE: gss_import_name completed successfully.
14:00:40.839966 10151 (gssapi.c:401):gss_init_sec_context: actual mechanism at (nil)
14:00:40.840034 10151 (gssapi.c:417):gss_init_sec_context: min_stat 2529638919 "Unknown error -1765328377"
14:00:40.840052 10151 (gssapi.c:457):gss_init_sec_context: GSS_S_FAILURE: The routine failed for reasons that are not defined at the GSS level.
14:00:40.840083 10151 (login.c:599):login packet rejected
14:00:40.840098 10151 (query.c:3757):tds_disconnect() 
14:00:40.840189 10151 (util.c:179):Changed query state from IDLE to DEAD
14:00:40.840208 10151 (util.c:333):tdserror(0x55d8de03d760, 0x55d8de05f3e0, 20002, 0)
14:00:40.840224 10151 (odbc.c:2369):msgno 20002 20003
14:00:40.840242 10151 (util.c:363):tdserror: client library returned TDS_INT_CANCEL(2)
14:00:40.840255 10151 (util.c:386):tdserror: returning TDS_INT_CANCEL(2)
14:00:40.840270 10151 (mem.c:656):tds_free_all_results()
14:00:40.840452 10151 (error.c:414):odbc_errs_add: "Unable to connect to data source"
14:00:40.840484 10151 (error_export.h:107):SQLError((nil), 0x55d8de03bfa0, (nil), 0x7ffc8c3d8aaa, 0x7ffc8c3d7e74, 0x7ffc8c3d8d80, 513, 0x7ffc8c3d7e72)
14:00:40.840505 10151 (error.c:559):SQLGetDiagRec: "[FreeTDS][SQL Server]Adaptive Server connection failed"
14:00:40.840540 10151 (error_export.h:107):SQLError((nil), 0x55d8de03bfa0, (nil), 0x7ffc8c3d8aaa, 0x7ffc8c3d7e74, 0x7ffc8c3d8d80, 513, 0x7ffc8c3d7e72)
14:00:40.840555 10151 (error.c:559):SQLGetDiagRec: "[FreeTDS][SQL Server]Unable to connect to data source"
14:00:40.840582 10151 (error_export.h:107):SQLError((nil), 0x55d8de03bfa0, (nil), 0x7ffc8c3d8aaa, 0x7ffc8c3d7e74, 0x7ffc8c3d8d80, 513, 0x7ffc8c3d7e72)
14:00:40.840597 10151 (odbc.c:4139):SQLFreeConnect(0x55d8de03bfa0)
14:00:40.840609 10151 (odbc.c:4102):_SQLFreeConnect(0x55d8de03bfa0)
14:00:40.840622 10151 (bcp.c:673):_bcp_free_storage(0x55d8de03bfa0)
14:00:40.840638 10151 (odbc.c:4164):SQLFreeEnv(0x55d8de03d700)
14:00:40.840651 10151 (odbc.c:4149):_SQLFreeEnv(0x55d8de03d700)

Any idea what should I do?

[freddy77]

You are not passing username and password. Use isql <dns_name> <username> <password>.

[mcepl]

Oh, D’oh, that works.

So, now when I have the connection, is there a way how to get SQL dump of whole database (or at least data and schema) via that connection?

[freddy77]

Looks like an administrator question. From FreeTDS you could want to use the freebcp utility. I would try to turn the current installation into a virtual machine image, just to be safe. Probably you have some MSSQL 2000 tool installed, you could use them to do some dump.
What do you need to save? Only data or also user types, views, functions, store procedures, users, permissions?

[mcepl]

It is a hosted database, where I don’t have other access than over this port (and most Microsoft tools I could find don’t work with it any more). Yes, freebcp is probably the best I could hope for, but if I would like to recreate the database elsewhere (i.g., in my local SQLite), I am missing schemata and anything else which might be useful. Any ideas how to get out of that database something more resembling an SQL database dump?

[freddy77]

Do you have sa or system account or only standard account? I would try either not Microsoft management tools supporting SQL Server or trying to download all clients for SQL Server 2000 somewhere.

[mcepl]

Only standard account (no “sa”). I was searching and searching, but I couldn’t find anything supporting MSSQL2000 anymore (mind you, I am on Linux, which makes the situation even more complicated).

[freddy77]

I would use a Windows machine, maybe installed on a VM temporarily.
Beside that I would start from sysobjects table, it contains a list of objects in the database, search for documentation of it.