Connecting to SQL Server with integrated authentication

Modified on Mon, 5 Aug at 6:30 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) Download the Microsoft SQL Server JDBC driver ZIP file

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

From Omniscope 2024.1 onwards, the driver is bundled. You will see something like this:

If you have an older installation, you will typically have your own download configured here. You should delete that and use the bundled driver if you have 2024.1 or later. Restart Omniscope after deleting.

However, to use Windows Authentication, you will need to download the same version of the driver that is shown on the above page, in order to obtain the necessary compatible DLL file. Visit here (or here for older releases) to obtain the driver with the closest matching version number. Download in zip format.

Extract this zip.

(2) Install the DLL file in Windows

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

Copy it to your Windows system32 folder, typically C:\Windows\System32. On some systems you may not have permission to copy the file here, or may appear to succeed but the file will not persist. Another location to try is C:\Users\YOUR_ACCOUNT\AppData\Local\Microsoft\WindowsApps. If this is not workable, contact your system administrator for help.

(3) 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.

(4) 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.

(5) 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 "Additional 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.