Wednesday, November 19, 2014
Dedicated Admin Connection (DAC) in SQL Express
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;
sp_configure 'remote admin connections', 1;
sp_configure 'show advanced options', 0;
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.