Tag Archives: sqlcmd.exe

Jan 02

Recover SA Password on Microsoft SQL Server

Posted on by

Applies to:

Microsoft SQL Server 2005, Microsoft SQL Server 2008

 

Overview

If you ever lost a SA password, you may have thought your only option is to reinstall SQL and re-attach to the DB’s.  However, SQL server provides a much better disaster recovery method which preserves objects and data in the master DB.  Members of the server’s Local Administrator’s group can access SQL server by starting it in single-user mode.

 

How to Recover SA Password

  • Open SQL Server Configuration Manager
  • Stop the SQL Server Instance you need to recover the SA password
  • Open the properties on the SQL Server Instance and click on the Advanced tab
    • Change the Startup parameter by adding -m;  at the begging of the line and click OK
  • Start the SQL Service Instance
  • Open the command prompt
  • Run sqlcmd and press enter
  • Run a Transact-SQL command to add an existing account or a new account to the sysadmin server role.  Replace DOMAIN\Username with the account you want to add. *NOTE: if you  receive errors, I’ve listed some common issues at the end of this article.
EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';
GO

 

  • Open SQL Server Configuration Manager
  • Stop the SQL Server Instance
  • Open the properties on the SQL Server Instance and click on the Advanced tab
    • Change the Startup parameter by removing the -m;  at the begging of the line and click OK
  • Start the SQL Service Instance
  • Open Microsoft SQL Server Management Studio and login with the account you added
    • Under the DB, expand Security, then Logins
    • Open the properties for the sa account, and reset the password

Common Error: Running SQLCMD HResult 0x2, Level 16, State 1

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Typically, this error is caused by the SQL Browser service not running or the named pipes not being enabled or incorrect.  If the SQL Browser Service isn’t started, start it.  If there is a specific instance name, try connecting to that instance (ie: sqlcmd -S tcp:localhost\instancename).  SQLCMD is connecting through named pipes and the pipe name for the default instance is:

\\.\pipe\sql\query

To connect to a named instance, it connects to

\\.\pipe\<instancename>\sql\query

To make sure the named pipe is enabled and to change it to the default:

  • Open SQL Server Configuration Manager
  • Expand SQL Server Network Configuration
  • Click on Protocols for <InstanceName>, the default is MSSQLSERVER
  • Open the properties on Named Pipes