How to reset sa password in MS SQL Server

The sa account has full rights in the MS SQL Severand then press ENTER:sp_password @old = null,
environment. When you install the MS SQL Server@new = ‘complexpwd’, @loginame
program, the sa account is created and sa password=’sa’ go
is default blank (NULL). You can also change the blankNote Make sure that you replace “complexpwd”
sa password to a strong one when the setupwith the new strong password. A strong password
program is running.includes alpha-numeric and special characters, and a
How to verify if the SA password is blankcombination of upper and lower case characters.
1 On the computer that is hosting the instance ofYou will receive the following informational message,
MSDE to which you are connecting, open a commandwhich indicates that your password was changed
prompt window.successfully: Password changed.
2 At the command prompt, type the followingBesides, taking use of the third part tool to change sa
command, and then press ENTER:osql -U sapassword is also a good way. It is fast and easy.
This connects you to the local, default instance ofMore refer:
MSDE by using the sa account. To connect to aHow to determine or change your authentication mode
named instance installed on your computer type:osql -UImportant This section, method, or task contains steps
sa -S servernameinstancenamethat tell you how to modify the registry. However,
You are now at the following prompt:serious problems might occur if you modify the registry
Password:incorrectly. Therefore, make sure that you follow these
3 Press ENTER again. This will pass a NULL (blank)steps carefully. For added protection, back up the
password for sa.registry before you modify it. Then, you can restore
If you are now at the following prompt, after you pressthe registry if a problem occurs. For more information
ENTER, then you do not have a password for the saabout how to back up and restore the registry, click
account:the following article number to view the article in the
1>Microsoft Knowledge Base:
We recommend that you create a non-NULL, strong322756 ( ) How to back up and restore the registry in
password to conform with security practices.Windows
However, if you receive the following error message,If you are not sure how to verify the authentication
you have entered an incorrect password. This errormode of your MSDE installation, you can check the
message indicates that a password has been createdcorresponding registry entry. By default, the value of
for the sa account:the Windows LoginMode registry subkey is set to 1 for
“Login Failed for user ’sa’.”Windows Authentication. When Mixed Mode
4 The following error message indicates that theauthentication is enabled, this value is a 2.
computer that is running SQL Server is set to· The location of the LoginMode subkey depends on
Windows Authentication only:whether you installed MSDE as the default MSDE
Login failed for user ’sa’. Reason: Notinstance or as a named instance. If you installed MSDE
associated with a trusted SQL Server connection.as the default instance, the LoginMode subkey is
5 You cannot verify your sa password while inlocated in the following registry subkey:
Windows Authentication mode. However, you caninMode
create a sa password so that your sa account isNote If you are using SQL Server 2005, whatever you
secure in case your authentication mode is changed toinstalled a default instance or a named instance, locate
Mixed Mode in the future.the following registry subkey. MSSQL.x is a
If you receive the following error message, SQLplaceholder for the corresponding value for your
Server may not be running or you may have providedsystem:
an incorrect name for the named instance of SQLsoft SQL ServerMSSQL.xMSSQLServer
Server that is installed:· If you installed MSDE as a named instance, the
[Shared Memory]SQL Server does not exist orLoginMode subkey is located in the following registry
access denied.subkey:
[Shared Memory]ConnectionOpen (Connect()).HKLMSoftwareMicrosoftMicrosoft SQL
Or, you can install MS SQL Server Password UnlockerServer%InstanceName%MSSQLServerLoginMode
to check if the sa password is blank or not. Just oneNote Before you switch authentication modes, you
mouse click, you can view the result. Note that thismust set a sa password to avoid exposing a potential
program just identify .mdf file, and the sa password issecurity hole.
saved in master.mdf which is usually at C:ProgramTo switch from Mixed Mode to Integrated (Windows)
FilesMicrosoft SQL ServerMSSQLDatamaster.mdfauthentication, follow these steps:
1 To stop MSSQLSERVER and all other related
MS SQL Server Password Unlockerservices (such as SQLSERVERAgent), open the
How to change your SA passwordloadTOCNode(2,Services applet in Control Panel.
’summary’);2 Open the Registry Editor. To open the Registry
1. On the computer that is hosting the instance ofEditor, click Start, click Run, and then type:
MSDE to which you are connecting, open the“regedt32? (without the quotation marks)
command prompt window.Click OK.
2. Type the following command, and then press3 Locate either of the following subkeys (depending on
ENTER:osql -U saAt the Password: prompt, presswhether you installed MSDE as the default MSDE
ENTER if your password is blank or type the currentinstance or as a named instance:
password. This connects you to the local, defaultrverMSSqlServer
instance of MSDE by using the sa account. To4 Or
connect by using Windows authentication, type thisSQL Server<Instance Name>MSSQLServer
command: use osql -ENote If you are using SQL5 In the right-pane, double-click the LoginMode subkey.
Server 2005 Express, avoid using the Osql utility, and6 In the DWORD Editor dialog box, set the value of
plan to modify applications that currently use the Osqlthis subkey to 1. Make sure that the Hex option is
feature. Use the Sqlcmd utility instead.For moreselected, and then click OK.
information about the Sqlcmd utility, visit the following7 Restart the MSSQLSERVER and the
Microsoft Developer Network (MSDN) Web site: (SQLSERVERAgent services for this change to take
3. Type the following commands, on separate lines,effect.