- How to bulk detach / attach databases in SQL Server - February 2, 2024
- How to enforce password policy in an Oracle database - 4 January 2024
- How do we change the name of the user / schema in an Oracle database - 1 December 2023
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 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
If it shows us this text, it means that we tried to connect from object explorer:
To compile without this error message we must connect from File — New — Database Query Engine:
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';
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.
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
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