36 Chambers – The Legendary Journeys: Execution to the max!

April 8, 2010

How to Set Up Aliases For Named Instances In SQL Server

Filed under: Programming & Work — Kevin Feasel @ 7:10 pm

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.

Update: Check out my other post on this situation as well.

About these ads

13 Comments »

  1. 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.)

    Comment by Jesse Vaughan — April 8, 2010 @ 8:31 pm

    • 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.

      Comment by Kevin Feasel — April 8, 2010 @ 8:58 pm

  2. [...] How to Set Up Aliases For Named Instances In SQL Server « 36 Chambers – The Legendary Jo… [...]

    Pingback by How do I use mySQL? I can't get to the part where I can make tables and stuff Excel-style!? — April 8, 2010 @ 11:18 pm

  3. 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.

    Comment by Chris Conner — April 21, 2010 @ 11:31 am

  4. [...] Kevin Feasel @ 9:30 pm On Friday at work, I realized that there was a bit of configuration in my previous post on aliases for named instances that I was missing.  Here’s a recap:  you have three instances of SQL Server and want to [...]

    Pingback by Update On Aliases For Named Instances « 36 Chambers – The Legendary Journeys: Execution to the max! — May 29, 2010 @ 9:36 pm

  5. 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

    Comment by Ying — September 1, 2010 @ 11:38 am

  6. named instance is \mssql2, the application wants to ODBC to it using DEV.

    Comment by Ying — September 1, 2010 @ 11:40 am

  7. 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.

    Comment by Kevin Feasel — September 1, 2010 @ 7:24 pm

  8. 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

    Comment by Ying — September 3, 2010 @ 11:33 am

  9. Thanks for the great info..

    Comment by Jackson — March 29, 2011 @ 4:15 pm

  10. [...] and Kudos to Kevin Feasel who’s blog i found this solution [...]

    Pingback by Lync Server 2010 Publishing Topology – MSDE SQL Instance | UnifiedCom.ms — May 14, 2011 @ 1:37 pm

  11. [...] with the following valuesDisableLoopbackCheck Value:1Oh, and Kudos to Kevin Feasel who’s blog i found this solution on.About the Author Misha HaninHi, my name is Misha Hanin. I have served as [...]

    Pingback by Lync Server 2010 Publishing Topology – HRESULT: 0×80070005 (E_ACCESSDENIED) | Curu | Exchange, Lync and more IT Solutions — August 25, 2011 @ 3:06 pm

  12. 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.

    Comment by Jacob Steelsmith — December 6, 2011 @ 9:09 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 100 other followers

%d bloggers like this: