Unable to login to SQL server

A few days back one of my clients were facing a business down scenario, where in the DBA who was administering the SQL instance had quit the company and no other have the login access to that instance.

When they try to login, we get the following error:

Description: can’t log in to MS SQL studio. Getting an error “log in failed for user account 18456”
Error(s): “log in failed for user account 18456.

The client did not even have the SQL AUTHENTICATION (sa) ACCOUNT password as well.

So, as an initial step, we stopped the SQL SERVICES and the SQL AGENT SERVICES and started the SQL server using SQLCMD in the single user mode using the below command. We specify /m”SQLCMD” to make only SQLCMD to access the SQL Instance and to prevent any other connections except the one we are about to make.

C:\Windows\system32>net start MSSQLSERVER /m"SQLCMD"

Thus we had connected to SQL Instance now as shown below.

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

Now we added the Local Windows account to the Local administrators group and opened another command prompt as an ADMINISTRATOR.

Now we connected or logged into the SQL server instance using the below command :

SQLCMD -E -S  (for default instance)

SQLCMD -E -S <SERVERNAME> (for named instance)

Now we created the login in SQL server for the Local Windows Admin account through which we had logged into the machine by executing the below commands :

CREATE LOGIN [Domain\Login] FROM WINDOWS;

ALTER SERVER ROLE SYSADMIN ADD MEMBER [Domain\Login]

GO

Then we removed the -m trace flag from the SQL configuration manager and restarted the SQL services and logged into SSMS using the GUI. Thus we now had the Windows Authentication account in the list of SQL Logins and had the sysadmin privilege.

Thus we could perform all sorts of operations and also further created the other logins and the company was recovered from the business down scenario.

Hope this helps!! Happy Logging!!

Comments

• Anonymous
  June 03, 2018
  Great job Shreyas!! Very well composed!!
  • Anonymous
    November 06, 2018
    Thank you!!