Subscribe to PortalGuard's Quarterly Newsletter for News & Updates on the Latest Release! Click to Subscribe

PortalGuard SQL Backend Configuration and Requirements


Problem

You need to configure the SQL Backend for PortalGuard.

Solution

Use the existing scripts to create the 'pstar' database and service account, then configure PortalGuard to utilize the new database.

Quick Navigation

Requirements

  • Microsoft SQL Server 2008 or later
    • Express, Standard and Enterprise editions are supported. 
    • 'Server Authentication' MUSTbe set to 'SQL Server and Windows Authentication mode'.  If only 'Windows Authentication mode' is enabled, this integration will not work. 
  • The PortalGuard server must have network connectivity to the SQL server via ODBC.
    • The default port is 1433.
  • SQL Server Management Studio
    • SSMS must be installed to allow direct configuration/maintenance of the 'pstar' database.

Creating the 'pstar' Database

  1. Copy PortalGuard's SQL Scripts to the SQL Server Machine. 
    • The script files are located in the PortalGuard Install kit under the 'ADDINS\SQL Scripts' folder.
    • For new instances, you will only need the 3 'create...' scripts.
  2. On the SQL Server, open SQL Server Management Studio (SSMS) and authenticate as the system administrator (sa) account, OR a DBA.
  3. Open the 'create_database.sql' script file copied over in step #1 above. 
  4. Change/update the path in lines #2 and #4 of this script.
    • Line #2 refers to the location of the database file (.mdf) itself. 
    • Line #4 refers to the target location for the log file (.ldf) for this database. 
      • Typically these are stored in the same location. 
  5. Execute this query.
    • NOTE: If you receive an error when running this script 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.
  6. Open the 'create_tables.sql' script file copied over in step #1 above.
  7. Execute this script without modification. 
  8. Open the 'create_storedprocs.sql' script file copied over in step #1 above.
  9. Execute this script without modification. 

Creating the 'pg_update' Service Account

  1. In SQL Server Management Studio (SSMS), navigate to the 'Security' folder in the left-hand panel and expand it. 
  2. Within the 'Security' folder, right-click 'Logins' and select 'New Login...'.
  3. In the 'Login - New' window, create a new user for 'SQL Server Authentication' with a name of 'pg_update'. Be sure to UNcheck the 'Enforce password policy' option, and set the 'Default Database' to 'pstar'. The screenshot below shows the expected result:
  4. Once configured, navigate to the 'User Mapping' page under 'Select a page' on the left-hand column of this window. 
  5. Check the box for 'pstar' in the 'Users mapped to this login:' section on the right-hand side of the window, and check the box for 'db_owner' on the bottom, as seen in the following screenshot:
  6. Click the 'Ok' button to create the user and map it to the database. 
    • You may receive an SQL Error here if you are not authenticated to SQL Server Management Studio (SSMS) with an account that has the privileges to create users. 
  7. In the right-hand panel of the main view in SQL Server Management Studio (SSMS), expand the 'Databases' folder, expand the 'pstar' entry, expand the 'Security' folder, expand the 'Users' Folder, and then select the 'pg_update' user:
  8. Right-click the user and select 'Properties':
  9. Select the 'Owned Schemas' page under 'Select a page' on the left-hand column of the new window and check the box for 'db_owner':
  10. Click the 'Ok' button to commit these changes. 

Configure ODBC on the PortalGuard Server

  1. Navigate to the PortalGuard server and open ODBC Data Source Administrator (64-bit).
  2. Click on the System DSN tab and then click on the 'Add...' button. 
  3. Select the 'SQL Server' driver from the new 'Create New Data Source' window, and then click the 'Finish' button. 
  4. Fill in the 'Name', 'Description', and 'Server' fields for your environment:
    • Name: This will be the identifier to utilize this connection from the PortalGuard Configuration Editor.
    • Description: Purely for your reference. 
    • Server: This should point to your SQL Server.
      • If using SQL Express, this format should be: SQLSERVER\SQLEXPRESS
  5. Click on the 'Next' button. 
  6. Select the 'With SQL Server Authentication...' radio button and provide credentials for the SQL Service Account created above. Clicking 'Next' will attempt to make the connection. 
  7. On the following screen, ensure that the checkbox next to 'Change the default database to:' is checked:
  8. Click 'Next'.
  9. Click the 'Finish' button.
  10. Click on the 'Test Data Source' button:
  11. Upon a successful test, click 'Okay' to close out of the ODBC Data Source Administrator (64-bit) program.

Configuring PortalGuard to Use the SQL Backend

  1. Open the PortalGuard Configuration Editor and click on the 'Edit Bootstrap' button:
  2. Navigate to the User Data tab and select the SQL sub-tab.
  3. Fill in the fields on this tab accordingly:
      • DataSource:
        • The name of the ODBC Data Source connection configured in the previous section (typically 'DS_pstar').
      • Database:
        • The name of the database: e.g. pstar
      • SQL Username:
        • The username for the SQL Service Account created and referenced in the above sections (typically 'pg_update').
      • SQL Password:
        • The password for the SQL Service Account.
  4. IMPORTANT: To make use of the SQL backend for user profile storage, you MUST select 'SQL' for the 'User Data Repository' as shown in the image above. 
  5. Click the 'Save' button. 
  6. Click 'Apply to PortalGuard Server'.
  7. Click 'Sync'.

REV. 03/2019 | PortalGuard

  • 90
  • 08-Mar-2019
  • 317 Views