SQL Server Connection Faulted

Ignition
,
1

After replacing a development installation of Ignition 8.1 with 7.9.17, I am suddenly unable to communicate with my local SQL server. The connection was fine before the reinstall but now it's faulted and showing this error:

Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "No appropriate protocol (protocol is disabled or cipher suites are inappropriate)".)

I've double checked all the usual SQL server settings (SQL server authentication enabled, TCP/IP enabled, SQL server browser running etc.) and everything seems to be fine, which isn't surprising seeing as the version 8 gateway wasn't having any issues communicating with it. I'm also able to connect to the server using the same login credentials that Ignition is configured to use, both locally and from another PC on the same network.

The only change I can think of between the two installations is that I've added a couple of extra user accounts to the PC Ignition and SQL are installed on, there was only one when I installed the original Ignition 8 gateway. The same user account was used to install both Ignition and SQL though so I'm not sure why simply having multiple users would cause issues.

Any suggestions would be appreciated.

1 Like
2

Hi @liam, have you seen this thread: Database Connection Error

3

I hadn’t, I think that was posted right after I finished my search of the forum.

That was exactly the problem though and downgrading Java worked, thank you for the link!

1 Like
4

I think what’s going on here is that newer versions of Java have disabled some of the older cipher suites by default and this is causing there to be no intersection between the set of suites enabled by default in Java and the set of suites enabled by default in SQL Server.

2 Likes
5

I ran into this the other evening with some old SQL Express installs. Rather than downgrading java, you can edit the java.security file to unblock TLSv1 and/or TLSv1.1. If you can’t upgrade what you are trying to connect, that is. ):

3 Likes
6

I’m having the same issue after upgrading to 8.1.7 from 8.1.4. Thanks for posting this Phil, I’ve unblocked TLSv1 and 1.1 in the java.security file in /usr/lib/jvm/java-1.11.0-openjdk-amd64/conf, and performed a reboot etc. but I’m still seeing the issue. Is there any other setting required to force older TLS etc? Thanks!

7

You might need extra connection properties in Ignition, either sslProtocol or enabledTLSProtocols, to specify the older version.

The opposite of the intent of the above StackOverflow post, but same properties.

8

Thanks again for helping Phil, I’ve tried both of the following: enabledTLSProtocols=TLSv1.1;sslProtocol=TLSv1.1

Individually and together and no change. Maybe I need to downgrade the driver for now…unless you have another other thoughts?

9

You might need to downgrade. The case I encountered was a SQL Server 2008, which isn’t supported past driver 6.2. See this compatibility chart:

I think with the correct driver, I didn’t need the extra connection properties, just the java.security changes. YMMV, of course.

10

Ya this is really interesting, the SQL version is MSSQL14 Express. So as far as I can tell it should actually support TLSv1.2. But still combing the online articles on this to get better acquainted. Thanks again for jumping in on the weekend, much appreciated.

11

You’re welcome, and I understand. SQL Server makes me want to pull my hair out, for numerous reasons. ):

1 Like
12

Found instructions on setting registry values to enable TLS1.2 on the SQL server machine. Still no dice! Going to step back, have a coffee and resume later.

Here’s a link to info on Microsoft server settings: Enable TLS 1.2 on servers - Configuration Manager | Microsoft Docs

13

@pturmel Just to confirm, is it correct to change the java.security file in /usr/lib/jvm/java-1.11.0-openjdk-amd64/conf ? Or is there another place that would be more specific to Ignition’s java settings? This area is out of my wheelhouse so apologies if this is a dumb question.

14

It needs to be the java.security file for the java that is running Ignition. For v7.9, that would be some version of java 8.

15

ok, in this case we’re running Ignition 8.1.7, so I’m assuming that’s the correct location to make the changes.

16

Well, probably not. Ignition v8+ packages its own java. It doesn’t use your system’s java.

(This post is tagged v7.9–forgot you weren’t the OP.)

1 Like
17

Just circling back to say thanks and we have this sorted now. It was just about removing the TLS1 and 1.1 version disables in the correct java.security file in the Ignition java deployment. Followed by an Ignition restart. Thanks again @pturmel

1 Like
18

Remember to do the same on the redundant backup if you have one!

1 Like
19

Could you please provide the steps to remove version disables in detail?. TIA

20

I just went through this issue on 8.1.7. If you haven’t fixed it yet, I modified the file C:\Program Files\Inductive Automation\Ignition\lib\runtime\jre-win\conf\security\java.security

Deleted TLSv1 and TLSv1.1

Restarted ignition service and database connections worked just fine.

11 Likes