FreeTDS Development Group

["W.H. van Atteveldt" <wouter AT 2at.nl>]

Dear List,

I'm afraid I have not been able to solve my connection problem yet. I
tried bringing my laptop running the sql server to the linux machine
physically (to circumvent the firewall/ssh tunnel) but that did not
help.

Summarizing, the problem is that the handshake between the machines
fails. The SQL Server encrypts using a 'server certificate' from the
company domain controller. I suspect that I have to register this
certificate as trusted somewhere with freetds/openssl but I have no clue
whether this is correct and how to do this.

Any help greatly appreciated!

Wouter

(see post below for more details)

> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org [mailto:freetds-
> bounces AT lists.ibiblio.org] On Behalf Of W.H. van Atteveldt
> Sent: 10 December 2005 02:10
> To: FreeTDS Development Group
> Subject: Re: [freetds] 'secure connection' to SQLserver. Integrated
> security,ssl?
> 
> 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?)
> 
> 
> > 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?
> 
> > 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?
> 
> -- 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..|
> 
> 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....|
> 
> 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..|
> 
> 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
> 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()
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds