Closure
Closure
Home » Databases » Microsoft SQL Server » How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)

How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)

How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
, , , , Leave a comment
How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
Latest posts by Stratos Matzouranis (see all)

Sometimes we will not be able to connect to the SQL Server Instance nor even like sysadmin user, either because of a problem with the instance, or as we have forgotten the password of the sysadmin user or not we have / know user sysadmin .

As a solution for the first n Microsoft SQL Server 2008 and later offers the tool DAC (Diagnostic Connection for Database Administrators). This allows us to connect almost every time in the instance even in the most extreme conditions.

As a solution for the second and third we will see how we connect to Single User mode without having a user in the SQL Server instance.

When is DAC useful to us?

We use it when we don't have access to the base with any account either SQL Authentication either Windows Authentication. Also some very specialized tasks are allowed by SQL Server only if we are connected via DAC.

What limitations do we have with DAC?

With its use we must remember that we have the following limitations:

  • Only user can be composed sysadmin and indeed only one at a time.
  • Actions are not executed in parallel.
  • DAC queries are executed with limited resources.

How we enable the DAC and for remote access

We can also connect remotely from another computer as long as we have access to the port 1434 it uses and the parameter is enabled remote admin connections:

exec sp_configure 'remote admin connections', 1;
go
reconfigure
go

How we connect via SQLCMD to DAC

The only thing different we have to do compared to a normal connection is to add the parameter -A. The account we will connect to must be sysadmin:

sqlcmd /S SMATZOURANISLP\SQL19 /d db_test -Usqladmin -Ppassword -A
How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
01

How do we connect via SSMS to DAC

To connect via SQL Server Management Studio (SSMS) all we have to do is to add in front of the servername the text admin: :

How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
02

If it shows us this text, it means that we tried to connect from object explorer:

How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
03

To compile without this error message we must connect from File — New — Database Query Engine:

How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
04

How to see if there is a user connected to DAC

To easily find which user is connected through DAC and we are not allowed to enter, we run the following query:

select t.name,ec.* from sys.dm_exec_connections ec
inner join sys.tcp_endpoints t on ec.endpoint_id=t.endpoint_id
where t.name='Dedicated Admin Connection';
How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
05

I still can't connect, what should I try?

We case you that even with DAC we can't connect, we can try to start it SQL Server Service in Minimal Configuration Single User Mode.

In this mode SQL Server does not create the tempdb, remote access is not allowed, only one user can log in at a time and only necessary processes are activated.

All we have to do is go to SQL Server Configuration Manager, to right-click Properties on the SQL Server Service and tab Startup Parameters to add the parameter -f (don't forget to remove it and restart the service when we solve the problem). After completing the process required restart of SQL Server Service.

How do I connect to SQL Server when I can't connect any other way (DAC, lost password, missing sysadmin)
06

When the restart is complete, we try to connect to either sqlcmd either with SQL Server Management Studio.

However, in the event that we still cannot connect with the following error:

Login Failed for user A. Reason: Server is in single user mode. Only one administrator can connect at this time (Microsoft SQL Server, Error: 18461)

It means that another user is connected to the one that starts the instance, so in this case we start the instance from Command Prompt in Single User Mode without minimal configuration -m with an option to only be able to connect through sqlcmd:

net stop MSSQLSERVER
net start MSSQLSERVER /mSQLCMD

and we can connect:

sqlcmd /S MSSQLSERVER -Usqladmin -Ppassword

In case we have forgotten the password of SQL Logins with sysadmin and we do not have / know Windows Logins with sysadmin

In Single User Mode all users who are local admins on the server get the sysadmin role and can login in the SQL Server Instance, so we can connect to the following (provided we have started the instance with /mSQLCMD which we saw in the previous step) :

sqlcmd -S MSSQLSERVER -E

In case we have a default instance, we also connect to the following one without declaring an instance:

sqlcmd -S. -E

Then depending on the case, we can either change credentials to a user or create a new one and give it its role sysadmin:

create login dba with password ='123Pa#123';
alter server role sysadmin add member dba;
go

When we finish the process we can restart the SQL Server instance in normal mode:

net stop MSSQLSERVER
net start MSSQLSERVER

Sources:

Share it

Leave a reply