Latest revision as of 12:52, 11 May 2017

MSSQL Database How To Guide

Introduction

This configuration document outlines how to integrate Swivel using a MS SQL Database. Swivel creates the required tables in the MS SQL database and populates them with data from the Swivel server and also handles deletion of records. The database needs to be running for Swivel to start.

Prerequisites

Fully configured Swivel server

MS SQL Server 2005-2012 (see note for using SQL Server 2000)

Configure SQL server with empty Swivel database

Configure SQL user with dbo permissions

SQL Server Driver for JDBC

SQL Server Account (not an integrated account) with DBO privileges and read/write. The JDBC driver can't use integrated authentication, you have to use SQL authentication.

Sufficient Disk Space for database. As a guide allow 3Kb per user.

Baseline

MS SQL Server 2012

Swivel 3.10.2

MS SQL Server 2005

Swivel 3.5

CMI 0.9w

Webmin 1.430

Recommendations

It is recommended that the following are in place:

Regular Backups of the MS SQL database

Clustering and High Availability of Database

Architecture

Information is taken from the repository data source and inserted into the MS SQL database. Multiple Swivel servers can use the MS SQL database for user authentication. The MS SQL database uis replicated to other Swivel servers and DR sites.

Repository Synchronisation Considerations

Multiple Swivel servers can connect to a database. It is essential that when multiple Swivel servers are synchronising data from the same data source they do so at different times so as to avoid the same user being created at the same time on different cluster members. This can be done by setting only one active synchronisation, or setting different times for synchronisation.

Installation

Creating the Database

You need to create a new, empty database in MS-SQL Server to contain the Swivel data. You also need to create a user account on MS-SQL Server, or use an existing one, and give that user dbo privileges on the new database. Note that Swivel is unable to use integrated authentication: it must be a SQL Server account.

Installing a JDBC driver

The driver will need to be downloaded see:

http://www.microsoft.com/en-us/download/details.aspx?id=11774 (version 4.0)

It is recommended that you select the link for the version that corresponds to the operating system you are downloading to, not the target operating system. The contents are the same for both: it is only the packaging method that is different.

Expand the downloaded file on your local machine and locate either sqljdbc.jar if you are using Java 5, or sqljdbc4.jar if you are using Java 6 or 7. DO NOT SELECT BOTH. Copy the selected file to <path to apache Tomcat>/webapps/pinsafe/WEB-INF/lib.

Hint: To verify the java version run the following at the command line: java -version

Ensure that the permissions, and any file or group ownership are correct, i.e. the same as the other files in the group.

Restart Tomcat

NOTE: the above driver is not compatible with SQL Server 2000. Use the older driver (version 2.0) from the following link:

http://www.microsoft.com/downloads/details.aspx?familyid=99B21B65-E98F-4A61-B811-19912601FDC9&displaylang=en

Configure the Swivel Database

Note For their data sources Master servers should run in Synchronised mode and slave servers should run in slave mode.

The following parameters need to be set for the database:

Identifier: Database Name

Class: Swivel Java class used for database

Driver: Software driver used for communication with database

URL: Location of the database

Username: Database username

Password: Database Password

Sample MS SQL database config

NOTE: there is a preconfigured entry containing the Identifier and Class that you can use

Identifier MS SQLServer

Class: com.swiveltechnologies.pinsafe.user.database.MSSqlDatabase (for Swivel 3.5 and earlier)

Class: com.swiveltechnologies.pinsafe.server.user.database.MSSqlDatabase (for Swivel 3.6 and later)

Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

URL: jdbc:sqlserver://192.168.0.1:1433;databaseName=PINsafe

Username: pinsafe

Password: pinsafe

Note: At this stage DO NOT set the database to MS SQL5 but keep it as Internal

Create Local database admins on each Swivel server

Ensure that the name of each XML repository is unique to each Swivel server. Ensure that each Swivel server has a unique admin account name. See Repository

Migrate Data into MS SQL database

Migrate the users from the Swivel server into the MS SQL database. See also Migrate How to guide.

From the Swivel Administration console select Migrate > Data then select the MSSQL database and enter MIGRATE, click on apply. A message indicates the data has been successfully migrated. Note, this feature began from v3.9.2 onwards.

The logs will indicate the migration was successful.

The length of time for Migration will vary between install sites, but 500 users will approximately take 2 minutes.

Select the MS SQL database

Select the MS SQL database

From the Swivel Administration console select Database/General then select the MS SQL database and click on apply.

The logs will indicate the datavase selection was successful.

Verify that the status page lists an Active database of MS SQL

Database Backup and Restore

The database should be regularly backed up. When backing up and restoring the database, security settings may need to be adjusted.

Verifying the Installation

The tables should now contain data.

Troubleshooting

Check the Swivel, Apache Tomcat logs and logs on the MS SQL server.

Error Messages

Driver loaded successfully.

Swivel has loaded the SQL sriver

Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.user.database.DatabaseException: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

The SQL driver could not be found, ensure the SQL driver is installed in the correct path.

PINsafe data migration failed! com.swiveltechnologies.pinsafe.user.database.DatabaseException: Please supply the JDBC Driver class

The SQL driver could not be found, ensure the SQL driver is installed in the correct path.

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

This can occur when the driver has been copied and the Tomcat not restarted

Failed to select database MS SQL Server.

This is seen in the Swivel administration console when a database connection cannot be made. Check the Swivel logs.

Unable to open the database: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect

Swivel cannot open the the SQL database. Check database port, IP, DNS, instance name, username, password.

Unable to open the database: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.1.1, port 1433 has failed. Error: "Connection refused. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

Swivel cannot open the SQL database. Check database port, IP, DNS, instance name, username, password.

Unable to open the database: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.1.2/data, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

Swivel cannot open SQL database. Check database port, IP, DNS, instance name, username, password.

Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.user.database.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host has failed. java.net.ConnectException: Connection refused: connect

Check that the MS SQL database exists and that the Swivel server can connect to it. Check database port, IP, DNS, instance name, username, password.

PINsafe data migration failed! com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.1.2/data, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

Migrating data to the SQL database failed, Swivel cannot connect to the SQL database. Check database port, IP, DNS, instance name, username, password.

PINsafe data migration failed! com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.1.2, port 1433 has failed. Error: "Connection refused. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

Swivel cannot connect to SQL database. Check database port, IP, DNS, instance name, username, password.

admin:Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.lang.ClassNotFoundException: com.microsoft.sqlserver:jdbc.SQLServerDriver

ERROR 127.0.0.1 admin:Failed trying to load JDBC driver class

The Java class path for the driver is incorrect and cannot be loaded (in this instance a : has been used instead of a .)

Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver

The java database driver cannot be found. Ensure that it has been uploaded to the correct location and has the correct file ownership and permissions.

Testing the Port Connectivity

From the Swivel virtual or hardware Appliance or server telnet to the MS SQL port;

Example: telnet 192.168.0.1 1433 Connecting to 192.168.0.1...Could not open connection to the host on port 1433: Connect failed

Known Issues and Limitations

If you want to use SQL Server 2000, you must have Swivel 3.6 or later, and change the Swivel class name to com.swiveltechnologies.pinsafe.server.user.database.MsSql2000Database. Note that there is not a pre-configured database definition for this, so you will have to create a new entry.

IMPORTANT: there is a known issue with JDBC in Java version 1.6.0 buillds 27 to 29: see http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7105007. This will cause JDBC connections to hang. The solution is to upgrade Java to a later version.

Additional Information

For assistance in the Swivel installation and configuration please firstly contact your reseller and then email Swivel Secure support at support@swivelsecure.com