Encrypted fields for SQL Server

In versions of Dexterity prior to 5.5, encrypted strings were stored as character fields on SQL Server. We have become aware of situations that may cause data stored in these type of encrypted fields to become damaged. To prevent this problem, encrypted strings are now stored in binary fields, rather than character fields.

This section describes a procedure you can use to convert existing tables. It also provides recommendations for using encrypted fields in SQL tables.

Converting existing tables

If your application stores encrypted strings, and you have data stored on Microsoft SQL Server 7.0, you must perform a conversion procedure to convert the encrypted strings from character fields to binary fields. The following procedure describes how to convert a table that contains encrypted string fields.

1. Locate tables with encrypted string fields.

Find any tables that contain encrypted string fields. As an example, assume that the Agent field of the Seller_Data table is an encrypted string field.

2. Duplicate the tables that contain encrypted string fields.

Use Dexterity Utilities to duplicate each table that contains encrypted string fields. Continuing the example, the Seller_Data table was duplicated, and the new table was named Seller_Data_Temp.

3. Change the physical name of the new table.

In Dexterity, open the table definition for duplicate table that was created. Change the physical name of the table to something that uniquely identifies the table. In this example, the Seller_Data_Temp table was opened and its physical name was changed to SELLDATTEMP.

4. Unmark the Encrypt option for each encrypted field.

In the table definition for the duplicate table, unmark the Encrypt option for each table field that is encrypted. In this example, the Encrypt option for the Agent field was unmarked.

5. Save the table.

Save the changes you made to the table definition.

6. Add the conversion procedure.

Add a procedure to your application to perform the conversion. The following procedure converts the Agent field in the Seller_Data table. This procedure requires the user to be logged into the appropriate data source before it can be run. The procedure performs the following actions:

• Drops the stored procedures for the current Seller_Data table.
• Uses pass-through SQL to rename the Seller_Data table so that is can be accessed using the duplicate table definition.
• Re-creates the Seller_Data table.
• Copies each record from the duplicate table to the Seller_Data table. The Agent field is read exactly as it is stored, decrypted using the Utility_DecryptTableString() function, then copied to the Seller_Data table.
• Deletes the duplicate Seller_Data table.

local long SQL_connection, status;
local boolean result;
local text SQL_Statements;
local integer int_result;

{Connect to the SQL data source.}
status = SQL_Connect(SQL_connection);
if status = 0 then

	{Drop the auto-generated stored procedures for the Seller_Data table.}
	int_result = Table_DropProcedures(0, table Seller_Data);
	if int_result <> 0 then
		error "Stored procedures could not be dropped.";
	end if;

	{Close the Seller_Data table.}
	close table Seller_Data;

	{Use pass-through SQL to rename the Seller_Data table.}
	{Build the SQL statements.}
	SQL_Statements = "sp_rename SELLDAT, SELLDATTEMP";

	{Execute the SQL statements.}
	status = SQL_Execute(SQL_connection, SQL_Statements);
	if status <> 0 then
		warning "Unable to rename the Seller_Data table.";
	else
		{Turn on table auto-create.}
		result = Table_SetCreateMode(true);

		{Create the "new" Seller_Data table by opening it.}
		open table Seller_Data;
	
		{Read each record from the Seller_Data_Temp table.}
		get first table Seller_Data_Temp;
	
		repeat
			{Copy all of the fields.}
			copy from table Seller_Data_Temp to table Seller_Data;
		
			{Decrypt the Agent field.}
			Agent of table Seller_Data=Utility_DecryptTableString (Agent of table Seller_Data_Temp);
		
			{Save the new data.}
			save table Seller_Data;
		
			{Get the next record.}
			get next table Seller_Data_Temp;
		until err() <> OKAY;
		
		{Delete the Seller_Data_Temp table.}
		close table Seller_Data_Temp;
		open table Seller_Data_Temp, exclusive;
		delete table Seller_Data_Temp;
	
		{Set access privileges for the new Seller_Data table.}
	
	end if;

	{Disconnect from the SQL data source.}
	status = SQL_Terminate(SQL_connection);
else
	{An error occurred creating the pass-through SQL connection.}
	warning "An error occurred creating the pass-through SQL connection: " + str(status);
end if;

Recommendations

We recommend that you use as few encrypted fields as possible in SQL tables. If you must have encrypted data in your tables, but want the fields to be stored as character fields rather than binary fields, use the Utility_EncodeString() and Utility_DecodeString() functions to manually encrypt the data to be stored. These two functions can encode and decode string values in a form that can’t be easily read by users or other applications.

Documentation Feedback

---