SqlClient: SqlDependency.Start throws NRE against Azure SQL Database

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

About this issue

Original URL
State: closed
Created 3 years ago
Comments: 25 (15 by maintainers)

Most upvoted comments

Yeah, I was thinking something like this unless there’s a better way:

SELECT CASE 
	WHEN SERVERPROPERTY ('EngineEdition') = 5 THEN 0 
	ELSE (select is_broker_enabled from sys.databases where database_id=db_id()) 
END

ascott18 on Oct 7, 2021

related to #40 ?

ErikEJ on Oct 7, 2021

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 on Sep 21, 2021

Lack of user rights?

ErikEJ on Sep 14, 2021