Skip to Content.
Sympa Menu

freetds - Re: [freetds] 'secure connection' to SQLserver. Integrated security, ssl?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "W.H. van Atteveldt" <wouter AT 2at.nl>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] 'secure connection' to SQLserver. Integrated security, ssl?
  • Date: Sat, 10 Dec 2005 02:09:34 +0100

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()




Archive powered by MHonArc 2.6.24.

Top of Page