How to reset sa password in MS SQL Server ?

How to reset sa password in MS SQL Server ?

The sa account has full rights in the MS SQL Sever environment. When you install the MS SQL Server program, the sa account is created and sa password is default blank (NULL). You can also change the blank sa password to a strong one when the setup program is running.

How to verify if the SA password is blank

1 On the computer that is hosting the instance of MSDE to which you are connecting, open a command prompt window.

2 At the command prompt, type the following command, and then press ENTER:

osql -U sa

This connects you to the local, default instance of MSDE by using the sa account. To connect to a named instance installed on your computer type:

osql -U sa -S servernameinstancename

You are now at the following prompt:

Password:

3 Press ENTER again. This will pass a NULL (blank) password for sa.

If you are now at the following prompt, after you press ENTER, then you do not have a password for the sa account:

1>

We recommend that you create a non-NULL, strong password to conform with security practices.

However, if you receive the following error message, you have entered an incorrect password. This error message indicates that a password has been created for the sa account:

“Login Failed for user ‘sa’.”

4 The following error message indicates that the computer that is running SQL Server is set to Windows Authentication only:

Login failed for user ‘sa’. Reason: Not associated with a trusted SQL Server connection.

5 You cannot verify your sa password while in Windows Authentication mode. However, you can create a sa password so that your sa account is secure in case your authentication mode is changed to Mixed Mode in the future.

If you receive the following error message, SQL Server may not be running or you may have provided an incorrect name for the named instance of SQL Server that is installed:

[Shared Memory]SQL Server does not exist or access denied.
[Shared Memory]ConnectionOpen (Connect()).

Or, you can install MS SQL Server Password Recovery to check that if the sa password is blank or not. Just one mouse click, you can view the result. Note that this program just identify .mdf file, and the sa password is saved in master.mdf which is usually at C:Program FilesMicrosoft SQL ServerMSSQLDatamaster.mdf

How to change your SA password

loadTOCNode(2, ‘summary’);

1. On the computer that is hosting the instance of MSDE to which you are connecting, open the command prompt window.
2. Type the following command, and then press ENTER:

   osql -U sa

   At the Password: prompt, press ENTER if your password is blank or type the current password. This connects you to the local, default instance of MSDE by using the sa account. To connect by using Windows authentication, type this command: use osql -E

   Note If you are using SQL Server 2005 Express, avoid using the Osql utility, and plan to modify applications that currently use the Osql feature. Use the Sqlcmd utility instead.

3. Type the following commands, on separate lines, and then press ENTER:
   sp_password @old = null, @new = ‘complexpwd’, @loginame =’sa’ go
   Note Make sure that you replace “complexpwd” with the new strong password. A strong password includes alpha-numeric and special characters, and a combination of upper and lower case characters.

   You will receive the following informational message, which indicates that your password was changed successfully:

   Password changed.

Besides, taking use of the SQL password recovery tool to change sa password is also a good way. It is fast and easy.

How to determine or change your authentication mode

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs.  How to back up and restore the registry in Windows If you are not sure how to verify the authentication mode of your MSDE installation, you can check the corresponding registry entry. By default, the value of the Windows LoginMode registry subkey is set to 1 for Windows Authentication. When Mixed Mode authentication is enabled, this value is a 2.

· The location of the LoginMode subkey depends on whether you installed MSDE as the default MSDE instance or as a named instance. If you installed MSDE as the default instance, the LoginMode subkey is located in the following registry subkey:

HKLMSoftwareMicrosoftMSSqlserverMSSqlServerLoginMode

Note If you are using SQL Server 2005, whatever you installed a default instance or a named instance, locate the following registry subkey. MSSQL.x is a placeholder for the corresponding value for your system:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.xMSSQLServer

· If you installed MSDE as a named instance, the LoginMode subkey is located in the following registry subkey:

HKLMSoftwareMicrosoftMicrosoft SQL Server%InstanceName%MSSQLServerLoginMode

Note Before you switch authentication modes, you must set a sa password to avoid exposing a potential security hole.

To switch from Mixed Mode to Integrated (Windows) authentication, follow these steps:

1 To stop MSSQLSERVER and all other related services (such as SQLSERVERAgent), open the Services applet in Control Panel.

2 Open the Registry Editor. To open the Registry Editor, click Start, click Run, and then type: “regedt32″ (without the quotation marks)

Click OK.

3 Locate either of the following subkeys (depending on whether you installed MSDE as the default MSDE instance or as a named instance:

HKEY_LOCAL_MACHINESoftwareMicrosoftMSSqlserverMSSqlServer

4 Or HKEY_LOCAL_MACHINESoftwareMicrosoftMicrosoft SQL Server<Instance Name>MSSQLServer

5 In the right-pane, double-click the LoginMode subkey.

6 In the DWORD Editor dialog box, set the value of this subkey to 1. Make sure that the Hex option is selected, and then click OK.

7 Restart the MSSQLSERVER and the SQLSERVERAgent services for this change to take effect.

Article Source:http://www.articlesbase.com/security-articles/how-to-reset-sa-password-in-ms-sql-server–1611022.html