MID server can't retrieve any data

ewilks · ‎06-03-2014

We are upgrading from Berlin to Dublin. First of all, the test server in question was cloned from the production server before upgrading to Dublin.

We stood up a new MID server that shows as being up from our SN test instance Dublin version. I have updated my Data Sources so that they use the MID server we want them to use and I am trying to connect to a number of databases. I have tested the connection to an oracle database (the query we are trying to execute return 1030 rows using oracle) and a SQLServer database (which returns 626 rows when running the query in SQL Server).

But when I open the Data Sources and click on Test Load 20 Records, the queries attempt to run for about 45 seconds before returning myriad errors (shown for each db below). I'm assuming that there is something that I need to request of the server team, but I wouldn't know what to ask at this point. The ports that we're attempting to use are the same as what's working on production.

Thanks for any assistance...

I get the following errors in Oracle (sorry, I know most of it will be useless, but figured I might as well put it all in there):

MID Server reported error: java.sql.SQLException: java.sql.SQLException: Io exception: The Network Adapter could not establish the connection

oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)

oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)

oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:255)

oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:387)

oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:441)

oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)

oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)

oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)

java.sql.DriverManager.getConnection(Unknown Source)

com.service_now.mid.connections.jdbc.JDBCConnection.connect(JDBCConnection.java:74)

com.service_now.mid.connections.jdbc.JDBCConnectionFactory.create(JDBCConnectionFactory.java:45)

com.service_now.mid.connections.ConnectionCachePool.getAvailableConnection(ConnectionCachePool.java:79)

com.service_now.mid.connections.ConnectionCache.get(ConnectionCache.java:90)

com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:640)

com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:93)

com.service_now.mid.probe.AProbe.process(AProbe.java:63)

com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:95)

com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:36)

com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:644)

com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:93)

com.service_now.mid.probe.AProbe.process(AProbe.java:63)

com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:95)

com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:36)

AND When I try to connect to a SQLServer database I get this list of errors:

MID Server reported error: java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host mwnpmsv1.markwest.com, port 1433 has failed. Error: "connect timed out. 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.".

com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)

com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)

com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)

com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)

com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)

com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)

com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)

com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)

java.sql.DriverManager.getConnection(Unknown Source)

com.service_now.mid.connections.jdbc.JDBCConnection.connect(JDBCConnection.java:74)

com.service_now.mid.connections.jdbc.JDBCConnectionFactory.create(JDBCConnectionFactory.java:45)

com.service_now.mid.connections.ConnectionCachePool.getAvailableConnection(ConnectionCachePool.java:79)

com.service_now.mid.connections.ConnectionCache.get(ConnectionCache.java:90)

com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:640)

com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:93)

com.service_now.mid.probe.AProbe.process(AProbe.java:63)

com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:95)

com.service_now.mid.queue_worker.AManagedThread.run(AManagedThread.java:36)

com.service_now.mid.probe.JDBCProbe.getJDBCConnection(JDBCProbe.java:644)

com.service_now.mid.probe.JDBCProbe.probe(JDBCProbe.java:93)

com.service_now.mid.probe.AProbe.process(AProbe.java:63)

com.service_now.mid.queue_worker.AWorker.runWorker(AWorker.java:95)

Jacob_Andersen · ‎06-03-2014

I don't believe your MID server is in a location that can communicate with the DB hosts. A quick way to tell would be to log into the mid server machine and type the following in a command prompt:

telnet [DB Host] [port you specified in the data source]

Let me know what response you get.

ewilks · ‎06-03-2014

The reply is:

Connecting To erpprdsv5.*********.com...Could not open connection to the host, on

port 1526: Connect failed

I think the problem has something to do with ports needing to be specifically permitted on the db hosts. But that's just a guess.

Jacob_Andersen · ‎06-03-2014

Yes, I think you're correct. There is an environment issue - nothing related to your data source setup.

faisal_dadabhoy · ‎06-03-2014

If you are using a hostname to connect, it may be worth it to try with the IP address.

Just to make sure there is no DNS issues.

Good luck:-)