How a misleading transaction-nesting error hid the real culprit — a denied EXECUTE permission — and how Extended Events finally surfaced it.
Intro
The context transaction which was active before entering user defined routine, trigger or aggregate "GetParts" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
Last week one of our dev teams hit this while testing an application, and the message sent us chasing the wrong problem. It blames transaction nesting. The real cause is something the message never mentions: a SQLCLR function (written in VB.NET) was calling stored procedures the login didn’t have EXECUTE permission on.
The short version of what actually happened: the function’s Try/Catch caught those permission errors, swallowed them, and returned normally — but the failed calls had already ended the transaction the function was running in. When control came back to SQL Server, @@TRANCOUNT had dropped from 1 to 0, so the engine raised 3991 and complained about transaction nesting. The permission errors that caused all of it never surfaced.
The fix was almost anticlimactic: grant the login EXECUTE on the two procedures. But I wanted to know why the original errors vanished, and that pulled me into the genuinely confusing world of SQL Server error and transaction handling. Erland Sommarskog’s series is what made something click: Error and Transaction Handling in SQL Server
What follows is a reduced repro of the problem, plus the two questions it left me with.
The setup
In the real system this call chain is buried under a lot of code, which is part of why it was so hard to see. Stripped down, the pieces are simple: an INSERT fires an AFTER INSERT trigger, the trigger selects from a CLR table-valued function, and that function calls two stored procedures the user can’t execute.
First, the VB code behind the CLR function. Two things matter. GetParts() calls dbo.DeniedProc1 and dbo.DeniedProc2 through a small helper, RunProc; and RunProc wraps each call in a Try/Catch that, on any error, simply returns its (empty) list — no rethrow.
Imports System
Imports System.Collections
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Server
Public Class Demo3991
' Runs one proc on the context connection and returns the result list.
' On ANY error it returns the (empty) list from the catch
Private Shared Function RunProc(ByVal procName As String) As List(Of String)
Dim items As New List(Of String)
Try
Using conn As SqlConnection = New SqlConnection("context connection=true")
conn.Open()
Using cmd As SqlCommand = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = procName
cmd.ExecuteNonQuery() ' EXECUTE denied -> raises 229
End Using
End Using
items.Add(procName & ":ok")
Return items
Catch ex As Exception
Return items ' <-- swallow + return the list
End Try
End Function
' CLR table-valued function
<SqlFunction(FillRowMethodName:="FillRow", _
TableDefinition:="PartName nvarchar(100)", _
DataAccess:=DataAccessKind.Read)> _
Public Shared Function GetParts() As IEnumerable
Dim parts As New List(Of String)
parts.AddRange(RunProc("dbo.DeniedProc1")) ' 229 #1 (swallowed via Return, like the moldings proc)
parts.AddRange(RunProc("dbo.DeniedProc2")) ' continues -> 229 #2 (swallowed via Return, like the panel proc)
Return parts ' returns the (now empty) list normally
End Function
Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef PartName As SqlString)
PartName = New SqlString(CStr(obj))
End Sub
End ClassCompile that into Demo3991.dll, then register the assembly and create the function:
USE MyBlogDB;
GO
EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
GO
/* Trust the DLL by hash (SQL Server 2017+, avoids signing / TRUSTWORTHY) */
DECLARE @hash varbinary(64) =
(SELECT HASHBYTES('SHA2_512', BulkColumn)
FROM OPENROWSET(BULK 'C:\temp\CLRDemo\Demo3991.dll', SINGLE_BLOB) AS f);
IF NOT EXISTS (SELECT 1 FROM sys.trusted_assemblies WHERE [hash] = @hash)
EXEC sys.sp_add_trusted_assembly @hash, N'Demo3991';
GO
/* ---- CLR TVF that swallows on error ---- */
CREATE ASSEMBLY Demo3991 FROM 'C:\temp\CLRDemo\Demo3991.dll' WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION dbo.GetParts()
RETURNS TABLE (PartName nvarchar(100))
AS EXTERNAL NAME Demo3991.[Demo3991].GetParts;
GO
Create the two procedures the function calls:
/* ---- the two procs the user is NOT granted permissions to execute ---- */
CREATE PROCEDURE dbo.DeniedProc1 AS BEGIN SET NOCOUNT ON; SELECT 1 AS x; END
GO
CREATE PROCEDURE dbo.DeniedProc2 AS BEGIN SET NOCOUNT ON; SELECT 2 AS x; END
GO
Add the table whose INSERT starts everything, the destination table, and the AFTER INSERT trigger that calls GetParts():
/* ---- table whose INSERT kicks everything off ---- */
CREATE TABLE dbo.Orders_Demo (OrderID int IDENTITY PRIMARY KEY, Note nvarchar(50) NULL);
GO
/* ---- destination table ---- */
CREATE TABLE dbo.PartsInProcess (PartName nvarchar(100) NULL);
GO
/* ---- AFTER INSERT trigger (runs inside the INSERT's transaction) ---- */
CREATE TRIGGER dbo.trg_Orders_Demo ON dbo.Orders_Demo AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.PartsInProcess (PartName)
SELECT PartName FROM dbo.GetParts();
END
GO
Finally, create a test user with INSERT on the orders table but no EXECUTE on the two procedures — exactly the misconfiguration that bit us during testing:
/*
INSERT on Orders_Demo is all that's needed; the
rest of the chain (MSTVF, CLR TVF, INSERT into PartsInProcess) is reached via
ownership chaining. EXECUTE of the two procs over the CLR context connection
is re-checked and denied -> error 229 (the CLR function opens a separate connection
with context connection=true and runs the procs through it,
which behaves like dynamic SQL — a fresh execution context that breaks the chain). */
CREATE USER dbg_demo_user WITHOUT LOGIN;
GRANT INSERT ON dbo.Orders_Demo TO dbg_demo_user;
DENY EXECUTE ON dbo.DeniedProc1 TO dbg_demo_user;
DENY EXECUTE ON dbo.DeniedProc2 TO dbg_demo_user;
GO
Reproducing the failure
Now insert a row as that user:
EXECUTE AS USER = 'dbg_demo_user';
INSERT INTO dbo.Orders_Demo (Note) VALUES ('A');
GO
REVERT;
GO
And here is what comes back:
The context transaction which was active before entering user defined routine, trigger or aggregate "GetParts" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting.
Notice what’s missing: any mention of permissions. The message points squarely at transaction nesting, so that’s where we looked — hunting for places the transaction was opened and closed unevenly. We even asked Claude for help. After an hour or two it had us weighing rewrites of the CLR code and of the large outer transaction that wraps the insert. All of it was beside the point.
The reveal: Extended Events
What finally cracked it was capturing the errors the engine actually raised, instead of the one it chose to show us. An Extended Events session on error_reported does exactly that:
IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name='Debug_Demo3991')
DROP EVENT SESSION Debug_Demo3991 ON SERVER;
GO
CREATE EVENT SESSION Debug_Demo3991 ON SERVER
ADD EVENT sqlserver.error_reported
( ACTION (sqlserver.tsql_stack, sqlserver.transaction_id, sqlserver.sql_text)
WHERE ([severity] >= 11) )
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION Debug_Demo3991 ON SERVER STATE = START;
GO
Run the insert again, then read the ring buffer:
/* ---- what the engine actually raised (expect two 229s + a 3991 per failing run) ---- */
;WITH xe AS (
SELECT CONVERT(xml, t.target_data) AS x
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON t.event_session_address = s.address
WHERE s.name = 'Debug_Demo3991' AND t.target_name = 'ring_buffer'
)
SELECT e.n.value('(@timestamp)[1]','datetime2') AS event_time,
e.n.value('(data[@name="error_number"]/value)[1]','int') AS error_number,
e.n.value('(action[@name="transaction_id"]/value)[1]','bigint') AS transaction_id,
e.n.value('(data[@name="message"]/value)[1]','nvarchar(max)') AS message
FROM xe
CROSS APPLY xe.x.nodes('//event') AS e(n)
ORDER BY event_time, error_number;
GO
There they were: two “EXECUTE permission was denied” errors (229) sitting above the 3991. Granting the user EXECUTE on both procedures made the whole thing disappear.
Why did this happen?
That fixed it, but two questions stuck with me:
- What rolled back the transaction inside the CLR function and produced the 3991?
- Why did the 229 errors never come back to me?
There is no explicit rollback inside the CLR, so what rolled back the transaction?
Erland’s series has the answer. In Part Two he sorts errors into classes by how they behave.
| Without TRY-CATCH | With TRY-CATCH | |||||||
|---|---|---|---|---|---|---|---|---|
| SET XACT_ABORT | OFF | ON | OFF | ON | ON or OFF | OFF | ON | |
| Class | Name | Aborts | Rolls Back | Catchable | Dooms transaction | |||
| 1 | Fatal errors | Connection | Yes | No | n/a | |||
| 2 | Batch-aborting | Batch | Yes | Yes | Yes | |||
| 3 | Batch-only aborting | Batch | No | Yes | Yes | No | Yes | |
| 4 | Statement-terminating | Statement | Batch | No | Yes | Yes | No | Yes |
| 5 | Terminates nothing at all | Nothing | No | Yes | Yes/No | Yes | ||
| 6 | Compilation: syntax errors | (Statement) | No | Yes | No | Yes | ||
| 7 | Compilation: binding errors | Scope | Batch | No | Yes | Outer scope only | No | Yes |
| 8 | Compilation: optimisation | Batch | Yes | Outer scope only | Yes | |||
| 9 | Attention signal | Batch | No | Yes | No | n/a | ||
| 10 | Informational and warning messages | Nothing | No | No | n/a | |||
| 11 | Uncatchable errors | Varying | Varying | No | n/a | |||
| 12 | Errors that only sets @@error | No | No | No | n/a | |||
The error here aborts and rolls back the transaction; XACT_ABORT was off; and it isn’t a compilation error, which rules out his compilation class. Fatal errors terminate the connection, and ours clearly didn’t. That leaves batch abortion with rollback — errors that, with no CATCH handler on the stack, abort execution on the spot and roll back any open transaction.
GRANT SELECT ON GetParts TO dbg_demo_user;
EXECUTE AS USER = 'dbg_demo_user';
SET XACT_ABORT OFF;
BEGIN TRAN;
SELECT * FROM dbo.GetParts(); -- DeniedProc1/2 -> 229 (swallowed) -> empty result
SELECT @@TRANCOUNT AS TranCount_StillOpen; -- expect 1 (transaction survived)
COMMIT; -- expect success
REVERT;
GO
No 3991, and no permission error surfaces either (more on that next). The transaction is still open returned from the call (no transaction rollback within the CLR) and the result set is simply empty.
Why didn’t the 229 errors come back?
Because the function ate them. Look at the catch again:
Catch ex As Exception
Return items
End Try
There’s no Throw. A caught exception that’s never rethrown doesn’t propagate, so the 229 never travels from the function up to the trigger, let alone back to us.
But swallowing the exception only hides the error; it does nothing about the side effect, which is the rolled-back transaction. When control returns to the outer INSERT and SQL Server finds the transaction it was counting on gone, it raises the only complaint it has left: 3991. And that is what costs us a few hours of troubleshooting because of the misleading error message.
Takeaway
- Don’t swallow errors, whatever your reason — users deserve to know what’s actually going on, and an error message that’s only reacting to the damage can confuse everyone.
- error_reported in Extended Events shows you everything that was raised — not just the last one left standing.
- There’s another case where 3991 is thrown and the original error never surfaces: when the error is both batch- and transaction-aborting and there’s explicit ROLLBACK inside the SQLCLR (Error and Transaction Handling in SQL Server Appendix 2: CLR Modules)
Conclusion
In my opinion, I don't like using CLR. The safest default is to reach for CLR only when T-SQL genuinely can't do the job. It isn't that CLR caused this bug — the swallowed exception and a missing GRANT did — but CLR brings extra moving parts: an assembly security model you have to get right (SAFE vs. EXTERNAL_ACCESS/UNSAFE, and signing or trusting assemblies under clr strict security), and error handling that behaves differently across the managed/T-SQL boundary. In terms of the performance, it can be faster than T-SQL for CPU-bound work, but for data-access-heavy logic, set-based T-SQL is usually the better and simpler call.
More to read:
Error and Transaction Handling in SQL Server
Cleanup code
ALTER EVENT SESSION Debug_Demo3991 ON SERVER STATE = STOP;
DROP EVENT SESSION Debug_Demo3991 ON SERVER;
DROP TRIGGER dbo.trg_Orders_Demo;
DROP FUNCTION dbo.GetParts;
DROP ASSEMBLY Demo3991;
DROP TABLE dbo.PartsInProcess;
DROP TABLE dbo.Orders_Demo;
DROP PROCEDURE dbo.DeniedProc1;
DROP PROCEDURE dbo.DeniedProc2;
DROP USER dbg_demo_user;
No comments:
Post a Comment