SQL Server - Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name

This issue was tracked down to two instances of SQL on the same server with an IP each. The instances were configured that each instances had port 1433 on their respective IP. Problem occurred that SQL default instance was referenced using the hostname for this example sql01. While SQL instance SQL01\Inst02 was referenced by custom domain for example sql02.domain.local. This can be issue with two or more named instances of SQL also.

Problem

Hostname for the server by default registered all of the IPs to DNS as per settings on network settings. When referencing instance A by hostname, DNS will round robin select those IPs to return for the hostname. While running a policy check to that server you might get the error below.

Attempt to retrieve data for object failed for Server %

Invalid Urn filter on server level: filter must be empty, or server attribute must be equal with the true server name

 

Solution 1 – Hostname & Custom Domain

1. Go to Network settings and IP v4 properties
2. Uncheck the register this connection’s addresses in DNS
3. Open DNS mmc and remove old A records for that SQL01
4. Create new A record for the SQL01\MSSQLSERVER
5. Create new A record for the SQL01\Inst02 for SQL02.domain.local
6. Repeat for other named SQL instances

 

Solution 2 – Custom Domains

1. Add enough IPs for the number SQL instances
2. Create new A record for the SQL01\MSSQLSERVER for SQL01.domain.local
3. Create new A record for the SQL01\Inst02 for SQL02.domain.local
4. Repeat for other named SQL instances

 

Solution 3 – Use all Named SQL instances by Hostname

There is a few good reasons you might need to use the hostname to connect to SQL Server, for this example it was SQL01. One of the main issues with using custom domains to access SQL Server is applications that do not support TCP\IP connections and only support Shared Memory. I’ll post another examples of connection types shortly. Shared Memory requires your connection to be the original hostname that SQL was installed with.

Determine what your Server name is below:

select @@servername

To use named SQL instances on the same hostname with one or more IPs the bindings must be bound to same IPs to avoid the issue above. Verify your hostname in DNS matches the IPs bound in SQL Configuration Manger.