Migrating ASE login passwords across platforms
(originally published at www.sybase.com in May 2009; moved to SCN in December 2012)
Migrating an ASE server or ASE database to a different platform has become more common over the years with the growing popularity of platforms like Linux and Solaris/Intel. However, cross-platform migration has always presented some hurdles to DBAs.
Prior to ASE 12.5.3, the only way to do this was by recreating the schema, followed by copying the data through bcp or CIS (i.e. proxy tables). Prior to 12.5.3, it was not possible to load database dumps created on a different platform (well, formally speaking that was the situation. In practice, it might actually work if both platforms were using a CPU with the same Endian-ness (i.e. Big or Little Endian); however, Sybase did not formally support cross-platform loading of database dumps in those days).
Life got better with version 12.5.3, when ASE was extended with support for cross-platform loading of database dumps. This made the process much easier: now you could simply load a database dump originally made on a different platform. ASE automatically takes care of all required byte-swapping related to Big Endian/Little Endian differences between the platforms for columns with datatypes such as smallint, int, datetime, float etc. Some datatypes, like (var)binary and text/image are not byte-swapped by ASE at all, since these datatypesare expected to contain binary data in an application-defined format — in other words, ASE wouldn’t know how to convert such columns anwyay. In practice, this is unlikely to cause any problems for business applications.
However, there is an unexpected side to all this, with practical relevance for cross-platform migration of an ASE server. The issue is that login passwords (as well as role passwords) are stored in syslogins.password, which happens to be a ‘varbinary’ column. This column does not contain the password itself, but a hash of the password (a hash is the result of a one-way cryptographic operation, often called ‘encryption’, though this is somewhat incorrect as this suggests there is a possibility of decryption which is not possible for one-way hash functions). When the user types his password when connecting to ASE, this password is hashed by the same algorithm and the result is compared with the value in syslogins.password to determine whether the correct password was specified. For this mechanism to function properly, the correct byte ordering is absolutely crucial. If something were to go wrong a user’s login password will not be accepted when connecting to ASE.
Now, because the cryptographic algorithm to perform this hashing was implemented natively inside ASE before version 15.0.2, platform-specific byte-order differences would occur in the hashed data. Therefore, when copying the contents of syslogins.password to a platform with different Endian properties, the same original password will be guaranteed NOT to produce the same hash — meaning that the end-user can not simply retain his password when moving the server to a different platform.
Of course, the solution is to let the DBA (who has sso_role) simply reset the login’s password after having migrated to the new platform, and require the user to change it after logging into ASE. However, this is one of those cases that is simple in theory but cumbersome in practice: when you have 1000’s of logins in your ASE server, this creates overhead and inconvenience for DBAs and end-users alike ….and also think of unattended batch jobs using on a dedicated login that is never used interactively.
Prior to ASE 15.0.2, these was no solution for this problem, other than to forcefully have all passwords changed by the DBA. In version 15.0.2 however, improvements were made to the hashing algorithm for the passwords of logins and roles. First, a stronger algorithm is now used for hashing the passwords: the industry-strength ‘SHA-256’ algorithm has replaced the algorithm that’s been in use since secure password storage was introduced in version 10, back in 1993. This change in hashing algorithm is totally transparent to end-users, who do not need to change their passwords (for details on how this is achieved, see the section on security in the New Features Guide for ASE 15.0.2).
What’s more relevant to the discussion above, is that the cryptographic algorithm is no longer implemented natively inside ASE, but is now located in 3rd-party crypto libraries outside the ASE executable. Since these 3rd-party implementations generate a platform-independent hash value, it means that you can now migrate login passwords across platforms while retaining the password, provided both servers are on version 15.0.2 or later. This migration can simply be done by copying the contents of syslogins or syssrvroles through ‘bcp’ (note that a dump of the master database cannot be loaded cross-platform).
So, in summary, the new password hashing algorithm in ASE 15.0.2 not only provides better password security, but also makes life easier for DBAs migrating their ASE servers across platforms.