Re: tsql with SSL on SQL Server
"Tony Baldarelli" <[email protected]>
| Newsgroups | gmane.comp.db.tds.freetds |
|---|---|
| Message-ID | <013001cbf569$9f1f4930$dd5ddb90$@com> |
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: [email protected]
[mailto:[email protected]] 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
<[email protected]> 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: [email protected]
> [mailto:[email protected]] 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
> <[email protected]> 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: [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
>>
>> _______________________________________________
>> 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