How to connect to SQLServer when a logon trigger error occurs

If you create a logon trigger with some bad code in the trigger, you will get an error similar to the displayed one when you try to log on.

If you create a logon trigger with some bad code in the trigger, you will get an error similar to the displayed one when you try to log on.

Error:




If the logon trigger cannot be properly executed, the logon will fail.

For example, if this trigger is created, you can design the following code to achieve the purpose of failure.
The Code is as follows:
Create trigger BadLogonTrigger ON ALL SERVER FOR LOGON
AS
BEGIN
Insert into BadDB. dbo. SomeTable VALUES ('test ');
END;
GO

No database is called BadDB, which means that no table is named SomeTable in BadDB. Therefore, any normal attempt to log on to the server will fail because the trigger involves a non-existent object. To correct this problem, you also need:
Use an existing connection to establish a connection. This connection has the appropriate permissions.
Use a dedicated administrator connection (DAC) to connect to SQL Server.
If you can delete a trigger or make the trigger unavailable for an existing connection, use the existing connection to correct the problem. However, in some cases, your connection does not have this function, so you need to rely on the dedicated administrator to connect.
By default, this dedicated administrator connection can only be used on the local server. This means that you need to log on to the local computer or use another method such as remote desktop to connect. Once you log on, you can use SQLCMD or SSMS.
If you use SQLCMD, You need to specify A-A switch through the dedicated administrator connection. If you use SSMS to connect, you must specify ADMIN: Before the server name to connect.


This is because SQL Server uses a dedicated administrator connection to minimize connection checks and resources. When one or more processes consume one SQL Server and cause abnormal logon, this method will give the database administrator a "backdoor ". When using a DAC to connect, SQL Server does not execute any logon trigger. Therefore, you can use DAC, and you will not be blocked by this bad trigger. If necessary, you can disable or delete the trigger.
For example, once a DAC is connected, I can execute the following command to completely get rid of this trigger:
The Code is as follows:
Drop trigger BadLogonTrigger on all server;
GO