I stumbled across this problem the other day whilst trying to setup a new Log Shipping configuration in SQL Server. The big difference in this environment, was that this implementation had DNS records in place to route the DR (Disaster Recovery) traffic via it’s own dedicated link.
So for example, when specifying the database backup location, I was trying to use a DNS record (called SQLVM1-DR in this case), that referenced the very server (SQLVM1) that I was working from:
The Secondary server (SQLVM2) was able to see this location, but the Primary server couldn’t, I just kept getting prompted for credentials, even after filling in the correct credentials each time, and having sufficient permissions! It seems this is the default behaviour in Windows, and in the end I had to make the following changes to get this working…
- Edit the following registry key to allow other machines to use file sharing via the alias:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\lanmanserver\parameters
Add a new entry called DisableStrictNameChecking with a DWORD Value and set to it to 1
- Edit the following registry key to allow this machine to use file sharing with itself via the alias:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
Add a new entry called BackConnectionHostNames with a Multi-String Value
For the value, enter the non-FQDN alias that is to be used on the computer. For example: SQLVM1-DR
NOTE: You need to type each host name/alias on a separate line here.
- Register the Kerberos Service Principal Names (SPNs) for the alias(es), by using the setspn tool like below:
setspn -A host/<ALIAS_NAME> COMPUTER_NAME setspn -A host/<ALIAS_NAME.DOMAIN> COMPUTER_NAME
For example, my machine name here is SQLVM1, and I want to use the DNS alias SQLVM1-DR.mydomain.com (FQDN):
setspn -A host/SQLVM1-DR SQLVM1 setspn -A host/SQLVM1-DR.mydomain.com SQLVM1
NOTE: For Windows 2003 machines, you’ll need to download and install Windows Support Tools first.
- Finally, reboot the machine… and job done 🙂
Apparently, you can disable the authentication loopback check instead, by updating the following registry entry with a value of 1:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\DisableLoopbackCheck
…but this isn’t the recommended approach as it reduces security somewhat. Hope this helps!
References:
KB281308: Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
KB926642: Error message when you try to access a server locally by using its FQDN or its CNAME alias after you install Windows Server 2003 Service Pack 1: “Access denied” or “No network provider accepted the given network path”