How to reset SQL server password loss

If you forget your Windows Password and SQL server password and cannot connect to SQL Server, is there a way to restore the password? The answer is to start data in single-user mode, connect to SQL Server with Windows local administrator, and reset the password.

 

You may have faced the issue of losing the SQL Server sa password. Perhaps you followed the security best-practice of removing the builtin \ administrators from the SysAdmin server role, and no one
You can find is in the SysAdmin role. at this point you may think that your only options are to reinstall SQL Server and attach the databases, or to directly access the master database files, which may potentially damage the data.

SQL Server 2005 provides a better disaster recovery option for this scenario that is non-intrusive for Master dB and that will help you preserve any objects and data stored in master dB (such as logins,
Certificates, Service master key, etc .) intact. members of the Windows Administrators Group now have access to SQL Server when SQL Server is in started in single-user mode, also known as "maintenance mode".

Using the single-user mode, SQL Server 2005 prevents a Windows administrator to abuse this privilege to act on behalf of the SysAdmin without being noticed. This allows Windows administrator accounts
To perform certain maintenance tasks, such as installing patches.

In order to start SQL Server in single-user mode, you can add the parameter "-M" at the command line. You can also useSQL Server Configuration ManagerTool, which provides proper controls
For the file access and other privileges. To useConfiguration ManagerTool to recover your system, use the following steps:

1. OpenConfiguration ManagerTool from the "SQL Server 2005 | configuration" menu

2. Stop the SQL server instance you need to recover

3. navigate to the "advanced" tab, and in the properties text box Add ";-M" to the end of the list in the "startup parameters" option

4. Click the "OK" button and restart the SQL server instance

Note: Make sure there is no space between ";" and "-M", the Registry parameter parser is sensitive to such typos. You shoshould see an entry in the SQL
Server errorlog file that says "SQL Server started in single-user mode ."

5. After the SQL server instance starts in single-user mode, the Windows Administrator account is able to connect to SQL server using the sqlcmd Utility Using Windows authentication. You can use Transact-SQL
Commands such as "SP_ADDSRVROLEMEMBER" to add an existing login (or a newly created one) to the SysAdmin server role.

The following example adds the Account "Buck" in the "contoso" domain to the SQL Server "SysAdmin" role:

Exec sp_addsrvrolemember' contoso \ Buck ', 'sysadmin ';

Go

6. Once the SysAdmin access has been recovered, remove the ";-M" from the startup parameters using the Configuration Manager and restart the SQL server instance

Important Security notes:

This process shoshould only be used for disaster recovery when no other method to access the system with a privileged (I. e. SysAdmin or equivalent) is available.

This process allows a Windows Administrator Account to override their privileges within SQL Server. It requires explicit and intrusive actions that can be monitored and detected, including:

·
Stop SQL Server and restart it in single use mode

· Connecting to SQL Server using Windows credentials

Reference: http://blogs.msdn.com/ B /raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx