How to configure RDBMS synchronization in Cisco secure ACS for windows version 3.3 ?

[TCC_2]

• Introduction:

Introduction:

This document talks about a scenario where user has to implemet RDBMS Sync in Cisco ACS.

Whats is RDBMS?

RDBMS stands for Relational Database Management System. It can be defined as a system which empowers the user to create, update and easy administration of a relational database.Structured Query Language (SQL) is most commonly used to access the database.

RDBMS identify and read information in form of "tables".Each table is identified with the help of "unique identifier" or "primary key". Remaining tables are connected or related to each other using "foreign keys". A foreign key can be defined as primary key attached to every table.

For example:

"Student ID" is primary key (PK) for one table but foreign key (FK) for other tables.

Core issue

The Relational Database Management Systems (RDBMS) synchronization feature updates the  CiscoSecure user database with information from an Open Database Connectivity (ODBC)-compliant data source.  The ODBC-compliant data source can be the RDBMS database of a third-party  application. It can also be an intermediate file or database that a third-party  system updates. Regardless of where the file or database resides, Cisco Secure  ACS for Windows (ACS) reads the file or database through the ODBC connection. RDBMS synchronization can also be regarded as an Application Program Interface (API) of much of what can be configured for a user,  group, or device through the ACS HTML interface. Alternatively, it is possible to maintain the group through this feature. RDBMS synchronization supports addition, modification, and deletion for all data items it can access.

Synchronization can be configured to occur at regular intervals. Synchronizations can be performed manually, in which case the Cisco Secure user database is updated on demand.

Synchronization performed by a single ACS can  update the internal databases of other ACSes, so that the RDBMS Synchronization configuration only needs to take place on one ACS. ACSes listen on TCP port 2000 for synchronization data. RDBMS Synchronization  communication between ACSes is encrypted with a 128-bit encrypted, proprietary algorithm.

Resolution

To perform RDBMS synchronization, follow this procedure:

1. Create an accountactions.csv file and save it to C:\Program Files\CiscoSecure      ACSvx.x\CSDBSync\Databases\CSV (the      assumption is a default installation of ACS).

Note: The order of the fields is very important for the RDBMS synchronization. The basic format for the accountactions.csv file is SequenceId, Priority, UserName, GroupName, Action, ValueName, Value1, Value2, Value3, DateTime, Message No., ComputerNames, AppId, Status.

The sequence ID is any unique number because ACS usually starts to apply the codes with highest priority and then lowest ID. This is why the sequence ID must be unique for all the entries, as shown in this example:

1,0,user1,Group 1,100,,password1,,,10/07/2005 0:00,0,,,0

2,0,user2,Group 1,100,,password2,,,10/07/2005 0:00,0,,,0

3,0,user3,Group 1,100,,password3,,,10/07/2005 0:00,0,,,0

4,0,user4,Group 1,100,,password4,,,10/07/2005 0:00,0,,,0

This accountactions file, adds usernames user1,user2,user3,user4 to Group 1, with passwords password1, password2, password3 and password4 respectively.

Note: This file can be created in Microsoft (MS) Excel, as long as these columns are incorporated in the same order. This is an example of how this file is created in MS Excel:

SequenceID

Priority

UserName

GroupName

Action

ValueName

Value1

Value2

Value3

DateTime

MessageNo

ComputerNames

AppId

Status

Note: The accountactions.csv file must start with a blank line (or a line without actual import definitions), as the first line is skipped by the MS ODBC driver. Also, regardless of the action, fields 1, 2, 5, 10, 11, and 14 (SequenceID, Priority, Action, DateTime, MessageNo, Status) are mandatory. For a detailed description of the fields and action definitions, refer to the RDBMS Synchronization Import Definitions section of User Guide for Cisco Secure ACS for Windows Server Version 3.3.

1. Change the Registry      Settings on the ACS:
   1. Access the HKEY_LOCAL_MACHINE\SOFTWARE\Cisco\CiscoAAAvx.x\CSDBSync key.
   2. Change the OdbcUpdateTable       value from AccountActions to accountactions.csv.
   3. Save the changes to the       registry.

Note: Failure to perform this procedure results in the [Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only error message.

Synchronization cannot be performed with a relational database table after the OdbcUpdateTable value has been changed to accountactions.csv. To do so, change the OdbcUpdateTable value back to AccountActions.

1. Configure a System Data Source Name for RDBMS      synchronization with these steps:

1. Go to Administrative Tools >       Data Sources (ODBC).
2. Choose the System DSN tab and click Add.
3. Choose Microsoft Text       Driver(*.txt,*.csv) and       click Finish.
4. Enter a name in the Data       Source Name field and a description in the Description field.
5. Uncheck Use Current Directory and click Select Directory.
6. Browse to the C:\Program Files\CiscoSecure       ACS vx.x\CSDBSync\Databases\CSV directory       and click OK.
7. Click on the Options button and then the Define Format button.
8. Click on accountactions.csv and then OK.
9. Click OK if the Failed to save table attributes of (null) into       (null) message is received.
10. Click OK, and then OK again.

1. Configure ACS to use an ODBC Data Source with      these steps:
   1. Go to Interface Configuration >       Advanced Options.
   2. Check the RDBMS Synchronization checkbox and click Submit.
   3. Go to System Configuration >       RDBMS Synchronization.
   4. Choose the newly created ODBC       Data Source from the dropdown list.
   5. Keep the username and password       fields empty.
   6. Synchronization scheduling       must be set to Manually.
   7. Make sure the server is listed       in the Synchronize column of the Synchronization Partners section and       click Submit.
   8. Go back into RDBMS Synchronization and click Synchronize Now. Ideally,       the updates now take place.

Note: Unlike other methods of RDBMS synchronization, the lines are not deleted from the .csv file as they are added to the CSDB. This is because the MS ODBC text driver has read-only access. If ACS version 3.0 is run, it may be necessary to change the [accountactions] line in the C:\Program Files\CiscoSecure ACS vx.x\CSDBSync\Databases\CSV\schema.ini file to [accountactions.csv].

Note: RDBMS synchronization attempts made using the text driver with ACS installed on the Win2k Advanced Server do not work. These attempts most likely result in the the ;[Microsoft][ODBC Text Driver] Text file specification field separator matches decimal separator or text delimiters error message.

For additional details, refer to the RDBMS Synchronization section of User Guide for Cisco Secure ACS for Windows Server Version 3.3.