Layer7 Access Management

Tech Tip - CA Single Sign-On: Setting up IBM DB2 v9.5 as Policy Store

By wonsa03 posted 07-11-2016 11:52 PM

  

CA Single Sign-On Tech Tip by Sau Lai Wong, Principal Support Engineer for 12th July 2016

 

1. Create a new database on IBM DB2 server.

 

2. Setup Siteminder schema with sm_db2_ps.sql from <siteminder>\db\tier2\DB2 directory. Copy the sm_db2_ps.sql content into a query (from DB2 Control Center against the new database) and execute the query.

 

3. Getting some errors against some database table creation:

==============================================

CREATE TABLE smactiveexpr5 ( activeexproid VARCHAR(64) NOT NULL, domainoid VARCHAR(64) NOT NULL, usesvariables INTEGER NOT NULL DEFAULT 0, expr VARCHAR(4000), PRIMARY KEY (activeexproid) )

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0286N A default table space could not be found with a page size of at least "8192" that authorization ID "DB2ADMIN" is authorized to use. SQLSTATE=42727

 

CREATE TABLE smvariable5 ( variableoid VARCHAR(64) NOT NULL, domainoid VARCHAR(64) NOT NULL, variablename VARCHAR(255) NOT NULL, definition VARCHAR(4000) NOT NULL, prefetchflag INTEGER NOT NULL DEFAULT 0, returntype INTEGER NOT NULL DEFAULT 0, metadata VARCHAR(4000), variabletype VARCHAR(64), variabledesc VARCHAR(1024), PRIMARY KEY (variableoid) )

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0286N A default table space could not be found with a page size of at least "16384" that authorization ID "DB2ADMIN" is authorized to use. SQLSTATE=42727

 

CREATE TABLE smodbcquery4 ( odbcqueryoid VARCHAR(64) NOT NULL, odbcqueryname VARCHAR(255) NOT NULL, odbcquerydesc VARCHAR(255), queryenumerate VARCHAR(2000), querygetobjinfo VARCHAR(2000), querylookup VARCHAR(2000), queryinituser VARCHAR(2000), queryauthenticateuser VARCHAR(2000), querygetuserprop VARCHAR(2000), querysetuserprop VARCHAR(2000), querygetuserprops VARCHAR(2000), querylookupuser VARCHAR(2000), querygetgroups VARCHAR(2000), queryisgroupmember VARCHAR(2000), querygetgroupprop VARCHAR(2000), querysetgroupprop VARCHAR(2000), querygetgroupprops VARCHAR(2000), querylookupgroup VARCHAR(2000), querysetpassword VARCHAR(2000), PRIMARY KEY (odbcqueryoid), UNIQUE (odbcqueryname) )

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0286N A default table space could not be found with a page size of at least "32768" that authorization ID "DB2ADMIN" is authorized to use.  SQLSTATE=42727

==============================================

 

4. Create buffer pools with various sizes and table spaces that associated with each buffer pool:

db2 create bufferpool bp8k pagesize 8K

db2 create tablespace db8k pagesize 8K bufferpool bp8K

 

db2 create bufferpool bp16k pagesize 16K

db2 create tablespace db16k pagesize 16K bufferpool bp16K

 

db2 create bufferpool bp32k pagesize 32K

db2 create tablespace db32k pagesize 32K bufferpool bp32

 

5. Run the sm_db2_ps.sql script again and it’s executed successfully this time.

 

6. Copy XPS schema file DB2.sql from <siteminder>\xps\db directory to the IBM DB2 server.

 

7. Open Command Window from DB2 and execute the following command:

td@ -v -f C:\Users\Administrator\Desktop\db2.sql

 

 

8. Once the above script executed successfully, configure the IBM DB2 Data Source (via system_odbc.ini (UNIX) or ODBC Data Source) and configure Policy Server to reference this IBM DB2 as policy store (via SM Management Console).

 

9. Reset Siteminder superuser password with following command:

smreg –su <password>

 

10. Import the Default Policy Store Data Definitions, run the following command from Policy Server (<sitmeinder>\xps\dd):

XPSDDInstall SmMaster.xdd

 

 

11. Getting the following error from XPSDDInstall command:

==============================================

[XPSDDInstall - XPS Version 12.52.0101.640]

Log output: /opt/CA/siteminder/log/XPSDDInstall.2016-07-11_152449.log

Initializing database, please wait...

(ERROR) : [sm-xpsxps-00870] An error occurred when calling "SQLExecDirect" for "Initial Policy Data Read" query

(ERROR) : [sm-xpsxps-00810] Native Diagnostic: HY000:-1585 [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]A system temporary table space with sufficient page size does not exist.

(ERROR) : [sm-xpsxps-00810] Native Diagnostic: 56098:-727 [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]An error occurred during implicit system action type '2'. Information returned for the error includes SQLCODE '-1585', SQLSTATE '54048' and message tokens ''.

(ERROR) : [sm-xadobj-00020] Failed to initialize global objects.

(FATAL) : [sm-xpsxps-03570] SiteMinder interface initialization failed.

(FATAL) : [sm-xpsxps-04120] Unable to initialize the XPS library.

==============================================

 

12. Create system temporary table spaces associated with the various size of buffer pool:

db2 CREATE SYSTEM TEMPORARY TABLESPACE ts8k PAGESIZE 8192 MANAGED BY SYSTEM USING ('C:\DB2 9.5\ts8k', 'C:\ts8k') bufferpool bp8k

 

db2 CREATE SYSTEM TEMPORARY TABLESPACE ts16k PAGESIZE 16384 MANAGED BY SYSTEM USING ('C:\DB2 9.5\ts16k', 'C:\ts16k') bufferpool bp16k

 

db2 CREATE SYSTEM TEMPORARY TABLESPACE ts32k PAGESIZE 32768 MANAGED BY SYSTEM USING ('C:\DB2 9.5\ts32k', 'C:\ts32k') bufferpool bp32k

 

13. Run XPSDDInstall again and it’s executed successfully this time.

 

14. Import the Default Policy Store Objects, run the following command from Policy Server (<siteminder>\db):

XPSImport smpolicy.xml –npass

 

15. Once the import is executed successfully, start Policy Server and check on the smps.log.

 

 

NOTE: Ensure that the admin account defined in the SM Management Console has the appropriate privileges for the driver to create and bind packages with this specified admin. These privileges are BINDADD for binding packages, CREATEIN on the collection specified by the Package Collection option, and GRANT EXECUTE on the PUBLIC group for executing the packages. These are typically the permissions of a Database Administrator (DBA).

 

Test  with bind27 executable residing under <siteminder>\odbc\bin -- bind27 <DSN> . It will return with error if user does not have the privilege/ authority to create package.

 

Example:
[smuser@wonsa03-I151000 bin]$ ./bind27 'SiteMinder Data Source'
Datasource not found.[smuser@wonsa03-I151000 bin]$ ./bind27 'SiteMinder Data Source'
User Name: ssoadmin
Password:
SecurityMechanism: ''
Creating packages ...Packages created and bound.

 

Also, by default, Policy Server through ODBC driver is sending clear-text user credentials (AuthenticatioMethod=0) to DB2 for authentication. If other authentication method is configured at DB2, please update AuthenticationMethod accordingly.

0 comments
1 view