Connecting to SQL Server with integrated authentication

Modified on Mon, 16 Jan 2023 at 01:41 PM

Here's how to connect Omniscope (when installed on a Windows server) to a local Microsoft SQL Server installation using Windows authentication (single sign on / SSO) via the local ActiveDirectory Windows account that Omniscope is running under.

(1) Obtain the latest Microsoft SQL Server JDBC driver

Go to the admin app (https://yourserverip/_admin_/) and select Database drivers:

You will see a link where to download the specific Microsoft SQL Server JDBC driver:

Open this link and download the latest JDBC ZIP file. At time of writing it is version 11.2 and the zip file you'll download is sqljdbc_11.2.3.0_enu.zip. You'll see something similar, with just a higher version number as time goes by.

Extract this zip.

(2) Install the JAR file in Omniscope

In the extracted zip, locate the "mssql-jdbc-....jre11.jar" file. At time of writing it's mssql-jdbc-11.2.3.jre11.jar.

Upload it on the same Omniscope Database Drivers admin page as above:

(3) Install the DLL file in Windows

Also in the extracted zip, locate the "auth\x64\mssql-jdbc_auth-....x64.dll" file. At time of writing it's mssql-jdbc_auth-11.2.3.x64.dll.

Copy it to your Windows system32 folder, typically C:\Windows\System32.

(4) Update the Windows service configuration

If you're running Omniscope via the desktop shortcut, skip this step. This step applies only if you have Omniscope running as a Windows service.

Edit your Omniscope's wrapper.conf file (in either C:\Users\YOUR_ACCOUNT\AppData\Local\Visokio Omniscope app\service, or in C:\Program files (x86)\Visokio Omniscope\service).

Find the line with wrapper.java.library.path.1, and add the following as another line immediately after it:

wrapper.java.library.path.append_system_path=TRUE

(if it doesn't already exist - it was added by default in new installations of Omniscope version 2023.1 onwards).

Save and close.

Note that wrapper.conf is preserved when installing a new version on top of the existing version.

(5) Restart Omniscope

From the blue system tray icon, quit Omniscope and start again from the desktop shortcut. Or, if using a Windows Service, use Services to restart the Visokio Omniscope service.

(6) Configure a Database block

In the Omniscope web app, create a project and add a Database block. Open the block and select Microsoft SQL Server / Azure SQL in the Connection settings:

Fill in the other details (host, port, instance, database) as needed. Port is typically 1433 and instance is not always needed.

Now add "Addition parameters (advanced)" as follows:

Name	Value
encrypt	true
trustServerCertificate	true
integratedSecurity	true

Click "Connect". If all is well, the Options tab should appear where you can select a Table to query, or enter custom SQL.

You should now bookmark this block, whereupon it will be available in other projects from the Add Block menu, as a template.