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

2015-02-04 15:12 GMT+00:00 Geoff Montee <[email protected]>:
> 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.
>

Did you try isql from mysql account? Could be something related to
some permissions or restrictions like SELinux.

>>> [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
>

Unfortunately SQLDriverConnect (our implementation) is not much
helpful here, mostly connection errors report the same message.

> [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.
>

I don't think these parameters are the problem. The second is not
used, first just set a timeout to 15 seconds (quite long). You could
also try a dsn-less connection with
http://www.freetds.org/userguide/dsnless.htm.

> Thanks for your reply!
>
> Geoff

Frediano