10

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

gecclesinc
  • 190
  • 1
  • 15
  • It's probably a database-level trigger `select * from sys.triggers where parent_class = 0` – Charlieface Feb 06 '21 at 19:37
  • Thanks for the idea @Charlieface, but `select * from sys.triggers` is returning no rows at all. – gecclesinc Feb 06 '21 at 20:04
  • Under which database? `sys.triggers` is per database, so you would do something like `myDb.sys.triggers` or preface with `USE mydb;` – Charlieface Feb 06 '21 at 20:05
  • @Charlieface - 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. – gecclesinc Feb 06 '21 at 20:24
  • Don't disable those. Hold it, `INSTANCENAME` and `MSSQLLocalDB` are different instances, which one are you logged in to? – Charlieface Feb 06 '21 at 20:28
  • @Charlieface - I am in INSTANCENAME. MSSQLLocalDB is where I have the problem. Hrm. I see where you are going with that I think. – gecclesinc Feb 06 '21 at 20:29
  • 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 – Charlieface Feb 06 '21 at 20:31
  • @Charlieface - 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. – gecclesinc Feb 06 '21 at 20:40
  • Not sure if it shows up there. Can you access it by setting the database as `master` in the connection parameters? – Charlieface Feb 06 '21 at 20:51
  • @Charlieface - There are no databases listed when I go to Options. If I 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: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476 ------------------------------ – gecclesinc Feb 06 '21 at 20:54
  • @Charlieface - 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. – gecclesinc Feb 06 '21 at 20:57
  • @Charlieface - I also tried with `Database=master` in the additional parameters – gecclesinc Feb 06 '21 at 21:04
  • 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](https://learn.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-ver15) then re-attach your DBs – Charlieface Feb 06 '21 at 21:12
  • @Charlieface - I am actually in the process of reattaching all databases to the SQLEXPRESS instance. I already uninstalled and reinstalled SQL on this computer entirely. – gecclesinc Feb 06 '21 at 21:20
  • @Charlieface - 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. – gecclesinc Feb 06 '21 at 21:38
  • Uninstall and reinstall LocalDB then. That will give you a new master DB – Charlieface Feb 06 '21 at 21:39
  • @Charlieface - 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` – gecclesinc Feb 06 '21 at 21:45
  • My experience is IDENTICAL. Same error. Uninstall LocalDB. Reinstall LocalDB. Same error. Can't figure out the cause let alone the solution. – Jerry Nixon Apr 05 '21 at 22:10

2 Answers2

37

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:

enter image description here

Jerry Nixon
  • 31,313
  • 14
  • 117
  • 233
  • 4
    Your post about these SqlLocalDB stop MSSQLLocalDB -k SqlLocalDB delete MSSQLLocalDB SqlLocalDB create MSSQLLocalDB -s was the solution. Thank you so much. – Ola_leke Dec 22 '21 at 12:21
  • 1
    Thanks for posting the solution! – Johan B Jul 12 '22 at 18:37
  • 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. – mbx Apr 19 '23 at 11:56
  • Forth time now, and still no Idea what triggers this issue – mbx Jun 12 '23 at 08:40
  • This worked for me, on Windows 11, with localdb version 15.0.4153.1 – Kirkaiya Jul 06 '23 at 17:26
0

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.
  1. If the log files are different, the procedure won't work
  2. Create the offending path (e.g. C:\Users\USER1\AppData\Local\Temp\VS11CodeIndex in the example above)
  3. 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)
  4. Now you should be able to login with SSMS
  5. Navigate to Server Objects\Triggers
  6. Delete the offending triggers. As of today, we have these two
  • Trigger_Repository_Microsoft.VsCodeIndex_Drop
  • Trigger_Repository_Microsoft.VsCodeIndex_Repository.Item_Logon_SetSecurityClaims
  1. Delete the fake database
Yennefer
  • 5,704
  • 7
  • 31
  • 44