none
Can't access sql server instance using sql alias

    Question

  • Hi,

                  I am having a slight issue connecting to my sql instance using the alias that I set up: Steps I took in setting up the alias is what I normally use but I don't know why it's not working this time.

    I've added the alias in active directory, I have added it in sql server manager, I have checked the prot, the ip e.t.c

    if I try to connect using <server name>, 1435. (which is my port it works fine) but if I try to use the sqlalias,1435 it bring up an error

    "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: ip address]event error : 18452"

    Please any idea of what to check ? Thanks

    Tuesday, April 08, 2014 11:48 AM

Answers

  • Thanks did all that before but what worked in my case was that I disabled the loop back check.

    Method 1: Specify host names (Preferred method if NTLM authentication is desired)

    To specify the host names that are mapped to the loopback address and can connect to Web sites on your computer, follow these steps:
    1. Set the
      DisableStrictNameChecking
      registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
      281308 Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
    2. Click Start, click Run, type regedit, and then click OK.
    3. In Registry Editor, locate and then click the following registry key:
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
    4. Right-click MSV1_0, point to New, and then click Multi-String Value.
    5. Type BackConnectionHostNames, and then press ENTER.
    6. Right-click BackConnectionHostNames, and then click Modify.
    7. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then clickOK.
    8. Quit Registry Editor, and then restart the IISAdmin service.

    Method 2: Disable the loopback check (less-recommended method)

    The second method is to disable the loopback check by setting the DisableLoopbackCheck registry key.

    To set the DisableLoopbackCheck registry key, follow these steps:
    1. Set the
      DisableStrictNameChecking
      registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
      281308 Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
    2. Click Start, click Run, type regedit, and then click OK.
    3. In Registry Editor, locate and then click the following registry key:
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
    4. Right-click Lsa, point to New, and then click DWORD Value.
    5. Type DisableLoopbackCheck, and then press ENTER.
    6. Right-click DisableLoopbackCheck, and then click Modify.
    7. In the Value data box, type 1, and then click OK.
    8. Quit Registry Editor, and then restart your computer.

    Thanks

    Wednesday, April 09, 2014 2:44 PM

All replies

  • Hello,

    What happen if you use the cliconfg.exe on the remote computer (not on the SQL Server computer) ( the program can be located at C:\Windows\System32 or  at C:\Windows\SysWOW64)? Once you click on the Enable button to enable the TCP/IP protocol (General Tab) then click on the protocol and then click on Properties button and add the port you are using for the instance.

    On the “Alias” tab, click on the “Add” button, add the alias name, click on the TCP/IP radio button, then add the physical name of the server and the port.

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Tuesday, April 08, 2014 2:28 PM
  • Thanks did all that before but what worked in my case was that I disabled the loop back check.

    Method 1: Specify host names (Preferred method if NTLM authentication is desired)

    To specify the host names that are mapped to the loopback address and can connect to Web sites on your computer, follow these steps:
    1. Set the
      DisableStrictNameChecking
      registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
      281308 Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
    2. Click Start, click Run, type regedit, and then click OK.
    3. In Registry Editor, locate and then click the following registry key:
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
    4. Right-click MSV1_0, point to New, and then click Multi-String Value.
    5. Type BackConnectionHostNames, and then press ENTER.
    6. Right-click BackConnectionHostNames, and then click Modify.
    7. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then clickOK.
    8. Quit Registry Editor, and then restart the IISAdmin service.

    Method 2: Disable the loopback check (less-recommended method)

    The second method is to disable the loopback check by setting the DisableLoopbackCheck registry key.

    To set the DisableLoopbackCheck registry key, follow these steps:
    1. Set the
      DisableStrictNameChecking
      registry entry to 1. For more information about how to do this, click the following article number to view the article in the Microsoft Knowledge Base:
      281308 Connecting to SMB share on a Windows 2000-based computer or a Windows Server 2003-based computer may not work with an alias name
    2. Click Start, click Run, type regedit, and then click OK.
    3. In Registry Editor, locate and then click the following registry key:
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
    4. Right-click Lsa, point to New, and then click DWORD Value.
    5. Type DisableLoopbackCheck, and then press ENTER.
    6. Right-click DisableLoopbackCheck, and then click Modify.
    7. In the Value data box, type 1, and then click OK.
    8. Quit Registry Editor, and then restart your computer.

    Thanks

    Wednesday, April 09, 2014 2:44 PM