User create script

Right off the bat when I try to insert a new userid (using sql) into t_metuser, I get an interesting error:

Error: Cannot insert protected data. Either the encryption or indexing keys are not available or the indexing key is not valid for MAC generation.

Doing a trace to see what the application does when creating a new user, I do not see any relevant code executed prior to this point, so my concern is that the application is hard-coded to do something that allows inserting a new row into t_metuser.  If so, there may not be a way around this.  Another possibility is permissions, but I am doing my insert statement as sa...

Thoughts?

Hey Jason,

T_METUSER (I believe) uses a combination of the Tessitura security certs and the database asymmetric keys for encryption, so you probably have to open the key before you can insert the data correctly.

The SQL for that is basically:
OPEN SYMMETRIC KEY Key_name DECRYPTION BY <decryption_mechanism>

This link has detailed information on the above statement:
http://technet.microsoft.com/en-us/library/ms190499.aspx

Kevin.

That pointed me in the right direction and I was able to open the symmetric keys and create the tessitura users.

My issue now is that I cannot see via profiler what the application executes to create the system login and sql user accounts.  What I get is:

-- 'CREATE LOGIN' was found in the text of this event.
-- The text has been replaced with this comment for security reasons.

From what I have read there is no way around this as it is done at the sql server level.  I don't suppose anyone from Tessitura can comment on what code is being called at this point? :)   My other option would be to write my own create user and create login statements....I just want to make sure I do not miss something.

EDIT:  So there is a little more information in my trace after that msg, however, I don't believe everything is shown...still looking at this.

I would recommend looking for stored procedures used for customer account creation, rather than trying to insert directly into system tables, not that I know what they are off the top of my head.

The steps to create Tessitura users programmatically would be:

• Open symmetric key
• create login for sql
• Insert into t_metuser
• sp_grantdbaccess
• @userid ,@userid
• sp_addrolemember
• 'ImpUsers' ,@userid
• AP_UPDATE_PASSWORDS - for tessitura password
• AP_UPDATE_PASSWORDS - for sql password