How to reset/crack the password of the "dba" account - SQL Anywhere

 

• Trying to login as account "dba/sysadmin" and receiving the error -103 "Invalid user ID or password."
• We have "lost/forget" the password of the "dba/sysadmin" account.
• How  can we reset the password of the "dba"  account?

password_error   

Cause

• Prior to build 17.0.5739
• The default password for the "dba"¹ account is "sql" has been changed.²
• A different password can be defined to the account at creation time or later using the "ALTER USER ..." statement.
• A password change, after the creation of the database, will be logged in the transaction action log, with the password encrypted.
• There is no option to start a database and print a new dba password to console (as in ASE or SQL Server).
  SQL Anywhere has been designed to run on systems where security from the operating system level cannot be guaranteed.
• Passwords are stored encrypted from the moment they are written to the database and there is no option to decrypt them or reset it, 
  unless another administrator account or transaction log with logged password change is available (see below).
• From build 17.0.5739 or above, a new feature has been introduced:
  

  New feature is added to allow to reset the forgotten password.

  1. New role (SYS_OFFLINE_RESET_PASSWORD_ROLE) is added. It can be granted by the role administrator to a user who is authorized to reset password.
  2. New server command line switch is added to reset the password of the specified user by the user whom SYS_OFFLINE_RESET_PASSWORD_ROLE role has been granted.

          -orp "{UserID|UID}=<userid>;{ NewPassword|NEWPWD}=<newpassword>;{ AuthUserID |AUTHUID}=<authuserid>;{ AuthPassword|AUTHPWD}=<authpassword>"

  • <userid>: the user whose password is going to be reset.
  • <newpassword>: new password of the <userid>
  • <authuserid>: the user who has SYS_OFFLINE_RESET_PASSWORD_ROLE granted by the role administrator
  • <authpassword>: password of <authuserid>

  If -orp is used, the server would start a database and attempt to reset the password with given parameters, and immediately stop the database and shutdown. The console output reports the success message or an error message if failed.

  Note:

  - The password reset would not be allowed if the <userid> has no password.

  - Must specify one database with the -orp option

  - No database option is not allowed with -orp server option

  - No other server option (except -o) is allowed with -orp server option

  - minimum password length of <newpassword> is 6.

 Notes:

1. Some applications may have chosen a different account to serve as their dba but you may still be
   able to follow this KBA if you know what the name of that account is supposed to be.
2. There is no predefined default dba or password as of version 17.

Resolution

You do have three options to resolve this situation

• use the -orp database server option added in version 17.0.5739 or above
• reset the password using another DBA account
• rebuild the database using the transaction log files

Use the -orp database server option added in version 17.0.5739 or above

Example:

User ‘DBA’ had password ‘sql123’ and the user ‘reset_user’ has password ‘sql456’. As a role administrator, user ‘DBA’ granted SYS_OFFLINE_RESET_PASSWORD_ROLE to user ‘reset_user’ by the following statement.

grant role sys_offline_reset_password_role to reset_user;

Suppose that user ‘DBA’ has forgotten the password. After shutting down the server, it can be reset to ‘newpassword’ by the following server command line.

dbeng17 -orp "UID=DBA;NEWPWD=newpassword;AUTHUID=reset_user;AUTHPWD=sql456" mydb.db

Reset the password using another DBA account

The account "dba" is the default super-user of a database. Additional super-user accounts can exist in a database. If such an additional super-user exists and the credentials are known, this account can be used to reset the password of the "dba" account. Please refer to KBA 2207121  for more information about super-user account creation.

1. In Sybase Central use the credentials of another DBA account to log in to the database (If you do not have Sybase Central please refer to KBA 2040854)
2. In the left hand Folders Double Click Users
3. Right Click the user credentials you want to reset > Click Properties
4. Change the password of the account, confirm it and click OK
1. 
   

Rebuild the database using the transaction log files

A rebuild can be done, if one the following conditions is meet.

• All Transaction log files do exist, since the creation of the database.
• A database file backup is available and all transaction log files, created after the backup, do exist.

The important aspect is that the database can only be rebuild, using one or more transaction log files, if these do contain all data definition language (DDL) and data manipulation language (DML) statements since the creation of the database.

Using all transaction log files, since the database was created

1. Create a new database, with the same parameters as the original database was built with.
• For example: dbinit -dba DBA,sql -p 4k demo.db
  You can find the original parameters of a database from a console/server log. In the console log file, on the top it should say something like the following, describing how much memory/cache size/ page size are you using
  
  
2. Translate the transaction log files into .sql script files, using the SQL Anywhere tool "dbtran".
• For example: dbtran demo.log demo.sql
3. If the password of the "dba" account was changed after the creation of the database, the "ALTER USER" statement needs to be located in the created .sql files and commented out.
   OR
   If you cannot find the ALTER USER statement, go to the bottom of .sql file and add the following statement:
   ALTER USER dba IDENTIFIED BY sql;
   Please replace sql with the new password you want for your database.
   Adding this statement will override the previous password change.
4. In the same order as the corresponding transaction files were created, apply each single .sql script file to the database, using e.g. Interactive SQL (dbisql)
• For example: dbisql -c "DBF=demo;UID=DBA;PWD=sql" demo.sql

Using all transaction log files, since the backup of the database was created

1. Translate the transaction log files into .sql script files, using the SQL Anywhere tool "dbtran".
• For example: dbtran demo.log demo.sql
2. If the password of the "dba" account was changed after the creation of the database, the "ALTER USER" statement needs to be located in the created .sql files and commented out.
   OR
   If you cannot find the ALTER USER statement, go to the bottom of .sql file and add the following statement:
   ALTER USER dba IDENTIFIED BY sql;
   Please replace sql with the new password you want for your database.
   Adding this statement will override the previous password change.
3. Get the backup of the database
4. In the same order as the corresponding transaction files were created, apply each single .sql script file to the database, using e.g. Interactive SQL (dbisql)
• For example: dbisql -c "DBF=demo;UID=DBA;PWD=sql" demo.sql

Caveats:

Rebuilding a database has in general some side effects you should be aware of.

• This will break any MobiLink or SQL Remote synchronization that is ongoing.
• This should only be done in a development environment.
• For a database of an OEM Authenticated edition, further steps might be required to re-authenticate the database.
• If there is an event that created and scheduled in the past, error "Start date/time for non-recurring event %1 is in the past" might return. You need to open the translated sql file, and comment out all the event that schedule in the past or change the schedule time to future time.

Notes: The tool "dbtran" prints the start & end offset of the translated transaction log file. This way you can verify that no transaction log is missing. The end offset of the first transaction log should be identical to the start offset of the second transaction, and so on.

Source:Sybaseinfocenter