Re: Problems on CentOS 7 connecting to MS SQL Server with ODBC and SQLDriverConnect

Hi Frediano,

On Wed, Feb 4, 2015 at 4:42 AM, Frediano Ziglio <[email protected]> wrote:
>
> With -k you have to pass a connection string, not only a DSN value, so
> something like
>
> isql -v -k 'DSN=connect_test_azure;UID=user;PWD=password;'
>

Oh, you're right. Passing a connection string, rather than just a DSN,
did allow "isql -k" to connect.

>> [IM002][unixODBC][Driver Manager]Data source name not found, and no
>> default driver specified
>
> The error came from the DM (Driver Manager) so unixODBC didn't even
> load FreeTDS.
>

Now that I know "isql -k" requires a connection string, rather than a
DSN, this makes sense. However, I get a FreeTDS-related error when
connecting with the CONNECT engine (see below) using the exact same
connection string.

> Seems from the bug report link you provided that you solved the problem
>

The problem hasn't been solved for CentOS 7 yet. A future release of
the CONNECT engine will offer the option to use SQLConnect instead of
SQLDriverConnect. However, I'm still wondering if there's something I
can do to make the current version work with CentOS 7.

Everything works fine on some platforms, such as Ubuntu 14.04.

Your information regarding "isql -k" enabled me to collect some more
information.

This is the ODBC trace when SQLDriverConnect fails (with the CONNECT engine):

[ODBC][2443][1423062226.652619][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x7f14bda89a00
[ODBC][2443][1423062226.652765][SQLAllocHandle.c][375]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x7f14bda89a00
[ODBC][2443][1423062226.652818][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x7f14c4321000
[ODBC][2443][1423062226.652855][SQLSetConnectOption.c][345]
                Entry:
                        Connection = 0x7f14c4321000
                        Option = SQL_ATTR_LOGIN_TIMEOUT
                        Value = 15
[ODBC][2443][1423062226.652889][SQLSetConnectOption.c][508]
                Exit:[SQL_SUCCESS]
[ODBC][2443][1423062226.652922][SQLSetConnectOption.c][345]
                Entry:
                        Connection = 0x7f14c4321000
                        Option = SQL_ATTR_ACCESS_MODE
                        Value = 1
[ODBC][2443][1423062226.652954][SQLSetConnectOption.c][508]
                Exit:[SQL_SUCCESS]
[ODBC][2443][1423062226.652997][SQLDriverConnect.c][726]
                Entry:
                        Connection = 0x7f14c4321000
                        Window Hdl = 0x1
                        Str In =
[DSN=connect_test_azure;UID=connect_test;PWD=*********;][length = 54
(SQL_NTS)]
                        Str Out = 0x7f14a9800448
                        Str Out Max = 512
                        Str Out Ptr = 0x7f14d98fba50
                        Completion = 0
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

                DIAG [08S01] [FreeTDS][SQL Server]Unable to connect:
Adaptive Server is unavailable or does not exist

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

[ODBC][2443][1423062226.747652][SQLDriverConnect.c][1353]
                Exit:[SQL_ERROR]
[ODBC][2443][1423062226.747782][SQLError.c][434]
                Entry:
                        Connection = 0x7f14c4321000
                        SQLState = 0x7f14d98fb7a0
                        Native = 0x7f14d98fb790
                        Message Text = 0x7f14d98fb7b0
                        Buffer Length = 511
                        Text Len Ptr = 0x7f14d98fb780
[ODBC][2443][1423062226.747811][SQLError.c][471]
                Exit:[SQL_SUCCESS]
                        SQLState = S1000
                        Native = 0x7f14d98fb790 -> 0
                        Message Text = [[unixODBC][FreeTDS][SQL
Server]Unable to connect to data source]


This is the ODBC trace when SQLDriverConnect succeeds (with "isql -k"
and the connection string):

[ODBC][17148][1423061462.682044][__handles.c][460]
                Exit:[SQL_SUCCESS]
                        Environment = 0x1ff2750
[ODBC][17148][1423061462.682135][SQLAllocHandle.c][375]
                Entry:
                        Handle Type = 2
                        Input Handle = 0x1ff2750
[ODBC][17148][1423061462.682162][SQLAllocHandle.c][493]
                Exit:[SQL_SUCCESS]
                        Output Handle = 0x1ff3050
[ODBC][17148][1423061462.682190][SQLDriverConnect.c][726]
                Entry:
                        Connection = 0x1ff3050
                        Window Hdl = (nil)
                        Str In =
[DSN=connect_test_azure;UID=connect_test;PWD=*********;][length = 54
(SQL_NTS)]
                        Str Out = 0x7fffd6a5f070
                        Str Out Max = 2048
                        Str Out Ptr = (nil)
                        Completion = 0
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

[ODBC][17148][1423061462.877878][SQLDriverConnect.c][1582]
                Exit:[SQL_SUCCESS]
                        Connection Out
[[DSN=connect_test_azure;UID=connect_test;PWD=*********;][length = 54
(SQL_NTS)]]


As far as I can see, the main difference is that the CONNECT engine
sets a couple of options (SQL_ATTR_LOGIN_TIMEOUT,
SQL_ATTR_ACCESS_MODE) before attempting to connect. Do you know if
these options can cause problems with some versions of FreeTDS, but
not others? As mentioned above, the CONNECT engine fails to connect
with CentOS 7, but connects just fine on Ubuntu 14.04.

Thanks for your reply!

Geoff