SSL support for TDS

[mjaric]

Moved ecto_sql issue 227 since it is tds related.

Original request:

Environment

• Elixir version (elixir -v):

Erlang/OTP 22 [erts-10.6.2] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe]

Elixir 1.10.0 (compiled with Erlang/OTP 21)

• Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): Microsoft SQL Azure (RTM) - 12.0.2000.8 Apr 9 2020 16:39:55 Copyright (C) 2019 Microsoft Corporation
• Ecto version (mix deps):

* ecto 3.4.3 (Hex package) (mix)
  locked at 3.4.3 (ecto) 9b6f18de
* ecto_sql 3.4.3 (Hex package) (mix)
  locked at 3.4.3 (ecto_sql) ec9e59d6
* phoenix_ecto 4.1.0 (Hex package) (mix)
  locked at 4.1.0 (phoenix_ecto) c5e666a3

• Database adapter and version (mix deps): tds 2.1.0
• Operating system: windows

Current behavior

Database communication is done in clear text even though the ssl option is set to true.

How to reproduce:

• Set up azure db with ssl cerificate
• Create new phoenix project with mssql: mix phx.new --datbase mssql test_tds_ssl
• Configure database in config/dev.exs, ensure ssl is set to true
• Start project in iex, send query: TestTdsSsl.Repo.query("select * from INFORMATION_SCHEMA.TABLES;")
• Observe traffic with wireshark for example. Note that queries and responses are sent in clear text

Expected behavior

Database communication should be encrypted when the ssl option is enabled.

[cbourg2]

Hi are there any new developments on this?

I am attempting to connect to a remote MSSQL DB using this library (standalone and via the ecto_sql adapter) and have been unsuccessful owing, I believe, to the login process implemented here.

I see that there are functions that seem to imply a two step process

https://github.com/livehelpnow/tds/blob/11e2f911a0b3b8452b65505a1e7e2451ec32ab79/lib/tds/protocol.ex#L544-L557

Though I do not see Tds.Protocol.prelogin/1 called anywhere nor is it a defined callback for the DBConnection behaviour. (it's not called from that library either as far as I can tell.

I'm wondering if maybe there is a configuration option that will invoke prelogin/1? Or maybe it's an unfinished feature?

[mjaric]

Hi @cbourg2 , it is not implemented yet. At the moment no one is working on this as I'm aware

[cbourg2]

@mjaric Ok. Thank you very much for the quick response.

[mjaric]

This is kind of tricky to implement, erlang :ssl module completely hides buffer from user code so it is impossible to extract servers "hello" message from second PreLogin.

So problem is this

1. client connects with gen_tcp
2. client sends first Prelogin message saying it is supports encryption
3. if server responds with its Prelogin saying encryption is ON ot REQUIRED
4. client sends new Prelogin message (header only) preceding SSL Hnadshake payload (SSL Hello record)
5. server (if encryption is ON) responds with Prelogin message (header only) preceeding SSL Handshake payload

So above requires that :ssl.connect(socket, sock_options, timeout) call can accept callback that still can inject preceding unencrypted 8 bytes as Prelogin header and on match header with 8 bytes that is returned from server as response before client handshake part is processed by :ssl internal code.

One solution is to build Tds.Tsl module that reuses erlang :tls internal modules just for handshake purpose, and luckily if possible continue with regular :ssl module after successful handshake.

For instance, mssql client written in Rust, has TlsPreloginWrapper it handles tds header before stream is processed by Tls module.

[josevalim]

@mjaric do you have a small proof of concept code that you can share with us so we understand this better? :)

[mjaric]

Partially, here is a "hack" I did to mimic client sending PreLogin header before ssl hello record that is sent with :ssl.connect/3 few lines below

In this case sever will respond with package that contains first byte 0x12 (18 dec) + 7 bytes as content of servers PreLogin response header, the rest bytes in message are SSL handshake from server. Since erlang expects first byte to correspond to cheaper code e.g. 0x16 it will throw error. And this is only required during handshake, after that :ssl module should do the rest

{:tls_alert, {:unexpected_message, 'TLS client: In state hello at tls_record.erl:539 generated CLIENT ALERT: Fatal - Unexpected Message\n {unsupported_record_type,18}’}}

[josevalim]

Ah, I see, but the receiving part is not done yet, right? Have you tried the handshape option on connect? Maybe it allows you to control the handshape at exactly this point.

[mjaric]

I tried handshake, but maybe I set wrong size in Prelogin header, I'll give it a try again... but it looks like only server socket can use hanshake_continue, I'm not sure how to validate and remove first 8 bytes before ssl reads that hello record

[mjaric]

Yep, like it says here

Performs the TLS/DTLS server-side handshake.

[mjaric]

Here is full debug with :ssl.connect/3

❯ mix test test/login_test.exs
Compiling 1 file (.ex)
Excluding tags: [:manual]

.PRELOGIN RESPONSE: <<0, 0, 26, 0, 6, 1, 0, 32, 0, 1, 3, 0, 33, 0, 0, 4, 0, 33, 0, 1, 6, 0, 34, 0,
  1, 255, 14, 0, 7, 235, 0, 0, 2, 0, 0>>
.PRELOGIN RESPONSE: <<0, 0, 26, 0, 6, 1, 0, 32, 0, 1, 3, 0, 33, 0, 0, 4, 0, 33, 0, 1, 6, 0, 34, 0,
  1, 255, 14, 0, 7, 235, 0, 0, 1, 0, 0>>

18:17:45.042 [debug] Upgrading connection to SSL/TSL.

18:17:45.094 [debug] [direction: :outbound, message: {:client_hello, {3, 3}, <<95, 190, 145, 185, 32, 41, 105, 199, 57, 16, 26, 183, 141, 131, 7, 86, 239, 187, 16, 231, 36, 197, 34, 172, 159, 243, 163, 6, 51, 10, 85, 7>>, "", :undefined, [<<0, 255>>, <<19, 2>>, <<19, 1>>, <<19, 3>>, <<19, 4>>, <<19, 5>>, <<192, 44>>, <<192, 48>>, <<192, 36>>, <<192, 40>>, <<192, 46>>, <<192, 50>>, <<192, 38>>, <<192, 42>>, <<0, 159>>, <<0, 163>>, <<0, 107>>, <<0, 106>>, <<192, 43>>, <<192, 47>>, "̩", "̨", <<192, 35>>, <<192, 39>>, <<192, 45>>, <<192, 49>>, <<192, 37>>, <<192, 41>>, <<0, 158>>, <<0, 162>>, "̪", <<0, 103>>, <<0, 64>>, <<192, 10>>, <<192, 20>>, <<0, 57>>, <<0, 56>>, <<192, 5>>, <<192, 15>>, <<192, 9>>, <<192, ...>>, <<...>>, ...], [0], %{alpn: :undefined, client_hello_versions: {:client_hello_versions, [{3, 4}, {3, 3}]}, ec_point_formats: {:ec_point_formats, [0]}, elliptic_curves: {:supported_groups, [:x25519, :x448, :secp256r1, :secp384r1]}, key_share: {:key_share_client_hello, [{:key_share_entry, :x25519, <<134, 227, 18, 238, 145, 114, 129, 48, 16, 188, 214, 222, 91, 38, 241, 151, 247, 68, 146, 38, 42, 234, 219, 22, 60, 93, 198, 118, 7, ...>>}]}, max_frag_enum: :undefined, next_protocol_negotiation: :undefined, renegotiation_info: {:renegotiation_info, :undefined}, signature_algs: {:signature_algorithms, [:ecdsa_secp521r1_sha512, :ecdsa_secp384r1_sha384, :ecdsa_secp256r1_sha256, :rsa_pss_pss_sha512, :rsa_pss_pss_sha384, :rsa_pss_pss_sha256, :rsa_pss_rsae_sha512, :rsa_pss_rsae_sha384, :rsa_pss_rsae_sha256, :rsa_pkcs1_sha512, :rsa_pkcs1_sha384, :rsa_pkcs1_sha256, :ecdsa_sha1, :rsa_pkcs1_sha1, {:sha512, :ecdsa}, {:sha512, :rsa}, {:sha384, :ecdsa}, {:sha384, :rsa}, {:sha256, :ecdsa}, {:sha256, :rsa}, {:sha224, :ecdsa}, {:sha224, :rsa}, {:sha, :ecdsa}, {:sha, :rsa}, {:sha, :dsa}]}, signature_algs_cert: :undefined, sni: :undefined, srp: :undefined}}, protocol: :handshake]
>>> TLS 1.3 Handshake, ClientHello
[{client_version,{3,3}},
 {random,
     <<95,190,145,185,32,41,105,199,57,16,26,183,141,131,7,86,239,187,16,231,
       36,197,34,172,159,243,163,6,51,10,85,7>>},
 {session_id,<<>>},
 {cookie,undefined},
 {cipher_suites,
     ["TLS_EMPTY_RENEGOTIATION_INFO_SCSV","TLS_AES_256_GCM_SHA384",
      "TLS_AES_128_GCM_SHA256","TLS_CHACHA20_POLY1305_SHA256",
      "TLS_AES_128_CCM_SHA256","TLS_AES_128_CCM_8_SHA256",
      "TLS_ECDHE_ECDSA_WITH_AES_256_GCM_SHA384",
      "TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384",
      "TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA384",
      "TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384",
      "TLS_ECDH_ECDSA_WITH_AES_256_GCM_SHA384",
      "TLS_ECDH_RSA_WITH_AES_256_GCM_SHA384",
      "TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA384",
      "TLS_ECDH_RSA_WITH_AES_256_CBC_SHA384",
      "TLS_DHE_RSA_WITH_AES_256_GCM_SHA384",
      "TLS_DHE_DSS_WITH_AES_256_GCM_SHA384",
      "TLS_DHE_RSA_WITH_AES_256_CBC_SHA256",
      "TLS_DHE_DSS_WITH_AES_256_CBC_SHA256",
      "TLS_ECDHE_ECDSA_WITH_AES_128_GCM_SHA256",
      "TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256",
      "TLS_ECDHE_ECDSA_WITH_CHACHA20_POLY1305_SHA256",
      "TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256",
      "TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256",
      "TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256",
      "TLS_ECDH_ECDSA_WITH_AES_128_GCM_SHA256",
      "TLS_ECDH_RSA_WITH_AES_128_GCM_SHA256",
      "TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA256",
      "TLS_ECDH_RSA_WITH_AES_128_CBC_SHA256",
      "TLS_DHE_RSA_WITH_AES_128_GCM_SHA256",
      "TLS_DHE_DSS_WITH_AES_128_GCM_SHA256",
      "TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256",
      "TLS_DHE_RSA_WITH_AES_128_CBC_SHA256",
      "TLS_DHE_DSS_WITH_AES_128_CBC_SHA256",
      "TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA",
      "TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA","TLS_DHE_RSA_WITH_AES_256_CBC_SHA",
      "TLS_DHE_DSS_WITH_AES_256_CBC_SHA",
      "TLS_ECDH_ECDSA_WITH_AES_256_CBC_SHA",
      "TLS_ECDH_RSA_WITH_AES_256_CBC_SHA",
      "TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA",
      "TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA","TLS_DHE_RSA_WITH_AES_128_CBC_SHA",
      "TLS_DHE_DSS_WITH_AES_128_CBC_SHA",
      "TLS_ECDH_ECDSA_WITH_AES_128_CBC_SHA",
      "TLS_ECDH_RSA_WITH_AES_128_CBC_SHA"]},
 {compression_methods,[0]},
 {extensions,
     #{alpn => undefined,
       client_hello_versions => {client_hello_versions,[{3,4},{3,3}]},
       ec_point_formats => {ec_point_formats,[0]},
       elliptic_curves => {supported_groups,[x25519,x448,secp256r1,secp384r1]},
       key_share =>
           {key_share_client_hello,
               [{key_share_entry,x25519,
                    <<134,227,18,238,145,114,129,48,16,188,214,222,91,38,
                      241,151,247,68,146,38,42,234,219,22,60,93,198,118,7,
                      208,238,116>>}]},
       max_frag_enum => undefined,next_protocol_negotiation => undefined,
       renegotiation_info => {renegotiation_info,undefined},
       signature_algs =>
           {signature_algorithms,
               [ecdsa_secp521r1_sha512,ecdsa_secp384r1_sha384,
                ecdsa_secp256r1_sha256,rsa_pss_pss_sha512,rsa_pss_pss_sha384,
                rsa_pss_pss_sha256,rsa_pss_rsae_sha512,rsa_pss_rsae_sha384,
                rsa_pss_rsae_sha256,rsa_pkcs1_sha512,rsa_pkcs1_sha384,
                rsa_pkcs1_sha256,ecdsa_sha1,rsa_pkcs1_sha1,
                {sha512,ecdsa},
                {sha512,rsa},
                {sha384,ecdsa},
                {sha384,rsa},
                {sha256,ecdsa},
                {sha256,rsa},
                {sha224,ecdsa},
                {sha224,rsa},
                {sha,ecdsa},
                {sha,rsa},
                {sha,dsa}]},
       signature_algs_cert => undefined,sni => undefined,srp => undefined}}]

18:17:45.097 [debug] [direction: :outbound, message: [<<22, 3, 3, 1, 6>>, [1, <<0, 1, 2>>, <<3, 3, 95, 190, 145, 185, 32, 41, 105, 199, 57, 16, 26, 183, 141, 131, 7, 86, 239, 187, 16, 231, 36, 197, 34, 172, 159, 243, 163, 6, 51, 10, 85, 7, 0, 0, 90, 0, 255, 19, 2, 19, 1, ...>>]], protocol: :record]
writing (267 bytes) TLS 1.2 Record Protocol, handshake
0000 - 16 03 03 01 06 01 00 01  02 03 03 5f be 91 b9 20    ..........._...
0010 - 29 69 c7 39 10 1a b7 8d  83 07 56 ef bb 10 e7 24    )i.9......V....$
0020 - c5 22 ac 9f f3 a3 06 33  0a 55 07 00 00 5a 00 ff    .".....3.U...Z..
0030 - 13 02 13 01 13 03 13 04  13 05 c0 2c c0 30 c0 24    ...........,.0.$
0040 - c0 28 c0 2e c0 32 c0 26  c0 2a 00 9f 00 a3 00 6b    .(...2.&.*.....k
0050 - 00 6a c0 2b c0 2f cc a9  cc a8 c0 23 c0 27 c0 2d    .j.+./.....#.'.-
0060 - c0 31 c0 25 c0 29 00 9e  00 a2 cc aa 00 67 00 40    .1.%.).......g.@
0070 - c0 0a c0 14 00 39 00 38  c0 05 c0 0f c0 09 c0 13    .....9.8........
0080 - 00 33 00 32 c0 04 c0 0e  01 00 00 7f 00 0d 00 34    .3.2...........4
0090 - 00 32 06 03 05 03 04 03  08 0b 08 0a 08 09 08 06    .2..............
00a0 - 08 05 08 04 06 01 05 01  04 01 02 03 02 01 06 03    ................
00b0 - 06 01 05 03 05 01 04 03  04 01 03 03 03 01 02 03    ................
00c0 - 02 01 02 02 00 33 00 26  00 24 00 1d 00 20 86 e3    .....3.&.$... ..
00d0 - 12 ee 91 72 81 30 10 bc  d6 de 5b 26 f1 97 f7 44    ...r.0....[&...D
00e0 - 92 26 2a ea db 16 3c 5d  c6 76 07 d0 ee 74 00 0a    .&*...<].v...t..
00f0 - 00 0a 00 08 00 1d 00 1e  00 17 00 18 00 0b 00 02    ................
0100 - 01 00 00 2b 00 05 04 03  04 03 03                   ...+.......

18:17:45.097 [debug] [direction: :outbound, message: [<<21, 3, 3, 0, 2>>, <<2, 10>>], protocol: :record]
writing (7 bytes) TLS 1.2 Record Protocol, alert
0000 - 15 03 03 00 02 02 0a                                .......

18:17:45.097 [info]  TLS :client: In state :hello at tls_record.erl:539 generated CLIENT ALERT: Fatal - Unexpected Message
 - {:unsupported_record_type, 18}

18:17:45.098 [error] GenServer #PID<0.284.0> terminating
** (Tds.Error) Unable to establish secure connection to server due {:tls_alert, {:unexpected_message, 'TLS client: In state hello at tls_record.erl:539 generated CLIENT ALERT: Fatal - Unexpected Message\n {unsupported_record_type,18}'}}
    (db_connection 2.2.0) lib/db_connection/connection.ex:87: DBConnection.Connection.connect/2
    (connection 1.0.4) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.13.2) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil
State: Tds.Protocol


  1) test tsl (LoginTest)
     test/login_test.exs:34
     ** (EXIT from #PID<0.281.0>) killed

[josevalim]

They document the handshake under connect though: https://erlang.org/doc/man/ssl.html#connect-3 - and it has a separate return type.

[mjaric]

:) let me try

[mjaric]

If the option {handshake, hello} is used the handshake is paused after receiving the server hello message and the success response

I need to reach buffer before HELLO :)