Do old logs need to be encrypted the same way as the current database for SQL Remote?

2

This is another follow-up to this question.

At the risk of pushing the limits of the "personal questions per day" ratio I would like to know whether a SQL Remote instance (consolidated or remote) needs all relevant files have equally encrypted.

With relevant files, I'm refering to the database file(s), the current translog (and mirror) and all old log files that may be used to resend messages.

I'm citing from an older NNTP thread ("Questions about encryption of existing databases in a SQL Remote setup" started 2006-09-19)

So far, we have used simple encryption for all dbs. Now I'm figuring out if I could strengthen the system's security by choosing strong encryption.

My questions:

  1. Do I have to choose the same type of encryption for all dbs, or can different dbs be encrypted differently? (I guess they are independent.)

  2. Would it be sufficient to execute the "CREATE ENCRYPTED FILE statement" for a db and its log(s), or would this change log offsets and the like?

  3. The docs say: "If a database is encrypted using this statement, the corresponding transaction log file (and any dbspaces) must also be encrypted with the same algorithm and key in order to use the database." What about the old logs in a SQL Remote setup that may be used in order to resend operations - do they have to be encrypted, too, or is this only necessary for the current log (and - in case of recovery - for logs that must be reapplied)?

  4. Are there any consequences for DBXTRACT if I want to reextract from a strongly encrypted consolidated db into a simply encrypted remote (besides I have to specify the key in the connection parameters for the cons)? (Note: I do not use -ac or -ac, extracting is done via a reload.sql file.)

And the answer that Reg has given:

This is going un-answered, so let me spend a few minutes and answer to the best of my ability without actually trying out the things I'm suggesting...

  1. You can use a different type of encryption at each site.
  2. I do not believe that CREATE ENCRYPTED FILE will change the log offsets of the database file.
  3. All the old logs that SQL Remote may scan must be encrypted as well.
  4. No, this should be fine. ...

Question:

According to current tests with a 12.0.1 cons (strongly encrypted) and a 8.0.3 remote that has incidentally lost old messages, the cons can use their old (and simply encrypted) v8 logs without problems when having to re-scan them to re-send messages.

So it seems unnecessary to change the encryption of those old log files (which is fine, as it seems not even possible to do so - see the question above).

So I would need to know whether the answer to point 3 is not valid (or no more valid in the combination v12/v8).

For obvious reasons, I would need a clear statement as I do not want to risk to have lots of remotes re-extracted in case messages get lost. And we do have lots of old log files still waiting to be confirmed...

(Sidenote: I'm aware that scanning pre-v10 logs is done in a different way then scanning v10+ logs. May this be the reason that there is possibly no need to use the same encryption here?)

asked 15 Apr '11, 12:23

Volker%20Barth's gravatar image

Volker Barth
39.9k●360●547●817
accept rate: 34%

edited 18 Apr '11, 04:25

Any words to confirm or correct my assumptions are still very welcome... I won't migrate (or might have to keep on with simple encryption) until this topic is clarified:(

(19 Apr '11, 10:56) Volker Barth

Volker, I'll need a bit of time to answer this. If you need an immediate answer, you should open a tech support case.

(20 Apr '11, 10:45) Reg Domaratzki
Replies hidden

Reg, I don't need an immediate answer - a timely one should do:)

Thanks for looking after that issue! (And somehow I knew you would be the one...)

(20 Apr '11, 12:03) Volker Barth
One Answer:
active answersoldest answersnewest answerspopular answers
1

When changing your encryption scheme on a database involved in synchronization or replication, there are some things that will work, and some things that won't.

  • You CANNOT have an old logs directory that with transaction logs that contain a mix of strong encryption keys. If, for example, your determined that your encryption key was compromised, and changed the key of the consolidated database, you would end up with a mix of old transaction with "key1" and "key2", but with the ability to only specify a single key on dbremote/dbmlsync command line.
  • You CAN have a mix of non-encrypted, simply encrypted and strongly encrypted transaction logs in your old logs directory. When a log scanning tool (dbremote/dbmlsync/dbtran) attempts to open an offline log and is provided an encryption key, it will first attempt to read the log without the key, which will be successful if the offline log is using no encryption or using our old "simple" encryption.

I've attached a ZIP file that shows this in action. Just unzip the contents into an EMPTY directory, then edit the rep.bat file and change the following two lines to point to v8 and v12 SA installs on your computer.

set SA8=c:\sybase\asa803\win32
set SA12=c:\sybase\asa12m\bin32

Next, open a DOS prompt, CD into the directory when the files exist, and type "rep". The sample will set up a V8 replicating environment, and then upgrade the simply encrypted v8 databases to strongly encrypted v12 databases, ensuring that there are operations in the offline simply encrypted v8 transaction logs and offline strongly encrypted v12 transaction logs that still need to replicate. Finally, the v12 dbremote process will run and process operations from all the offline logs.

permanent link

answered 20 Apr '11, 13:54

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k●3●43●117
accept rate: 37%

Well, if you call that a "non-immediate" answer, how fast are your responses usually?

Thanks a lot for clarifying the behaviour of my tests. I won't be able to do some tests currently but do understand the situation.

From your answer, I conclude that only if I would change from one strong encryption key to another one, then I would have to re-encrypt the old logs. And that would be doable with V8/V9 logs, too, whereas encrypting them from simple to strong is doomed to fail, as Graeme has stated.

(20 Apr '11, 14:13) Volker Barth

Reg, I have tested and studied your sample today. Big thanks for putting this together - and a fine example in batch programming, too...

Now I fell confirmed to go on with my migration.

BIG THANKS

(Yes, I felt obliged to answer with a (fine?) example in "visual commenting":)

(26 Apr '11, 08:26) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×438
×103
×46
×33

question asked: 15 Apr '11, 12:23

question was seen: 6,460 times

last updated: 26 Apr '11, 08:30

Related questions

Migrate from ASA8 to ASA12 over a nine month period

Compatibility of the DbTools API / SQL Remote

How to change from simple to strong encrytion for old SQL Remote logs?

Error: Primary key in table article_cols not unique during dbxtract

SQL Remote: Messages with duplicate log operations

Django 1.8.5 & Sql Anywhere 12.0.1 Developer Edition

Decrypting AES in C# encrypted in SQL Anywhere 12.0 or earlier

Alter column statement replicated from SA12 to ASA9 db

Creating a custom migration process for SQL Remote databases / handling of SQL Remote tables

Revoke Remote failing