Computer name and SQL server name

  • sqlfriend

    SSC Guru

    Points: 52433

    A question about computer name and SQL server name.

    We want to migrate a database from old server to new server and want to keep the old server name. and later decomission old server.

    So after we migrate database from MyServer to MyServerNew, we renamed the new server to MyServer and  the original server to  MyServerOld. And in the  new server I also renamed the SQL server to the same as the computer name.

    But I did not rename the old SQL server name.

    Then we got some error message in the new server's log

    DESCRIPTION:   Login failed for user 'mydomainname\myServerOld$', Reason: Could not find a login matching the name provided. [CLIENT: 10.50.....]

    So I guess this is because there are two instances with same SQL server names.  And it is weird it goes to the new server log.

    Does it first look for the computer name, then SQL server name?

    thanks

  • Site Owners

    SSC Guru

    Points: 80376

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    I think you are mixing some things up and getting confused.  A LOGIN FAILED message in the log means that a login was attempted but was not successful.  The CLIENT value of that is the IP of the machine trying to connect.  Since the login has a $ at the end, it is (usually) a computer.

    What I am expecting is that the OLD server is still online with the name myServerOld.  I expect that it has some SQL related services still installed on it and set to start automatically.  My expectation is that one of these services is currently running now and since its configuration says to look for MyServer\MyServer for the SQL Server, it tries to connect using that connection and the computer name.  This fails and thus you are getting that error.  The easy fix - turn off the old server if it is no longer required or disable the SQL services on the old server that are migrated to the new one.

    This has nothing to do with the computer name and/or SQL Server name look ups or with having 2 instances with the same SQL Server names and it is not weird that it goes into the new server log because that is the server that is getting the connection.  This is expected.

  • sqlfriend

    SSC Guru

    Points: 52433

    Yes, the old server is still running .  I can certainly shut down the  service on  old server after a day or two.But just want to figure out the concept. The old server computer name is renamed, but the old SQL server name is not.

    So the new SQL server has myserver, and the SQL instance name is the same as the old one that still existing.

    So the account with the computer name $ comes from the old server to  the new server log, but not to the old server log.

    The reason it takes precedence  Myserver(computername)-myserver(SQLserverName) over  MyServerOld (computername)-Myserver(SQLserverName). it takes precedence that the computer name is the same as the sql server name.

    Correct?  thanks

     

     

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    Close... My expectation is it isn't taking precedence for one over the other, it is configured to use one over the other.

    When SQL was installed originally on the old server, the SQL instance name was "Myserver\myserver"  (computer name\instance name).  So when the old server was renamed to "Myserverold", nothing was changed on the SQL side to tell the services to look for "MyserverOld", so all of the services are still looking at "Myserver\myserver".  So the services are trying to connect to "Myserver\myserver" not "localhost\myserver" or "MyserverOld\myserver" or  ".\myserver" and "Myserver\myserver" is the NEW server.

    There is nothing with "precedence" here.  When you connect to the SQL instance, since it is a named instance, you would use the name "myserver\myserver" when connecting (unless you have a SQL alias set up) and the SQL browser service will pass you over to the proper port.

    You can verify where the connection is coming from by looking at that CLIENT: section of the error.  In your case 10.50.... is going to be the IP of the computer trying to connect to Myserver\myserver.  I am confident that if you look up that IP, it will tie back to MyserverOld and not an end user machine.

    The reason it is NOT in the old server log is that it is not failing to connect to the old server.  Similar to if you tried to connect to the server myserver\myserver as sa but had the wrong password, you would not expect that to show up in the log for myserverold\myserver as you were not connecting to myserverold\myserver.

  • sqlfriend

    SSC Guru

    Points: 52433

    Thanks for the detailed explanation. That makes a lot of sense. and quite helpful.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply