ANSI_NULLS: When indexes break a query

How often does adding an index in SQL Server break something? Let’s say it’s under normal operating conditions, no corruption, not adding unique or other constraints.

As a consultant and with 15+ years experience, it’s just not something I’ve seen very often at all. It is so rare, that even “seasoned pros” like yours truly may run into this and be surprised.

What I’m talking about here is the conflict between the ANSI_NULLS setting and certain types of indexes, that when combined, cause a query to simply error out. The error message is actually very clear and descriptive. It is even documented, which would be great, if I thought I needed to check there first!

Microsoft has a good primer here on ANSI_NULLS settings and its effect on Boolean logic. There are several people who have written detailed explanations elsewhere, but please just try to keep ANSI_NULLS ON for any new work. This story involves some legacy code that has been around for a long time and whose author is now unknown.

The Error:
DELETE failed because the following SET options have incorrect settings: ‘ANSI_NULLS’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

In this case it was a delete statement in a stored procedure that started to fail and I had just added an index on a computed column.

The Cause:
That error message pretty much said it all. A new index was added to a computed column on a table and this seemingly unrelated delete statement in a stored procedure started to fail. Which lead me to question, where exactly was this ANSI_NULLS being set incorrectly (to FALSE)?

The uses_ansi_nulls flag can be set in various places such on the table itself, on stored procedures, and various other objects.
In this case the table had uses_ansi_nulls set to 1 but the stored procedure was set to 0.

It’s important to note here that uses_ansi_nulls is not part of the stored procedure’s definition so you wont find it after the CREATE PROC. It’s part of the schema object and can be viewed through system views such as sys.sql_modules and sys.tables. It is defined when the object is created or altered based on the session’s execution setting. This can be changed explicitly via the SET ANSI_NULLS option. Most people will likely just roll with the defaults that SSMS or Azure Data Studio provides and not even realize this option is there.

The fix:
In this case I had to remove the index until such time when I can go back and test the stored procedure altered with ANSI_NULLS ON. Sure I could have just ran an ALTER PROC with the same definition with correct session options but that carries a risk of changing the semantics of code I hadn’t reviewed yet. I could imagine a scenarios where the original developer had not used “IS NULL” anywhere in the WHERE clauses and my simple change would potential change data being modified. Not something I wanted to do right now!

The lesson learned:
Check the uses_ansi_nulls settings for any objects dependent on the table where I’m about to add the index. This won’t catch ad-hoc statements but it’s a start. Beyond that, have a test environment and test plan!

The repro:
Here’s a code snippet that will show how to reproduce this error using a trivial table and computed column. Keep in mind that this is just the computed column scenario. The filtered index scenario is also just as likely and then there’s XML, Spatial, and Query Notifications (as mentioned in the error) which I haven’t tried to reproduce.

SET ANSI_NULLS ON
GO

CREATE TABLE dbo.Person
(
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
FullName AS (CONCAT(FirstName, ' ', LastName))
)
GO

CREATE INDEX IX_Person_FullName on dbo.Person(FullName)
GO

SET ANSI_NULLS OFF
GO

CREATE OR ALTER PROC dbo.PopulatePerson
as
BEGIN
	DELETE FROM dbo.Person

	INSERT INTO dbo.Person (FirstName, LastName)
	VALUES ('Harold', 'Potter')
END
GO

SET ANSI_NULLS ON
GO

EXEC dbo.PopulatePerson
GO

Addition reading / references:
SQL Docs: Indexes on Computed Columns
SQL Docs: CREATE INDEX > Filtered Indexes

SSRS and Kerberos with a Cluster

I recently came across an error involving SQL Server Reporting Services (SSRS) that I recognized immediately but still took me some time to resolve due to intricacies of this particular environment.

This involves a scenario with a Web App calling into SSRS which then connects to a separate SQL Cluster.

The admin of the Web App reported this error when calling SSRS:

An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source ‘DataSource’. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors

As usual, not a lot of details. So I went into the Report Manager web interface (from a workstation) and tested the datasource, which turned out to be using Windows integrated security. I got

Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’

I had a feeling this was Kerberos related right away. The Web App admin, agreed and mentioned adding SPNs.

After much research, I found that the needed SPNs are

  • HTTP / SSRS server, with no port
  • SQL cluster, with SQL port

In my environment the SSRS server is accessed by its machine name but if it had a separate DNS entry or was load-balanced, this would have changed the name I needed to register.

We also had multiple nodes in the SQL cluster but the report datasource used the cluster name, so it is the name that matters for the “second hop”.

The solution that worked for us was like this:

setspn -a HTTP/ReportServer mydomainsqlreportsvc setspn -a HTTP/ReportServer.mydomain.local mydomainsqlreportsvc setspn -a MSSQLSvc/SqlCluster:1433 mydomainsqlEngineServiceAcct setspn -a MSSQLSvc/SqlCluster.mydomain.local:1433 mydomainsqlEngineServiceAcct 

In this example the SSRS machine name is “ReportServer” in the “mydomain.local” domain running with a services account of “sqlreportsvc”. The multi-node SQL cluster had a cluster name of “SqlCluster” and it was running on port 1433. Names have been changed to protect the innocent.

Shout out:
I have to say Scott Stauffer’s chapter on Kerberos in SQL Server MVP Deep Dives was an excellent resource for me during the problem solving and fix of this issue.