SqlDependency.Start throws NRE against Azure SQL Database

[ascott18]

Describe the bug

Calling SqlDependency.Start against an Azure SQL Database produces a NRE, rather than SQL.SqlDependencyDatabaseBrokerDisabled()

This is because the query to detect the service broker returns zero rows on Azure SQL, rather than a single row with a boolean. SqlDependencyListener is casting (bool)null.

Exception message:
System.NullReferenceException: 'Object reference not set to an instance of an object.'
Stack trace:
   at System.Runtime.CompilerServices.CastHelpers.Unbox(Void* toTypeHnd, Object obj)
   at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlDependencyListener.cs:line 103
   at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlDependencyListener.cs:line 1592
   at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlDependencyListener.cs:line 1528
   at Microsoft.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlDependency.cs:line 489
   at Microsoft.Data.SqlClient.SqlDependency.Start(String connectionString, String queue) in /_/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlDependency.cs:line 394

To reproduce

string connectionString = "<any azure sql database>"
SqlDependency.Start(connectionString);

Expected behavior

A deliberate InvalidOperationException is thrown, not an unhandled NRE.

Further technical details

Microsoft.Data.SqlClient version: 3.0.0
.NET target: 5.0
SQL Server version: Azure SQL
Operating system: Windows 10

[JRahnama]

@ascott18 I never got to the exception you mentioned with Azure SQL.

Below is exception message

and this is the trace:

I tried this on Managed and native SNI. Am I missing something?

[ascott18]

I get zero rows when I execute the query in this code:

SqlClient/src/Microsoft.Data.SqlClient/netcore/src/Microsoft/Data/SqlClient/SqlDependencyListener.cs

Lines 96 to 106 in 2c2f100

	_com = new SqlCommand()
	{
	Connection = _con,
	// Determine if broker is enabled on current database.
	CommandText = "select is_broker_enabled from sys.databases where database_id=db_id()"
	};
	if (!(bool)_com.ExecuteScalar())
	{
	throw SQL.SqlDependencyDatabaseBrokerDisabled();
	}

The fact that it is returning zero rows is what triggers the error. This is on the Basic tier, if that matters at all.

I see in your call stack that it is making it to CreateQueueAndService, which is ~20 lines of code after where I'm getting my exception.

[JRahnama]

@ascott18 my test on SSMS is returning

let's try this:
the _com.ExecuteScalar() is suppose to return true or false (in SSMS 1 or 0), however in your case it does not exist.

db_id() returns the database ID of the current database and in the image you provided it is not 0 or 1.

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled] 
 ,[service_broker_guid]
FROM [sys].[databases] 
GO

if you get the value 1 it means the broker is enabled for the related database. otherwise you can try:

USE [master]
GO
ALTER DATABASE [DatabaseName] SET NEW_BROKER
GO

The only change to cover scenarios like this would be changing:
if (!(bool)_com.ExecuteScalar()) to if (!(bool)_com.ExecuteScalar() || _com.ExecuteScalar() is null) which in doubt about it.

However I was not able to replicate the issue as you have it, hence were not able to test it.

I will seek other team members opinion and will get back to you soon. Meanwhile please let me know the results from above suggestion.

[ErikEJ]

Lack of user rights?

[ascott18]

It appears that this is because db_id() does not work as expected on Azure SQL. See remarks at https://docs.microsoft.com/en-us/sql/t-sql/functions/db-id-transact-sql?view=sql-server-ver15#remarks.

Query executed as the server admin user.

Also of note, @JRahnama the queries you provided contain USE [master], but you cannot use USE on Azure SQL Database. I want to make sure we're on the same page - I'm not talking about Azure SQL Managed Instance or Azure SQL VMs. I realize I did not use its full and proper name "Azure SQL Database" in my original post.

Your proposed change also does not work because the cast will still fail before the is null check is reached. However, it looks like the root issue here is that the query itself is flawed, and that db_id() cannot be relied on.

[Wraith2]

The code should be resistant to the query not returning results so I don' tthink ExecuteScalar() is appropriate, it will also box a bool which generally offends me. It would be better to use ExecuteReader() and handle having no rows as a failure to find the broker being enabled and then throw as not supported.

Separately the azure behaviour of DB_ID() is interesting and probably means that the query being used needs to be changed to avoid the use of the function and to join on sys.databases.

[ascott18]

I also just noticed that Azure SQL Database is reporting 1 for is_broker_enabled, which is very strange since service broker is very clearly listed as unsupported: https://docs.microsoft.com/en-gb/azure/azure-sql/database/features-comparison

[JRahnama]

I also just noticed that Azure SQL Database is reporting 1 for is_broker_enabled, which is very strange since service broker is very clearly listed as unsupported: https://docs.microsoft.com/en-gb/azure/azure-sql/database/features-comparison

That made me confused as well. I will start testing different scenarios to see if changing the query is better or changing ExecuteScalar to ExecuteReader or maybe both! I will update you soon.

[JRahnama]

@ascott18 can you check your code with this artifact and let me know if the issue is solved or not?

[ascott18]

@JRahnama I'm not sure how I'm supposed to consume that - there's no indication that there's anything to download on that page.

However, I can say from looking at the diff that this only half fixes the issue.

The issue is not that db_id() returns null (it doesn't), but rather that db_id() returns a nonsensical value on Azure SQL Database. It might be a value that doesn't match any row in sys.databases, or it might be the correct row, or it might point to a totally different database than the current connected database.

As @Wraith2 mentioned, the query itself probably needs to be reworked to not use db_id(), and also be reworked to account for the fact that Azure SQL Database incorrectly reports is_broker_enabled=1 for Azure SQL Database, despite service broker being an unsupported feature on Azure SQL Database.

[Wraith2]

Use this link https://dev.azure.com/sqlclientdrivers-ci/7540d8a3-c1bc-4428-a007-d4b50b7762f4/_apis/build/builds/39534/artifacts?artifactName=Nuget&api-version=6.0&%24format=zip