If you connect to a SQL Server with a native MS SQL JDBC Driver the following error possibly occurs:
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host HOSTNAME, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.
Possible Cause 1
Most of the times the problem is exactly as stated in the Exception text. The given Port for your connection has to be allowed in the firewall settings.
Possible Cause 2
Sometimes this error occurs because of the TCP/IP configuration in the Sql Server Configuration Manager Console.
- Open the Sql Server Configuration Manager Console.
- Expand the SQL-Server Network Configuration item in the tree on the left side.
- Right click on the TCP/IP entry on the left side.
- Click on the tab for the IP adresses
- Scroll down to the IPAll configuration
- Make sure that the configuration of TCP DynamicPorts is empty
- sometimes it is preconfigured with a value 0 or a value above 50000
- Make sure that the correct port number for the connection is configured for the TCP Port
- Make sure that the configuration of TCP DynamicPorts is empty
Possible Cause 3
If you are trying to specify a SQL Server instance name with the hostname in the JDBC URlL like this
jdbc:sqlserver://<hostname>\<instancename>:<port>;databaseName=<database_name>;...
then the instance name might not be resolved correctly by the SQL Server (Driver). Instead use the following syntax to specify the instance name:
jdbc:sqlserver://<hostname>:<port>;instanceName=<instance_name>;databaseName=<database_name>;...
For JobScheduler releases before 1.11 such changes to the JDBC URL have to be applied manually after installation as they cannot be specified with the installer.
For JobScheduler releases starting from 1.11 you can specify external files for the Hibernate connections used by the JobScheduler Master and JOC Cockpit installers:
- Provide Hibernate configuration files:
- Hibernate configuration files include
hibernate.cfg.xml
reporting.hibernate.cfg.xml
- For new installations
- check the How to change the database connection, password, user, database port etc. article for samples of Hibernate configuration files.
- For existing installations
- Find the Hibernate configuration files in the
./config
folder of the JobScheduler Master and in the./jetty_base/resources/joc
folder of JOC Cockpit.
- Find the Hibernate configuration files in the
- Hibernate configuration files include
- Apply changes for use of a SQL Server instance name:
- Modify the following setting in the Hibernate configuration files:
<property name="hibernate.connection.url">jdbc:sqlserver://hostname:port;instanceName=instance_name;databaseName=database_name;...
</property>
- Use the instance name as a separate URL parameter.
- Modify the following setting in the Hibernate configuration files:
- Assign Hibernate configuration files with the installer:
- You can specifiy with the installer not to add database connection information individually but to use pre-existing hibernate configuration files.