FreeTDS Development Group

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

LOCALHOST:

Since TDS is a TCP session protocol, *everything* is a bit 'remote', not
'local', and you need a host.  In contrast, classic local mode Interbase
reads the database files for itself, avoiding all TCP/IP and server
processes in favor of server threads and a shared memory link to a lock
manager.

Host name 'localhost' is fairly implicitly always a local loopback device on
IP address 127.0.0.1.  Using 'localhost' just saves you having to know and
keep in sync the local host network name or IP address.

You should be able to test any TCP based authentication using just one host,
and optionally using localhost as the host name.  After all, 'can be remote'
is not 'must be remote'.  Since the packets never leave local memory, it
usually has a lot more snap, too.

Localhost does relate to some security schemes, such as when your TCP server
listens on localhost (INADDR_LOCALHOST) not 0.0.0.0 (INADDR_ANY), so only
local clients can connect, and the host login provides a security mechanism,
but this is not often done in production sites.  It just moves the problem
to the host login and adds layers of complexity.  It does allow testing
servers with reduced security worries.

-----Original Message-----
From: W.H. van Atteveldt [mailto:wouter AT 2at.nl]
Sent: Friday, December 09, 2005 8:10 PM
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