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 (https://blog.sqlauthority.com)
38 Comments. Leave new
i always prefer dot(.)
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?
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.
for me, 127.0.0.1 is nice
Prefer using FQDN
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.
Hi Imran,
I don’t use this method to connect sql server. I just found this method while doing some experiment ,so thought to share on this post
I always prefer The system name, because it make easier for me to remember
There are two other ways:
1) using computer’s IP address
2) using ::1
Hi Pinal,
Q1) Method 5: Connecting using computer name
Q2) Method 2: Connecting using just a single dot (.) &
Method 3: Connecting using (local)
My favorite method is #2 (the dot) :-)
i am using instance name or dot
thanks pinal for explained different methods and can you explain depth way .. why this methods are working…
i am also using instance name and dot
.
l am preferred to instance name / ip address
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.
@Siva
Its not local, it should be (local). Please try this.
~ IM.
[1] Windows Key + R
[2] ssms /S . /E
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.
I always prefer Method 5. It makes me easy to remember.
Awesome Post. Thanks.. :)
Would Like to connect the sqlserver with server name and dot