How to fix "SqlException: Logon failed for login due to trigger execution" on my Windows PC?

Question

I created a Service-based Database in Visual Studio. I have been using this database for over a year. I changed my password for my Windows login and now I cannot connect to my database anymore. When I try to refresh my data connection in Server Explorer, I get the following error:

I get the same error if I try to create a new Service-base Database in Visual Studio or if I try to run my application in the Visual Studio debugger.

How do I resolve this issue?

Answer 1

You did not say that you are using LocalDB. LocalDB does not have an admin connection. (Well, the errorlog suggests you can get one with help of a trace flag, but I wasn't able to connect anyway.)

However, you can do this:

1. First stop your localdb instance: sqllocaldb stop MSSQLLOCALDB.
2. Next open the registry editor and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15E.LOCALDB\MSSQLServer. Note that the number 15 depends on your version of SQL Server. 15 is SQL 2019.
3. Here create a new key Parameters.
4. In Parameters create new string value SqlArg0 with the value -f. This sets SQL Server to start in minimal mode.
5. Start your localdb instance sqllocaldb start MSSQLLOCALDB.
6. Connect with SSMS in the normal fashion and run the query: SELECT * FROM sys.server_triggers
7. Run the command DROP TRIGGER triggername ON ALL SERVER, where you got triggername from the previous step.
8. Stop the localdb instance again.
9. In RegEdit delete SqlArg0 and Parameters.
10. Start you localdb instance again.

Everything should now be back to normal.

Kudos to Solomon Rutzky from whom I learnt the registry hack from this answer on Stackoverflow: https://dba.stackexchange.com/questions/200499/enabling-admin-connection-on-sql-server-express-to-fix-logon-trigger

Answer 2

due to trigger execution

Seems there is a (faulty) logon trigger on the SQL Server which blocks your logon attempts.

Only way around is to use a DAC = "Dedicated Admin Connection" to logon without trigger execution and then fix/drop that trigger; see

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-ver16

Answer 3

The issue is now fixed.

I deleted and re-created MSSQLLocalDB using the steps given in the following link: 

c# - Logon failed for login due to trigger execution - Stack Overflow

 

Windows PowerShell

Copyright (C) Microsoft Corporation. All rights reserved.

Try the new cross-platform PowerShell https://aka.ms/pscore6

PS C:\Users\tom.jefferson> sqllocaldb info

MSSQLLocalDB

ProjectModels

PS C:\Users\tom.jefferson> sqllocaldb delete "MSSQLLocalDB"

LocalDB instance "MSSQLLocalDB" deleted.

PS C:\Users\tom.jefferson> sqllocaldb create "MSSQLLocalDB"

LocalDB instance "MSSQLLocalDB" created with version 15.0.4153.1.

PS C:\Users\tom.jefferson> sqllocaldb info "MSSQLLocalDB"

Name:               MSSQLLocalDB

Version:            15.0.4153.1

Shared name:

Owner:              MKS\tom.jefferson

Auto-create:        Yes

State:              Stopped

Last start time:    7/12/2023 11:48:02 AM

Instance pipe name:

PS C:\Users\tom.jefferson>

 

 

 

 

 

Microsoft Windows [Version 10.0.19044.2965]

(c) Microsoft Corporation. All rights reserved.

 

• // This command has worked in the past and is now working again

C:\Users\tom.jefferson>sqlcmd -S "(localdb)\MSSQLLocalDB"

1> select name from sys.databases

2> go

name

---

master

tempdb

model

msdb

 

(4 rows affected)

1> exit

 

• // These commands specified in the documentation referenced by the link in your answer still all fail with the same error as before (even though my issue is fixed now)

C:\Users\tom.jefferson>sqlcmd -S "admin:(localdb)\MSSQLLocalDB"

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

 

C:\Users\tom.jefferson>sqlcmd -A

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

 

C:\Users\tom.jefferson>sqlcmd -A -d master

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF]. .

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..