Adding thousands of respondents to the database

[rgenter]

I am integrating Checkbox into our recipient communication platform. As part of this integration I need to be able to add thousands of users at a time to the Checkbox database and be able to extract their responses.

I've pretty much figured out how to do everything such as populating the ckbx_Credential and ckbx_CustomUserFieldMap tables and pulling data from the ckbx_ResponseAnswers and ckbx_Response tables (including using ckbx_ItemOptions and ckbx_Text). What I'm missing is how to limit the users to their specific survey.

It appears that I need to have a separate record in ckbx_AccessControlEntry for each respondent, plus a corresponding entry in ckbx_AccessControlEntries to map the respondent to the appropriate access control list. What I haven't been able to find is how to identify which access control list to use. How do I know which list enables the respondent for which survey?

Rick Genter
Silverlink Communications

[pwiesner]

It sounds like you have a pretty good handle on things. One possible variation would be to add your users to user groups; in this scenario you would only need to manage the group's ACL.

The ckbx_Template.AclID column maps a template to an ACL. I'm not sure what information you are using to identify your ResponseTemplate but if it is something other than the ID, ckbx_Template and ckbx_ResponseTemplate are related on the TemplateID and ResponseTemplateID columns.

[rgenter]

Thanks for your reply.

I have this mostly working, except that I can't get encrypted passwords to work. My understanding is that when a password is encrypted its MD5 hash should be stored in the database. I've tried storing both

HASHBYTES('MD5', password)

(which is a VARBINARY cast to VARCHAR) and an ASCII representation of the resulting binary string. In other words, I've tried both the equivalent of

CAST(0x123456789A.... AS VARCHAR(32))

and

'123456789A.....'

Neither of these worked. How should I store a password such that I can set Encrypted to 1 and have it work?

[pwiesner]

I do not believe it will be possible to generate an encrypted password in sql. If it is a requirement that you encrypt the passwords it would be our suggestion to use either the direct API or the user management web service to create users.

[rgenter]

I do not believe it will be possible to generate an encrypted password in sql. If it is a requirement that you encrypt the passwords it would be our suggestion to use either the direct API or the user management web service to create users.

Unfortunately neither of those solutions is acceptable, as both would require adding users one at a time, and that's not practical when adding thousands of users.

What is the algorithm used to encrypt the password? I was originally told that it was an MD5 hash, but apparently that's not the case.

[pwiesner]

Certainly a bulk SQL insert will be faster than using either the webservice or the direct API but both of these options are pretty fast. How many user accounts are you trying to create and how frequently will you be creating them?

As a data point, I was able to create 10,000 user accounts in 2 minutes and 17 seconds using the webservice; admittedly my test was pretty basic.

Do you remember who you spoke to about creating encrypted passwords directly in the database?

[rgenter]

Certainly a bulk SQL insert will be faster than using either the webservice or the direct API but both of these options are pretty fast. How many user accounts are you trying to create and how frequently will you be creating them?

A typical batch may be anywhere from a few thousand to a few hundred thousand. We'll probably be creating these on a weekly basis.

As a data point, I was able to create 10,000 user accounts in 2 minutes and 17 seconds using the webservice; admittedly my test was pretty basic.

We would really prefer to be able to do this from SQL, as it can then be integrated into our overall workflow management system.

Do you remember who you spoke to about creating encrypted passwords directly in the database?

It was John Fischer:

"One of my developers familiar with the way that Checkbox does encryption says that Checkbox uses an MD5 Hash with no Salt."

[pwiesner]

I spoke with John and it appears there was a miscomunication. The developer he spoke to was unaware that your ultimate goal was to replicate this logic in SQL. It is true that user passwords are hashed using MD5 and that no salt is used. Additionally the hash is ASCII encoded. In .NET the ASCII encoding process drops the most signifciant bit of characters which are none standard ASCII.