IdM 12.6 900 bytes index limit problem for challenge / responses

[docldap]

Hi all,

 

I have my users store on a MS-SQL Server, which has a limit of 900 bytes for the index. When I try to load my challenge/responses (I have 8 mandatory), insert can not be saved on database.

 

Log entry is below. I checked value from {RC2} to last value and is 882 chars, then plus the user id, the sum exceeds the 900 bytes index limit. I understand that this encrypted string has all pairs of challenge/responses concatenated (because one question and answer should be far smaller than this 882 chars).

 

How can I control how the questions and answers are saved to database? Can I explicitly configure that every pair goes to a single row? (for example userid1:qa1 in a row, userid2:qa2 in other row, etc..). Or perhaps somebody can tell me how to by pass the 900 bytes index limit for SQL-Server? Many thanks!

 

Failed to add tblUserPasswordhints.passwordhint with value: {RC2}:BNN9rJO6R7SGI9842TfKbnCRihJEG/+rSP6pe340qjS+iYOIXThs3VWomxmF4B22mDtfeUFtmtygxwa5TTBYeJaDiihW/SgE9///Wwrcgz7ZNP5/1q7u8q9a4eEk0TRRl1qNnMHX12Am5kfgrjx9davBPMc9C4w8G+Z8rAtxEDY12fUHVer5ea+wMN23+DGBIFNMTETlrYsCfskXHgtteyZeGk4Ji64cWS2xWtOBVXWCsM61pNyQVSqCjHjI+hU1u+/CNSilAQdsDOPQdwxjLD8U9pHkiFHblYrDbxO/UgFaKxIfCPQxnWKhd7H+DsOkgg7xDgGNjC/SwLHBYP6IUkOHzpzGxxQnxmFXNfAwUXgw8MP56+7eIlcD78/sD6LtPGu70T5q7xive40EWOpPJZv2lxuGGRcFkFEHeWrhDTeMl8a+6tm6dD9CuXEMMuzYeQQncDy6xWGyUGfDUs8dz/bpJJ7F5//Jbz0EfYCruVslZYt8oEeNePe5Gd/xkETzMi0ROuA0lxlY1BMIdaYJ4OA+NWR5Fj9WHBi4V1d0Tp+sH/5LqiOS73TIPbj6cXfKc/L54ooPBhh/otqlhgR1z4PyrBT8Vn/1ue+JSFBx1f802LsZ3xe8KwORY27T48KCz0XwEic5ThQx1e+zpN5E7zvweqVgVO9OcmyLabN1z/Bhai7Dfr7oUCopdp+paYyATQE5moTMftSzMgnz11q7vba1qowSKv2zU2MRk0WQyp5+W7FU8LUafNom2EvIesDhrNvJR/k8Q5Bgy67wjNfuP3JGPvGcMEesx9XAUCE+P4xesn8ifEnzQveOvdOhfO9Q06T4GuJc9jJsHqNlWtyD9OsF8U0I7cFaruWPmVDkXp0= to ObjectType::USER managed object superadmin

[losru01]

Hi, I am not an expert on SQL Server but I don't think you can remove the 900 byte index limit. What you can possibly change however is the way index is created. i.e. which columns the index will contain or which columns it include. Similar to the issue that is described in the IM documentation here for the snapshot db: 

 

General Issues - CA Identity Manager - 14.1 - CA Technologies Documentation 

 

I recommend you work with CA Support.

 

KR
Russi

[docldap]

Hi losru01 many thanks for your response. I revised this doc, but unfortunately it does not apply here.

 

tlbUserPasswordhints has 2 columns, one for user id and the other for the password hint (question/answer), so at least ootb, the 2 columns are the primary key of the table.

 

So, at the moment of creating this table, a unique index is created for both columns. Sadly, the 900 bytes limit applies to both columns. So, question+answer+userid has to be lower than this 900 bytes.

 

That's why I was wondering a way to bypass this limit.

[docldap]

Sorry I missed the last part of this. As I said, index is on both columns because it is a primary key. I can not alter this index, without modifying the primary key.

 

I thought that there could be a way (that is one thing I was asking about), if I recreate this table with only userid as primary key. Problem is that User Console stores each question/answer pair per row, and each row with the user id. So, I should have (assuming ? as separator):

 

userid ... passwordhint

1        question1?answer1

1        question2?answer2

2        question1?anotheranswer1

...........................

 

and that's why I can not make userid only to be primary key.

 

So, I am figuring if there is some configuration I can do, so all challenges are saved in a unique row (and then I do be able to have userid as primarykey, and index will be always shorter than 900 bytes):

 

userid.........passwordhint

1...........question1?answer1#question2?answer2...

2...........question1?anotheranswer1#question2?anotheranswer2...

[Philippe.Guihard]

Hi docldap,

You cannot bypass this index size limit;
(https://technet.microsoft.com/en-us/library/ms163207(v=sql.105).aspx)

 

Based on the uniqueness of the userid column, I think the passwordhint column can be removed from the index definition.
So just define a primary key against this userid column.

e.g.:
CREATE TABLE [dbo].[tblUserPasswordhints](
[userid] [int] NOT NULL PRIMARY KEY,
[passwordhint] [nvarchar](2000) NOT NULL
)
GO
ALTER TABLE [dbo].[tblUserPasswordhints] WITH CHECK ADD CONSTRAINT [FK_tblUserPasswordhints_tblUsers] FOREIGN KEY([userid])
REFERENCES [dbo].[tblUsers] ([id])
GO
ALTER TABLE [dbo].[tblUserPasswordhints] CHECK CONSTRAINT [FK_tblUserPasswordhints_tblUsers]
GO

 

So backup the data (e.g. as INSERT statements) drop the foreign key constraint, drop the tblUserPasswordhints table.and redefine the table with such previous statements then re-insert your data.

Regards,

Philippe.

[docldap]

Thanks  guiph01, I am trying this tomorrow on the client environments.

 

I think this should work. Also, I found the ForgottenPasswordHandler class Javadoc, and saw the posible values for schema option in the Management Console configuration (i did not find this documented yet).

 

As Javadoc stays, these are the possible values (last column):

 

public static final java.lang.String	SCHEMA_MV_ATTRIBUTE	"multivalued_attribute"
public static final java.lang.String	SCHEMA_SEPARATE_ATTRIBUTES	"separate_attributes"
public static final java.lang.String	SCHEMA_SIN_ATTRIBUTES	"singlevalued_attributes"

 

And description for these values are as follows

 

SCHEMA_MV_ATTRIBUTE

public static final java.lang.String SCHEMA_MV_ATTRIBUTE

Constant used to get questions and answers stored in a single, multi-valued attribute from the Logical Attribute handler.

 

See Also:

Constant Field Values

---

SCHEMA_SIN_ATTRIBUTES

public static final java.lang.String SCHEMA_SIN_ATTRIBUTES

Constant used to get a question and answer pair stored in a single attribute from the Logical Attribute handler.

 

See Also:

Constant Field Values

---

SCHEMA_SEPARATE_ATTRIBUTES

public static final java.lang.String SCHEMA_SEPARATE_ATTRIBUTES

Constant used to get each question and each answer when stored separately from the Logical Attribute handler.

 

See Also:

Constant Field Values

 

I think the 2 firsts can work.. ootb is set to multivalued_attributes. I will try altering the index like you say and playing a little with these 2 options.

 

I will confirm you tomorrow how did it go.. many thanks to all.

[docldap]

Hi guiph01, not luck yet. With singlevalued_attributes and separate_attributes I have an error that says "|question1|, |answer1|, |question2|....... nullPointerExeption" (this on screen, nothing is shown on server.log).

 

So I am trying with multivalue_attribute option.

 

With unencrypted option, result is like pic below. This is the reason I can not let only userid to be the primary key, because there are several rows with userid 9 as value (in this example).

 

Problem is when I encrypt questions and answers. It appears that the encrypted value is getting to long for the 900 bytes of the (userid, passwordhint) index..

 

 

[docldap]

I am seeing something that can help.

 

I have 2 working environments, with encryption option enabled for forgotten password handler. In these environments, all password hints columns begins with

 

{PBES} and after that, a very short string (also it appears that the string length is proportional to the question/answer size.

 

On the other side, this environment that does not work with encryption, where log shows a value beginning with {RC2} followed by this very long string.

 

This non working environment was inherited to me by other providers and does not have implementation documentation. Do you know where to change the encryption algorithm? I will ask this in a new thread to separate themes.

[docldap]

Hi all!

 

I found a way to achieve this. First of all, I had to delete the index restriction because it does not allow to store index values more than 1k. If we have only one row for each user, only user id must be primary key so rebuilding table with this column as pk only gets it works. Second, I modified User directory entry on Management Console. In Password Hint attribute, I added this modifier: AttributeLevelEncrypt So, all questions and answers are encrypted before being stored on database. Now DB stores only one entry for each user. 

 

I also added an Encryption Key to the Forgotten Password Handler, and saw that longer the key, longer the value stored for all questions and answers. So, more secure I think.

 

Thanks everybody for your contributions.