How to connect Policy Express to SQL Server data source and work with tables, stored procedures and functions.

Products

• CA Directory
• CA Identity Manager
• CA Identity Governance
• CA Identity Portal
• CA Risk Analytics
• CA Secure Cloud SaaS - Arcot A-OK (WebFort)
• CLOUDMINDER ADVANCED AUTHENTICATION
• CA Secure Cloud SaaS - Advanced Authentication
• CA Secure Cloud SaaS - Identity Management
• CA Secure Cloud SaaS - Single Sign On
• CA Security Command Center
• CA Data Protection (DataMinder)
• CA User Activity Reporting

Issue/Introduction

This document will provide you with a technical overview and discuss the key points in connecting Policy Express to MS SQL Server database to retrieve data or execute SQL procedures.

Environment

Release:
Component: IDMGR

Resolution

Policy Express is a CA utility that's part of Identity Manager. This utility allows you to develop business rules and apply them to the accurate business or technical circumstances in order to achieve many provisioning goals. The Policy Express consists of a number of elements each with a different purpose. This will concentrate on the Data element of the Policy Express. The Data element is allowing you to connect Policy Express to numerous data sources in order to retrieve information that is necessary for the policy that's being developed. Among these data sources you are able to connect to a MS SQL server to retrieve information in a form of a query, a function or a stored procedure.

Below you see a screen shot of the Data Tab on the policy that's being developed. To connect to a database select:

1. Category: Data Sources, Type: SQL Query Data, Function: Get.

In the lower section you must enter a Data Source and a Query.

1. Data Source - needs to the JNDI name of your database as defined in the application server running IDM. In the example below you can see the data source is: userstore.
   
   For JBoss ( for example ) you need to create the underlying resource file under jboss_home\server\default\deploy also. So, my datasource descriptor file deployed has:

   <datasources>   <no-tx-datasource>        <jndi-name>userstore</jndi-name>...

   Here you will specify you connection string to the database for this jndi name resource.

   ...    </no-tx-datasource></datasources>

   Note: The Data Source field must contain a jndi name for a resource that contains a descriptor in your application server. You cannot use it to connect to databases not defined in your application server. If you require to connect to a new database you'll need to create such a descriptor file as mentioned above and then use its jndi name.
   
   
2. Query - The Query field allows you to specify a SQL simple query/function/procedure that will get the information you need. You must know that the Query field must return a String data type as required by the Policy Express. So you need to make sure that either your query and function or stored procedure will return a String data type so that no exceptions arise during run-time.
   
   To run a simple query: Type your select query in the field. The screen shot below uses a simple query example.
   
   To run a function: a function must be created in your database that returns a string. To call the function use: "select FunctionName()" in the query field.
   
   To run a stored procedure: create a stored procedure in your database. To run the procedure type the following into the Query field:
   
   exec stored_procedure_name followed by a comma separated list of parameters being passed to the stored procedure.