SA Password mis-placed.greenspun.com : LUSENET : SQL Server Database Administration : One Thread
Hello Mr. Eric,
As you have helped me time to time.. With this, I need your advice again on a problem I am encountering.
Presently, I mis-placed my Devlopment server SA Password and cannot access the server with the SA User ID. I can access the development server with only the user name login's.... But, I would like to migrate the database's which are on the Development machine to another stand by machine.
Would you please let me know, How can I do this?
Thank you and awaiting for your help.
-- Anonymous, August 10, 1999
This is what Neil Pike of Protech Computing Ltd. has to say.
Q. I've forgotten the sa password for SQL Server - what can I do? (v1.0 17.12.1998) A. The easiest way around this is to logon to the actual SQL Server NT machine itself as administrator. Then connect to SQL Server using any of the tools and specify "." as the server name and ask for a trusted connection. You should then has sa rights and be able to reset the password. Otherwise you can try and find a dba's machine who already has the server registered to SQL Enterprise Manager with sa and use that copy of SQL EM to connect. [Maybe this is the case with the stand by machine.]
Kalen Delany writes:
Do you have an Enterprise Manager installation registered with the correct password? If so, you can find the password in the Registry: hkey_current_user/software/Microsoft/MSSQLServer/SQLEW/Registered Servers/SQL 6.5 Make sure the tree and data panes are visible. Select the name of the server in the data (right) pane. From the View menu choose 'Display Binary Data'. In the Binary Data Box, make sure the format is Byte. Scroll down the data window, and you should see 'sa' followed a few lines later by the password. If you don't have a Enterprise Manager already registered, you can do the following: log into the NT server where SQL is installed using a local admin account run isql/w and select the option to use trusted connection for the (local) server click Connect, no need to enter a login or password You will be connected as 'sa' and can run the procedure sp_password to change the password to something known. Read the docs on sp_password before you use it.
In addition to Neil's and Kalen's advice, I would advise you to look through the stored procedures, bat files, scheduled SQL tasks, bcp command files, and client code. VERY often these sorts of files use the sa account and have the sa password in them.
If all else fails, call Microsoft Product Support Services. I suspect (since only 40 bit encryption is used to encrypt the password) that they may already have a way of hacking the sa password.
You might also try getting advice from Internet Security Systems. They market a product that is descended from SQL Auditor by DBSecure. This product scans SQL Servers and cracks passwords (that is, checks if the passwords are in its weak password dictionary). Perhaps they have a product or service that could solve your problem.
-- Anonymous, August 11, 1999
The problem has been solved.. I tried connecting to SQL Server with passing on a "." as the server name and then requesting for a trusted connection.. It connected to the SQL Server with SA id and then I was able to ran SP_password SP to change the password..
1,000,000 times thank you for your help.
-- Anonymous, August 16, 1999