Sql-server – SQL Server configuration manager – New alias grayed out

Summarizing what we discovered in the chat.

When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication. Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.

In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM. Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.

References

Understanding Kerberos Keys

Registering an SPN

It's a little tricky with multiple IP addresses and different ports when configuring SQL Server. The most important bit, is that the whole configuration has to be done with the SQL Server Configuration Manager.

1. Open up the SQL Server Configuration Manager with Runas Administrator...
2. Navigate through the SQL Server Network Configuration branch and you should find two sub-branches for ...
   a. Protocols for NAMED_INSTANCE_1
   b. Protocols for NAMED_INSTANCE_2

Repeat the following steps for each instance:

3. Click on the Protocols for NAMED_INSTANCE_n branch and in the right pane right-click on the TCP/IP Protocol Name entry.
4. Open up the properties.
5. In the Protocol register verify the following:
   a. Enabled = Yes
   b. Listen All = No
6. Switch to the IP Addresses register
7. You should find sections in the form of IPn for each of your IP addresses 10.10.1.1 and 10.10.1.2. This is the tricky bit: You are already in an instance and want to configure only one IP address for this instance.
8. In the IPn section corresponding to the IP address for this instance check the following:
   a. Active = Yes
   b. Enabled = Yes
   c. IP Address = 10.1.0.n
   d. TCP Dynamic Ports = leave empty
   e. TCP Port = 1433
9. Scroll down to the IPAll section
10. Verify the following:
    a. TCP Dynamic Ports = leave empty
    b. TCP Port = leave empty
11. In all other IPn sections ensure the following:
    a. Enabled = Yes
    b. Active = No (unless you want to communicate with the instance via the IP configuration settings in the section).

Repeat from step 3. for each instance.

You have to restart the instance and then it will only pick up on the IP address and port configured.

We have SQL Servers with up to 10 instances configured this way, but we leave the port at 1433 so that each instance will pick up on the default port. This allows for easier firewall configuration.

Good luck.