Encryption not working when we update manually for AD password

Question

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

Markus Weiss-Ehlers · Accepted Answer

Hi Jaipal, 
 you can't using SQL. 
 By the way, you should use the object layer at any time (if possible).

Markus Weiss-Ehlers · Answer

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)

Markus Weiss-Ehlers · Answer

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