Introduction
Sometimes when making changes to SQL Server, you may end up in a situation where you cannot connect to the server anymore. For example, if you create an invalid logon trigger, all connections may fail. In such a situation, you receive an error:
Login failed for login … due to trigger execution
So how to make corrections if you cannot connect to the database in the first place. SQL Server has a concept called DAC, Dedicated Admin Connection. This is a special type of connection which for example bypasses logon triggers. You can have only one active admin connection at a time.
Making an admin connection is basically straight-forward but you may still find yourself in a situation where the connection isn’t accepted. In such a case, use the following check list.
Connecting using SSMS
Using SSMS (SQL Server Management Studio), do not try to connect using the standard connection from object explorer. With this connection type, SSMS uses several simultaneous connections which isn’t possible when using DAC.
Initiate the connection for example from File menu using New / Database Engine Query. This ensures that you take only single connection.
When prompted for connection details, in the server name, use format:
ADMIN:[ServerName], for example ADMIN:MyDbServer
or with named instances:
ADMIN:[ServerName]\[InstanceName], for example ADMIN:MyDbServer\MyInstance
SQL Browser is Not Running
If you receive an error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found ….
Ensure that SQL Browser is running. For example, in case of Express Edition, the SQL Browser is disabled by default.
- Open the Computer Management.
- Expand Services section.
- Locate SQL Server Browser.
- Both enable the service and start it and try the connection again.
DAC Port Not Active
By default, SQL Server Express Edition does not listen on DAC port so even if SQL Server Browser is up and running, you may still end up in connection failing. The fix is to use trace flag 7806.
Open SQL Server Configuration Manager. In Windows 8 and 10, this may be hard to find since you should locate it from Windows folders. Much easier way is to press Windows key + R to bring up the Run window and then type for example SQLServerManager13.msc to it and press ok. This opens configuration manager for SQL Server 2016.
For more detailed information, refer to SQL Server Configuration Manager.
Once the configuration manager is open, select the SQL Server instance and open Properties window. Go to Startup Parameters tab and add –T7806 to the parameter list.
Now restart the SQL Server instance and try the connection again.
Still Not Working, Use IP Address and Port
If you still cannot connect to the server, let’s try using IP address along with the port. To know where you need to connect to, open SQL Server ERRORLOG–file from the Log folder. The log folder is located in the instance installation folder, for example something like:
C:\Program Files\Microsoft SQL Server\MSSQL13.InstanceName\MSSQL\Log
The ERRORLOG is a plain text file so you can open it using Notepad. Now find the rows containing text “Server is listening on”. You should be able to spot both the IP address and the port the server is listening for DAC connections. For example:
...
2016-10-04 23:03:49.88 Server Server is listening on [ 127.0.0.1 <ipv4> 54153].
2016-10-04 23:03:49.88 Server Dedicated admin connection support was established for listening
locally on port 54153.
2016-10-04 23:03:49.97 spid13s SQL Server is now ready for client connections. This is an
informational message; no user action is required.
...
Now using this information, try connecting with SSMS to server using the following server name syntax
ADMIN:127.0.0.1,54153
Of course, change the address and port based on the information in your ERRORLOG.
The Last Resort, Minimal Configuration
If you’re still unable to connect to the SQL Server, the last trick is to use minimal configuration. In order to do this, go back to the SQL Server Configuration Manager and the startup options. You can remove the trace flag –T7806 but add –f parameter and restart the SQL Server instance. Now the instance is running in minimal configuration and for example logon triggers are not executed. Hopefully, you can establish the connection at least now.
When All Is Done
When you have fixed the problem, remember to undo modifications. Especially don’t leave trace flags or minimal configuration options into the startup parameters.
References
History
- 5th October, 2016: Created
