Connecting to MS SQL Server

Direct connectons to Microsoft Sequel Server databases

The Omniscope Database Connection dialog accessible under File > Connect to database guides you through the connection parameters.

Connecting to Microsoft SQL Server 2008

As with SQL Server 2005, the same configuration applies. You will need to have downloaded and installed the latest Microsoft SQL Server JDBC Driver.

The same connection settings for SQL Server 2005 apply.

The Driver jar path is different however, you must enter:
  "C:\Program Files\Microsoft SQL Server JDBC Driver 2.0\sqljdbc_2.0.1803.100_enu\sqljdbc_2.0\enu\sqljdbc4.jar" or similar (depending on your installation).

Use "Connect to database" as usual, specifying a username and password, choosing a table, and optionally customising the SQL query.     

Connecting to Microsoft SQL Server 2005

As with SQL Server 2000, your database server must be configured to use mixed mode authentication. The server must also have the TCP/IP network protocol enabled. You will need to have downloaded and installed the Microsoft SQL Server JDBC Driver. Do not use the "sa" administrative login; instead create a dedicated user for the database(s) you need to connect to.

Open the Omniscope Database Connection dialog under File > Connect to database. Choose the generic "JDBC" option on the first page.

• For Driver jar path, enter "C:\Program Files\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_1.0\enu\sqljdbc.jar" or similar (depending on your installation).
• For Driver class, enter "com.microsoft.sqlserver.jdbc.SQLServerDriver".
• For JDBC URL, enter "jdbc:sqlserver://localhost;databaseName=AdventureWorks" (for example; see the Microsoft JDBC driver documentation for help).

Connecting to Microsoft SQL Server 2000

There is a specific option for SQL Server 2000. You will need to know the database server details (host name and port) and login details (username and password). At present, due to limitations in the Microsoft JDBC driver, your database must be configured to use Mixed Authentication and not Trusted/Windows Authentication (see here for more information).

Frequently Asked Questions

Q: I am getting an error establishing socket when using JDBC?

A: Try the following known solutions:

• Enabling protocols on your SQL Server to see if this fixes the problem. Information
  on how to do this can be found at http://support.microsoft.com/default.aspx?scid=kb;en-us;313178
  Then restarting the server by stopping and restarting the SQL Server instance and then trying again.

• Open SQL Enterprise Manager
  Invoke properties by right clicking the local host (SQL server)
  Check the check box for Autostart SQL Server agent
  Check the check box for Autostart MSDTC