Sybase ASE
All versions
Last updated: 02 June 2013
ASE Quiz Questions: answers 2013
 
More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


June 2013
Let's say you want to send someone a SQL script to create a login in his ASE server with a password that only you will know -- so you don't want to put the actual password in the script. In other words: keep the password secret for the guy who will receive and run the script.
How can you do that?

Answer:
First, this is not a totally artificial use case. For example, you might like to do this is when you want to create a 'maintenance login' into someone's system that you will be able to use, but nobody else should know the password.
Another examples of using the same trick are at the end of the discussion.

In order to create a login, it has always been required that you specify a password in one of the following ways:
1> sp_addlogin my_login, 'MyS3cretP@sswd'
2> go

or, in ASE 15.7 : 

1> create login my_login with password 'MyS3cretP@sswd'
2> go
As it happens, ASE 15.7 also supports a lesser-known syntax, namely the following:
1> create login my_login with encrypted password 
2> 0xc007d9e95a7dfea76521d53e8e2d531e9c7ae4284cefc76557b95996138cec1ae3b155b2932cdbb4db39
3> go
What this does is create a new login, but with the password specified in encrypted form. This hexadecimal string (without surrounding quotes!) is inserted directly into the master..syslogins.password column.

You can use this feature by taking the value in master..syslogins.password for an existing login whose actual password you know, and create a new login with it. This will cause the new login to have the same password as the one which already existed and whose password you knew. Effectively, you're cloning the password without disclosing the actual password.
Just try running the statement above in your own ASE 15.7 server: you'll find that the password is 'MyS3cretP@sswd' (without the quotes).

One of the interesting things is that this works cross-platform. As I explained in my blog on password migration, as of ASE 15.0.2, ASE uses a new, platform-independent encryption algorithm for encrypting passwords in the ASE catalogs.
In pre-15.0.2, you couldn't copy an encrypted password from, say, Linux to Solaris, since these platforms have a different endian-ness which mattered to the old encryption algorithm. You could still have achieved the same effect as above, but you'd have to (i) figure out the encrypted text for every target platform and (ii) write a procedure that updates master..syslogins directly to patch in the encrypted password text (more on that below).
But today, things are simpler since the create login statement above works identically for ASE 15.7 (or later) on any platform.

Some notes:
  • The encrypted password is the result of a cryptographic 'hash', which means the original password can never be derived from the encrypted text (because crypto hashing is a one-way function). In other words, your password cannot be cracked this way (unless someone can crack the SHA-256 algorithm, in which case the IT sector of this planet has a bigger problem anyway).
  • The password complexity rules as defined by sp_passwordpolicy, as well as other settings, are ignored since these can only be used when specifying a cleartext password. That means you can create a login with a password that does not satisfy the password complexity rules (more on that below).
  • Modifying the encrypted password text of an existing login cannot be done with a DDL command; instead you'll have to update (master..syslogins.password directly.
  • The ability to specify the password in its encrypted form in a DLL command is only available with create login. If you want to use the same trick for role password, you'll have to update master..syssrvroles directly.
Back in 1999, I published a stored proc sp_blank_password on my web site which lets you reset an ASE login password to blanks. You can still download the proc, and read the corresponding article, here.
This has not lost its relevance: all the old reasons for needing a blank password still apply. In addition, in ASE 15.7 ESD#2, most tools no let you create a new ASE server with a blank 'sa' password, but you must enter a 6-digit password right up front. Any client applications still using a blank password may then find themselves blocked from connecting so a backdoor is still welcome.
(Obviously, it is a much better idea not to use blank passwords, but use strong passwords instead. But hey, it's an imperfect world.)
Due to the change in encryption algorithm in ASE 15.0.2, the encrypted password strings in this stored proc no longer work in 15.0.2 or later. At the same time, creating a login with a blank password got simpler as the following SQL will now work on any platform for ASE 15.0.2+:
-- creates a login with a blank password:
1> create login my_login with encrypted passwd 
0xc007581afdc491f31883b9fed0ab17c86182a9844dced6d0a54e8e9ee479b21ad57f6269c054b3ba8572
2> go

-- modifies an existing login and sets a blank password:
1> update master..syslogins
2> set password = 
3> 0xc007581afdc491f31883b9fed0ab17c86182a9844dced6d0a54e8e9ee479b21ad57f6269c054b3ba8572
4> where name = 'your_login'
5> go


Summarising, you can use this trick to:
  • Distribute an ASE login's password without disclosing it (which is what Sybase Replication Server needed this feature).
  • Clone a login's password.
  • Set a password not complaint with the password complexity rules (though this is not recommended).
  • Create a blank password or reset an existing password to blanks (though this should be used only if all other options are a no-go).


How would you rate this ASE quiz question?
Great    OK    Average    Boring     

March 2013
Some time ago, I was looking at an issue with the speed of making a database dump, which was unusually slow in a particular Linux system.
What matters here is that we wanted to factor out different aspects to find where the bottleneck was. One such factor is the bandwith/IO capacity of the dump device. Others include the capacity of the ASE data devices being read from; the resources used by the Backup Server itself, etc.

We wanted to see the impact on dump performance if Backup Server was dumping to a device that has no speed limitations. The obvious thing to do is to dump to the NULL device (/dev/null) which is a computer's equivalent of a black hole, and is capable of accepting lots of input very fast by immediately ignoring it all (thus, effectively throwing everything away that is written to the NULL device).
Anyway, when you try to dump to the NULL device, Backup Server raises an error.
Still, this is actually possible. But how?

Answer:
First, let's verify that you cannot dump to the NULL device:
1> dump database model to '/dev/null'
2> go
Backup Server session id is: 14. Use this value when executing the 
'sp_volchanged' system stored procedure after fulfilling any volume 
change request from the Backup Server.
Backup Server: 4.134.2.1: Invalid Device: you cannot dump to or load 
from the null device, '/dev/null'.
Msg 8009, Level 16, State 1
Server 'SYB157', Line 1
Error encountered by Backup Server.  Please refer to Backup Server
messages for details.
...so Backup Server appears to explicitly check whether you're using the NULL device. Which sounds pretty conclusive. Or does it?

Let's create a symbolic link to the NULL device, and then try to dump to this symlink instead:
On OS level:
# ln -s /dev/null /tmp/mynulldev

In ASE:
1> dump database model to '/tmp/mynulldev'
2> go
Backup Server session id is: 14. Use this value when executing the 
'sp_volchanged' system stored procedure after fulfilling any volume 
change request from the Backup Server.
Backup Server: 6.52.1.1: OPERATOR: Volume to be overwritten on 
'/tmp/mynulldev' has unrecognized label data.
Backup Server: 6.78.1.1: EXECUTE sp_volchanged
        @session_id = 14,
        @devname = '/tmp/mynulldev',
        @action = { 'PROCEED' | 'RETRY'
| 'ABORT' },
        @vname = ,
        @bs_name = { NULL | 'X157_BCK' }
Hmmm... this is not quite what we had in mind.
But let's see what the error message actually says -- always a good idea.
It says the dumpdevice has "unrecognized label data". That sounded vaguely familiar from many years ago.... I guess it means BackupServer is treating this dump device like a tape?
Since the NULL device is just a black hole, it's no wonder it doesn't find any tape labels.

But maybe we can just pretend this is a uninitialized blank tape, and use the "with init" option to inialize it? Let's try:
1> dump database model to '/tmp/mynulldev' with init
2> go
Backup Server session id is: 14. Use this value when executing the 
'sp_volchanged' system stored procedure after fulfilling any volume 
change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'model130770135C  ' section 
number 1 mounted on disk '/tmp/mynulldev'
Backup Server: 4.188.1.1: Database model: 1950 kilobytes (100%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database model: 1958 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database model).
Bingo! That worked.

So this was our solution to better understand the throughput of BackupServer.
I don't think this trick is specifically documented, but since we're just using regular documented commands, I don't see anything wrong with it.
Just do not try to load any dumps from this dump device -- I don't think there is a way to get that working....

How would you rate this ASE quiz question?
Great    OK    Average    Boring     


More ASE quiz questions: 2013 - 2012 - 2011 - 2010 - 2009 - 2008 - 2007 - 2006 - 2005 - 2004 - 2003 - 2002 - 2001
For a randomly selected quiz question, click here.


 
 This document is located at www.sypron.nl/quiz2013a.html