ASA 9.0.1.1899 to ASA 11.0.1.2222

ATM we are migrating customer databases from 9.0.1 to 11.0.1. As well we
are changing the collation from CP1252 to UTF-8.

The process goes like this:
* unload "old" database (ASA 9.0.1.1899; CP1252) using dbunload.exe 11.0.1
* reload structure and data using dbisqlc.exe 11.0.1
* LOAD TABLE ... ENCODING 'cp1252'

Here comes the problem:
GRANT CONNECT TO "username" IDENTIFIED BY ENCRYPTED '\x97\...x96\xc6'

Now if the user had a password with special characters like ä,ö,ü,... he
cannot log in after the migration.

What's it all about? How can the encoding specified for "GRANT CONNECT
TO"? Are we doing something totally wrong in the migration process?

TIA Gerrit Edzards

As with pretty much any secure password system, passwords are stored as
a non-reversible hash -- that's what the encrypted password is. Old
versions of SQL Anywhere hashed the password as it existed in the
database charset encoding. When you log in to your new UTF8 database, it
gets the password in UTF8 (which is different from the 1252
representation if you use non-ASCII characters) and hashes _that_. The
hash of the password encoded in UTF8 will not match the hash of the
password encoded in cp1252 and therefore the password will not be
accepted. There's really no way around that -- you will need to reset
the password.

With new databases (I think starting at 10.0.0 but maybe it was 11.0.0),
password hashes are always based on the UTF8 encoding of the password
text regardless of the charset that is being used by the database. That
way, the password hash can be migrated to a database that uses a
different charset without encountering the problem you are having now.

-john.

--
John Smirnios
Senior Software Developer
iAnywhere Solutions Engineering

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

Gerrit Edzards wrote:

Ok! We will consider the problem and give the information to our
customers. Thanks.

- Gerrit Edzards