[freetds] MS SQL - Force protocol encryption

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Fri Dec 10 06:24:35 EST 2004


> 
> > 
> > Il mer, 2004-12-08 alle 03:31, Danny Raetzsch ha scritto:
> > > Hi,
> > > 
> > > I was having problems a while back being unable to 
> connect to an SQL
> > > server across campus using tsql, getting an error message 
> > which reads,
> > > "Could not read from server."  I was finally able to get 
> out of the
> > > server admin that they have enabled the "force protocol 
> encryption"
> > > option on the SQL Server.  Is it likely  that this is where 
> > my problem
> > > is coming from?
> > > 
> > > It's unlikely that I will be able to get the admin to turn 
> > it off.  Is
> > > there any way I can get around this with freetds?  If not, 
> > are there any
> > > commercially available alternatives that I should check out 
> > that might
> > > be able to solve the problem?  I've already looked into Easysoft's
> > > ODBC-ODBC bridge software but this requires installation on 
> > the server,
> > > which will not be possible.
> > > 
> > > Someone else suggested I try routing tsql through stunnel (an SSL
> > > wrapper).  I'm not real familiar with the SSL protocol - or 
> > encryption
> > > mechanisms at all for that matter - but it seems to me this 
> > is a bit of
> > > a long shot.  I gave it a try anyway, but to no avail.  Am 
> > I barking up
> > > the wrong tree here?
> > > 
> > > Thanks,
> > > Dan
> > 
> > Still not supported by FreeTDS (and no plan to support it).
> > No know commercial mssql ODBC driver (only bridges or FreeTDS
> > modifications...)
> > >From what I know encryption in mssql use ssl so I think that 
> > client have
> > to accept certificate... 
> > An stunnel could work... I never tried to enable encryption 
> > so I cannot
> > do some tests by myself.... If you can help to do a 
> connection to this
> > server and do a capture we can try to help you. Are you able 
> > to setup a
> > DSN with ODBC (from Control Panel -> Administrative Tools) 
> > and test it ?
> > 
> > freddy77
> > 
> 
> I'm working on this (sometimes I like to play with bits :) ). 
> Some bits left but mainly it's ssl...
> 

Catched: 

Now prelogin packet (tds 8.0 only)

client to server 

00000000  12 01 00 34 00 00 00 00  00 00 15 00 06 01 00 1b ...4....
........
00000010  00 01 02 00 1c 00 0c 03  00 28 00 04 ff 08 00 01 ........
.(......
00000020  55 00 00 01 4d 53 53 51  4c 53 65 72 76 65 72 00 U...MSSQ
LServer.
00000030  38 09 00 00                                      8...

00 00 15 00 06 netlib version (0) start from 0x15 length 0x06
  8.341.0
01 00 1b 00 01 encrypt flag (1) start from 0x1b length 0x01
  1, see below
02 00 1c 00 0c instance name (2) start from 0x1c length 0x0c
  "MSSQLServer\0"
(\0 padding ???)
03 00 28 00 04 process id (3) start from 0x28 length 0x04
  0x938 (why in little endian ??)
ff             end

Meaning of encryption flag (client):
0x00 normal
0x01 high encryption (128 bit ??)

server to client

00000000  04 01 00 25 00 00 01 00  00 00 15 00 06 01 00 1b ...%....
........
00000010  00 01 02 00 1c 00 01 03  00 1d 00 00 ff 08 00 02 ........
........
00000020  f8 00 00 01 00     

Similar

00 00 15 00 06 version (0) start from 0x15 length 0x06
  8.760.0 (or 8.0.760.0 ??)
01 00 1b 00 01 encrypt flag (1) start from 0x1b length 0x01
 1, see below
02 00 1c 00 01 instance name (2) start from 0x1c length 0x01
 "\0"
 (yes, perhaps it's just null terminated)
03 00 1d 00 00 process id (3) start from 0x1d length 0x0
 no info
ff             end

Meaning of encryption flag (server):
0x02, no certificate, no encryption required
  just do normal jobs (like FreeTDS)
0x00, certificate, no force
  prelogin to see certificate (TLS 1.0, not SSL 2 or 3 !!)
  login encrypted, after login changed crypt and do normal TDS (not
encrypted)
0x01, certificate, high encryption required by client
  prelogin to see certificate
  login encrypted, after login changed crypt (my client close connection
perhaps a configuration problem...)
0x03, certificate enabled, force encryption configured on server
  prelogin like 0x00, remains in SSL after login

Following a valid encrypted connection (0x00 from server, no force)

>From client to server
00000034  12 01 00 46 00 00 00 00  16 03 01 00 39 01 00 00 ...F....
....9...
00000044  35 03 01 41 b9 5e 02 f8  7d 45 81 31 d9 73 9e 93 5..A.^..
}E.1.s..
00000054  91 b2 dd f4 4a 80 a3 92  a8 0f aa 67 32 8a 72 6d ....J...
...g2.rm
00000064  4b 22 07 00 00 0e 00 09  00 64 00 62 00 03 00 06 K"......
.d.b....
00000074  00 12 00 63 01 00                                ...c..

prelogin packet (0x12), content TLS 1.0 (you can see a ClientHell0, see
RFC 2246)

>From server to client
00000025  04 01 03 da 00 00 01 00  16 03 01 03 cd 02 00 00 ........
........
00000035  46 03 01 41 b9 5e 03 4b  c1 bf 9b a5 7d 83 74 57 F..A.^.K
....}.tW
00000045  00 03 de b5 fb fc 4d f8  84 15 ce 07 d9 ab fe 2b ......M.
.......+
00000055  57 3c ad 20 96 10 00 00  6f 31 af e4 17 ae 2a 2b W<. ....
o1....*+
00000065  37 29 0e 57 8a 4d 1d 32  aa d9 ed 62 6b 3d 3c d1 7).W.M.2
...bk=<.
00000075  d1 c6 a9 cb 00 09 00 0b  00 03 7b 00 03 78 00 03 ........
..{..x..
00000085  75 30 82 03 71 30 82 02  da a0 03 02 01 02 02 01 u0..q0..
........
00000095  00 30 0d 06 09 2a 86 48  86 f7 0d 01 01 04 05 00 .0...*.H
........
000000A5  30 81 88 31 0b 30 09 06  03 55 04 06 13 02 49 54 0..1.0..
.U....IT
000000B5  31 10 30 0e 06 03 55 04  08 13 07 42 6f 6c 6f 67 1.0...U.
...Bolog
000000C5  6e 61 31 10 30 0e 06 03  55 04 07 13 07 42 6f 6c na1.0...
U....Bol
... omissis...

normal reply packet (0x4), ServerHello(2), content TLS (certificate)

>From client to server
0000007A  12 01 00 c6 00 00 00 00  16 03 01 00 86 10 00 00 ........
........
0000008A  82 00 80 3a c6 96 ba 55  ce 8e 4b a4 e2 d7 b7 bd ...:...U
..K.....
0000009A  5d 5e f4 28 30 c6 c7 b9  4e 66 60 80 45 ce cb 4e ]^.(0...
Nf`.E..N
000000AA  f6 f7 91 d7 9b 05 79 f8  ad f7 c7 13 77 36 cb 8c ......y.
....w6..
000000BA  04 58 33 3f 51 c8 0a bb  6a 95 8f 65 a1 e9 74 c5 .X3?Q...
j..e..t.
000000CA  c9 c6 4a 11 b1 36 87 84  f2 96 82 d0 19 8a dd dc ..J..6..
........
000000DA  d1 32 6a 32 ab 73 47 76  58 69 16 fd 9f 0b bd d7 .2j2.sGv
Xi......
000000EA  72 79 a7 86 9a 71 2b 70  9a d1 8f e2 54 63 46 81 ry...q+p
....TcF.
000000FA  3e 6d 8a f7 8d 2e 26 02  3f 2d 0c a1 bc 63 ac 0a >m....&.
?-...c..
0000010A  8a 38 0e 14 03 01 00 01  01 16 03 01 00 28 12 09 .8......
.....(..
0000011A  d5 2d 93 8c 60 aa ae ec  e3 9b 2b 3c 27 63 46 ad .-..`...
..+<'cF.
0000012A  b1 b9 3d 1e 06 60 18 49  6d bb 76 80 8b 7b 51 70 ..=..`.I
m.v..{Qp
0000013A  b7 79 14 b8 ba 62                                .y...b

prelogin (0x12), still TLS handshake, client_key_exchange (??) (0x16,
0x10), change crypt (0x14), handshake (I don't understand what type...
perhaps crypted)

>From server to client
000003FF  04 01 00 3b 00 00 01 00  14 03 01 00 01 01 16 03 ...;....
........
0000040F  01 00 28 96 3c 2e 41 42  09 d3 a8 77 82 19 7f 4b ..(.<.AB
...w...K
0000041F  ac 04 b8 96 4b f1 65 c2  35 9e ef 6a 1d c2 41 2d ....K.e.
5..j..A-
0000042F  a3 98 b8 2b 5c 0f 40 d1  98 b0 1e                ...+\. at . ...

normal reply (0x4), still TLS

0x14 means change crypt, followed by 0x16 (handshake crypted), this
change crypt method (always after login success)

>From client to server
00000140  17 03 01 01 08 d7 3a b5  c3 fd a7 4d 14 b7 ce c7 ......:.
...M....
00000150  c6 6f f3 c5 03 1c 5c 86  7b 15 98 45 2a 93 73 c7 .o....\.
{..E*.s.
00000160  72 75 72 23 c2 11 20 7d  5c b1 be e7 ac 72 ac b3 rur#.. }
\....r..
00000170  47 41 4f 45 d8 fa 22 2b  94 b1 67 a5 7f de af 96 GAOE.."+
..g.....
00000180  05 ad bb fc e4 33 66 3a  a2 f1 8d c5 5f 84 8b 38 .....3f:
...._..8
00000190  86 b0 df e8 87 e7 2c 26  e6 c0 66 2e b1 53 86 40 ......,&
..f..S.@
000001A0  98 0d 9e 2f 49 0b 17 b2  9d 55 d3 e3 7e 08 ca b9 .../I...
.U..~...
000001B0  de 62 87 23 14 98 6e 10  d0 dd c2 94 70 4c 33 4b .b.#..n.
....pL3K
000001C0  09 8d b8 46 e5 a2 31 52  4d 89 06 b2 10 a8 ed b0 ...F..1R
M.......
000001D0  a8 21 02 79 ab 99 de 67  28 a3 6c ea 18 88 b5 63 .!.y...g
(.l....c
000001E0  02 ab f4 a1 78 0d 83 ec  b6 7b 61 7d 42 d2 38 bb ....x...
.{a}B.8.
000001F0  50 fc e1 9e 1e e0 51 69  93 ea 05 9f d5 a4 a8 2b P.....Qi
.......+
00000200  18 7f 79 4f 29 1a c0 35  3c 55 83 b0 9f af b7 de ..yO)..5
<U......
00000210  6d 2d 12 fa 27 ef 28 6b  a9 83 12 e6 a1 09 58 00 m-..'.(k
......X.
00000220  30 b3 3d f0 60 00 97 84  ee 28 b0 ae 31 78 50 d7 0.=.`...
.(..1xP.
00000230  85 82 19 9f 57 ca a6 1c  d2 81 0f 6b 2d fb 47 41 ....W...
...k-.GA
00000240  37 58 8a ba 4f 38 f6 00  23 24 56 c2 35          7X..O8..
#$V.5

Well... you can note that this it's not a TDS packet !!! This should be
a fully crypted packet (in this case I suppose a login)

>From server to client
0000043A  04 01 01 79 00 33 01 00  e3 1b 00 01 06 6d 00 61 ...y.3..
.....m.a
0000044A  00 73 00 74 00 65 00 72  00 06 6d 00 61 00 73 00 .s.t.e.r
..m.a.s.
0000045A  74 00 65 00 72 00 ab 66  00 45 16 00 00 02 00 25 t.e.r..f
.E.....%
0000046A  00 43 00 68 00 61 00 6e  00 67 00 65 00 64 00 20 .C.h.a.n
.g.e.d. 
0000047A  00 64 00 61 00 74 00 61  00 62 00 61 00 73 00 65 .d.a.t.a
.b.a.s.e
0000048A  00 20 00 63 00 6f 00 6e  00 74 00 65 00 78 00 74 . .c.o.n
.t.e.x.t
0000049A  00 20 00 74 00 6f 00 20  00 27 00 6d 00 61 00 73 . .t.o.
.'.m.a.s
... omissis ...

well... this should be know :)

With force login (0x03 from server) after crypted login packet server
and client continue to keep encrypting full packets

freddy77



More information about the FreeTDS mailing list