print versionprint version  
Sybase ASE
11.0 11.5 11.9 12.0 12.5 15.x
Last updated: 04 June 2000
How to set ASE login passwords (back) to blank
 
Officially, ASE login passwords are mandatory: you cannot create a login without a password. When following the normal procedures, there is no way to avoid the use of passwords: to create a login with "sp_addlogin" or change a password with "sp_password", you must at least specify a 6-character password, or the operation will fail with an error status.

By itself, this is a good thing, as it improves security. However, sometimes it can be necessary to use a login without a password (a.k.a. a "blank" password or a NULL password). This can be achieved by using a backdoor trick, as will be explained below.

The only exception to the "mandatory password" rule is when a new server has been created: the sa login will have a blank password in this situation. Now consider the following real-life scenario: after a server was created, the sa password was left blank for some time, but now you finally changed it to some normal password (as is indeed recommended). However, the next day, some important applications appear not to work anymore because they use a hard-coded, blank sa password. To fix this, you would like to reset the password back to blanks, but because this isn’t possible, your only option seems to be to restore a dump of the "master" database.

Fortunately, there is a solution for this situation. In version 10.0 and later, encrypted login passwords are stored in the password column of the master..syslogins table as a hexadecimal string. The blank login password for the sa login in a new server is not stored as blanks or NULL, but also corresponds to an encrypted hexadecimal string. Using this knowledge, you can set a password to blanks as follows:
  1. Create a new server on your platform
  2. Log in to this new server as sa and run the following query:
    select password 
    from master..syslogins 
    where suid = 1
    
  3. Using the hexadecimal string returned by this query, manually update the syslogins.password column set a blank password for login "some_login" (warning: this should be done in a transaction !) :
  4. update master..syslogins
    set password = <hex-string-returned-by-above-query>
    where name = "some_login"
    
  5. Now you can log into the server as "some_login", using a blank password. Note that this will work for any login, not just for sa.

Because the encrypted string for a blank password is platform-dependant, there is no common value that will work for all possible servers. You probably won’t have to go through the above steps yourself, as you can use the stored procedure sp_blank_password, which already includes the blank-password strings for the major ASE platforms. This procedure can be downloaded from http://www.sypron.nl/blankpwd.html .

Although not supported or guaranteed by Sybase, the encrypted password strings appear to be version-independent, i.e. they work in all ASE versions on the same platform.

Please note that the trick described in this article only applies to ASE version 10.0 and later; earlier versions use unencrypted passwords which can be updated in master..syslogins directly. Also note that, in version 10.0 and later, non-blank passwords cannot be shorter than 6 characters, as was possible in version 4.9.x and earlier.



Author: Rob Verschoor (rob@sypron.nl)

Sybase, Inc. This article first appeared in the 2nd Quarter Issue 1999 of the ISUG Technical Journal.
A PDF version of the original publication can be found here . height="31">



Additional information (added 04-June-2000):

Alternative ways for setting a blank (and short) password in ASE 12.x
In ASE 12.0 or later, the procedure for setting a blank password as described in the above article still works fine. However, in ASE 12.x, the same goal may be also be achieved through the new password-related features that were introduced in ASE 12.0.
In ASE 12.x, a minimum password length can be specified; when setting this length to 0, a blank password can be set as described below.
To set the server-wide default minimum password length to 0 :

sp_configure "minimum password length", 0

Note: this configuration option is dynamic. Also note that this server-wide setting also applies the (optional) passwords for user-defined roles.



To set a minimum password length for a specific login (note the quotes !):
sp_modifylogin login_name, "min passwd length", "0" 
        

To set a password to blank for your own login:
sp_password your_own_current_passwd, NULL
        

To set a password to blank for another login (requires sso_role):
sp_password your_own_current_passwd, NULL, login_name
        

By using this feature, it is also possible to set passwords of less than 6 characters in ASE 12.x.
 
 This document is located at www.sypron.nl/blankpwd_txt.html