FreeTDS Development Group

["Pickett, David" <David.Pickett AT phlx.com>]

Correct me if I am wrong, but I believe certificates are a bit like DNS: you
chain up a tree.  In this case, the server and you should both check each
other's certificates chain up to a common trusted ancestor like VeriSign or
your company.  You need a certificate from that trusted ancestor (the
company domain controller if this is all within one company), so you are
hanging in the trust tree.  Once 'you' are trusted, you can give out
certificates to 'others' you 'trust', extending the tree.  VeriSign says
your company certificate server is really '2at.nl', the '2at.nl' certificate
server says you are really 'wouter', and so on.

The process can be modeled as registering a certificate or getting a
certificate, but what matters is that the certificate signatures decode as
being chained together.

BTW, the certificates are time limited, so trust has to be renewed, which is
good: stale relationships are not valid forever (and also, I guess VeriSign
gets paid periodically).

My browser is always complaining about expired or unhooked certificates.

If you are creating you own little trust domain, you can set yourself up as
a local root, distributing to all clients and servers both configuration
that your certificate is one of their trusted root certificates and
certificates for them that point to your root certificate.  VeriSign and
such are primarily needed for spontaneous, unforeseen, global relationships
like we have on the nasty old Internet.  It's a bit like IP addresses:
choosing to use either the unregistrable class A IPv4 subnet 10.0.0.0 on
your intranet or registered IP addresses.  Often, 10.0.0.0 turns out to be a
hassle after a merger or in a join venture with extranets, and sells
firewalls that cost far more than IP addresses.

Best regards,

David

-----Original Message-----
From: W.H. van Atteveldt [mailto:wouter AT 2at.nl]
Sent: Wednesday, December 14, 2005 10:01 AM
To: FreeTDS Development Group
Subject: Re: [freetds] 'secure connection' to SQLserver. Integrated
security, ssl?


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
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds