I am facing issues connecting to the data source Microsoft SQL Server, i am getting the following error
Connection failure. You must change the Database Settings.
java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host
The TCP/IP is enabled in SQL Server configuration manager, but still same error.
I wanted to try with Microsoft SQL Server(OBDC Driver) but somehow that doesnt seem to be an option in the free version, i am currently using Open Studio - Data Integration.
Never use the ODBC driver. Talend Open Studio ships with a well working JDBC driver for Microsoft SQL Server!
If you use the tMSSQLInput or Output components you will get components with a well working database driver!
You can also choose between 2 different drivers - both are working, you should check which works for you better.
The error message looks pretty clear. There is a network issue and you have to solve this.
I suggest you check the connectivity from your host with a tool like DBeaver.
As per the error, the issue is observed as the studio machine is unable to connect to the TCP port 1433 . The following commands are used to confirm this:
1) The 'ping DBServer' command is successful.
2) The 'telnet DBServer 1433' always fails. There is no firewall configured on the server hosting MSSQL Server.
Let us know if it is OK with you.
Cool thanks everyone for quick replies.
yes i did test the 1433 port connectivity and it failed.
C:\Users\502662774>telnet VDCGWP03012.logon.ds.ge.com 1433
Connecting To VDCGWP03012.logon.ds.ge.com...Could not open connection to the hos
t, on port 1433: Connect failed
But one other question, i can easily connect same database via Tableau, is it just because we dont need to give port there?
Confirm the instance of the SQL Server Database Engine is installed and running.
Logon to the computer hosting the instance of SQL Server.
Start SQL Server Configuration Manager. (Configuration Manager is automatically installed on the computer when SQL Server is installed. Instructions on starting Configuration Manager vary slightly by version of SQL Server and Windows. For help starting Configuration Manager, see SQL Server Configuration Manager.)
Using Configuration Manager, in the left pane select SQL Server Services. In the right-pane confirm that the instance of the Database Engine is present and running. An instance named SQL Server (MSSQLSERVER) is a default (unnamed) instance. There can only be one default instance. Other (named) instances will have their names listed between the parentheses. SQL Server Express uses the name SQL Server (SQLEXPRESS) as the instance name unless someone named it something else during installation. Make a note of the name of the instance that you are trying to connect to. Also, confirm that the instance is running, by looking for the green arrow. If the instance has a red square, right-click the instance and then click Start. It should turn green.
If you are attempting to connect to a named instance, make sure the SQL Server Browser service is running.
Get the IP Address of the computer.
On the Start menu, click Run. In the Run window type cmd, and then click OK.
In the command prompt window, type ipconfig and then press enter. Make a note of the IPv4 Address and the IPv6 Address. (SQL Server can connect using the older IP version 4 protocol or the newer IP version 6 protocol. Your network could allow either or both. Most people start by troubleshooting the IPv4 address. It's shorter and easier to type.)
Get the TCP port number used by SQL Server. In most cases you are connecting to the Database Engine from another computer using the TCP protocol.
Using SQL Server Management Studio on the computer running SQL Server, connect to the instance of SQL Server. In Object Explorer, expand Management, expand SQL Server Logs, and then double-click the current log.
In the Log Viewer, click the Filter button on the toolbar. In the Message contains text box, type server is listening on, click Apply filter, and then click OK.
A message similar to Server is listening on [ 'any' <ipv4> 1433] should be listed. This message indicates that this instance of SQL Server is listening on all the IP addresses on this computer (for IP version 4) and is listening to TCP port 1433. (TCP port 1433 is usually the port used by the Database Engine. Only one instance of SQL Server can use a port, so if there is more than one instance of SQL Server installed, some instances must use other port numbers.) Make a note of the port number used by the instance of SQL Server that you are trying to connect to.
make sure jdbc driver is downloaded and registered - Talend will usually prompt for this