health check - asp.net core api healthcheck

Question

I'm adding a few healthchecks to my netcore website. One of them is checking whether the connection string is pointing to a database that has the support for in memory tables activated (essentially if the filegroup was created with the CONTAINS MEMORY_OPTIMIZED_DATA flag).

The healthcheck is det to use this query:

IF((SELECT COUNT(1) FROM sys.filegroups FG
JOIN sys.database_files DF
ON FG.data_space_id = DF.data_space_id
JOIN sys.master_files MF
ON DF.file_id = MF.file_id
JOIN sys.databases DB
ON DB.database_id = MF.database_id
where FG.type = 'FX'
and DB.name ='MyDB')>0)
BEGIN
SELECT 1
END
ELSE
BEGIN
RAISERROR ('Memory file group not set',  
18, -- Severity,  
-1); -- State);
END

And registered via:

.AddSqlServer(connectionString: connString,
    healthQuery: myHealthQuery, name: HealthCheckNames.InMemoryState)

The query does return 1, without any error. I've set up a profiler to run through it, and no error is thrown (and I can see the Select 1 being returned).

Still, that healthcheck returns unhealthy.

Any idea why?

question from:https://stackoverflow.com/questions/65617189/asp-net-core-api-healthcheck

Answer 1

It turns out it was a permission issue. The connection string's user was missing a permission (the VIEW ANY DEFINITION one), which I'm not willing to assign.

I solved it by skipping the join to the sys.master_files table and by using a like operator

IF((SELECT Count(1) FROM sys.filegroups FG
JOIN sys.database_files DF
ON FG.data_space_id = DF.data_space_id
where FG.type = 'FX'
and Df.name  LIKE 'MyDB%') > 0)
BEGIN
    SELECT 1
END
ELSE
BEGIN
    RAISERROR ('Memory file group not set',  
        18, -- Severity,  
        -1); -- State);
END