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

Insalivate

It's probably a database-level trigger select * from sys.triggers where parent_class = 0 – Picnic

Thanks for the idea @Charlieface, but select * from sys.triggers is returning no rows at all. – Insalivate

Under which database? sys.triggers is per database, so you would do something like myDb.sys.triggers or preface with USE mydb; – Picnic

@Picnic - Ah ha! I do have some in the msdb database. I put them all in an edit in my original question. None of them have parent_class = 0. I will try disabling them temporarily and see what happens. – Insalivate

Don't disable those. Hold it, INSTANCENAME and MSSQLLocalDB are different instances, which one are you logged in to? – Picnic

@Picnic - I am in INSTANCENAME. MSSQLLocalDB is where I have the problem. Hrm. I see where you are going with that I think. – Insalivate

OK, you need to get start up LocalDB in single user, or try get a DAC connection to it. There will be a trigger on there. By the looks of the error message, it is DB level, not server level, and will be on whatever the default DB is for that login – Picnic

@Picnic - Thanks for your help. I have now seen I have other problems now. I dont appear to even have a MSSQLLocalDB instance listed in SQL Server Configuration Manager. I don't know what happened to this thing. I was just running select queries one minute then this problem the next. – Insalivate

Not sure if it shows up there. Can you access it by setting the database as master in the connection parameters? – Picnic

@Picnic - There are no databases listed when I go to Options. If I <Browse Server...> it just gives the same error message: TITLE: Browse Server for Database ------------------------------ Logon failed for login 'loginname' due to trigger execution. Changed database context to 'master'. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892) For help, click: go.microsoft.com/… ------------------------------ – Insalivate

@Picnic - I should also add that I try to connect with the DAC and when I try admin:(localdb)\MSSQLLocalDB it just acts like the server doesn't exist or is inaccessible. – Insalivate

@Picnic - I also tried with Database=master in the additional parameters – Insalivate

Don't think LocalDB has a DAC. I wonder if there is a way to start it in single-user mode. You may need to just rebuld master then re-attach your DBs – Picnic

@Picnic - I am actually in the process of reattaching all databases to the SQLEXPRESS instance. I already uninstalled and reinstalled SQL on this computer entirely. – Insalivate

@Picnic - Well, I can run my app locally again with changing to my local instance. This is not ideal since it is a shared project, but we will manage with switching the connection strings when we have to debug against our local databases. Thanks again for all your help. – Insalivate

Uninstall and reinstall LocalDB then. That will give you a new master DB – Picnic

@Picnic - What's funny is I already did that this morning. I just uninstalled SQL Server 2019 LocalDB again and reinstalled, then rebooted. Still the same login trigger error when connecting with (localdb)\MSSQLLocalDB – Insalivate

My experience is IDENTICAL. Same error. Uninstall LocalDB. Reinstall LocalDB. Same error. Can't figure out the cause let alone the solution. – Petrie

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:

Petrie

Your post about these SqlLocalDB stop MSSQLLocalDB -k SqlLocalDB delete MSSQLLocalDB SqlLocalDB create MSSQLLocalDB -s was the solution. Thank you so much. – Exploration

Thanks for posting the solution! – Qualls

That only resolves the symptom though - I'm running into this for third time now and still wonder what might have caused the corrupted state in the first place. – Cristincristina

Forth time now, and still no Idea what triggers this issue – Cristincristina

This worked for me, on Windows 11, with localdb version 15.0.4153.1 – Wickliffe

@Cristincristina were you ever able to find out the cause? – Pinchbeck

@YitzakHernandez unfortunately not, and coming just out of vacation I just ran into that issue once more without even a clue what might caused it probably a few weeks ago. – Cristincristina

it's now VS2022 17.9.5 o'clock and I am again seeing this bug... Last time I saw this was November 2023, now April 2024. This is a DST-Change bug, isn't it? – Cristincristina

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

Zito