Re: tsql with SSL on SQL Server

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: [email protected]
[mailto:[email protected]] 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
<[email protected]> 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: [email protected]
[mailto:[email protected]] 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: [email protected]
[mailto:[email protected]] 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 <[email protected]>:
>> 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
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds