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://:1433;databaseName=;encrypt=true;trustServerCertificate=true`` * **Username:** ```` * **Password:** ```` **Class Names:** The Java class required is ``com.swiveltechnologies.pinsafe.server.user.database.MSSqlDatabase``. .. notes:: Do not select MS SQL Server as the active database yet. SQL Server 2022 requires encrypted connections by default: you will need to add the following to the URL: ``;encrypt=true;trustServerCertificate=true`` 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. .. code-block:: sql SELECT A AS ID, B AS NAME FROM PINSAFEL **Query: Users who have never logged on (All Repositories)** .. code-block:: sql 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)** .. code-block:: sql 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. .. code-block:: bash 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.