Encryption not working when we update manually for AD password

Hi All,

         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

Thanks

Jai

  • Hi Jaipal,

    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.

    Thanks.

  • In reply to Markus Weiss-Ehlers:

    Hi Markus

    I just tried to update the password value (isSecret=true) over the object browser.
    It seems, that the objectlayer doesn't do the encryption. It must be somewhere else.

    Is there another way to generate the encrypted string to set it without the SynchronizationEditor?
  • 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:

    References VI.Projector.Database.dll
    
    Dim encryption As New VI.Projector.Security.DatabaseEncryption(Session)
    Dim enPassword As String = encryption.Encrypt(valPassword)
    

  • In reply to Markus Weiss-Ehlers:

    Hi Markus

    That works!
    Thanks for the support.

    I'm considering to write a on saving script on the table to make shure, that the value is encrypted when isSecred is set and the value doesn't start with "[E]".
  • In reply to prueegg:

    You could do this in the format script if you want to.