I have configured Encrypt functionality for my D1IM 7.0 RP2 installation. Then configured AD synchronization, now i can see encrypted AD password in the table called DPRSystemVariables as CP_d1impassword. When update password value through Synchronization editor, system encrypting password value and stored in the database.
Where as if i run SQL Update command from Object Browser or SQL Management studio, column value stored as Plain Text.
I have used following command to update from SQL:
UPDATE DPRSYSTEMVARIABLE SET VALUE='TESTING1234' WHERE NAME='CP_D1IMPASSWORD'
How can is encrypt database value while running query from SQL? Currently the column property set as SECRET.
D1IM version: D1IM 7.0 RP2
Database: MS SQL Server 2012
you can't using SQL.
By the way, you should use the object layer at any time (if possible).
I believe what he is asking or stating is that the value of CP_Password is shown in clear text in the database. This should have been marked as encrypted if it's marked as "IsSecret". Perhaps a design flaw?
As a workaround, we can change the encryption type of that column.
To encrypt, we just need to enable Encryption flag on the DPRSystemVariable.Value column from Designer. Commit and compile.
When you run the Crypto Configuration, you will see that new column to encrypt.
Now verify the values:
Markus is correct. If you update the field value through a SQL query, you are not using the object layer, and therefore there is no way to encrypt the value. The solution is to update the value using the object layer (as synchronization manager does).
So I miss read Jaipal's email. Markus and George is correct. We need to use the SDK to update the passwords. I thought Jaipal said that the value was still shown as plain text.
Thanks Markus, George and Viloun.
I am aware of calling Object Layer to perform any action on D1IM objects. Just want to check is there any other process exist.
Thanks for your quick responses.
In reply to Markus Weiss-Ehlers:
In reply to VilounV:
DO NOT set the flag Encrypted for the column DPRSystemVariable.Value
If you would do so, each and every variable would be encrypted which is very annoying when you are working with the synchronization projects and might break some processes.
It is a known issue in 7.0.2 that variables, that have been created before the database encryption has been configured, aren't automatically encrypted during the encryption configuration.
Support for the automatic encryption of existing synchronization variables during the encryption of the database with the Crypto Config tool has been added starting with 7.0.3 and 7.1.1
In reply to prueegg:
As the DPRSystemVariables are partially encrypted (not every item is encrypted) you need either the Synchronization Editor to encrypt them or you use code like the following:
Dim encryption As New VI.Projector.Security.DatabaseEncryption(Session)
Dim enPassword As String = encryption.Encrypt(valPassword)