SQL Server Password

By | October 9, 2015

Introduction

How to reset SQL Server password. This post is all about helping users to unlock, or have a backdoor access to SQL Server database when they are locked out. The post will help in resetting SA password or adding a new user with SysAdmin privileges. You can always trim down user role later but this will help in getting access from a lock down condition without going through the longer process of reinstalling SQL Server and reattaching databases to the new instance.

Scenario

The post will be helpful in following scenario’s: –

  1. Forgot SA password.
  2. SA password is not available.
  3. SQL Server can’t be connected using Windows Authentication or to be simple there are no users who have windows authentication login available on SQL Server.
  4. SQL Server database files are available on a server hidden/wrapped under a third party application which has not provided direct access to database.

Background

Recently, i was investigating an old third party software installation at a client’s place. This investigation was to find possibilities of writing a RESTful services by connecting to its internal database. The third party software didn’t did good job in hiding its internal database.

I was able to figure out about the SQL Server instance by looking at SQL Server services where application was hosted. There was a single service instance specific for that application.

Connecting to that server instance using SQL Server Management Studio failed as we didn’t had any Server logins. The application wrapped its connectivity under the underlined custom framework they used in the software. A decompile of .NET application failed to get important information as the code was obfuscated.

Requirement

You need someone who has admin privileges on the box where the SQL Server is running. He need not be a user who has a windows authentication login to SQL Server.

Adding a new database login

  1. User Login

    • User with admin privileges on the box having SQL Server need to login.
  2. STOP SQL Server Service

    1. Open Services window by running Services.msc from Run.
    2. In Service.msc Search for SQL Server.
    3. Select SQL Server from services list and click on Stop link.

    Services window

  3. Open Command prompt in Administrator Mode

    Method 1

    • Click Start, click All Programs, and then click Accessories.
    • Right-click on Command prompt, and then click on “Run as administrator“.
    • If the User Account Control dialog box appears, click “Yes“.

    Method 2

    • Click Start
    • In the Start Search box, type cmd, and then press CTRL+SHIFT+ENTER.
    • If the User Account Control dialog box appears, click “Yes“.
  4. Find SQL Server Installation

    • Find SQL Server executable sqlservr.exe on the server.
    • In my remote server the default installation path is : “c:\Program Files\Microsoft SQL Server\MSSQL12.ACT7\MSSQL\Binn>
    • Change current directory in console to where you find SQL Server installation Binn folder.

    In SQL Server Binn

  5. Start SQL Server in Single User Mode

    • Start SQL Server in single user mode (Admin mode) by executing
    • sqlservr -m on command prompt for default instance.
    • sqlservr -s<instancename> -m example sqlservr -sServer7 -m Where Server7 is instance name. We don’t need to have <ServerName>\<InstanceName>.
    • Keep this command window open. It will keep SQL Server running in Single user mode.
  6. Open another Command prompt in Administrator Mode

    • Execute one of the command depending on installation.
      • sqlcmd -S”<ServerName>” for default instance
      • sqlcmd -S”<ServerName>\<InstanceName>” example sqlcmd -S”TestServer\Server7 
      • Once executed you will get a command prompt with 1>
    • Create a new Login by executing below given command
      • CREATE LOGIN ‘Apple’ WITH PASSWORD=’Any Password’ and press enter key.
      • GO and press enter key.
      • Command executes successfully and creates a new SQL login by name “Apple
    • Add Role to newly created login by executing below given command
      • SP_ADDSRVROLEMEMBER ‘Apple‘, ‘SYSADMIN’ and press enter key.
      • GO and press enter key.
      • Command executes successfully and assigns sysadmin role to the newly created SQL login ‘Apple’.
C:\Windows\system32> sqlcmd -S"TestServer\Server7"
1> CREATE LOGIN Apple WITH PASSWORD='Apple'
2> GO
1> SP_ADDSRVROLEMEMBER 'Apple','SYSADMIN'
2> GO
1>
Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL
+C.

Once you have created a new login and successfully assigned role you can terminate both command windows.

  • Go to Services.msc and restart SQL Server service
  • Connect to SQL Server using the SQL Authentication by providing the new login.
  • The new Login will allow you to modify or reset SA password through command line or by using SQL Server Management Studio.
  • Once you are able to connect you can drop or reduce privileges of this login.