Dedicated Admin Connection (DAC) in SQL Express

I've tried to do some exercises on my local VM with SQL Server Express edition, required Dedicated Admin Connection (DAC).

Faced the problem that I can not actually do it. After a short research found very easy solution.

1. Run SQL Server Configuration Manager. You can use an icon from windows menu or if it is not available you might use the following (or modified for your system/version) command:
C:\Windows\SysWOW64\mmc.exe /32 C:\Windows\SysWOW64\SQLServerManager12.msc

2. In Configuration Manager locate your SQL Server instance and choose properties by right click on it.



3. In Properties, choose "Startup Parameters" tab:


4. In the box specify a trace flag: "-T7806" and press Add.

5. Then hit OK and restart SQL Server Service.

Another way to do the same thing is would be editing Windows registry by RegEdit.exe
You just have to add  new key "SQLArgX" with "-T7806" in
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\Parameters

That should look something like this:



 That also require SQL Server instance restart.

 
Also, in order to be able to establish DAC you have to enable it within your SQL Server configuration:

---

sp_configure 'show advanced options', 1;
GO
RECONFIGURE
GO
sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO 
sp_configure 'show advanced options', 0; 
GO
RECONFIGURE
GO

---

The last step of DAC is connection itself. You have to specify word "ADMIN:" before the server name:



You can have ONLY ONE Dedicated Admin Connection per server at any given moment.
That means: do not forget to close your connection after the exercise and leave ability to connect to your server open at any moment in the future. Learn to do the same in production too.