K8s container with external MS SQL database

Ignition
1

I have Ignition running on K8s and I am having difficult time connecting to my database server.

Is there something special I need to define in my deploy.yaml for K8s that will allow the ignition contain to be able to connect to my DB server that is not in K8s?
Error from gateway: Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Unexpected rethrowing".)

2

What version of SQL Server are you trying to connect to? If it is an older version, it might be trying to use an encryption algorithm that is disabled by default in later JREs.

3

Trying to connect to MS SQL 2019. I think the issue maybe with egress blocking.

4

@Kevin.Collins I am getting to MS SQL now but I getting kicked by MS SQL. Looks like my MS SQL server is kicking my connection Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: IPV6 address]. I removed the IPV6 address form message.

5

I suppose it might be worth trying the latest version of SQL Server JDBC driver and trying that in Ignition. Keep in mind that if you're adding the JAR file to the user-lib/jdbc folder via a derived image, you need to remove the existing one (currently mssql-jdbc-9.4.0.jre11.jar in 8.1.26).

6

I will use mssql-jdbc-12.2.0.jre11.jar that is the current version according to MS SQL and will let you know if that solves my issue. I am also enabling TLS as well so this could be TLS.

I will post back once I get this going. 8.1.26 works out of the box on my local K8s setup but does not work on my enterprise setup ATM.

7

This is using the gateway 8.1.26 with default MS SQL JDBC mssql-jdbc-9.4.0.jre11.jar

There were a couple issue causing this.

  1. My K8s pod is on an IPV6 only network
  2. MS SQL connection needed extra Connection Properties.

Solution
Add argument in k8s deployment file - -Djava.net.preferIPv6Addresses=true
This allows IPV6 to be prefer
Add additional to Extra Connection Properties
databaseName=DatabaseName;ipaddresspreference=IPV6First;sslProtocol=TLSv1.2

This has solved connecting to MS SQL using a MS SQL user. Still working on a solution for connecting and AD user as this is difficult because the container in not AD joined.

8

@Kevin.Collins connect to MS SQL 2019 with AD account is successful.

  1. Restart Deployment kubectl rollout restart deployment ignition -n namespace
  2. Connection URL need to have port. jdbc:sqlserver://ignitionDB.domain.com:1433
  3. UserName should look like user@DOMAIN.COM and yes the domain.com must be in upper case
  4. Extra Connection Properties needs many extra items
    databaseName=IgnitionDB;ipaddresspreference=IPV6First;encrypt=true;integratedSecurity=true;authenticationscheme=JavaKerberos;authenticationscheme=NTLM;sslProtocol=TLSv1.2;trustServerCertificate=true;

This works in for gateway 8.1.26 running on k8s that connects to external MS SQL server on IPV6 only network. Should also work for anyone that is wanting to use AD authentication for MS SQL.