Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

unable to establish secure connection between ADO.net and the Database using SSL/TLS

lop2loplop2lop
edited November 3 in General Discussion

Hello.
We use Vertica v.9.2.1-0 and recently, our IT security team tried to enable SSL/TLS and based on following tests and results it seems to be working well.

  1. The vsql logs shows it connected using SSL:

SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2)

  1. We tried ODBC and it works great:

SELECT user_name,client_label,ssl_state FROM sessions;
dbadmin | ODBC using SSL | Server

But in ADO.net, I can't establish connection when SSL is set to true.
It always gives me this exception : "The remote certificate is invalid according to the validation procedure"

What i've tried:

  1. Install certificates (Client.crt, server.crt and even serverca.crt) on Windows Key Store (both Current User and Local Machine , both "Automatically select the certificate store based on the type of certificate" and "Trusted Root Certification Authorities")

  2. Override dotnet TLS defaults by including below line in the Runtime section of config file of my application :
    <AppContextSwitchOverrides value="Switch.System.Net.DontEnableSystemDefaultTlsVersions=false"/>

  3. Using hostname instead of IP address (Placed in windows HOST file)

Did we missed something or should we do anything else ?

I know the default TLS version in .NET Framework lower than .NET 4.7 is TLS 1.1 (or maybe older!) and Vertica ADO.net dll (Vertica.Data.dll v.10.0.1.0) target framework is .NET 3.5 ! Could this be the cause of the problem ?

Our SSL activation instructions:

openssl genrsa -out servercakey.pem
openssl req -new -x509 -key servercakey.pem -out serverca.crt

openssl genrsa -out server.key
openssl req -new -key server.key -out server_reqout.txt
openssl x509 -req -in server_reqout.txt -days 3650 -sha256 -CAcreateserial -CA serverca.crt -CAkey servercakey.pem -out server.crt

openssl genrsa -out client.key
openssl req -new -key client.key -out client_reqout.txt
openssl x509 -req -in client_reqout.txt -days 3650 -sha256 -CAcreateserial -CA serverca.crt -CAkey servercakey.pem -out client.crt

chmod 700 server.crt server.key
chmod 700 client.crt client.key
chmod 700 client.crt client.key

[optional] sudo chown -R dbadmin:verticadba /opt/vertica/
[optional] sudo chown -R dbadmin:verticadba /home/dbadmin/server.key
[optional] sudo chown -R dbadmin:verticadba /home/dbadmin/server.crt
[optional] sudo chown -R dbadmin:verticadba /home/dbadmin/serverca.crt
[optional] sudo chown -R dbadmin:verticadba /home/dbadmin/serverca.crt

admintools -t set_ssl_params -d DBNAME -p PASSWORD -k /home/dbadmin/server.key -c /home/dbadmin/server.crt

SELECT set_config_parameter('EnableSSL', '1');

admintools -t stop_db -d DBNAME -p PASSWORD
admintools -t start_db -d DBNAME -p PASSWORD

My ConnectionStringBuilder:

var verticaConnectionStringBuilder = new VerticaConnectionStringBuilder
            {
                Host = "192.168.33.34",
                Database = "DBName",
                User = "dbadmin",
                Password = "password",
                Label = "ADO.net using SSL",
                SSL = true,
                PreferredAddressFamily = AddressFamilyPreference.Ipv4
            };

Answers

  • I’m facing with the same problem as you, did you find any solution for it?

  • lop2loplop2lop
    edited November 27
    > @verban said:
    > I’m facing with the same problem as you, did you find any solution for it?

    Not yet ...
    But if I find the solution, I will surely comment about it.
  • Any comment?
    @Jim_Knicely

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.