Fixing a Microsoft SQL Server LocalDB trigger error

This is a note on how I fixed a relatively minor issue with Microsoft SQL Server Express LocalDB. The solution was a bit difficult to find. Therefore I'm writing this in the hope that it will be found by other people who have the same problem and Google the error message text. This is based on a StackOverflow answer by Yennefer. I've added a bit more detail and an alternative way to delete the triggers. I'm leaving out most of my mistakes, several dead ends, etc.

I'm certainly no expert on anything in this document. I'm just sharing what worked for me.

The error

When I opened Microsoft's Volume Activation Management Tool (VAMT) and tried to connect to my LocalDB instance as usual, I got this error:

Volume Activation Management Tool has encountered an error

Logon failed for login 'HOSTNAME\Username' due to trigger execution.
Changed database context to 'VAMT'.
Changed language setting to us_english.

And later on, once I got sqlcmd working, I got the same error from that as well:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Logon failed for login 'HOSTNAME\Username' due to trigger execution..

I hadn't made any changes to the database or LocalDB setup, so I had no idea why this would be happening. Searching for the error message text eventually led me to this question on StackOverflow. One of the answers suggested deleting the LocalDB instance, but I was worried about data loss and so decided to leave that as my last resort. The other answer turned out to describe the exact cause of the problem and offer a solution with ~no risk of data loss. I could do the first part, but I didn't have SSMS installed and didn't want to install it. So I tried to use sqlcmd...

`sqlcmd`

If you don't have sqlcmd (or you just want to update it), it's available on some Microsoft page. There's a Go version, which might be what you're supposed to use these days, but I didn't bother with it.

Direct links to the sqlcmd installer version 15.0.4298.1 (April 7, 2023):

ODBC Driver 17

When I ran first ran sqlcmd, I got this error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server :  Data source name not found and no default driver specified.

This error message is especially unhelpful. It sounds like it's using ODBC Driver 17, but it turned out that's actually what I was missing. I had version 18 installed, but apparently sqlcmd needs 17 specifically. You can download it from some Microsoft SQL Server documentation page.

Direct links to ODBC Driver version 17.10.5.1 (October 10, 2023):

Stopping LocalDB

I had to stop LocalDB to get access to the database files I wanted to copy. So I tried to cleanly shut down the instance by running SqlLocalDB stop MSSQLLocalDB, which produced this vague error:

Stop of LocalDB instance "MSSQLLocalDB" failed because of the following error:
Unexpected error occurred inside a LocalDB instance API method call. See the Windows Application event log for error details.

When I went to Event Viewer to see what it was talking about, I saw a bunch of stuff like this:

Windows API call LogSqlDiagRec returned error code: 0. Windows system error message is: The operation completed successfully.
Reported at line: 3884. [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english.

Windows API call LogSqlDiagRec returned error code: 0. Windows system error message is: The operation completed successfully.
Reported at line: 3883. ODBC returned -1, 01000, 5701:

So, not very enlightening. I think it's due to the same trigger issue though.

I decided that maybe I just needed to use a bit more force. The -k option to SqlLocalDB stop "kills LocalDB instance process without contacting it". After figuring out the slightly odd argument/option order, I successfully stopped the instance with SqlLocalDB stop MSSQLLocalDB -k.

You can run SqlLocalDB info MSSQLLocalDB to check whether the instance is still running. Once it said State: Stopped, I was able to copy the files I needed.

Finding the missing database filename

The LocalDB error logs are in %LOCALAPPDATA%\Microsoft\Microsoft SQL Server Local DB\Instances\MSSQLLocalDB. The most recent log should be named error.log; for me, it was the most recently modified file in that directory.

The relevant part was right at the end:

<date> <time> spid62      Error: 17204, Severity: 16, State: 1.
<date> <time> spid62      FCB::Open failed: Could not open file C:\Users\<username>\AppData\Local\Temp\VS11CodeIndex\Microsoft.VsCodeIndex_<uuid>.mdf for file number 0.  OS error: 2(The system cannot find the file specified.).
<date> <time> spid62      Error: 5120, Severity: 16, State: 101.
<date> <time> spid62      Unable to open the physical file "C:\Users\<username>\AppData\Local\Temp\VS11CodeIndex\Microsoft.VsCodeIndex_<uuid>.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
<date> <time> Logon       Error: 17892, Severity: 20, State: 1.
<date> <time> Logon       Logon failed for login 'HOSTNAME\Username' due to trigger execution. [CLIENT: <named pipe>]

If you don't see something like this anywhere in the logs, you probably have a different problem.

The filename you need appears in both the FCB::Open failed: Could not open file and Unable to open the physical file lines.

There's a bunch of other stuff in that directory, including .mdf and .ldf files you might be able to use as replacements in this process. (I haven't tried that.)

Copying the database files

First, go to %LOCALAPPDATA%\Temp and create the directory VS11CodeIndex. (It didn't exist for me. If it already exists for you, that's probably fine I guess.)

Next, find another pair of database files (.mdf and .ldf). I used the VAMT database (VAMT.mdf and VAMT_log.ldf), which was located in %USERPROFILE% for me. Copy both files to the directory you created, %LOCALAPPDATA%\Temp\VS11CodeIndex.

Rename the copied files to match the expected filenames. The log contains the exact filename you need for the .mdf file. Take that .mdf filename, replace .mdf with _log.ldf, and that's the .ldf filename. Don't forget the _log part. For example, if you had Microsoft.VsCodeIndex_abc.mdf, the other file would be Microsoft.VsCodeIndex_abc_log.ldf.

Starting LocalDB

Once the database files are in place, restart the LocalDB instance.

I ran SqlLocalDB start MSSQLLocalDB, and... for once, it just worked. No problems here.

Deleting the triggers

Connect to the database with:

sqlcmd -S "(localdb)\MSSQLLocalDB"

To list the triggers, run:

SELECT * FROM sys.server_triggers;
GO

Within the highly wrapped table, I spotted the two "VsCodeIndex" triggers that I knew were the problem: Trigger_Repository_Microsoft.VsCodeIndex_Repository.Item_Logon_SetSecurityClaims and Trigger_Repository_Microsoft.VsCodeIndex_Drop.

Delete them with:

DROP TRIGGER [Trigger_Repository_Microsoft.VsCodeIndex_Drop] ON ALL SERVER;
DROP TRIGGER [Trigger_Repository_Microsoft.VsCodeIndex_Repository.Item_Logon_SetSecurityClaims] ON ALL SERVER;
GO

If you forget the square brackets, you'll get this error:

Msg 1094, Level 15, State 1, Server hostname\LOCALDB#xxxxxxxx, Line 1
Cannot specify a schema name as a prefix to the trigger name for database and server level triggers.

To make sure the triggers are gone, you can run this again:

SELECT * FROM sys.server_triggers;
GO

When I saw (0 rows affected), I knew the battle was over.

The End

Now you can use VAMT again. The copied database files are still there. I may just leave them for now.

Notes

I ended up doing some of this while being logged in as sa, which I had enabled during previous efforts to delete the triggers. I'm not sure if that was necessary, but if you run into permission issues you can try that.

throwaway96/localdb-rm-triggers.md