none
Connecting Using 127.0.0.1 as Server Name

    Pertanyaan

  • hi all, I am absolutely new using SQL Server 2008 R2, I am trying to Login use Server Name: 127.0.0.1 but its say its not found, so I am trying search the problem in SQL Server Configuration Manager, I already Enable TCP/IP, Shared Memory and Named Pipes. Also I already Enable Remote Server from Login in SQL Studio Management -> Right Click Instance Name -> Properties -> Connection -> "Allow Remote Connections to this server" but its still doesn't work but its give me other error description:

    "Arithmetic operation resulted in an overflow. (System.Data)"

    but when I am trying login with (local) as Server Name, its works, but its doesn't work when I am use 127.0.0.1 as Server Name...

    Btw I am using Mixed SQL Server Authentication (with username sa) and Default Instance Name (MSSQLSERVER)

    Please help me, Thanks :D


    19 Februari 2012 16:07

Jawaban

  • It still sounds like you are trying to connect to 127.0.0.1 from a second computer. That will never work. 127.0.0.1 only works from a computer to itself. Every computer has a 127.0.0.1 (though not necessarily enabled) but that IP address cannot be use to connect from one computer to another computer.

    In a command prompt, execute IPCONFIG to find out what IP addresses your computer is really using.

    (Sorry if I misunderstand your intent.) Rick


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    21 Februari 2012 16:16
    Penjawab Pertanyaan

Semua Balasan

  • If TCP/IP is enabled in SQL server configuration manager

    loclahost resolves to 127.0.0.1:1433 and it should work.

    Try enabling SQL Server Browser service and see it works.

    You can also try

    a) ComputerName (where computerName is hostname of the computer where SQL server is running)

    b) (local)

    c) .    (just period) OR

    d) localhost


    19 Februari 2012 16:23
  • >Btw I am using Mixed SQL Server Authentication (with username sa) and Default Instance Name (MSSQLSERVER)

    Hi AmaterialImpure

    Just checking, you installed a default instance? meaning 127.0.0.1 should connect

    Or you used a default instance name of MSSQLSERVER, meaning 127.0.0.1\MSSQLSERVER should connect?

    19 Februari 2012 23:57
  • If TCP/IP is enabled in SQL server configuration manager

    loclahost resolves to 127.0.0.1:1433 and it should work.

    Try enabling SQL Server Browser service and see it works.

    You can also try

    a) ComputerName (where computerName is hostname of the computer where SQL server is running)

    b) (local)

    c) .    (just period) OR

    d) localhost



    I already enable and run the SQL Server Browser, but still cannot connect to 127.0.0.1 or 127.0.0.1:1433
    20 Februari 2012 8:51
  • >Btw I am using Mixed SQL Server Authentication (with username sa) and Default Instance Name (MSSQLSERVER)

    Hi AmaterialImpure

    Just checking, you installed a default instance? meaning 127.0.0.1 should connect

    Or you used a default instance name of MSSQLSERVER, meaning 127.0.0.1\MSSQLSERVER should connect?

    I am trying use 127.0.0.1\MSSQLSERVER as Server name, but When I try to login, there are an error:
    Cannot connect to 127.0.0.1\MSSQLSERVER.

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)


    20 Februari 2012 8:54
  • Well, you have a Default instance of SQL Server (MSSQLServer)

    for e.g. I have a default instance on my development computer as below

    In that case you can't use 127.0.0.1\MSSQLServer you need to use 127.0.0.1 as server name only.

    If you install another **instance** of SQL Server as  MSSQL than you can use 127.0.0.1\MSSQL


    20 Februari 2012 14:08
  • Well, you have a Default instance of SQL Server (MSSQLServer)

    for e.g. I have a default instance on my development computer as below

    In that case you can't use 127.0.0.1\MSSQLServer you need to use 127.0.0.1 as server name only.

    If you install another **instance** of SQL Server as  MSSQL than you can use 127.0.0.1\MSSQL


    Yeah, same with me:

    I already tired use 127.0.0.1 as my server name, but when I login, its always give me an error "Arithmetic operation resulted in an overflow. (System.Data)"

    Please help me ASAP :(

    Thanks!

    20 Februari 2012 15:42
  • I am running out of ideas but If I were you I would try reinstalling client tools (SQL management studio)

    20 Februari 2012 15:58
  • Some thoughts.

    Why are you using 127.0.0.1? That IP address is a special local only IP address called the loopback connector. Are you using it just to test how TCP is working? 127.0.0.1 can only be used on the same computer.

    Are you trying to connect from a client on another computer? That will not work with 127.0.0.1

    Can you connect using real IP address of the computer?

    If you specify the IP address and TCP port number in the connection string, use a comma instead of two colons. As in 123.123.123.123,1433

    However, if the default instance (not a named instance) is using the default TCP port (1433) then you don't need the port number. Just connect to 123.123.123.123

    More troubleshooting steps are here How
    to Troubleshoot Connecting to the SQL Server Database Engine


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    20 Februari 2012 16:32
    Penjawab Pertanyaan
  • I am running out of ideas but If I were you I would try reinstalling client tools (SQL management studio)

    I Already tired Reinstall SQL management studio but still doesn't work :(

    its seem something error with Named Pipes, when I trying Disable the Named Pipes, its say Server name is not accesable or not exists

    20 Februari 2012 16:34
  • Hi AmateriaImpure,

    >> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)

    Regarding to the error message you provided, which is related to connection issue.

    When setting your TCP/IP login protocol make sure on the protocol tab you have it enabled. On the IP Addresses tab have SQL Server listen on port 1433 under the IPAll section. Other things to check from your client are that it can resolve SERVER DNS (ping SERVER) and you can open 1433 port. To test the port from remote uses the command 'telnet SERVER 1433'. The response should be a blank screen with a blinking cursor. If the ping fails client can't resolve servers DNS and if telnet fails it most likely is firewall related or IP/Port bindings.

    Though you have enabled UDP 1434, TCP 1433 and 2 programs (SQL Server and SQL Server Browser) to Windows Firewall, please ensure that whether the TCP/IP protocol is enabled and the SQL Server Services is listening on TCP 1433. You can check this in the SQL Server Configuration Manager --> Protocols for <InstanceName> --> TCP/IP.

    In addition please refer to the following steps in below link which describes How to Troubleshoot Connecting to the SQL Server Database Engine.

    Moreover, please do not dsable Named Pipes protocols, please refer to this article. 

    If this does not work, please post the SQL error log for further troubleshooting.


    Regards, Amber zhang


    21 Februari 2012 6:46
    Moderator
  • Hi AmateriaImpure,

    >> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid) (Microsoft SQL Server, Error: 87)

    Regarding to the error message you provided, which is related to connection issue.

    When setting your TCP/IP login protocol make sure on the protocol tab you have it enabled. On the IP Addresses tab have SQL Server listen on port 1433 under the IPAll section. Other things to check from your client are that it can resolve SERVER DNS (ping SERVER) and you can open 1433 port. To test the port from remote uses the command 'telnet SERVER 1433'. The response should be a blank screen with a blinking cursor. If the ping fails client can't resolve servers DNS and if telnet fails it most likely is firewall related or IP/Port bindings.

    Though you have enabled UDP 1434, TCP 1433 and 2 programs (SQL Server and SQL Server Browser) to Windows Firewall, please ensure that whether the TCP/IP protocol is enabled and the SQL Server Services is listening on TCP 1433. You can check this in the SQL Server Configuration Manager --> Protocols for <InstanceName> --> TCP/IP.

    In addition please refer to the following steps in below link which describes How to Troubleshoot Connecting to the SQL Server Database Engine.

    Moreover, please do not dsable Named Pipes protocols, please refer to this article. 

    If this does not work, please post the SQL error log for further troubleshooting.


    Regards, Amber zhang


    Error is "Arithmetic operation resulted in an overflow. (System.Data)"

    I already Enabled Named Pipes and I already Enabled All IPs and All IP and IPALL TCP Ports is 1433. I can ping 127.0.0.1 but I can't telnet it with port 1433, its always "connecting 127.0.0.1..." and never end... I already disable Firewall but its still error

    21 Februari 2012 10:04
  • It still sounds like you are trying to connect to 127.0.0.1 from a second computer. That will never work. 127.0.0.1 only works from a computer to itself. Every computer has a 127.0.0.1 (though not necessarily enabled) but that IP address cannot be use to connect from one computer to another computer.

    In a command prompt, execute IPCONFIG to find out what IP addresses your computer is really using.

    (Sorry if I misunderstand your intent.) Rick


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    21 Februari 2012 16:16
    Penjawab Pertanyaan
  • It still sounds like you are trying to connect to 127.0.0.1 from a second computer. That will never work. 127.0.0.1 only works from a computer to itself. Every computer has a 127.0.0.1 (though not necessarily enabled) but that IP address cannot be use to connect from one computer to another computer.

    In a command prompt, execute IPCONFIG to find out what IP addresses your computer is really using.

    (Sorry if I misunderstand your intent.) Rick


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    No.. I am trying to connect to 127.0.0.1 from a main computer...

    Its OK misunderstand, Thanks for the reply :)

    24 Februari 2012 11:04