Microsoft SQL Server

Introduction

This guide outlines how to integrate Swivel Secure Sentry with a Microsoft SQL Server database. By default, Swivel uses an internal MariaDB database, but for enterprise environments, it can be configured to use an external MS SQL Server to store user data, handle record deletions, and support high availability.

Note

The external database must be running and accessible for the Swivel server to start once this configuration is active.

Prerequisites

Before commencing the integration, ensure the following are in place:

  • Swivel Server: A fully configured Swivel server instance.

  • MS SQL Server: Supported versions include 2005 through 2022 (See Known Issues for SQL 2000).

  • Network: The Swivel server must be able to communicate with the SQL Server port (default 1433) over TCP/IP.

  • Disk Space: Allow approximately 3Kb per user.

  • Permissions:

    • A SQL Server account (Note: Integrated/Windows Authentication is not supported; you must use SQL Authentication).

    • The user requires DBO privileges and Read/Write access.

Recommendations

  • Backups: Establish regular backups of the MS SQL database.

  • High Availability: It is recommended to use Database Clustering for redundancy.

Architecture and Synchronization

Information is taken from the repository data source (e.g., Active Directory) and inserted into the MS SQL database. Multiple Swivel servers can connect to the same MS SQL database for user authentication.

Warning

Synchronization Conflicts: If multiple Swivel servers connect to the same database, they must not synchronize data from the repository at the same time. This prevents duplicate user creation. Configure schedule times so only one server synchronizes at a time, or set only one active synchronization point in the cluster.

Installation

Step 1: Create the Database

  1. Create a new, empty database in MS SQL Server (e.g., named PINsafe).

  2. Create a SQL Server user account (or use an existing one).

  3. Grant this user dbo privileges on the new database.

Step 2: Install the JDBC Driver

Sentry requires the Microsoft SQL Server JDBC Driver to communicate with the database.

  1. Download the latest Microsoft JDBC Driver for SQL Server which is compatible with Java JRE8. This can be found in the Maven Central repository, among other places: https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc. Make sure you select the one that is compatible with jre8. Click on the latest version, then under Files, select jar.

  2. Copy this file to the Sentry appliance in the /home/admin folder.

  3. Open the command line on the appliance. If you do not have the command line password, you will need to contact the Swivel Secure Helpdesk for assistance.

  4. Copy the file to the correct folder: cp mssql*.jar /usr/local/tomcat/webapps/sentry/WEB-INF/lib

  5. Ensure file permissions/ownership match other files in that directory. chown swivel:swivel /usr/local/tomcat/webapps/sentry/WEB-INF/lib/mssql*.jar

  6. Ensure that this is the only mssql jar file in that folder - if there is an older version, delete it.

  7. Restart Tomcat to load the driver.

Configuration

Step 3: Configure Database Connection

Go to the Swivel Administration Console to configure the database connection details.

  1. Navigate to Database -> General.

  2. Enter the parameters below.

Required Parameters:

  • Identifier: MS SQLServer (or a custom name)

  • Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

  • URL: jdbc:sqlserver://<IP_Address>:1433;databaseName=<DB_Name>;encrypt=true;trustServerCertificate=true

  • Username: <SQL_User>

  • Password: <SQL_Password>

Class Names: The Java class required is com.swiveltechnologies.pinsafe.server.user.database.MSSqlDatabase.

Step 4: Migration and Activation

Before switching the active database, you must create local admins and migrate existing data.

  1. Create Local Admins: Ensure that the XML repository has a unique name and that each Swivel server has a unique admin account name (see Repository documentation).

  2. Migrate Data: * Go to Migrate > Data. * Select the MS SQLServer entry you created. * Enter the command MIGRATE and click Apply. * Check the logs to confirm success (approx. 2 minutes for 500 users).

  3. Select Database: * Go to Database > General. * Select the MS SQLServer entry from the list. * Click Apply. * Verify the Status Page now lists the Active Database as MS SQL.

Reporting and Queries

When using MS SQL, you can run direct queries against the database for reporting. This is useful for gathering information not available in the standard Administration Console.

Note

The Audit Log feature requires Swivel 3.4 or higher.

Differences from MySQL

  • Time: Use GETDATE() instead of NOW().

  • Intervals: Use DATEDIFF instead of INTERVAL.

Common SQL Queries

Query: Identify Repository IDs To query specific repositories, you need their internal IDs.

SELECT A AS ID, B AS NAME FROM PINSAFEL

Query: Users who have never logged on (All Repositories)

USE pinsafe_rep;
SELECT H AS Username
FROM PINSAFEJ
WHERE G NOT IN (SELECT DISTINCT A FROM PINSAFEN WHERE C=0)

Query: Inactive Users (Created >30 days ago, no login in last 30 days)

SELECT U.H
FROM PINSAFEJ U
JOIN PINSAFEN A1 ON U.G = A1.A AND A1.C = 3
LEFT OUTER JOIN PINSAFEN A2 ON U.G = A2.A AND A2.C=0
WHERE (A1.D < DATEADD(day, -30, GETDATE()))
AND (A2.D IS NULL OR A2.D < DATEADD(day, -30, GETDATE()))

Troubleshooting

Connectivity Testing

If Swivel cannot connect to the database, test network connectivity from the Swivel appliance using Telnet.

telnet 192.168.0.1 1433

  • Success: The screen goes blank or connects.

  • Failure: Connect failed or Connection refused.

Common Error Messages

Driver loaded successfully

  • Status: Normal. Swivel has loaded the SQL driver.

java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

  • Cause: The JDBC driver JAR file is missing from WEB-INF/lib, has incorrect permissions, or Tomcat was not restarted after copying the file.

Connection refused / TCP/IP connection to the host has failed

  • Cause: Swivel cannot reach the SQL server.

  • Action: Check the IP address, Port (1433), DNS resolution, and ensuring no Firewalls are blocking the connection. Ensure the SQL instance is running and TCP/IP is enabled in SQL Configuration Manager.

ERROR 127.0.0.1 admin:Failed trying to load JDBC driver class

  • Cause: Typo in the class name configuration (e.g., using a colon : instead of a dot .).

Locked user account list shows some users as not locked

  • Context: The locked user count includes accounts that have exceeded failed login attempts but haven’t been formally β€œlocked” by the system yet. The account will be marked as locked upon the next login attempt.

Isolating Regional Server Logins

  • Context: You cannot isolate which regional server a user logged into via SQL queries alone.

  • Action: This information is stored in the Swivel Logs. If the Swivel Agent is configured with the β€˜source’ option, the IP information is logged.

Known Issues

  • SQL Server 2000: Requires Swivel 3.6+ and the older JDBC 2.0 driver. The class name must be changed to: com.swiveltechnologies.pinsafe.server.user.database.MsSql2000Database.

  • Java 1.6 (Builds 27-29): There is a known JDBC bug in these specific Java builds that causes connections to hang. Upgrade Java to resolve this.