More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
We have lost the sa password, what can we do?
Remember Douglas Adams famous quote "Don't panic" is the first thing!
I know that most people use the 'sa' account all of the time, which is fine if there is only ever one dba administering the system. If you have more than one person accessing the server using the 'sa' account, consider using sa_role enabled accounts and disabling the 'sa' account. Funnily enough, this is obviously what Sybase think because it is one of the questions in the certification exams.
If you see that someone is logged using the 'sa' account or is using an account with 'sa_role' enabled, then you can do the following:
sp_configure "allow updates to system tables",1
go
update syslogins set password=null where name = 'sa'
go
sp_password null,newPassword
go
You must rememeber to reset the password before exiting isql or sqsh. I thought that setting it to null would be enough, and exited isql thinking that I would be able to get in with a null password. Take it from me that the risk is not worth it. It failed for me and I had to kill the dataserver and get a new password. I just tried the above method and it works fine.
If you have a user with sso_role enabled, login with that account and change the 'sa' password that way. It is often a good idea to have a separate site security officer, just to get you out of this sticky situation. Certainly stops you looking an idiot in managements eyes for having to reboot production because you have locked yourself out!
OK, so we have got to the point where there are no accounts with sufficient priviledges to allow you to change the 'sa' account password. (You are sure about that, since the next part can cause data loss, so have another quick look.) We now need to some more drastic stuff.
If the server is actually running, then you need to stop it. We know that the only accounts that can stop the server in a nice manner are not available, so it has to be some sort of kill. You can try:
kill -SIGTERM
or
kill -15
(they are identical) which is designed to be caught by ASE, which then performs the equivalent of shutdown with nowait. If ASE does not die, and you should give it a little while to catch and act on the signal, then you might have to try other measures, which is probably kill -9. Note that if you have tables with identity columns, most of these will jump alarmingly, unless you are using ASE 12.5 and the identity interval is set to 1.
Once down, edit the RUN_SERVER file ( RUN_SERVER.bat on NT) and add "-psa" (it is important not to leave a space between the"-p" and the "sa", and that it is all lower-case) to the end of the dataserver or sqlsrvr.exe line. You will end up with a file that looks a bit like:
#!/bin/sh
#
# Adaptive Server name: N_UTSIRE
# Master device path: /data/sybase/databases/N_UTSIRE/master.dat
# Error log path: /opt/sybase-11.9.2/install/N_UTSIRE.log
# Directory for shared memory files: /opt/sybase-11.9.2
#
# Regenerate sa password -psa
#
/opt/sybase-11.9.2/bin/dataserver \
-sN_UTSIRE \
-d/data/sybase/databases/N_UTSIRE/master.dat \
-e/opt/sybase-11.9.2/install/N_UTSIRE.log \
-M/opt/sybase-11.9.2 -psa \
(I add the line mentioning the regenerate, so that if I need to do this in a moment of extreme pressure it is there in front of my nose.
Now, start the server again and you should see the following on the screen:
00:00000:00001:2001/05/26 18:29:21.39 server 'bin_iso_1' (ID = 50)
00:00000:00001:2001/05/26 18:29:21.39 server on top of default character set:
00:00000:00001:2001/05/26 18:29:21.39 server 'iso_1' (ID = 1).
00:00000:00001:2001/05/26 18:29:21.39 server Loaded default Unilib conversion handle.
New SSO password for sa:tmfyrkdwpibung
Note that it is not written to the log file, so keep your eyes peeled.
On NT you will have to start the server from the command line and not use Sybase Central or the control panel.
Obviously, you will want to change the password to something much more memorable as soon as possible.
Remember to remove the "-psa" from the "RUN" file before you start the server again or else the password will be changed again for you.
(Continued on next question...)
