MS SQL Database How To Guide
From Swivel Knowledgebase Wiki
Contents |
MSSQL Database How To Guide
Introduction
This configuration document outlines how to integrate PINsafe using a MS SQL Database. PINsafe creates the required tables in the MS SQL database and populates them with data from the PINsafe server. PINsafe also handles deletion of records.
Prerequisites
Fully configured PINsafe server
MS SQL 2005/8 or SQL 2005 Express (see note for using SQL Server 2000)
Configure SQL server with pinsafe 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
Sufficient Disk Space for database. As a guide allow 3Kb per user.
Baseline
PINsafe 3.5
CMI 0.9w
MS SQL Server 2005
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 PINsafe servers can use the MS SQL database for user authentication. The MS SQL database uis replicated to other PINsafe servers and DR sites.
Repository Synchronisation Considerations
Multiple PINsafe servers can connect to a database. It is essential that when multiple PINsafe 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 samne time on different cluster members. This can be done by setting only one active synchronisation, or setting different times for sychronisation.
Installation
Creating the Database
You need to create a new, empty database in MS-SQL Server to contain the PINsafe 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 PINsafe 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:
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. 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
Configure the PINsafe 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: PINsafe 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 PINsafe 3.5 and earlier)
Class: com.swiveltechnologies.pinsafe.server.user.database.MSSqlDatabase (for PINsafe 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 PINsafe server
Ensure that the name of each XML repository is unique to each PINsafe server. Ensure that each PINsafe server has a unique admin account name. See Repository_How_to_Guide
Migrate Data into MS SQL database
Migrate the users from the PINsafe server into the MS SQL database. See also Migrate How to guide.
From the PINsafe Administration console select Migration/Data then select the MS SQL database and enter MIGRATE, click on apply. A message indicates the data has been successfully migrated.
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 PINsafe 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
Verifying the Installation
The tables should now contain data.
Troubleshooting
Check the PINsafe, Apache Tomcat logs and logs on the MS SQL server.
Driver loaded successfully.
PINsafe 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 PINsafe administration console when a database connection cannot be made. Check the PINsafe 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
PINsafe 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.".
PINsafe 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.".
PINsafe 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 PINsafe 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, PINsafe 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.".
PINsafe 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 PINsafe 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 PINsafe 3.6 or later, and change the PINsafe 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 PINsafe installation and configuration please firstly contact your reseller and then email Swivel Secure support at support@swivelsecure.com
