Troubleshooting connections to SQL Server

If you are unable to connect to SQL Server using Omniscope Classic or Evo, here are some steps you can try to resolve this, or to diagnose the problem.

For example, you might see "Unable to connect to the database: The server took too long to respond", or similar error messages.

Try the following suggestions in order. If you are able to solve this in other ways, please let us know.

Use 'Microsoft SQL Server/Azure SQL' instead of 'Microsoft SQL Server (legacy)'

In Omniscope EVO, unless you are using a very old version of SQL Server, you should normally use the Microsoft SQL Server/Azure SQL database type choice. The legacy driver may still work, but may become incompatible, eg. due to server updates requiring encryption, which may lead to timeouts.

If connecting to an on-premise SQL Server instance, it's likely that it doesn't have a proper certificate. You may also need to add a parameter in the 'Additional parameters (advanced section)'. Add a new parameter and select 'trustServerCertificate' and set the value to 'true'.  For security, you should only use this for trusted  internal instances within your organisation.

Related: if you wish to use Windows integrated authentication, or are having difficulty with this such as "the driver is not configured to use such authentication" errors, see this article.

Test another client

First, you should ensure you are able to connect to the database using another client software from the same PC, e.g. SSMS. If not, contact your database/system/network administrator for assistance.

Proxy settings

The most common fault is some conflict between your corporate proxy server configuration and Omniscope. Follow these instructions, then try to connect to your database again.

Connection method

You should be using the Microsoft SQL Server / Azure choice in the Database block. If attempting to use Windows authentication, see this guide.

As a fallback, try using the legacy option in the Database block, with both the jTDS and Microsoft driver choices in the database connection settings.

Security settings

Try connecting using both Windows Authentication and SQL Server authentication. (This is only worth trying if you had a different error message than shown above.)

IP address

Try connecting using the IP address of the database server, rather than its host name. There may be problems resolving the host name.

Test another JDBC client

Omniscope is a JDBC database client, so if another JDBC client is able to connect, Omniscope should be able to (see Connect using JDBC, below); and if not, the problem lies outside of Omniscope.

Instructions:

1. Obtain a 3rd party JDBC client such as SQuirrel:
   1. Download the plain zip standard version, currently 4.0.0. Extract. (No need to use an installer).
   2. To start it, double-click squirrel-sql.jar, inside the extracted zip folder (requires Java 8+)
2. Download and install the latest Microsoft JDBC driver compatible with Java 8 (at time of writing this was version 7.4)
3. Configure a new database connection:
   1. Launch Squirrel (see above)
   2. In the Drivers tab, open Microsoft SQL Server, and in 'Extra classpath', add the MS JDBC driver java 8 JAR file, e.g. mssql-jdbc-7.4.1.jre8.jar
   3. In the Aliases tab, add a new alias (+).
   4. Specify name "anything"
   5. Choose driver "Microsoft MSSQL Server JDBC Driver"
   6. Edit the JDBC URL as appropriate, for example:
      jdbc:sqlserver://your_server:1433;databaseName=your_database
   7. Enter the username and password as required
      (For more details, see the Microsoft guide Connecting to SQL Server with the JDBC driver)
4. Test the connection by connecting and executing a SELECT query or browsing available tables.

If you are able to connect, use the same settings in the next step.

If you are not able to connect, the problem lies outside of Omniscope; contact your database/system/network administrator for assistance.

Connect using JDBC (advanced)

In the Omniscope database configuration settings, choose "JDBC (advanced)" and enter the necessary JDBC configuration details as successfully tested in the previous section above:

1. Choose "JDBC (advanced)" database connection type
2. Browse to the MS JDBC driver java 8 JAR file, e.g. mssql-jdbc-7.4.1.jre8.jar
3. Specify the driver class name: com.microsoft.sqlserver.jdbc.SQLServerDriver 
4. Specify the JDBC URL in the format
   jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]
   for example:
   jdbc:sqlserver://localhost
5. Enter the username and password as required
   (For more details, see the Microsoft guide Connecting to SQL Server with the JDBC driver)

Report diagnostics and get in touch

Finally, if all else fails, send us some diagnostics:

1. In Omniscope Classic, enable: Settings > Application-wide settings > Advanced > Error handling > Report database errors as bugs.
2. Reproduce the database connection error. 
3. In Omniscope Classic, an error report dialog will appear; submit this to us.
   In Omniscope Evo, a saved error report will appear in "Visokio Error Reports" in your user profile. Send this to us.
4. Send us an email at support[at]visokio.com to let us know.
5. Revert the setting changed in step 1.