FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il giorno sab, 10/12/2005 alle 02.09 +0100, W.H. van Atteveldt ha
scritto:
> Dear Freddy / List,
> 
> Thanks for all the answers, I'm afraid I'm not quite successful yet
> however. 
>  
> > localhost?? No, when you use encryption you have to connect to server
> > directly.
> 
> Well actually I'm just testing using a connection to my laptop, which is
> behind a NAT firewall. The production server is on the Net directly, but
> is not using encryption yet, so I am testing on my laptop. Is it a
> problem to do a (redundant) encryption through an SSH tunnel (just for
> testing purposes?)
> 

No, is not a problem but it complicate a bit the situation.

>  
> > In snapshot you will find a tds_ssl.html that help you on how to add
> > certificate to mssql. Is not sufficient to check the flag.
> 
> Well actually my laptop has a computer certficiate issued by my company,
> signed by the company's root certificate. Do I need to import/trust this
> certificate in openssl somehow? 
> 

No, even Microsoft clients do not require this... (not that good but it
works so).

> > Mmm... you are not using TDS 8.0 (required for encryption)...
> 
> O that's really strange! My .freetds.conf file is:
> 
> [wva@swpc450 wva]$ cat ~/.freetds.conf
> [global]
>         tds version = 8.0
>         port = 1433
>         dump file = /tmp/freetds.log
>         debug flags = 0xffff
> 
> So I thought that should be enough. Just to be sure, I added an entry
> for localhost:
> 
> [localhost]
>         host = 127.0.0.1
>         tds version = 8.0
>         port = 1433
>         dump file = /tmp/freetds.log2
>         debug flags = 0xffff
> 
> Interesting enough, this gave a different result. It now says 'handshake
> failed' and 'login incorrect'. This seems sensible as the openssl does
> not trust the certificate? 
> 
> I tried adding the root certificate to the cert-bundle file by copy
> pasting it into it:
> 
> """
> 2AT Netwerkdiensten Root CA
> =========================================
> MD5 Fingerprint: 0d:32:fa:00:7e:c6:ef:ad:4b:90:0b:35:6e:ba:62:95
> PEM Data:
> -----BEGIN CERTIFICATE-----
> MIIGmTCCBIGgAwIBAgIQDTL6AH7G761LkAs1brpilTANBgkqhkiG9w0BAQQFADBK
> [...]
> r6HC4QrWGLwgNrn8QMRyheulws5hOIkwVnxOZ76REly+VSTkYoHlCT8PuUwVjWnX
> whw+y4njhRye77BOEA==
> -----END CERTIFICATE-----
> 
> ABAecom (sub., Am. Bankers Assn.) Root CA
> ========================================= 
> [...]
> """
> 
> But that didn't really seem to work. Googling 'import certificate
> openssl' does not give useful hits, so I'm guessing openssl does not
> really have a store of trusted roots... 
> 
> Thanks so much for all the help so far, I think I'm almost there :-). Do
> you think people would appreciate a 'secure mssql connection using
> freetds' writeup if everything works?
> 

Yes, I think so, I would prefer an update to our UserGuide to keep all
together.

> -- Wouter
> 
> ---
> 
> The .log file is:
> 
> 00:55:31.252956 13180 (util.c:288):Starting log file for FreeTDS
> 0.64.dev.20051209
>         on 2005-12-10 00:55:31 with debug flags 0xffff.
> 00:55:31.253128 13180 (iconv.c:195):names for ISO-8859-1: ISO-8859-1
> 00:55:31.253146 13180 (iconv.c:195):names for UTF-8: UTF-8
> 00:55:31.253156 13180 (iconv.c:195):names for UCS-2LE: UCS-2LE
> 00:55:31.253166 13180 (iconv.c:195):names for UCS-2BE: UCS-2BE
> 00:55:31.253176 13180 (iconv.c:361):iconv to convert client-side data to
> the "UTF-8" character set
> 00:55:31.253211 13180 (iconv.c:514):tds_iconv_info_init: converting
> "UTF-8"->"UCS-2LE"
> 00:55:31.253240 13180 (iconv.c:514):tds_iconv_info_init: converting
> "ISO-8859-1"->"UCS-2LE"
> 00:55:31.253260 13180 (net.c:168):Connecting to 127.0.0.1 port 1433.
> 00:55:31.253392 13180 (net.c:673):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 7c 33 00 00            -                        ||3..|
> 

Here we go! This IS TDS 8.0 ! And you compiled with SSL support.

> 00:55:31.274925 13180 (net.c:446):Received header
> 0000 04 00 00 25 00 00 01 00-                        |...%....|
> 
> 00:55:31.274953 13180 (net.c:542):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 08 00 01-37 00 00 03 00          |........ 7....|
> 

Server is telling you, I use certificates and I want it!
I saw also that version you are using is quite old. You should tell your
"administrator" (if we can call he so) that SP4 is out!!! 

> 00:55:31.274980 13180 (login.c:873):detected flag 3
> 00:55:31.275391 13180 (net.c:883):in tds_push_func
> 00:55:31.275413 13180 (net.c:842):in tds_pull_func
> 00:55:31.275425 13180 (net.c:673):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 43 9a 19 73 8c-57 fa 61 d5 aa e8 69 4e |5..C..s. W.a...iN|
> 0020 01 5e 83 6b 1c e9 72 a3-b4 11 4b fc 36 eb a6 d6 |.^.k..r. ..K.6...|
> 0030 c8 6d bf 00 00 0e 00 09-00 64 00 62 00 03 00 06 |.m...... .d.b....|
> 0040 00 12 00 63 01 00      -                        |...c..|
> 

Well.. this TLS inside TDS...

> 00:55:31.275483 13180 (net.c:855):have 0
> 00:55:31.275496 13180 (net.c:859):before read
> 00:55:31.319155 13180 (net.c:446):Received header
> 0000 04 01 07 51 00 00 02 00-                        |...Q....|
> 
> 00:55:31.326411 13180 (net.c:542):Received packet
> 0000 16 03 00 07 44 02 00 00-46 03 00 43 9a 29 7c a9 |....D... F..C.)|.|
> 0010 25 ed e4 d1 d0 25 f4 36-19 4f 2e f4 76 41 77 77 |%....%.6 .O..vAww|
> 0020 e7 98 93 78 70 4f 02 e1-0d 03 1a 20 61 23 00 00 |...xpO.. ... a#..|
> 0030 51 0f 41 e3 76 23 5e d1-b6 2c 2a 29 d1 94 f2 e8 |Q.A.v#^. .,*)....|
> 0040 57 95 e0 1d b2 95 58 0c-f4 2e e0 82 00 09 00 0b |W.....X. ........|
> 0050 00 06 f2 00 06 ef 00 06-ec 30 82 06 e8 30 82 04 |........ .0...0..|
> 0060 d0 a0 03 02 01 02 02 0a-1e ab 14 4c 00 01 00 00 |........ ...L....|
> 0070 00 8c 30 0d 06 09 2a 86-48 86 f7 0d 01 01 04 05 |..0...*. H.......|
> 0080 00 30 4a 31 15 30 13 06-0a 09 92 26 89 93 f2 2c |.0J1.0.. ...&...,|
> 0090 64 01 19 16 05 6c 6f 63-61 6c 31 13 30 11 06 0a |d....loc al1.0...|
> 00a0 09 92 26 89 93 f2 2c 64-01 19 16 03 32 61 74 31 |..&...,d ....2at1|
> <snip sensitive looking data/>
> 0540 03 82 02 01 00 b9 ec 08-32 6a 50 3a a2 2b d2 45 |........ 2jP:.+.E|
> 0550 5d 19 ed b8 b4 0e 0e 1e-30 97 71 d2 d3 c1 c2 fd |]....... 0.q.....|
> 0560 e9 98 13 86 cf 24 48 bc-0d 08 ac 2f 50 6a d4 23 |.....$H. .../Pj.#|
> 0570 f8 e7 de de 27 a0 6b be-a5 7b 5f 3e 38 e9 dc fa |....'.k. .{_>8...|
> 0580 74 32 f0 59 3c 98 50 67-0a 07 4d 8e b9 b7 87 14 |t2.Y<.Pg ..M.....|
> 0590 58 45 65 46 74 68 3b 69-4f ad ae b8 d6 7a 95 9f |XEeFth;i O....z..|
> 05a0 07 4e 55 be a1 41 0f 9a-37 9f ac 41 52 93 96 34 |.NU..A.. 7..AR..4|
> 05b0 75 64 c8 0f 66 4e 07 55-1b 3d 89 6a 57 05 1f d4 |ud..fN.U .=.jW...|
> 05c0 06 91 b3 23 9b 67 e3 30-d2 71 a2 d3 70 fb 6e ac |...#.g.0 .q..p.n.|
> 05d0 c5 e8 e2 49 e2 68 26 8f-ac 45 34 73 ed f3 34 e7 |...I.h&. .E4s..4.|
> 05e0 6d 8b ed 15 bb fb b1 60-ad f0 01 71 ed 19 2a 5c |m......` ...q..*\|
> 05f0 26 3d 61 40 ba 33 cf 26-f7 1c d4 62 22 69 df f7 |&=a@.3.& ...b"i..|
> 0600 df 0c 3e 5b ec a5 55 f2-48 11 d5 8c 4b f8 f7 2a |..>[..U. H...K..*|
> 0610 50 67 75 28 e1 15 00 0c-92 ba 51 84 71 b4 7b c0 |Pgu(.... ..Q.q.{.|
> 0620 e3 29 ba 5a bd fe 19 af-f1 8e f9 7a a1 dd 0a a6 |.).Z.... ...z....|
> 0630 7e 6c 7c 9f cf 69 0f 38-01 d4 77 83 d1 08 9a 0f |~l|..i.8 ..w.....|
> 0640 7e cb 1e de bc 7f cc a7-03 08 30 0f b0 7f 31 7b |~....... ..0...1{|
> 0650 55 66 55 ea ad 51 9d ea-3b f2 9a 99 3d f5 fa 8a |UfU..Q.. ;...=...|
> 0660 ef 36 00 74 b9 bd e6 28-39 35 55 f0 0f 12 6d 32 |.6.t...( 95U...m2|
> 0670 13 40 6e c3 23 cc 29 d0-33 a0 33 5b 5f 9e 24 bf |.@n.#.). 3.3[_.$.|
> 0680 d3 4d 25 e0 df 5f 67 32-a1 50 81 a0 72 e0 64 18 |.M%.._g2 .P..r.d.|
> 0690 c7 2e f3 53 d6 ab 04 91-03 b4 de a3 bb 82 53 ff |...S.... ......S.|
> 06a0 37 ce 96 15 4a a9 c9 e8-f4 74 a7 77 22 68 cd 56 |7...J... .t.w"h.V|
> 06b0 e3 e9 b2 76 49 3d a5 a7-6b 56 23 b4 7d 86 95 b3 |...vI=.. kV#.}...|
> 06c0 5a fa ff 8a a1 13 19 83-8b f1 7f d0 fd 02 c0 e8 |Z....... ........|
> 06d0 ed fa 3a 92 86 cd f6 86-6a d7 31 1e 93 76 ed e2 |..:..... j.1..v..|
> 06e0 0a 38 fb a6 1a 16 b6 37-ad d8 3f 50 0d 8d 8f b9 |.8.....7 ..?P....|
> 06f0 c1 e9 7e d1 8a cc 87 33-c7 cf 85 a0 1b 60 41 17 |..~....3 .....`A.|
> 0700 79 7c 67 c4 3b 21 3d 86-7e 50 34 be 29 52 d4 f0 |y|g.;!=. ~P4.)R..|
> 0710 2c 32 45 62 79 f8 5f 02-ea e4 88 16 1d 0a c0 ac |,2Eby._. ........|
> 0720 6f 09 41 d0 44 53 0f 2c-05 e7 67 cf e7 4a 8a 89 |o.A.DS., ..g..J..|
> 0730 4c 73 14 7c f1 3c 44 30-71 9c 65 f8 7c 83 b8 f3 |Ls.|.<D0 q.e.|...|
> 0740 e8 78 02 dc 67 0e 00 00-00                      |.x..g... .|
> 
> 00:55:31.327364 13180 (net.c:862):after read
> 00:55:31.327375 13180 (net.c:855):have 1865
> 00:55:31.327385 13180 (net.c:866):read 5 bytes
> 00:55:31.327407 13180 (net.c:883):in tds_push_func
> 00:55:31.327419 13180 (net.c:673):Sending packet
> 0000 12 01 00 0f 00 00 00 00-15 03 00 00 02 02 28    |........ ......(|
> 
> 00:55:31.327495 13180 (net.c:1140):handshake failed

I think this "handshake failed" is the key... I can't understand why
OpenSSL refuse connection just after reading only 5 bytes... perhaps we
only accept TLS protocol and not old SSL 3 ?? Try replacing
TLSv1_client_method in src/tds/net.c with SSLv23_client_method. This
should be due to the fact that server is using former version.

> 00:55:31.327531 13180 (util.c:119):Changing query state from IDLE to
> DEAD
> 00:55:31.327669 13180 (token.c:2240):tds_client_msg: #20014: "Login
> incorrect.".  Connection state is now 4.
> 00:55:31.327685 13180 (mem.c:475):tds_free_all_results()

freddy77