Sql windows authentication Login Issue

Question

Hello I used to login in my local db normally however now irecive the below error what should i do ?
please Advice

Answer 1

It seems that you have installed a logon trigger that does not work as intended.

You can get out of the situation this way. First stop the instance:

sqllocaldb stop MSSQLLOCALDB  

Then start Regedit and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15E.LOCALDB\MSSQLServer\Parameters. In the right pane, right-click and select New->String value. The name should be SQLArg0. For the value enter -f. This option says that you want to start SQL Server with minimal configuration.

Now start the instance:

sqllocaldb start MSSQLLOCALDB  

Connection should work at this point. Note that you may still see a message box saying that the connection failed. This due to that SSMS makes two connections. One for the query window, and one for intellisense. Since -f puts the server into single-user mode, the intellisense connection fails.

Once in a query window, find the name of the logon trigger by running

SELECT * FROM sys.server_triggers

Drop the offending trigger with

DROP TRIGGER <triggername> ON ALL SERVER

Or if you to retain the trigger to correct it:

DISABLE TRIGGER <triggername> ON ALL SERVER

Once you have completed this, stop the localdb instance again, and delete SQLArg0 from the registry and then start LocalDB again.

Answer 2

Hi @NALB

Logon failed for login <Login Name> due to trigger execution.

This error is raised when a logon trigger code cannot execute successfully. Logon Triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.

The problem could occur if there is an error when executing trigger code for that specific user account. Some of the scenarios include:

• The trigger tries to insert data into a table that does not exist.
• The login does not have permissions to the object that is referred to by the logon trigger.

You could choose the resolutions depending on the scenario you are in.

• Scenario 1: You currently have access to an open session to SQL Server under an admin account.
• Scenario 2: You do not have any current session that is open under admin privileges, but Dedicated Administrator Connection (DAC) is enabled on the SQL Server.
• Scenario 3: You neither have DAC enabled on your server nor have an existing admin session to SQL Server.

Please refer to this doc for detailed solutions.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.