Most PortalGuard features can operate without a SQL back-end. However, the use of a SQL server is recommended for installations of greater than 2000 users for performance reasons. The main use of SQL is storing PortalGuard’s user profile data (e.g. challenge answer hashes, last login timestamps, enrolled phone numbers, et al).
There are certain features that require the use of a SQL server such as self-registration, Credibility-Based Authentication (CBA) and CAS SSO. Additionally, SQL is required for PortalGuard’s more advanced reporting and if you intend to load-balance multiple PortalGuard server instances.
- Microsoft SQL Server 2005 or later (Express and Enterprise editions are supported)
- SQL Server authentication type must be enabled (not Windows)
- The PortalGuard server must have network connectivity to the SQL server via ODBC (e.g. port 1433)
- User profile storage is at most 10KB/user depending on what features are enabled (e.g. password recovery accounts for about 4K alone)
- Those storage numbers do not grow over time
- For 10K users, the user profile storage would only require approximately 100MB
- Report data collection is an optional feature that stores its data in a separate SQL table. When this is enabled, details about each intermediate user action through PortalGuard are written to SQL. It is heavily dependent on user activity, but as a general rule of thumb it can be around 2 GB/1000 users/month.
- For 10K users, it could be up to 20GB for the first month and up to 240 GB for the first year
- The SQL data can be archived using standard practices when the data is no longer needed for reports. PortalGuard does not have any special functionality around this archiving so it must be done outside of it.
PortalGuard v4.2 added support for SQL Connection Pooling. This dramatically increases SQL performance on the PortalGuard server by re-using existing connections rather than continually creating and destroying new connections to the SQL server. The setting affects all SQL activity within the PortalGuard server. It is disabled by default for backwards compatibility, but is strongly recommended if you store PortalGuard user profiles in SQL or have “Report Data Collection” enabled (which updates SQL for each user connection to PortalGuard).
The following steps will be used to install and configure the PortalGuard SQL Backend.
Run SQL scripts
Copy PortalGuard’s SQL scripts to the SQL Server machine. The script files are found in the PortalGuard kit under the folder:
On the SQL Server, open the SQL Server Management Studio and login as the system administrator or DBA
Open the create_database.sql file from the install package
Change the drive or path in lines 2 and 4 if necessary to reflect the SQL Server install path.
Execute the query by pressing F5
NOTE: If you receive an error when running this file on SQL Server 2014 or later, change the “@new_cmptlevel” parameter value from 90 to 120. Newer versions of SQL Server do not recognize a value of 90 which is specific to SQL Server 2005.
Open the create_tables.sql file and execute it by pressing F5
Open the create_storedprocs.sql file and execute it by pressing F5
Create PortalGuard User in SQL
Create a new user on the SQL Server named “pg_update”. This account will be used for writing event and user profile data to the database.
In the Object Explorer in Microsoft SQL Server Management Studio, expand the Security folder then right-click the Logins folder and choose the New Login… item
On the Login - New dialog:
a. Enter “pg_update” as the Login name
b. Choose the SQL Server authentication radio button
c. Enter a password for the user
d. Uncheck the Enforce password policy checkbox
e. Set the Default database drop-down to “pstar”
f. Click the “OK” button to create the login
10) In the Object Explorer, expand the following folders: Databases -> pstar -> Security, then right-click the Users folder and choose New User…
11) Set the User name and Login name fields to “pg_update”, set the Default schema to “dbo”, check the db_owner item at the top of the Role Members list and click the “OK” button to create the user for this database.
Create ODBC Datasource
12) On the PortalGuard server, open the “Data Sources (ODBC)” item under Start -> Control Panel -> Administrative Tools
13) Click the System DSN tab, then the “Add…” button
14) On the Create New Data Source dialog, scroll down to the “SQL Server” driver and click the Finish button
15) Set the fields on the Create a New Data Source to SQL Server dialog as below (using your own SQL Server name in place of “Balvenie”):
16) Set the fields on the next dialog as below, but specify the “pg_update” user and its password in the lower fields.
NOTE: The With SQL Server authentication… radio button must be chosen to work with this configuration.
17) On the following dialog, check the box for Change the default database to: and choose “pstar” from the drop-down:
18) Click the Finish button on the last dialog and then the “Test Data Source…” button to verify the settings. The resulting dialog should show the message “TESTS COMPLETED SUCCESSFULLY!”
Enabling SQL Connection Pooling
SQL Connection Pooling is controlled in both PortalGuard and Windows. The following steps will ensure connection pooling is in effect for PortalGuard. All steps are performed on the PortalGuard server.
19) In the ODBC Data Source configuration from Control Panel -> Administrative Tools, click the “Connection Pooling” tab:
20) Double-click the SQL driver being used by the PortalGuard data source to display the “Set Connection Pooling Attributes dialog.
21) Ensure the “Pool Connections to this driver” radio button is selected. You can also edit the idle connection timeout in the bottom field (the default is 60 seconds). Click OK to save any changes and close the ODBC Data Source dialog.
Update Bootstrap Configuration
22) On the PortalGuard server, launch the PG_Config editor from the desktop shortcut and click the Edit Bootstrap button
23) Go to the User Data -> SQL sub-tab and in the “User Data Repository” drop-down and enter the following fields:
a. Datasource: DS_pstar
b. Database: pstar
c. SQL Username: pg_update
d. SQL Password: <password you created for the ‘pg_update’ SQL account>
e. Check the Use SQL Connection Pooling checkbox
24) Save and apply the changes
25) Run “iisreset” from an administrative DOS prompt for all the changes to take effect.