OK, we're finally getting there ...
Remember, repserver knows nothing about encryption keys or encrypted data; the repserver is merely passing along *data*,
which in the case of encrypted data is actually just varbinary() strings.
The varbinary() strings (ie, encrypted data) get inserted directly (without the need for decrypting/encrypting anything)
into the RDB when the DSI preceds the inserts with 'set ciphertext on'. 'set ciphertext on' tells the RDS that the
varbinary() data is not be encrypted, but rather inserted directly as already-encrypted data.
If the DSI does not first issue the 'set ciphertext on' command, then the RDS will think the varbinary() strings are in
fact just varbinary() data (ie, non-encrypted data), so the RDS will then encrypt the varbinary() strings. Net result
is that a SELECT will return 1 of 2 different varbinary() strings depending on whether or not 'set ciphertext' is on or off.
With this in mind ...
---------------------
By not preceding the following INSERTs with a 'set ciphertext on' I am telling the dataserver that the varbinary()
strings are merely raw data, and that the dataserver will need to encrypt the varbinary() strings, thus generating
another varbinary() string to represent the encrypted form of the data.
> 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
The following SELECT (with 'set ciphertext on' enabled) shows us that the original varbinary() strings
(0x61caca35bac08728266cd50de212434201) have in fact been encrypted, with the net result being that we get a new
varbinary() string (0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301) to represent the encryption of
the inserted varbinary() string ...
> 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
Notice that the varbinary() string in the output (0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301)
is the same string that shows up in your earlier posts when running the above SELECT against the RDB.
This indicates that the DSI connection is *not* issuing a 'set ciphertext on' command prior to inserting the varbinary()
strings into the val_users.password column. So the RDB thinks that 0x61caca35bac08728266cd50de212434201 is the actual
plaintext (ie, unencrypted) password.
So, why isn't the DSI issuing the 'set ciphertext on' command?
The repserver's internal language is called Log Transfer Language (LTL). This is something that the various internal
threads have no problems dealing with. All internal commands and data are formatted in LTL. Before replicated data can
be sent to the RDB it must be converted back to T-SQL.
To convert LTL to T-SQL the dataserver needs a translator. This translator comes in the form of function strings which
are used to map LTL to T-SQL; these function strings are defined at the repdef level (eg, for insert, update, delete
operations).
There are other function strings defined at the connection level which aren't so much a translation from LTL to T-SQL,
but rather a set of default commands to be issued each time a DSI initiates a connection into the RDB (eg, set
ciphertext on/off, set textsize, etc.).
Function strings are assigned to function string classes. The function string classes are then assigned to the
individual DSI connections. (A DSI/database connection can only have one function string class assigned to it at a time.)
The function string classes, and the function strings that make up said classes, are stored in various tables in the RSSD.
So, in your case we first need to find the function string class assigned to your DSI ...
> 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
... so the function string class appears to be called 'VAL_USERS'.
While we could pull up a list of all the function strings defined in the VAL_USERS function string class, for now we're
only interested in the one that generates the desired 'set ciphertext on' command ...
> 1> rs_helpclassfstring VAL_USERS,rs_set_ciphertext
> 2> go
> Function Name FString Name
> ------------------------------ ------------------------------
> rs_set_ciphertext rs_set_ciphertext
> FString Text
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> --------------------------------------------------------
... and from this we see that the function string appears to be defined as an empty string.
What you should see is something like:
================================
rs_helpclassfstring rrs_sqlserver_function_class,rs_set_ciphertext
go
Function Name FString Name
--------------------- ---------------------
rs_set_ciphertext rs_set_ciphertext
FString Text
-------------------------------------------
set ciphertext on
================================
From this sample output (above) you can see that the function string is defined as the string 'set ciphertext on',
which is exactly what the DSI should be sending to the RDB when it first connects.
---------------------
In the rest of the code I had you run I was just making sure any changes to the DSI had been incorporated (by bouncing
the DSI) and then verifying what the above test have already told us ...
PDB: insert password = 'figurethis'
PDB: password encrypted to 0x61caca35bac08728266cd50de212434201
DSI: does *NOT* issue 'set ciphertext on'
RDB: inserts password = 0x61caca35bac08728266cd50de212434201
RDB: password encrypted to 0xe27c9acf5a6bbfb847d5acc8b640d55624a27ca847a6f2505ff70b4df6bc2b6301
---------------------
So at this point it looks like your problem is related to an incomplete/buggy function string class called VAL_USERS.
By default the repserver will assign the Sybase-supplied function string class named rs_sqlserver_function_class to all
DSI's that connect to ASE databases.
While it's certainly possible to create your own function string class, there are (obviously ?) a handful of steps you
need to follow to insure said function string class has all necessary function strings defined properly (which VAL_USERS
does not appear to have, eh).
Is there a reason why you are using your own function string class?
How did you create your function string class?
If you don't have any user-defined function strings then I would suggest modifying the DSI to use the default function
string class (rs_sqlserver_function_class); once assigned you can verify the setting by running the above rs_helpdb
command again. [NOTE: You'll need to bounce the DSI for the change to take effect.]
Once you've verified the rs_sqlserver_function_class function string class is in use, run the above rs_helpclassfstring
command to verify that the rs_set_ciphertext function string is properly defined.
If you find that the rs_set_ciphertext function string class is not properly defined then we'll need to look at fixing
your RSSD system tables (eg, deleting the wrong records from rs_systext can trash function string definitions thus
causing the DSI to fail to submit the properly formatted commands to the RDB).
If on the other hand you have a reason for using the VAL_USERS function string class, no problem ... but you're going to
need to go back to the beginning and look at building it properly (eg, make sure it has a pre-defined function string
class as a parent class from which it can inherit the basic function strings), or manually define/correct all of the
problematic function strings (eg, rs_set_ciphertext).
You can use the rs_helpclassfstring and rs_helpfstring stored procs to show you the function strings defined at the
class/database and repdef levels, respectively.
NOTE: Some of the output from these procs will display "*** System-Supplied Transact-SQL Statement ***"; this simply
means that the definition of the function string is hardcoded in the repserver internals.
|