Ignition cannot connect to SQL Server

Ignition
1

Hi,
I have a new installation made of the following items:

  • Ignition 7.9.8 (b2018060714)
  • Windows Server 2016
  • MS SQL Server 2017 (v. 14.0.1000.169) default instance.
  • Java build 1.8.0_172-b11

Ignition cannot connect to my SQL Server database, even if:

  • parametrization data are correct:

    (+ database name is correct + user name and password also corrrect)
  • tcp/ip is enabled on the SQL Server Instance configuration
  • SQL Server Browser is running (even if it is not necessary, since the database is served by the default instance on the 1433 port).

Full gateway exception is:

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (La connessione TCP/IP all'host localhost, porta 1433 non è riuscita. Errore: "Connection refused: connect. Verificare le proprietà di connessione e assicurarsi che un'istanza di SQL Server sia in esecuzione sull'host e accetti le connessioni TCP/IP alla porta. Verificare inoltre che nessun firewall blocchi le connessioni TCP alla porta.".)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:242)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:196)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1014)
	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: La connessione TCP/IP all'host localhost, porta 1433 non è riuscita. Errore: "Connection refused: connect. Verificare le proprietà di connessione e assicurarsi che un'istanza di SQL Server sia in esecuzione sull'host e accetti le connessioni TCP/IP alla porta. Verificare inoltre che nessun firewall blocchi le connessioni TCP alla porta.".
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1049)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841)
	at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
	at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
	... 12 more

7.9.8 (b2018060714)
Oracle Corporation 1.8.0_172

Any suggestion?

Thanks in advance, regards

2

Check this knowledge base article.

1 Like
3

Hallo Paul, thanks for your answer. I read through the knowledgebase article. My scenario is nr 1, slightly changed: my network administrator installed the “default” Sql Server instance for me and therefore I don’t need to give an instance name - just localhost is enough. Please consider that Ms Sql Server Managment studio can connect to that database both from the same machine and from another machine on the same network by using Sql Server Authentication. So I wouldn’t look for an incorrect parameter in the server installation, neither in the connection string. So where is the problem? The only difference I see between Management Studio and Ignition is the communication library: .NET vs JDBC. But again this is my 10th project with Ignition and Sql Server and this is the first time I cannot communicate with the database. Only difference from preceding projects: Windows Server 2016 instead of Windows 7 through 10 Professional and the machine is in a Windows domain instead of a stand alone machine.
Any more ideas?

Thanks again, regards

EDIT: solution found
Never had the need to do this before, but this was the way to go: just tell SQL Server that you want it to listen to port 1433 (it’s just the default port for the default instance as in my case).

I had to configure

1 Like