Okay, so you’re in charge of multiple named instances on a SQL Server server. The server in this case was 2008, but I don’t think that makes a difference. We’ll say that the server’s name is SVR, and your three instances are SVR, SVR\NETWORK, and SVR\DEV2
Because you don’t want to tell people to put SVR\DEV2 in their connection strings, you decide to use DNS to create some aliases. So we’ll call SVR Dev, SVR\NETWORK Net, and SVR\DEV2 Dev2. That way, they just type in Dev, Net, or Dev2 into Management Studio or some other application which wants information and they’re in.
In order to do this, we have three IP addresses on this server: 192.168.1.2, 192.168.1.3, and 192.168.1.4. Have your networking staff (and if you don’t have one, Google it; I don’t know how to do this part…) connect each IP up with one of those aliases. I called .2 Dev, .3 Net, and .4 Dev2 for this example.
So once we get that part set up, we now want SQL Server to respond to those names and only those names—you don’t want the Dev server to respond to calls to Dev2, as that would just confuse people. In order to set this up, you have to use the SQL Server Configuration Manager to set up TCP/IP. Once you open up the manager, go to SQL Server Network Configuration, and then select Protocols for MSSQLSERVER (for our Dev instance). In there, right-click TCP/IP and go to Properties. There are two things to do in order to set this up. First, turn “Listen All” off. That way, the server does not listen on all IPs and ports. We want to limit the IP range for each server, so that when you try to connect to 192.168.1.3, you really mean that you want to talk to the SVR\NETWORK named instance. So setting Listen All to “No” is the first step. After doing that, click on IP Addresses.
On the IP Addresses tab, you have a number of addresses available. For my purposes, I started with IP2 and added in 192.168.1.2. For IP3, I used 192.168.1.3, and for IP4, it was 192.168.1.4. I did this for all three named instances, just to be consistent. Make sure that the value in TCP Dynamic Ports is blank, NOT 0. If TCP Dynamic Ports is 0, that means that dynamic ports will be used, and it defeats the purpose of this exercise. Instead, I left that blank and entered a value for TCP Port. You can use the default SQL Server value of 1433, or choose your own, but fill it in. Finally, for the first named instance, set the Enabled option to “Yes” instead of “No” where you entered in 192.168.1.2. So in my case, it would look a bit like what you see on the left.
After doing that for the MSSQLSERVER instance, you can do the same thing for the NETWORK and DEV2 instances. Obviously, for the NETWORK instance, you want it to go to 192.168.1.3, as then it would respond to the “Net” alias on your domain, and 192.168.1.4 for Dev2.
Once you get finished with those tasks, congratulations! You can now connect to the servers using SQL Server Management Studio, as well as outside applications which use the SQL Server driver. But you’ll probably find out something fairly quickly: SQL Agent jobs don’t work any longer!
Here’s an example of one of the agent jobs that failed for me. In this case, it was a maintenance plan backup job. I’ve edited most of the incriminating evidence, and bolded the important parts.
Executed as user: [domain account]. Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:25:05 PM Error: 2010-03-24 12:25:06.13 Code: 0xC00291EC Source: {E245B3C9-FA14-4BFE-983B-D2B061B1DB5B} Execute SQL Task Description: Failed to acquire connection “Local server connection”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2010-03-24 12:25:06.13 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2010-03-24 12:25:06.15 Code: 0xC0024104 Source: Back Up Database Task Description: The Execute method on the task returned error code 0x80131904 (Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.). The Execute method must succeed, and indicate the result using an “out” parameter. End Error Error: 2010-03-24 12:25:06.15 Code: 0xC0024104 Source: {FF552BA2-26DA-412D-996D-898379CF7249} Description: The Execute method on the task returned error code 0x80131904 (Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.). The Execute method must succeed, and indicate the result using an “out” parameter. End Error Warning: 2010-03-24 12:25:06.15 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:25:05 PM Finished: 12:25:06 PM Elapsed: 0.5 seconds. The package execution failed. The step failed.
So what you can see is that there is some kind of permissions violation, and the agent job thinks that my service account (which is in Active Directory and has the correct privileges) is from an untrusted domain.
Looking in the event log, I checked the security section and found a login failure entry, with a failure reason being that “An Error occured during Logon.” Yeah, I know how to spell occurred, but hey, that’s what the error reads…
Checking the Application log, there were three entries. Two mentioned the failure, and the third one reads: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 192.168.1.4].
In the System log, I saw an entry reading: The program sqlservr.exe, with the assigned process ID 4724, could not authenticate locally by using the target name MSSQLSvc/dev2.[DOMAIN]:[PORT]. The target name used is not valid. A target name should refer to one of the local computer names, for example, the DNS host name.
Try a different target name.
I looked all over the internet for a while, and eventually hit on the solution. That came from this post, which is actually on an only tangentially related topic.
The solution is to go into the registry and go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa. Once you get there, add a REG_DWORD named DisableLoopbackCheck, and give it a value of 1.
Adding a registry key forces the server to go out to DNS and ignore loopback and the \etc\hosts file altogether disables a measure which prevents reflection attacks (thanks, Jesse Vaughan, not just for the original answer, but also the correction. This is proof that I’m not a network administrator!). Doing this fixed the problem, and now jobs run. It did not have any adverse effects on our servers, and all was well in the kingdom of SQL.
36 Chambers - The Legendary Journeys: Execution to the max! 
Actually, just to give you some accurate information, the DISABLELOOPBACKCHECK registry key has nothing to do with the loopback address (localhost) in the hosts file. It’s a security measure that was put in place to prevent reflection attacks against a server. A reflection attack is one that tricks the server to answer it’s own challenge and tricks the server into basically authenticating to itself automatically. I have found it useful to disable the loopback checks in certain situations (both for SQL and IIS) to disable the loopback check to allow an app pool to authenticate as a user. I didnt finish reading your post but my guess is you were on the server (SQL) trying to authenticate to sql. (so until you disabled the loopback test, you would have problems.)
Thank you for the correction (and for the original answer). I’ve updated the post.
You’re right in your guess—the SQL Agent service was trying to authenticate to the SQL Server instance with its service credentials.
Thank you for this post…the disableloopbackcheck fixed my issue with a SQL 2005 installed on Server 2008 Cluster. Errors are below to help out future searches:
The program sqlservr.exe, with the assigned process ID 7812, could not authenticate locally by using the target name MSSQLSvc/SERVER.domain.edu:1433. The target name used is not valid. A target name should refer to one of the local computer names, for example, the DNS host name.
Try a different target name.
A Kerberos Error Message was received:
on logon session
Client Time:
Server Time: 15:7:1.0000 4/21/2010 Z
Error Code: 0x7 KDC_ERR_S_PRINCIPAL_UNKNOWN
Extended Error: 0xc0000035 KLIN(0)
Client Realm:
Client Name:
Server Realm: DOMAIN.EDU
Server Name: MSSQLSvc/SERVER.domain.edu:1433
Target Name: MSSQLSvc/SERVER.domain.edu:1433@DOMAIN.EDU
Error Text:
File: 9
Line: d86
Error Data is in record data.
Hi Kevin,have you setup alias on instances of SQL SERVER 2008 clustered environment? For example, named instance is \, the application wants to use DEV on their ODBC.
Thanks
named instance is \mssql2, the application wants to ODBC to it using DEV.
Hi, Ying. The above post was based on a clustered environment. Following that and the follow-up post should at least start you along the way.
I did notice, however, that for some applications (and, I believe, ODBC connections), you have to put in the actual server name—it doesn’t accept DNS aliases.
Kevin, Thank you for your response. In a 3-nodes 2008 clustered environment,do we have to make the above configuration changes(TCPIP propertiies, etc) on all nodes? Thanks, Ying
Thanks for the great info..
Just to let you know, I had this exact same problem and tried method 1 from http://support.microsoft.com/kb/926642 (adding the FQDN) and it worked. MS recommends this over disabling loopback check.