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.
- The vsql logs shows it connected using SSL:
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256, protocol: TLSv1.2)
- 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:
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")
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"/>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.crtopenssl 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.crtopenssl 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.crtchmod 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.crtadmintools -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
};
) to see what's going on inside it and make changes if it's possible! It's kinda impossible to understand and rebuild it again !
