How to connect to SQLServer when a logon trigger error occurs

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.