Isql error could not sqlconnect - Исправление ошибок и поиск оптимальных решений проблем

Ultimate goal: connect my Xubuntu 16.04 machine to a remote MS SQL Server 2017 database using pyodbc.

Intermediate goal: get isql to work; tsql works, but not isql.

I tried mirroring the settings on my Mac — see this question for what worked. Unfortunately, no joy. I have looked through many SO questions, such as:
One, Two, and Three,
and tried their approaches, none of which work. I’m confident that the Linux machine has the right ports open, because tsql works from my Xubuntu 16.04 machine. Unfortunately, I cannot get isql to work. I get the dreaded

[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

errors. Also unfortunately, much of the documentation I’ve seen seems woefully out-of-date and contradictory. For example:

In the odbc.ini file, should it be Server = stuff, 1433 or Server = stuff or ServerName = stuff with a separate port = line, or should I go with instance? There are too many options, and not enough documentation to figure this out.
What exactly should go in the odbcinst.ini file? I don’t have a libtdsS.so file in /usr/lib/x86_64-linux-gnu/odbc directory. Should I leave out the Setup line?
What exactly should go in the freetds.conf file? Should I use the actual IP address?
If I want to force FreeTDS to use a particular protocol version, how do I do that? Changing freetds.conf doesn’t appear to affect the results of tsql -C. Or do I just need to log out and back in?
Taking a step back: is there a better approach to the whole darn thing? I’m not interested in pymssql, as it appears to be abandonware at this point, in favor of pyodbc.
I note that the tsql -LH stuff command produces no output. Is that command deprecated? Or how could I get the right settings from any command?
Is there up-to-date, clear documentation available anywhere? Or an up-to-date, clear installation and usage guide anywhere, with trouble-shooting steps for the more common problems?

Many thanks for your time!

[EDIT] Here are the contents of a tracelog after enabling:

[ODBC][34479][1567549751.158470][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x1913750
[ODBC][34479][1567549751.158517][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x1913750
[ODBC][34479][1567549751.158532][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x1914060
[ODBC][34479][1567549751.158551][SQLConnect.c][3721]
        Entry:
            Connection = 0x1914060
            Server Name = [ROEFDN806Q][length = 10 (SQL_NTS)]
            User Name = [mfadm210028][length = 12 (SQL_NTS)]
            Authentication = [********][length = 8 (SQL_NTS)]
[ODBC][34479][1567549751.158987][SQLConnect.c][3929]Error: IM002
[ODBC][34479][1567549751.159017][SQLError.c][424]
        Entry:
            Connection = 0x1914060
            SQLState = 0x7ffd099daf10
            Native = 0x7ffd099daf0c
            Message Text = 0x7ffd099daf20
            Buffer Length = 500
            Text Len Ptr = 0x7ffd099daf0a
[ODBC][34479][1567549751.159034][SQLError.c][474]
        Exit:[SQL_SUCCESS]
            SQLState = IM002
            Native = 0x7ffd099daf0c -> 0
            Message Text = [[unixODBC][Driver Manager]Data source name not found and no default driver specified]
[ODBC][34479][1567549751.159066][SQLError.c][424]
        Entry:
            Connection = 0x1914060
            SQLState = 0x7ffd099daf10
            Native = 0x7ffd099daf0c
            Message Text = 0x7ffd099daf20
            Buffer Length = 500
            Text Len Ptr = 0x7ffd099daf0a
[ODBC][34479][1567549751.159079][SQLError.c][474]
        Exit:[SQL_NO_DATA]
[ODBC][34479][1567549751.159089][SQLError.c][424]
        Entry:
            Environment = 0x1913750
            SQLState = 0x7ffd099daf10
            Native = 0x7ffd099daf0c
            Message Text = 0x7ffd099daf20
            Buffer Length = 500
            Text Len Ptr = 0x7ffd099daf0a
[ODBC][34479][1567549751.159099][SQLError.c][474]
        Exit:[SQL_NO_DATA]
[ODBC][34479][1567549751.159130][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x1914060
[ODBC][34479][1567549751.159144][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]
[ODBC][34479][1567549751.159156][SQLFreeHandle.c][220]
        Entry:
            Handle Type = 1
            Input Handle = 0x1913750

Here is my current odbc.ini file:

[ROEFDN806Q]
Description = "EOAM_RA Server"
Driver = FreeTDS
ServerName = ROEFDN806Q
Server = 10.146.186.7
Port = 1433
Database = EOAM_RA
UsageCount = 1
TDS_Version = 7.3

And here’s my odbcinst.ini file:

[FreeTDS]
Description = "FreeTDS ODBC Driver"
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

[ODBC]
Trace = yes
TraceFile = /tmp/odbctrace.txt

Источник

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and
privacy statement. We’ll occasionally send you account related emails.

Already on GitHub?
Sign in
to your account

Comments

What steps will reproduce the problem?
1. test tsql [works]
2. test isql [no luck]
3. test iusql [no luck]

What is the expected output? What do you see instead?
EXPECTED
$isql -v ODBCNAME USERNAME PASSWORD
+---------------------------------------+
| Connected!    

but get:

[ISQL]ERROR: Could not SQLConnect
and 
[ISQL]ERROR: Could not SQLDriverConnect


What version of the product are you using? On what operating system?
unixODBC 2.2.14
Ubuntu12.04
MSSQL2008

Please provide any additional information below.

/odbc.log
[ODBC][14658][1375140244.455899][SQLConnect.c][3616]
                Entry:
                        Connection = 0x147c082
                        Server Name = (SQL_NTS)]
                        User Name =  (SQL_NTS)]
                        Authentication = [PW](SQL_NTS)]
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

                DIAG [42000] [FreeTDS][SQL Server]Login failed for user ''.

                DIAG [S1000] [FreeTDS][SQL Server]Unable to connect to data source

Original issue reported on code.google.com by mseme...@gmail.com on 29 Jul 2013 at 11:42

Pls help...
$ isql -v ocs_cfg_db
[08004][unixODBC][ODBC Firebird Driver]File Database is not found
[ISQL]ERROR: Could not SQLConnect
$ odbcinst -q -s
[ocs_cfg_db]
$ odbcinst -q -d
[Firebird]
$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/danateq/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

 odbc.ini 
[ocs_cfg_db]
Description     = Firebird
Driver          = Firebird
Dbname          = host/port:ocs_cfg_db
User            = sysdba
Password        = masterkey
Role            =
CharacterSet    =
ReadOnly        = No
NoWait          = No



 odbcinst.ini 
[Firebird]
Description=InterBase/Firebird ODBC Driver
Driver=/usr/lib64/libOdbcFb.so.2.1.0
Setup=/usr/lib64/libOdbcFb.so.2.1.0
FileUsage=1
CPTimeout=
CPReuse=
UsageCount=10

Original comment by wmsi...@gmail.com on 25 Sep 2013 at 6:31

This took me almost a week to figure out I'll see if I can help.

/etc/freetds/freetds.conf

# Define a connection to the Microsoft SQL Server
[mssql]
        host = XXXXXXXXXXXX - This is your server name.
        port = 1433
        tds version = 8.0

/etc/odbc.ini

# Define a connection to a Microsoft SQL server
# The Description can be whatever we want it to be.
# The Driver value must match what we have defined in /etc/odbcinst.ini
# The Database name must be the name of the database this connection will 
connect to.
# The ServerName is the name we defined in /etc/freetds/freetds.conf
# The TDS_Version should match what we defined in /etc/freetds/freetds.conf
[mssql]
Description             = MSSQL Server
Driver                  = freetds
Database                = dbname #This is your Database name in MSSQL Server
Server                  = XXXXXXXXXXXX #This is your server name again
Port                    = 1433
TDS_Version             = 8.0

/etc/odbcinst.ini

# Define where to find the driver for the Free TDS connections.
[freetds]
Description     = MS SQL database access with Free TDS
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
UsageCount      = 1

Make sure you're in the right location here.

Big thing that stands out is what's that Firebird driver?  If you're using 
MSSQL just use unixODBC and freeTDS newest versions for both will suffice.

Original comment by whoisea...@gmail.com on 11 Nov 2013 at 4:38

Wonderful help.. my paths were mostly different, but finding the locations and 
pointing to them worked fine. Thanks for your week of work!

Original comment by Henry.Co...@gmail.com on 30 Jun 2014 at 7:24

1 participant

Источник

My connection from Oracle 12c to PostgreSQL is returning the following:

select * from «test»@PG_LINK

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[unixODBC]connction string lacks some options {08001,NativeErr = 202}

ORA-02063: preceding 2 lines from PG_LINK

Here is my setup. Anyone have a time to take a look?

.odbc.ini

[ODBC Data Sources]

PG_LINK=PostgreSQL

[PG_LINK]

Debug=255

CommLog=1

ReadOnly=no

Driver=/usr/pgsql-9.4/lib/psqlodbcw.so

Servername=<remove IP address>

FetchBufferSize=99

Username=csm_test

Password=<removed>

Port=5432

Database=postgres

TRACE=Yes

TraceFile=/tmp/sql.log

Protocol=7.4

sslmode=disable

[Default]

Driver=/usr/lib64/liboplodbcS.so.1

initPG_LINK.ora

HS_FDS_CONNECT_INFO = PG_LINK

HS_FDS_TRACE_LEVEL = 255

HS_FDS_SHAREABLE_NAME=/ora/dba/work/unixODBC/lib/libodbc.so

HS_LANGUAGE=american_america.we8iso8859p1

HS_NLS_NCHAR=UCS2

set ODBCINI=/u01/app/oracle/.odbc.ini

Generated trace file: PG_LINK_agt_13870.trc

Oracle Corporation — TUESDAY OCT 20 2015 15:04:11.308

Heterogeneous Agent Release

12.1.0.2.0

Oracle Corporation — TUESDAY OCT 20 2015 15:04:11.307

Version 12.1.0.2.0

Entered hgogprd

HOSGIP for «HS_FDS_TRACE_LEVEL» returned «255»

Entered hgosdip

setting HS_OPEN_CURSORS to default of 50

setting HS_FDS_RECOVERY_ACCOUNT to default of «RECOVER»

setting HS_FDS_RECOVERY_PWD to default value

setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG

setting HS_IDLE_TIMEOUT to default of 0

setting HS_FDS_TRANSACTION_ISOLATION to default of «READ_COMMITTED»

HOSGIP returned value of «UCS2» for HS_NLS_NCHAR

setting HS_FDS_TIMESTAMP_MAPPING to default of «DATE»

setting HS_FDS_DATE_MAPPING to default of «DATE»

setting HS_RPC_FETCH_REBLOCKING to default of «ON»

setting HS_FDS_FETCH_ROWS to default of «100»

setting HS_FDS_RESULTSET_SUPPORT to default of «FALSE»

setting HS_FDS_RSET_RETURN_ROWCOUNT to default of «FALSE»

setting HS_FDS_PROC_IS_FUNC to default of «FALSE»

setting HS_FDS_MAP_NCHAR to default of «TRUE»

setting HS_NLS_DATE_FORMAT to default of «YYYY-MM-DD HH24:MI:SS»

setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of «FALSE»

setting HS_LONG_PIECE_TRANSFER_SIZE to default of «65536»

setting HS_SQL_HANDLE_STMT_REUSE to default of «FALSE»

setting HS_FDS_QUERY_DRIVER to default of «TRUE»

setting HS_FDS_SUPPORT_STATISTICS to default of «FALSE»

setting HS_FDS_QUOTE_IDENTIFIER to default of «TRUE»

setting HS_KEEP_REMOTE_COLUMN_SIZE to default of «OFF»

setting HS_FDS_GRAPHIC_TO_MBCS to default of «FALSE»

setting HS_FDS_MBCS_TO_GRAPHIC to default of «FALSE»

Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION

setting HS_CALL_NAME_ISP to «gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo;gtw$:OPTTables;gtw$:OPTColumns;gtw$:OPTPrimaryKeys;gtw$:OPTForeignKeys;gtw$:OPTProcedures;gtw$:OPTStatistics»

setting HS_FDS_DELAYED_OPEN to default of «TRUE»

setting HS_FDS_WORKAROUNDS to default of «0»

setting HS_WORKAROUNDS to default of «0»

Exiting hgosdip, rc=0

ORACLE_SID is «PG_LINK»

Product-Info:

Port Rls/Upd:2/0 PrdStat:0

Agent:Oracle Database Gateway for ODBC

Facility:hsa

Class:ODBC, ClassVsn:12.1.0.2.0_0023, Instance:PG_LINK

Exiting hgogprd, rc=0

hostmstr: 0: HOA After hoagprd

hostmstr: 0: HOA Before hoainit

Entered hgoinit

HOCXU_COMP_CSET=1

HOCXU_DRV_CSET=31

HOCXU_DRV_NCHAR=1000

HOCXU_DB_CSET=873

HS_LANGUAGE is american_america.we8iso8859p1

rc=32512 attempting to get LANG environment variable.

HOCXU_SEM_VER=121000

HOCXU_VC2_MAX=4000

HOCXU_RAW_MAX=2000

Entered hgolofn at 2015/10/20-15:04:11

HOSGIP for «HS_FDS_SHAREABLE_NAME» returned «/ora/dba/work/unixODBC/lib/libodbc.so»