SQL SERVER – Fun Post – Connecting Same SQL Server using Different Methods

Yesterday I had faced error when I was connecting SQL Server using 127.0.0.1. I had immediately checked if SQL Server is working perfectly by connecting to it by specifiing my local box computer. While I was doing this suddenly I realize that it is indeed interesting to know how many different way we can connect to SQL Server which is installed in the local box.

I created list of 5 different way but I am sure there are many more ways and I would like to document there here. Here is my setup. I am attempting to connect to the default instance of SQL Server from the same system where it is installed.

Method 1: Connecting using local host IP 127.0.0.1

Method 2: Connecting using just a single dot (.)

Method 3: Connecting using (local)

Method 4: Connecting using localhost

Method 5: Connecting using computer name – in my case it is BIG

Here are my two questions for you? (Scroll below the image)

1) Which is your favorite method?

2) What are other methods you are familiar with to connect to local host?

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

38 thoughts on “SQL SERVER – Fun Post – Connecting Same SQL Server using Different Methods

  1. Once we are hooked up to the server for the 1st time, the details like “Server Name, User ID” is saved for future logins. So, I feel most of the DB developers will never bother to try the various methods. I should appreciate your curiosity in discovering so many methods to connect. Thanks for it.

    At the same time, when there are multiple NAMED INSTANCES running on a box, practically I have seen many developers breaking their heads to connect to server. In such case, I go to Control Panel – Services and find the list of all SQL Server instances on the box. Is there any single command, which we can run at OS level to find the list of SQL Server instances running? This will really come handy when there are both multiple versions of SQL Servers & mutiple named instances of SQL Severs running on the same box.

    In addition – As a developer who has SQL Express on local system, I feel MS should give an option to set “Auto-connect”. This will help to get rid of 1 extra step of clicking on “Connect” button. As soon as I click on SSMS, I directly see the list of Databases in the left side object explorer. I wonder if such option is already present?

  2. The question which method is my favorite depends how one is connecting to the database server.

    Method 1: Connecting using local host IP 127.0.0.1
    This would be the only method if you are connecting to non-trusted domain, since DNS cannot identify computer names, you are left with IP Address.

    Method 2: Connecting using just a single dot (.)
    This is only valid when you are actually logged on the database server, on personal laptops this is the case, but when you are working in companies, you always connect to SQL Server remotely using SSMS, at that time, we cannot connect using (.)

    Same applies to Method 3 and 4, you can use this method when you are actually on the database servers.

    Method 5: Connecting using computer name – in my case it is BIG
    Given Server is in the same domain or trusted domain, we can connect to it by Full computer name or computer name.

    I personally prefer Method 5 as it gives the names of the database server, its easy to distinguish from other database servers.

    ~IM.

  3. I prefer IP address.

    And I will like to add one more method.

    Using Link local IPv6 address : fe30::bce7:42fd:3gc7:3f12%16 .

    Dont know much about it But we can connect sql server using this

    • @ Sankoko

      Is there any specific reason why you use this specific method over many other simple menthods to connect to SQL Server, just curious to know.

      Thanks,
      IM.

  4. Hi Pinal,

    Q1) Method 5: Connecting using computer name
    Q2) Method 2: Connecting using just a single dot (.) &
    Method 3: Connecting using (local)

  5. Hi Pinal,

    Im tying to connect using local but its not connect its giving error like below

    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: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)

    Other methods are working fine.

  6. First I would like to thank Pinal Dave for a great blog and for being willing to share his immense knowledge of SQL Server with the rest of us. He has made a good and positive difference for us still learning at a basic level.

    Now to the blog question.
    Since I mainly connect to remote servers and sometimes more than one at a time, I like using the computer name. This assures me of the server I’m connected to.

    If you need to connect to multiple instances on the same computer via SSMS you simply add a comma and port number after the instance name.
    For example; server\instance,1435

    To find how many instances that are running you can use the netstat command at a console prompt. You may have to adjust the console window screen buffer to a larger number because there is a lot of information. I use 1000. (NOTE: this will only show instances that have connections to them.)

    The command I use is: netstat -b
    This will show all the SQL server execuables running and their port numbers plus other useful information.

  7. I prefer connecting as localhost. This is because most of the SSAS sample projects of Adventureworks datawarehouse (available online) are configured with localhost as the sql server name. Hence the sample projects work without any changes.

  8. How to fix this SQL server error during installation ?

    The SQL Server service failed to start. For more infomation, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually.”

  9. I know a lot of people who use the single dot (.), “(local)” and “localhost” to connect to their local instance, but it does suffer from one big drawback in an environment where servers are not rebuilt from scratch, but are instead cloned from a pre-configured disk image.

    When the disk image was built, the SQL server instance would have picked up the template name as default instance name. Later when the image is cloned, most IT departments forget to rename the SQL instances on that server. The connections to local host from the same machine as the instance works, but the moment they try to connect to that instance from a remote machine, all hell breaks loose.

    According to me, it’s *always* better to use the actual SQL Server instance name.

    (By the way: @Rajasekharan V: SSMS has some cool parameters, but this is the first time I have seen someone use the SSMS command-line parameters in daily life! It’s nice!)

  10. There is two more method to connect with local instance.
    . (dot)
    (local)
    localhost
    Host Name
    ::1
    Private IP
    Loopback IP(127.0.0.1)
    Name pipe (ie. \\.\pipe\sql\query)

    Mostly I am using Host Name or dot.

  11. hello sir…
    i unable to connect with default instance single dot (.) in my laptop.. it gives server not found error …. then what should i do…. ???

  12. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

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