Skip to Content.
Sympa Menu

freetds - Re: [freetds] tsql with SSL on SQL Server

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Tony Baldarelli" <tbaldarelli AT wmsvision.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] tsql with SSL on SQL Server
  • Date: Thu, 7 Apr 2011 17:20:28 -0400

I gave it below, but I guess it got lost. Here it is again. Note that it
does this for both machines I am working with ( 2 different versions of
Linux ). Thanks for your help.

------------------------------------------------BEGIN OF PASTED
SECTION---------------------------------------------------------------------
-----------[tonyb:v9_odbc:V3_18]> tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.82.1.dev.20110306
freetds.conf directory: /usr/local/etc
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 5.0
iODBC: no
unixodbc: yes
[tonyb:v9_odbc:V3_18]>
------------------------------------------------END OF PASTED
SECTION---------------------------------------------------------------------
--------------

Here is the portion of the freetds file you care about:
------------------------------------------------BEGIN OF PASTED
SECTION---------------------------------------------------------------------
-----------
[MyServer2005]
host = 192.168.254.91
port = 1433
tds version = 8.0
encryption = required
------------------------------------------------END OF PASTED
SECTION---------------------------------------------------------------------
--------------

Now as for the freetds.conf file, here is the full file:
------------------------------------------------BEGIN OF PASTED
SECTION---------------------------------------------------------------------
-----------
[tonyb:v9_odbc:V3_18]> more /usr/local/etc/freetds.conf
#
#
# $Id: freetds.conf,v 1.9 2003/12/04 22:26:55 freddy77 Exp $
#
# The freetds.conf file is a replacement for the original interfaces
# file developed by Sybase. You may use either this or the interfaces
# file, but not both.
#
# FreeTDS will search for a conf file in the following order:
#
# 1) check if a file was set programatically via dbsetifile() and
# is in .conf format, if so use that,
#
# 2) otherwise, if env variable FREETDSCONF specifies a properly
# formatted config file, use it,
#
# 3) otherwise, look in ~/.freetds.conf,
#
# 4) otherwise, look in @sysconfdir@/freetds.conf
#
# If FreeTDS has found no suitable conf file it will then search for
# an interfaces file in the following order:
#
# 1) check if a file was set programatically via dbsetifile() and
# is in interfaces format, if so use that,
#
# 2) look in ~/.interfaces
#
# 3) look in $SYBASE/interfaces (where $SYBASE is an environment
# variable)
#
# Only hostname, port number, and protocol version can be specified
# using the interfaces format.
#
# The conf file format follows a modified Samba style layout. There
# is a [global] section which will affect all database servers and
# basic program behaviour, and a section headed with the database
# server's name which will have settings which override the global
# ones.
#
# Note that environment variables TDSVER, TDSDUMP, TDSPORT, TDSQUERY,
# and TDSHOST will override values set by a .conf or .interfaces file.
#
# To review the processing of the above, set env variable TDSDUMPCONFIG
# to a file name to log configuration processing.
#
# Global settings, any value here may be overridden by a database
# server specific section
[global]
# TDS protocol version
tds version = 4.2

; initial block size = 512

# uses some fixes required for some bugged MSSQL 7.0 server that
# return invalid data to big endian clients
# NOTE TDS version 7.0 or 8.0 should be used instead
; swap broken dates = no
; swap broken money = no

# Database server login method, if both server and domain
# logins are enabled, domain login is tried first if a domain
# is specified, and if that fails the server login will be used.
# OBSOLETE
; try server login = yes
; try domain login = no

# The default authentication domain, can be overridden by
# specifying a username with a domain prefix, e.g. DOMAIN\username
# OBSOLETE use DOMAIN\username as username
; nt domain = WORKGROUP

# If the server responds with different domain try that one?
# OBSOLETE never been used
; cross domain login = no

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug level = 10

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out of memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# (Microsoft servers sometimes pretend TEXT columns are
# 4 GB wide!) If you have this problem, try setting
# 'text size' to a more reasonable limit
text size = 64512

# This is a Sybase hosted database server, if you are directly on the
# net you can use it to test.
[JDBC]
host = 192.138.151.39
port = 4444
tds version = 5.0

# The same server, using TDS 4.2. Used in configuration examples for the
# pool server, since the pool server supports only TDS 4.2.
[JDBC_42]
host = 192.138.151.39
port = 4444
tds version = 4.2

# The client connecting to the pool server will use this to find its
# listening socket. This entry assumes that the client is on the same
# system as the pool server.
[mypool]
host = 127.0.0.1
port = 5000
tds version = 4.2

# A typical Microsoft SQL Server 7.0 configuration
[MyServer70]
host = 192.168.254.22
port = 1433
tds version = 4.2

# A typical Microsoft SQL Server 2000 configuration
[MyServer2k]
host = 192.168.254.22
port = 1433
tds version = 7.0

[MyServer2005]
host = 192.168.254.91
port = 1433
tds version = 8.0
encryption = required

# A typical Microsoft SQL Server 6.x configuration
;[MyServer65]
; host = ntmachine.domain.com
; port = 1433
; tds version = 4.2
------------------------------------------------END OF PASTED
SECTION---------------------------------------------------------------------
--------------
 
Tony


-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Igor Korot
Sent: Thursday, April 07, 2011 5:08 PM
To: FreeTDS Development Group
Subject: Re: [freetds] tsql with SSL on SQL Server

Tony,
How did you configure freeTDS?

Thank you.

On Thu, Apr 7, 2011 at 1:24 PM, Tony Baldarelli
<tbaldarelli AT wmsvision.com> wrote:
> Here are the commands I did:
>        [tonyb:v9_odbc:V3_18]> date;tsql -S myserver2005 -U nortel -P
> builder789;echo;ls -lt freetds.log ;date
>        Thu Apr  7 16:10:03 EDT 2011
>        locale is "en_US"
>        locale charset is "ISO-8859-1"
>        Msg 20017, Level 9, State -1, Server OpenClient, Line -1
>        Unexpected EOF from the server
>        Msg 20002, Level 9, State -1, Server OpenClient, Line -1
>        Adaptive Server connection failed
>        There was a problem connecting to the server
>
>        -rw-rw-r--    1 tonyb    wmsV9        2222 Apr  7 16:10 freetds.log
>        Thu Apr  7 16:10:03 EDT 2011
>        [tonyb:v9_odbc:V3_18]>
>
>
> See TCPDUMP output here.  Tell me if it helps.  It is all gibberish to me.
> log.c:190:Starting log file for FreeTDS 0.82.1.dev.20110306
>    on 2011-04-07 16:10:03 with debug flags 0x4fff.
> iconv.c:197:names for ISO-8859-1: ISO-8859-1
> iconv.c:197:names for UTF-8: UTF-8
> iconv.c:197:names for UCS-2LE: UCS-2LE
> iconv.c:197:names for UCS-2BE: UCS-2BE
> iconv.c:363:iconv to convert client-side data to the "ISO-8859-1"
character
> set
> iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
> iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
> net.c:210:Connecting to 192.168.254.91 port 1433 (TDS version 8.0)
> net.c:266:tds_open_socket: connect(2) returned "Operation now in progress"
> net.c:307:tds_open_socket() succeeded
> util.c:162:Changed query state from DEAD to IDLE
> net.c:793: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 02 1d 00 00            -                        |....|
>
> net.c:604:Received header
> 0000 04 01 00 25 00 00 01 00-                        |...%....|
>
> net.c:683:Received packet
> 0000 00 00 15 00 06 01 00 1b-00 01 02 00 1c 00 01 03 |........ ........|
> 0010 00 1d 00 00 ff 09 00 0b-ee 00 00 03 01          |....ÿ... î....|
>
> login.c:1007:detected flag 3
> net.c:1049:in tds_push_func
> net.c:1008:in tds_pull_func
> net.c:793:Sending packet
> 0000 12 01 00 46 00 00 00 00-16 03 01 00 39 01 00 00 |...F.... ....9...|
> 0010 35 03 01 4d 9e 1a 1b 80-7b 02 fc f5 dc b0 f8 89 |5..M.... {.üõÜ°ø.|
> 0020 fc e1 b7 c7 08 8c 98 10-2c 80 6c 90 56 e4 22 8f |üá·Ç.... ,.l.Vä".|
> 0030 42 8b 6f 00 00 0e 00 09-00 64 00 62 00 03 00 06 |B.o..... .d.b....|
> 0040 00 12 00 63 01 00      -                        |...c..|
>
> net.c:1021:have 0
> net.c:1025:before read
> util.c:334:tdserror(0x80f8e68, 0x80f97a8, 20017, 0)
> util.c:368:tdserror: client library returned TDS_INT_CANCEL(2)
> util.c:389:tdserror: returning TDS_INT_CANCEL(2)
> util.c:162:Changed query state from IDLE to DEAD
> net.c:1306:handshake failed
> util.c:334:tdserror(0x80f8e68, 0x80f97a8, 20002, 0)
> util.c:368:tdserror: client library returned TDS_INT_CANCEL(2)
> util.c:389:tdserror: returning TDS_INT_CANCEL(2)
> mem.c:609:tds_free_all_results()
>
> Tony
>
>
> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Igor Korot
> Sent: Thursday, April 07, 2011 3:00 PM
> To: FreeTDS Development Group
> Subject: Re: [freetds] tsql with SSL on SQL Server
>
> Tony,
> It would be interesting to see the log dump....
> Like Frdiano said, it should work.
>
> Thank you.
>
> On Thu, Apr 7, 2011 at 11:47 AM, Tony Baldarelli
> <tbaldarelli AT wmsvision.com> wrote:
>> SQL Server 2005 and above.  Actually, I think we currently only use 2005.
>> And that is definitely what I am trying right this very second.
>>
>> Also, SQL Server is currently configured to force encryption.
>>
>> The ODBC driver that my application uses ( Easysoft ) works fine from my
>> application, and isql works fine.  So I know I can connect to the SQL
> Server
>> database from this linux machine.  I just cannot using tsql.
>>
>>
>> Tony
>>
>>
>> -----Original Message-----
>> From: freetds-bounces AT lists.ibiblio.org
>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Igor Korot
>> Sent: Thursday, April 07, 2011 2:22 PM
>> To: FreeTDS Development Group
>> Subject: Re: [freetds] tsql with SSL on SQL Server
>>
>> Hi, Tony,
>>
>> On Thu, Apr 7, 2011 at 8:31 AM, Tony Baldarelli
>> <tbaldarelli AT wmsvision.com> wrote:
>>> Ok, it did not work.  I will look up the encryption settings for this
>> version and see if maybe something else will work, but I would love some
>> feedback as to if I am missing something.
>>>
>>> See below:
>>> • End of /usr/local/etc/freetds.conf
>>>        [MyServer2005]
>>>            host = 192.168.254.91
>>>            port = 1433
>>>            tds version = 8.0
>>>            encryption = request
>>>
>>>        # A typical Microsoft SQL Server 6.x configuration
>>>        ;[MyServer65]
>>>        ;       host = ntmachine.domain.com
>>>        ;       port = 1433
>>>        ;       tds version = 4.2
>>> • Failed command ( password removed for security )
>>>        [tonyb:v9_odbc:V3_18]> tsql -S myserver2005 -U nortel -P PASSWORD
>>>        locale is "en_US"
>>>        locale charset is "ISO-8859-1"
>>>        Msg 20017, Level 9, State -1, Server OpenClient, Line -1
>>>        Unexpected EOF from the server
>>>        Msg 20002, Level 9, State -1, Server OpenClient, Line -1
>>>        Adaptive Server connection failed
>>>        There was a problem connecting to the server
>>> • Version
>>>        [tonyb:v9_odbc:V3_18]> tsql -C
>>>        Compile-time settings (established with the "configure" script)
>>>                                    Version: freetds v0.82.1.dev.20110306
>>>                     freetds.conf directory: /usr/local/etc
>>>             MS db-lib source compatibility: no
>>>                Sybase binary compatibility: no
>>>                              Thread safety: yes
>>>                        iconv library: yes
>>>                            TDS version: 5.0
>>>                                    iODBC: no
>>>                                   unixodbc: yes
>>> • Date stamp of tsql command
>>>        [tonyb:v9_odbc:V3_18]> ls -lt /usr/local/bin/tsql
>>>        -rwxr-xr-x    1 root     root      1487662 Apr  7 11:22
>> /usr/local/bin/tsql
>>>        [tonyb:v9_odbc:V3_18]>
>>
>> Are you trying to connect to the Sybase server or MS SQL server? If
>> the latter what version - 2005 or some other?
>>
>> Thank you.
>>
>>>
>>>
>>> Tony
>>>
>>>
>>> -----Original Message-----
>>> From: freetds-bounces AT lists.ibiblio.org
>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Tony Baldarelli
>>> Sent: Thursday, April 07, 2011 11:06 AM
>>> To: 'FreeTDS Development Group'
>>> Subject: Re: [freetds] tsql with SSL on SQL Server
>>>
>>> Thanks.  Is the feature you disabled in the patch important?  More
>> precisely, what kind of feature is it?  Always sort of makes me nervous
> when
>> I hear people disabled a feature, so that is why I ask.  I will
definitely
>> be trying this patch, so thanks for that idea.
>>>
>>>
>>> You might not be able to answer this, but what are the merits to using
>> openssl or gnutls with freetds?
>>>
>>> Tony
>>>
>>>
>>> -----Original Message-----
>>> From: freetds-bounces AT lists.ibiblio.org
>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Frediano Ziglio
>>> Sent: Thursday, April 07, 2011 5:07 AM
>>> To: FreeTDS Development Group
>>> Subject: Re: [freetds] tsql with SSL on SQL Server
>>>
>>> 2011/4/7 Tony Baldarelli <tbaldarelli AT wmsvision.com>:
>>>> Hello,
>>>>        We are moving to using SSL to connect to our SQL Server
> databases.
>>>> Currently I cannot get it to work and was wondering if there was some
>>>> documentation out there on it.  So far the documentation I have read
> does
>>>> not work.  For instance, it tells me to set "encryption" in the
>> freetds.conf
>>>> to either "request" or "required", which I have tried.
>>>>
>>>>        I currently use openssl, but I do see references to it working
>> with
>>>> gnutls, so I might look that up as well.
>>>>
>>>>
>>>> Tony Baldarelli
>>>>
>>>>
>>>> P.S. For those who are curious, here is some data to consider.
>>>>
>>>>
>>>> •       Machine 1 tsql ( no osql )
>>>>        o       Version:
>>>>                [tonyb:v9_odbc:V3_18]> tsql -C
>>>>                Compile-time settings (established with the "configure"
>>>> script):
>>>>                                           Version: freetds v0.63
>>>>                    MS db-lib source compatibility: no
>>>>                       Sybase binary compatibility: unknown
>>>>                                     Thread safety: yes
>>>>                                     iconv library: yes
>>>>                                       TDS version: 4.2
>>>>                                             iODBC: no
>>>>                                          unixodbc: yes
>>>>                [tonyb:v9_odbc:V3_18]>
>>>>        o       Error:
>>>>                [tonyb:v9_odbc:V3_18]> tsql -S  myserver2005 -U <user
>>>> removed for security> -P <password removed for security>
>>>>                locale is "en_US"
>>>>                locale charset is "ISO-8859-1"
>>>>                Msg 20004, Level 9, State 0, Server OpenClient, Line 0
>>>>                Read from SQL server failed.
>>>>                Msg 20014, Level 9, State 0, Server OpenClient, Line 0
>>>>                Login incorrect.
>>>>                There was a problem connecting to the server
>>>>                [tonyb:v9_odbc:V3_18]>
>>>>        o       freetds section for machine 1.  Note that "encryption"
>> does
>>>> not work with this version, so that is why it is commented out.
>>>>                [MyServer2005]
>>>>                    host = 192.168.254.91
>>>>                    port = 1433
>>>>                    tds version = 8.0
>>>>                    ##encryption = request
>>>> •       Machine 2 tsql, new version
>>>>        o       Version:
>>>>                [tonyb:v9_odbc:V3_18]> tsql -C
>>>>                Compile-time settings (established with the "configure"
>>>> script)
>>>>                                            Version: freetds v0.82
>>>>                       freetds.conf directory: /usr/local/etc
>>>>                     MS db-lib source compatibility: no
>>>>                        Sybase binary compatibility: no
>>>>                                      Thread safety: yes
>>>>                                      iconv library: yes
>>>>                                        TDS version: 5.0
>>>>                                              iODBC: no
>>>>                                           unixodbc: yes
>>>>                [tonyb:v9_odbc:V3_18]>
>>>>        o       Test:
>>>>                [tonyb:v9_odbc:V3_18]>  tsql -S  myserver2005 -U <user
>>>> removed for security> -P <password removed for security>
>>>>                locale is "en_US"
>>>>                locale charset is "ISO-8859-1"
>>>>                Msg 20017, Level 9, State -1, Server OpenClient, Line -1
>>>>                Unexpected EOF from the server
>>>>                Msg 20002, Level 9, State -1, Server OpenClient, Line -1
>>>>                Adaptive Server connection failed
>>>>                There was a problem connecting to the server
>>>>                [tonyb:v9_odbc:V3_18]>
>>>> •       Machine 2 tsql, old version
>>>>        o       Version:
>>>>                [tonyb:v9_odbc:V3_18]> tsql.old -C
>>>>                Compile-time settings (established with the "configure"
>>>> script):
>>>>                                     Version: freetds v0.64.dev.20051213
>>>>                    MS db-lib source compatibility: no
>>>>                 Sybase binary compatibility: unknown
>>>>                                 Thread safety: yes
>>>>                                   iconv library: yes
>>>>                                       TDS version: 5.0
>>>>                                       iODBC: no
>>>>                                      unixodbc: yes
>>>>                [tonyb:v9_odbc:V3_18]>
>>>>        o       Test:
>>>>                [tonyb:v9_odbc:V3_18]>  tsql.old -S  myserver2005 --U
>> <user
>>>> removed for security> -P <password removed for security>
>>>>                locale is "en_US"
>>>>                locale charset is "ISO-8859-1"
>>>>                Msg 20004, Level 9, State 0, Server OpenClient, Line 0
>>>>                Read from SQL server failed.
>>>>                Msg 20014, Level 9, State 0, Server OpenClient, Line 0
>>>>                Login incorrect.
>>>>                There was a problem connecting to the server
>>>>                [tonyb:v9_odbc:V3_18]>
>>>> •       Machine 2 osql
>>>>        o       It is supposed to go into isql when it is done, but it
>> does
>>>> not.
>>>>        o       Output:
>>>>                [tonyb:v9_odbc:V3_18]> osql -S wv8 -U nortel -P
<password
>>>> removed for security>
>>>>                looking for odbc.ini and odbcinst.ini in /usr/local/etc
>>>>                        reading "/home/tonyb/.odbc.ini"
>>>>                [wv8] not found in "/home/tonyb/.odbc.ini"
>>>>                        reading "/usr/local/etc/odbc.ini"
>>>>                [wv8] found in "/usr/local/etc/odbc.ini"
>>>>                found this section:
>>>>                        [wv8]
>>>>                        Driver          = Easysoft ODBC-SQL Server SSL
>>>>                        Description     = Easysoft SQL Server ODBC
driver
>>>>                        Server          = 192.168.254.91
>>>>                        Port            = 1433
>>>>                  Database        = wv8_162
>>>>                        User            = nortel
>>>>                        Password        = <password removed for
security>
>>>>                  Mars_Connection         = No
>>>>                        Logging         = Process
>>>>                        LogFile         =
>>>> /home/wms/v9_odbc/oracle/8.1.7/LOG/wv8_162.easysoft.ssl2
>>>>                  QuotedId        = Yes
>>>>                        AnsiNPW         = Yes
>>>>                        Language        =
>>>>                  Version7        = No
>>>>                        ClientLB        = No
>>>>                        Encrypt         = No
>>>>                  TrustServerCertificate          = No
>>>>                        PrivateKeyFile          =
>>>>                        CertificateFile         =
>>>>                  Entropy         =
>>>>                        Failover_Partner                =
>>>>                        VarMaxAsLong    = No
>>>>
>>>>                looking for driver for DSN [wv8]
>>>>                driver "Easysoft" found for [wv8] in odbc.ini
>>>>                found driver named "Easysoft"
>>>>                Easysoft is not a readable file
>>>>                looking for entry named [Easysoft] in
>>>> /usr/local/etc/odbcinst.ini
>>>>                no driver mentioned for [Easysoft] in odbcinst.ini
>>>>                [tonyb:v9_odbc:V3_18]>
>>>>  •      Machine 2 freetds setup ( uncommenting "encryption" does not
>> really
>>>> change anything, it still fails with the same errors above )
>>>>        [MyServer2005]
>>>>            host = 192.168.254.91
>>>>            port = 1433
>>>>            tds version = 8.0
>>>>            #encryption = request
>>>>
>>>
>>> At least use version 0.82, patched is better. Obviously we cannot
>>> support easysoft driver :)
>>> There is a known issue with early 0.82 and OpenSSL due to a security
>>> improvement in OpenSSL that is not compatible with MS software. A fix
>>> (we disable such a feature) has been incorporated in post 0.82
>>> versions (that is patched version, available at
>>> http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/ or
>>> freetds.sourceforce.net). Also recent versions support much better
>>> encryption settings.
>>>
>>> freddy77
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>>> _______________________________________________
>>> FreeTDS mailing list
>>> FreeTDS AT lists.ibiblio.org
>>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS AT lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page