Provide option/always to canonicalise hostname even when fully qualified when building SPN for Kerberos

[quom]

Client Operating System

Linux

Problem description

1. Expected behaviour: When an FQDN is provided for the serverName an SPN is constructed by resolving this to an IP address and doing a reverse lookup in DNS
2. Actual behaviour: DNS resolution and reverse lookup is only performed if there is no user specified SPN and the serverName provided is not fully qualified
3. Error message/stack trace: SPN cannot be found
4. Any other details that can be helpful:

An SPN is registered on the hostname of the sql server machine under the service user of the machine but not for the CNAME which corresponds to the alias (I've been told this is because its more troublesome to migrate the database to another server transparently).

Consequences of this behaviour:
When an unqualified CNAME is used as the serverName it must be canonicalised to construct the SPN (to find the correct realm). When the CNAME is provided fully qualified there is no attempt to resolve and therefore the SPN constructed is not valid. This is not so much an issue when everything is running under a single domain as the domain can be ommitted from the serverName and the DNS resolution for the connection still works correctly. When the application is running in a different domain then the CNAME cannot be resolved without being qualified with the correct domain and in this case the SPN is not constructed correctly.

I cannot see that this is expected behaviour of the SPN construction and can see two possible fixes:

• Always canonicalise the serverName when constructing the SPN - this may cause issues in systems where the PTR records are not setup correctly
• Provide an option to resolve the serverName before constructing the SPN

Is either of these options palatable or is it the recommended setup to have SPNs created for the alias serverNames too?

[peterbae]

Hi @quom, thanks for contacting us. I have a vague idea of what you're trying to describe, but I'm not clear on how the driver should behave in this scenario. Could you provide a sample scenario (with your CNAME/SPN/etc) that describes the situation above, and the expected/actual behavior of the driver currently? Thanks.

[quom]

Hi @peterbae, here's an example of how the driver behaves which I don't think is intended:
serverName=condor
CNAME=condor & condor.foo.net pointing to A record elp812.foo.net. PTR record for elp812.foo.net setup correctly
SPN=MSSQLSvc/elp812.foo.net:[email protected] (and associated others with/without realm and port)

In this scenario the driver resolved the CNAME to the A record to the IP and does a rDNS to find the host name and constructs the SPN from that - successfully matching the registered SPN.

Now, changing serverName=condor.foo.net the driver constructs an SPN like MSSQLSvc/condor.foo.net:[email protected] (which in our scenario does not work because the SPN is setup on the host that is resolved from the PTR record).

This becomes an issue where the default search domain is not foo.net because one has to use a fully qualified name in the serverName field (otherwise it won't be able to resolve an IP) but this will lead to the incorrect SPN being constructed. I believe this is unexpected behaviour because qualifying the serverName should not lead to a different SPN.

If we could agree on expected behaviour then I am happy to create a PR for it. I believe the expected behaviour is that the driver should make efforts (in all cases) to resolve the serverName and do a rDNS on it before constructing the SPN. If this fails then it can use the domain as is.

[peterbae]

Hi @quom, thank you very much for a detailed explanation. I think you should be able to use serverSpn connection property to both define a FQDN for serverName while setting the SPN to what you desire (https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver15). Could you check if this can solve your problem? Thanks.

[quom]

Hi @peterbae,
I understand that this would be possible but it defeats the point of using an alias for the database: One would have to update all references in the connection string if the database moved server. As it stands, when running in the same domain, it's possible to only have a reference to the alias in the connection string allowing easy database migration. The intention of the issue is to see if it would be possible to have this for cross-domain too.
Thanks

[peterbae]

Thanks for the explanation @quom. I'm not exactly sure if this would be a breaking change for some of the existing users, but if you could provide a PR for this, the team will test it internally to see if we can include this in the next preview. We appreciate your contribution.

[Malcolm-Stewart]

If memory serves, the SQLOLEDB provider and {SQL Server} driver (SQL 2000 time-frame) do the reverse lookup every time. However, that was changed for SQL 2005 (Provider=SQLNCLI) as a security risk due to DNS spoofing. The newer drivers concatenate the default DNS suffix to a NETBIOS name or use a fully-qualified name as-is. It is up to the app developer to get the server name correct, or if set in a config file of some sort, then it can be set by the machine admin. I suspect the JDBC behavior may follow similar reasoning.