Unable to connect to (localdb)\MSSQLLocalDB - Due to trigger execution

Question

I have a SQL Server Express instance on my machine that had been working fine and I have no idea what changed. I get this error now:

Logon failed for login 'myusername' due to trigger execution.
Changed database context to 'master'.

I have tried to follow this: https://dba.stackexchange.com/questions/218811/logon-failed-for-login-due-to-trigger-execution

So there are many ideas about just deleting the offending trigger and you can connect with the Dedicated Admin Connection (DAC) if needed to do this. I am not having to connect through the DAC because I can connect to sa using MACHINENAME\INSTANCENAME just fine. My problem is only when connecting with (localdb)\MSSQLLocalDB. For some reason LocalDB causes this error only.

When I log in with 'sa' and run this:

SELECT * FROM sys.server_triggers;

There are no rows returned. It is an empty result. So, where is the trigger that I need to disable? Is LocalDB not connecting to the same instance as my SQL Server Express instance or something? So, LocalDB connects to something else that has a trigger problem whereas connecting by MACHINENAME\INSTANCENAME has no triggers. I would think that the 'sa' login would be able to see triggers if they were there.

EDIT 1: OK. I have found all of these triggers now in the msdb database. I have checked all the databases and this is the only one that had any triggers.

syscollector_collection_item_parameter_update_trigger

syscollector_tsql_query_collector_delete_trigger

trig_targetserver_insert

syspolicy_update_system_health_state

sysmanagement_delete_shared_server_group_trigger

syspolicy_execution_trigger

trig_notification_ins_or_upd

trig_notification_delete

syspolicy_validate_events

syspolicy_insert_job_create_trigger

syspolicy_update_job_update_trigger

syspolicy_insert_policy_trigger

syspolicy_update_policy_trigger

syspolicy_delete_job_delete_trigger

syspolicy_instead_delete_policy_trigger

syspolicy_insert_condition_trigger

syspolicy_for_update_condition_trigger

trig_sysoriginatingservers_delete

syspolicy_after_update_condition_trigger

trig_sysjobs_insert_update

syscollector_collection_set_is_running_update_trigger

trig_sysmail_profile

trig_sysschedules_insert_update

trig_principalprofile

trig_sysmail_account

trig_sysmail_profileaccount

trig_backupset_delete

trig_sysmail_profile_delete

trig_sysmail_servertype

trig_sysmail_server

trig_sysmail_configuration

trig_sysmail_mailitems

syspolicy_insert_target_set_level_trigger

trig_sysmail_attachments

syspolicy_update_target_set_level_trigger

trig_sysmail_log

syspolicy_insert_target_set_trigger

syspolicy_delete_target_set_trigger

Answer 1

My solution, from an Admin Visual Studio CMD prompt:

SqlLocalDB stop MSSQLLocalDB -k
SqlLocalDB delete MSSQLLocalDB
SqlLocalDB create MSSQLLocalDB -s

Aside: Prior to this, I upgraded to 2019 using the SQL Express installer.

Finally, my RegEdit for your reference:

Answer 2

When it happens on our machines, this is usually due to Visual Studio VsCodeIndex database being removed before removing the trigger.

Since DAC does not work, we use this method to regain access to our instances without losing any data:

1. Close all Visual Studio/Code instances to prevent race conditions on the database
2. Locate the installation directory, which should be something like C:\Users\USER\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB
3. Open the most recent log file and verify that SQL Server is indeed trying to open a non existing database. The log file near the end should be like this

2023-05-22 16:03:52.89 spid51      Error: 17204, Severity: 16, State: 1.
2023-05-22 16:03:52.89 spid51      FCB::Open failed: Could not open file C:\Users\USER1\AppData\Local\Temp\VS11CodeIndex\Microsoft.VsCodeIndex_2f1ff123_0949_4b08_88ae_5cda46820c33.mdf for file number 0.  OS error: 3(Het systeem kan het opgegeven pad niet vinden.).
2023-05-22 16:03:52.89 spid51      Error: 5120, Severity: 16, State: 101.

3. If the log files are different, the procedure won't work
4. Create the offending path (e.g. C:\Users\USER1\AppData\Local\Temp\VS11CodeIndex in the example above)
5. Copy an existing database (an empty one is sufficient, we usually have a few around) in this folder and name it properly. In the example above, MDF is Microsoft.VsCodeIndex_2f1ff123_0949_4b08_88ae_5cda46820c33.mdf and the LDF is Microsoft.VsCodeIndex_2f1ff123_0949_4b08_88ae_5cda46820c33_log.ldf (be careful here, the file name ends with _log)
6. Now you should be able to login with SSMS
7. Navigate to Server Objects\Triggers
8. Delete the offending triggers. As of today, we have these two

• Trigger_Repository_Microsoft.VsCodeIndex_Drop
• Trigger_Repository_Microsoft.VsCodeIndex_Repository.Item_Logon_SetSecurityClaims

8. Delete the fake database