From:	joe-DBA	Date:	Tue Feb 24 13:51:17 2009
Subject:	Re: Replicating an Encrypted Column

<iron_horse@no_spamola.compuserve.com> wrote:
> OK, just a few more items to check ...
>
> ---------
>
> #1 : In the RDB/Target database please cut-n-paste the following and post back here with the results:
>
> [I'm assuming that you do not have any records in val_users with VU_ID va> values are already in use then modify these INSERTs to have new/unique VUe the same exact hex key value in the password
> column position for all 3 INSERTs.]
>
> ===========================
> use warehouse
> go
> set ciphertext off
> go
> insert val_users
> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
> values (600000,'testme_600000','test_600000','me',0x61caca35bac08728266cd50de212434201,getdate())
> go
> insert val_users
> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
> values (600001,'testme_600001','test_600001','me',0x61caca35bac08728266cd50de212434201,getdate())
> go
> insert val_users
> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
> values (600002,'testme_600002','test_600002','me',0x61caca35bac08728266cd50de212434201,getdate())
> go
> set ciphertext on
> go
> select VU_ID,UserSysID,LName,FName,password,Pword_Date
> from val_users where VU_ID between 600000 and 600002
> go
> ===========================
>
> ---------
>
> #2: In the repserver's RSSD run the following ...
>
> ===========================
> rs_helpdb SYB_WMS,warehouse
> go
> ===========================
>
ll call it <funcclass>).
>
> Then in the RSSD run the following and post the results back here:
>
> ===========================
> rs_helpclassfstring <funcclass>,rs_set_ciphertext
> go
> ===========================
>
> ---------
>
> #3: In the repserver suspend and then resume the DSI into SYB_WMS.warehouse.
>
> Then run the following in the PDB (change the VU_ID if 789123 is already in use):
>
> ===========================
> set ciphertext off
> go
> insert val_users
> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
> values (789123,'testme_789123','test_789123','me','figurethis',getdate())
> go
> ===========================
>
> Then run the following in both the PDB and RDB and post back here with the results:
>
> ===========================
> set ciphertext on
> go
> select VU_ID,UserSysID,LName,FName,password,Pword_Date
> from val_users where VU_ID = 789123
> go
> ===========================

See results below:
#1 : In the RDB/Target database please cut-n-paste the following and
post back here with the results:
1> use warehouse
2> go
1> set ciphertext off
2> go
1> insert val_users
2> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
3> values (600000,'testme_600000','test_600000','me',
0x61caca35bac08728266cd50de212434201,getdate())
4> go
(1 row affected)
1> insert val_users
2> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
3> values (600001,'testme_600001','test_600001','me',
0x61caca35bac08728266cd50de212434201,getdate())
4> go
(1 row affected)
1> insert val_users
2> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
3> values (600002,'testme_600002','test_600002','me',
0x61caca35bac08728266cd50de212434201,getdate())
4> go
(1 row affected)
1> set ciphertext on
2> go
1> select VU_ID,UserSysID,LName,FName,password,Pword_Date
2> from val_users where VU_ID between 600000 and 600002
3> go
 VU_ID        UserSysID            LName
FName
password
Pword_Date
 ------------ -------------------- -------------------------
-------------------------
--------------------------------------------------------------------
--------------------------
       600000 testme_600000        test_600000
me
0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301
Feb 24 2009  8:43AM
       600001 testme_600001        test_600001
me
0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301
Feb 24 2009  8:43AM
       600002 testme_600002        test_600002
me
0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301
Feb 24 2009  8:43AM


===========================

---------

#2: In the repserver's RSSD run the following ...

===========================
1> use gsis_ts_rs_rssd
2> go
1> rs_helpdb SYB_WMS,warehouse
2> go
 dsname                         dbname
dbid        controlling_prs
errorclass                     funcclass
         status
 ------------------------------ ------------------------------
----------- ------------------------------
------------------------------ ------------------------------
 
----------------------------------------------------------------------------------------------------
 SYB_WMS                        warehouse
107 GSIS_TS_RS                     rs_sqlserver_error_class
VAL_USERS
         Log Transfer is ON, Distribution is ON
(return status = 0)

From this output make note of the value in the 'funcclass' column
(we'll call it <funcclass>).

Then in the RSSD run the following and post the results back here:

===========================
rs_helpclassfstring <funcclass>,rs_set_ciphertext
go
===========================

1> rs_helpclassfstring VAL_USERS,rs_set_ciphertext
2> go
 Function Name                  FString Name
 ------------------------------ ------------------------------
 rs_set_ciphertext              rs_set_ciphertext
 FString Text

 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------

---------

#3: In the repserver suspend and then resume the DSI into
SYB_WMS.warehouse.

Then run the following in the PDB (change the VU_ID if 789123 is
already in use):

===========================
C:\temp>C:\sybase\OCS-12_5\bin\isql -Ujmorris -S GSIS_TS_RS -w132 -Hjm-
DBA
1> suspend connection to syb_wms.warehouse
2> go
Connection to 'syb_wms.warehouse' is suspended.
1> resume connection to syb_wms.warehouse
2> go
Connection to 'syb_wms.warehouse' is resumed.
1> exit
C:\temp>rhea

C:\temp>C:\sybase\OCS-12_5\bin\isql -Ujmorris -S rhea -w200 -X -Hjm-
DBA
1> use cops_master
2> go
1> set ciphertext off
2> go
1> insert val_users
2> (VU_ID,UserSysID,LName,FName,password,Pword_Date)
3> values
(789123,'testme_789123','test_789123','me','figurethis',getdate())
4> go
(1 row affected)

===========================

Then run the following in both the PDB and RDB and post back here with
the results:

===========================
1> set ciphertext on
2> go
1> select VU_ID,UserSysID,LName,FName,password,Pword_Date
2> from val_users where VU_ID = 789123
3> go
 VU_ID        UserSysID            LName
FName
password
Pword_Date
 ------------ -------------------- -------------------------
-------------------------
--------------------------------------------------------------------
--------------------------
       789123 testme_789123        test_789123
me
0x61caca35bac08728266cd50de212434201
Feb 24 2009  8:47AM

(1 row affected)

C:\temp>C:\sybase\OCS-12_5\bin\isql -Ujmorris -S syb_wms -w200 -X -Hjm-
DBA
-P12bjm34
1> use warehouse
2> go
1> set ciphertext on
2> go
1> select VU_ID,UserSysID,LName,FName,password,Pword_Date
2> from val_users where VU_ID = 789123
3> go
 VU_ID        UserSysID            LName
FName
password
Pword_Date
 ------------ -------------------- -------------------------
-------------------------
--------------------------------------------------------------------
--------------------------
       789123 testme_789123        test_789123
me
0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301
Feb 24 2009  8:52AM

(1 row affected)