How to enable remote connections in SQL Server 2008?
You experience the following error message:
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)
How to solve this issue?
There are a couple of things that might be going on here… (All of the following configurations are made on the computer running your SQL Server 2008 instance)
Allow remote connections to this server
The first thing you want to check is if Remote Connections are enabled on your SQL Server database. In SQL Server 2008 you do this by opening SQL Server 2008 Management Studio, connect to the server in question, right click the server…
… and open the Server Properties.
Navigate to Connections and ensure that Allow remote connections to this server is checked. Check if this solves the problem. If it does, here you go, continue with whatever you were doing and have a nice day.
Protocols for MSSQLServer
If you’re still running in issues let’s dig a bit deeper. The next good thing to check is the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).
Make sure that TCP/IP is enabled and try again. Even though I hope that this resolved your problems there might still be an issue with…
The Firewall
If there is still no communication happening between your computer and the remote SQL Server you most likely need to configure your firewall settings. A good first step is to figure out which port is being used by TCP/IP (and which you need to open in your firewall). You can do this by right clicking TCP/IP and selecting Properties.
Click on the tab IP Addresses and voilà – Port 1433 it is :-) That was easy enough and all there is left to do is to allow inbound TCP/IP traffic on Port 1433 in your firewall. In Windows 7 this works something like this. Open the Control Panel and navigate to Windows Firewall.
Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.
This opens the New Inbound Rule Wizard which you can use to allow inbound traffic on Port 1433 for TCP/IP (and which is exactly how you configured your SQL Server in the steps above). Just follow the steps outlined below and you should be good :-)
That’s it, success! From here you should be able to access your SQL Server remotely. Enjoy!
Cheers!
Daniel
P.S. There's a good article on msdn on this topic if you're looking for additional information >> Configuring the Windows Firewall to Allow SQL Server Access.
Update (June 14th, 2010):
- If you're running into SQL Server Error Code 26 check out SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
- If you're running into SQL Server Error Code 10060/SQL Server Error Code 10061 check out SQL Server Error: 10060 \ SQL Server Error: 10061
Comments
Anonymous
April 14, 2010
I see red X's instead of your screenshots.Anonymous
April 16, 2010
it is very good article.Thank you indeed.Anonymous
April 19, 2010
Hey Daniel, very nice article! One comment: Many enterprise firewalls are blocking the UDP-Ports. By default SQL-Server-Browser-Services use UDP-Port 1433. System administrators are not very happy when you ask them to open UDP-Ports, because they are frequently used for cyber-attacks. If SQL-Server-Browser-Services are not available, you cannot find the named instance of you server. An easy workaround is to append the TCP-Port of your server instance (in this case 1433) to the server address (e.g. 234.567.123.23, 1433) Not using SQL-Server-Browser-Services and blocking UDP-Ports makes systems much safer at no cost of usability. cheersAnonymous
April 20, 2010
@Oliver >> do you still have issues seeing the pictures? @Gerhard >> Great tip! Thanks very much!Anonymous
May 10, 2010
Thank you!! This is a very good article and it sorted my problem out. So much for the technet and MSDN online help.Anonymous
January 20, 2011
How to enable remote connections in SQL Server 2008 into Windows Server 2008 R2 Core?Anonymous
July 13, 2011
thank u so much ...it helps me lot..keep up ur good work.Anonymous
August 01, 2011
The comment has been removedAnonymous
August 11, 2011
Thank you so much for this. Saved me at midnight for a launch tomorrow!Anonymous
August 26, 2011
I didn't try your solution yet, but realy I want to thank you very much for your explanation.Anonymous
October 27, 2011
I tried till the step of enabling TCP. I still could not connect to database remotely. Windows firewall was off. I restarted sql server service. Then it worked :)Anonymous
November 05, 2011
what kind o microsoft IDIOT mind this ? what for ??? this way Everybady open ALL ! IDIOT !!!Anonymous
November 29, 2011
have you can do this automaticly with codeAnonymous
December 02, 2011
This is good guide, but you forgot that it is needed to create specific username and password to establish connections to the server, also select wich logins are going to be used to access the database, this is could be all done from the management studio, and the most important is you should enable sql server authentication and windows from the properties -> security. more info: videotutors.net/sqlserver-remote-access.aspxAnonymous
January 11, 2012
Thanks you dear this is very helpful document i have deployed its successfully.Anonymous
February 24, 2012
Thank you thank you thank you. For hours I was trying to find the answer to this problem. Searched youtube and google and luckily came across this! Cheers!Anonymous
February 26, 2012
The comment has been removedAnonymous
February 29, 2012
Thank You very much for providing for solving this dangerous error.......Anonymous
February 29, 2012
Thank You very much for providing solution to solve this dangerous error.......Anonymous
March 04, 2012
Thanks alot, that what I needed, so nice.Anonymous
March 08, 2012
Nice post. It helped me. I was facing problem for firewallAnonymous
March 17, 2012
This is very clever. It works for me. Thanks a lot. zugutakAnonymous
March 20, 2012
("Data Source=<Alias name/ipaddress,port>;Network Library=DBMSSOCN;Initial Catalog=DATAbaseNAME;User ID=SQLCreatedUSERname;Password=Pass;") what if thus is the errorAnonymous
April 24, 2012
Thanks!!! Worked like a charm and your instructions were so easy.Anonymous
May 03, 2012
In my case the TCPIP was disabled in the Network configuration section in the Configuration Manager. Will link this article to my blog if you don't mind. Thanks for sharing.Anonymous
May 09, 2012
Your article is very helpful to me... Thanks a lotAnonymous
May 09, 2012
The comment has been removedAnonymous
May 10, 2012
All the details are a great help. Thank you!Anonymous
May 18, 2012
Depending on your networking setup, you might need to set up port forwarding at the router tooAnonymous
May 28, 2012
You also need to make sure that the <strong>SQL Browser</strong> service is running. Mine was disabled for some reason, which prevented this from working.Anonymous
June 02, 2012
This article is a nice one, but i dont know where configuration manager is located in SQL Server 2012......help me please?Anonymous
June 12, 2012
Hi i still couldnt access my server.. my server and my local machine are on the same LAN network. Is it necessary for my server to have a user name and password.. i have used windows authentication methord. please help asap anyone. Thanks in advanceAnonymous
June 20, 2012
What if you still can't get onto it after doing all the steps? ThanksAnonymous
June 26, 2012
Hello, In the Firewall part you ask us to note the TCP Port (which in your case is 1433). The issue that I am facing is that in my case the TCP Port is empty. How can I resolve this? Any help would be greatly appreciated. Thanks.Anonymous
July 03, 2012
EXCELLENT! No pictures but text was spot on. My TCP/IP was disabled and you identified it at your second troubleshooting step. I'd suggest an update to the article to note that SQL Server requires you to restart the service AFTER enabling TCP/IP before the changes would apply. Thanks for taking the time to write this article!Anonymous
July 05, 2012
So after this what next. I was expecting to see what will happen after uploaded to a web server.Anonymous
July 18, 2012
it is realy very very very usefull.Anonymous
July 24, 2012
Thank You Very Much, This article helped me a lotAnonymous
July 30, 2012
Very good. Used it twice this week:-)Anonymous
August 16, 2012
I Solved this connection issue on XP and Vista But On 7 your article help me ! Thanks Pro!Anonymous
August 21, 2012
I see a red X instead of each screenshot. Thus, your comment "Just follow the steps outlined below and you should be good" provides no instruction, only red Xs.Anonymous
October 02, 2012
My server is accessible from some machines but from some machines not able to access the server. i have done all the things mentioned in your post. what could be possible reason?Anonymous
October 18, 2012
@Hemant I had the same problem. Turned out it was ipv4 vs ipv6 problem. Problem. 3 pc, client, iis and SQl. Client could connect to iis and sql iis cold not connect to sql. Solutoin Go to sql and get ipv4 (run cmd type ipconfig) user til ipv4 address as server name, when connection from ie. SQL server managemetn studio. Hope it helps And great article :)Anonymous
October 29, 2012
Thanks resolved problem on Windows 8 and SQL Server 2012Anonymous
December 30, 2012
It was the $%^ Windows Firewall! Thanks manAnonymous
January 10, 2013
i am still nt able to connect to other PC...bt d other pc is able to connect...wat could be d problem?..do replyAnonymous
January 10, 2013
also dat my pc is windows 8 n d other pc is windows 7..is dis a problem for connection??Anonymous
January 28, 2013
Thanks a lot for this article. I missed the tcp access here. Thumbs up for you.Anonymous
February 05, 2013
Thanks, man. A very helpful walkthrough.Anonymous
February 13, 2013
Hello, I was indeed facing that same problem. Your article helped you. Thanks a lot sirAnonymous
March 08, 2013
Thanks! Enabling TCP/IP did it for me. Great article!Anonymous
March 19, 2013
This was very helpful. The step by step approach was great and it worked for me. Thank you.Anonymous
March 25, 2013
very nice article.....it is very helpfullAnonymous
April 04, 2013
I loved this one! Thank you for sharing! Cheryl MatraskoAnonymous
May 22, 2013
Thanks for this article - most helpful indeed!Anonymous
May 24, 2013
Thank you very much! It was very useful in our project from Argentina.Anonymous
June 03, 2013
Thank You so much for this. Great, clear and concise article. Really appreciate posts like this.Anonymous
June 03, 2013
Worked like a charm! Thanks a lot :)Anonymous
June 11, 2013
@Daniel_Walzenbach Sir, please help me. :( I dont have "Protocols for MSSQLServer" in the SQL Server Configuration Manager. I only have "Protocols for SQLEXPRESS" What should I do?Anonymous
June 20, 2013
Thanks so much Daniel. Easy to follow instructions that even I could understand.Anonymous
July 09, 2013
Daniel, excellent stuff there, helped me A LOT !!!Anonymous
July 14, 2013
Thanks, this is exactly what I have been looking for. It worked.Anonymous
July 22, 2013
I am developing a application c# windows based with sql server 2008 i want this install this application in many computers and i want install sql server 2008 in a particular computer so that other computer get data from sql server 2008 through application my project is ready now so how should i do it now problem is with sql server sir please help..thanks in advanceAnonymous
August 14, 2013
Fantastic article. This absolutely solved my problem.Anonymous
September 10, 2013
really the setting worked thanks for the info.Anonymous
November 05, 2013
MUchas gracias es excelente la explicación, me ha ayudado mucho los felicitoAnonymous
November 07, 2013
Good article Daniel. This is the kind of thing I do so infrequently that each time I need to find instructions again. I have normally found this very difficult to achieve. these are the clearest instructions I have come across. The only step I found missing was to ensure that the 'SQL Service Browser' is running on your machine. Thanks ChrisAnonymous
November 08, 2013
Thanks Daniel. This is a very good article. I was able to connect to SQL Server from MySQL migration tool and migrate databases between these servers. Great Work.Anonymous
November 16, 2013
i am working in a Telecom company where in every 2 second a issue will rise and the website will update my question is i want to create a local websit which can access my main company website database and it should update when ever the main site is update please give me some ideaAnonymous
December 03, 2013
Thank you so much . you saved my day...:DAnonymous
December 31, 2013
I do not know if this is only with express but I was having same problem until I enabled and started "SQL Server Browser" service..Anonymous
January 15, 2014
Hi Thanks for a wonderful article. But I'm still not able to link. Could u please help. I am not seeing MSSQLSERVER in network configuration. What shall I do. Only showing sqlexpress in brackets.Anonymous
January 23, 2014
Very usefull article. Thanks for sharing.Anonymous
February 12, 2014
The comment has been removedAnonymous
February 20, 2014
It's very good, thanks you so much.Anonymous
February 23, 2014
So I did what is told in your article. But my client PC still can't detect the server PC through osql -L and sqlcmd -L. Are there other things that can prevent the client from seeing the server? Both have SQL server 2008, unblocked port 1433(both incoming and outgoing). I even went as far as unblocking SQL Server NT on the server. They can ping each other quite fine and I can even access the client from the server PC through remote.Anonymous
February 25, 2014
This was s great help to me. Thanks a lot.Anonymous
February 28, 2014
Hello, I tried all of this and still have the problem. I have windows 8, I don't understand the problem.Anonymous
March 02, 2014
Fantastic blog!!! This blog helps you to solve your any knid of windows 7 firewall error , its can get instant service provider.go through this link. <a href=windows7support.blogspot.in/.../fix-windows-7-error-1068.html>windows firewall error 1068 windows 7</a> Thanks Aalia lyonAnonymous
March 03, 2014
This is the standard approach. However by doing so you web server having Data source with same SQL Server gets affected. The application through web server is becoming slow as long as the port either 1433 or any specific port is open. And some time DSN (Data Source Name) which is created earlier will not work after setting the port open for remote access.w as long as the port eaither 1433 or any specific port is open.Anonymous
March 12, 2014
The comment has been removedAnonymous
March 12, 2014
Very very helpful and well defined. Keep it upAnonymous
March 20, 2014
To the point directions, easy to followAnonymous
April 08, 2014
The comment has been removedAnonymous
May 14, 2014
You Rule! Thank you so much! I went through all of these steps and finally succeeded on the last one!Anonymous
May 26, 2014
Many thanks. This was most helpful.Anonymous
May 29, 2014
Thanks Daniel. What a detailed & wonderful article! Regards, TonyAnonymous
June 06, 2014
Thanks very much - It worked first time and saved hoursAnonymous
June 06, 2014
Very informative and helpful. Thank you!Anonymous
June 07, 2014
This article can fixes my issues. Recommend for who is same problem.Anonymous
June 16, 2014
Thank you very much! The firewall configuration did the trick!Anonymous
June 17, 2014
Very nice article. Saved alot of my time. Thanks Daniel!Anonymous
June 24, 2014
Thanks you dear this is very helpful document i have deployed its successfully.Anonymous
July 06, 2014
Very useful article. Many thanks to you. Cheers.Anonymous
July 14, 2014
Thank you so much for this guide :) It has resolved my issue.Anonymous
July 19, 2014
Does not work on Win 8, still cannot telnet IP 1433 or connect with manager (Error 26)Anonymous
July 20, 2014
Thanks you very much. its works for me. nice solutionAnonymous
July 23, 2014
The comment has been removedAnonymous
August 20, 2014
Thanks! If you still have problems, start up SQL Server Browser.Anonymous
August 25, 2014
Thank you so much! This save me from half day struggling...Anonymous
September 12, 2014
I have the same problem as a couple other commenters: I cannot see the pictures. In my case at least, it is because you have placed the image files on a file sharing site, and my company blocks online file sharing sites at the firewall.Anonymous
September 17, 2014
Really thanx a lot...!!! Nice article.. It has helped me !!Anonymous
October 06, 2014
Really something Grate in this article Thanks for sharing this. We are providing DATABASES courses training online. After reading this slightly am changed my way of introduction about my training to people. And also refer my website for DATABASES Training and solutions of DATABASES applications. Please Visit Us @ <a href="www.tectist.com/databases-online-training.html " >DATABASES training courses online</a>Anonymous
October 07, 2014
Thanks a lot Daniel for you great article.Anonymous
November 03, 2014
Thank you Dear Mr.Daniel, it was very to the point problem solving, best of lack.Anonymous
November 03, 2014
This has been very helpful. Thanks a million. The PAnonymous
November 14, 2014
Hi, Is this also applicable on windows 8 pc? I tried to follow the instruction above but it will not work. The server pc is windows 8. And the workstation is windows 7. It will not connect on the server. any suggestion? ThanksAnonymous
November 18, 2014
Hello, This was very helpful ThanksAnonymous
December 17, 2014
The last suggestion - create inbound rule was needed in my case. Thank youAnonymous
January 06, 2015
Thank you indeed well written and it worked. Enable TCP and start stop the service and I am now connected from the living room to the host machine running SQL server database engine.Anonymous
January 29, 2015
thanks a lot ..it's very interesting.Anonymous
February 02, 2015
Thank you very much. My problem is solved. Easy Steps and easy to capture the issue..Anonymous
February 04, 2015
terimakasih atas artikelnya saya tidak menemui hambatan untuk connect sql 2008 r2 64 bit dengan sql server 2008 r2 32 bit thank you for your share your share it works warm regards kardiyanto@gmail.comAnonymous
February 09, 2015
Awesome!!! Thank you... you're a lifesaver!Anonymous
February 11, 2015
SQL Server Configuration Manager - TCP/IP was disabled. Thanks Very nice articleAnonymous
March 09, 2015
Nice job. Thank you, this saved me! :-)Anonymous
March 16, 2015
Man, very helpful article. Helped me a lot.....Thank you !Anonymous
April 01, 2015
Thank you! The FireWall instructions got me past my issue.Anonymous
April 05, 2015
Telnet sqlserver port => check telnet port is openAnonymous
April 13, 2015
how to do this in visual studio 2013?Anonymous
April 15, 2015
Thanks Daniel for sharing these screenshots.Anonymous
April 22, 2015
The comment has been removedAnonymous
April 26, 2015
Thank you so much, its a great article with very good step by step visual explanation cheersAnonymous
May 25, 2015
I followed your method, any network sql server can access my server only not database but I want to make my database accessible by all network instancesAnonymous
May 31, 2015
This Article has worked wonders for me. Only extra step was to "RESTART SERVER"Anonymous
June 11, 2015
great Suggestion. Cheers..........!Anonymous
June 12, 2015
Thank you for the concise tutorial. I am now able to connect remotely with SSMS!Anonymous
July 22, 2015
Thank You for this nice article. Please keep on posting such good articles.Anonymous
August 03, 2015
The comment has been removedAnonymous
August 21, 2015
Very good article. The most important step for me was adding the port rule in the firewall. Didn't need to set allow remote connections.Anonymous
September 17, 2015
I've done all of this and I'm still getting the error TITLE: Connect to Server
Cannot connect to THEDARSHTOPMSSQLSERVER.
ADDITIONAL INFORMATION: 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) For help, click: go.microsoft.com/fwlink
The parameter is incorrect
BUTTONS: OK
Anonymous
September 18, 2015
Well explained...Thanks for the article.Anonymous
September 23, 2015
Great instruction: work fine and detail. Thanks a lot.Anonymous
October 11, 2015
Hey Daniel, very nice article! One comment: Many enterprise firewalls are blocking the UDP-Ports. By default SQL-Server-Browser-Services use UDP-Port 1433. System administrators are not very happy when you ask them to open UDP-Ports, because they are frequently used for cyber-attacks. If SQL-Server-Browser-Services are not available, you cannot find the named instance of you server. An easy workaround is to append the TCP-Port of your server instance (in this case 1433) to the server address (e.g. 234.567.123.23, 1433) Not using SQL-Server-Browser-Services and blocking UDP-Ports makes systems much safer at no cost of usability. cheersAnonymous
October 18, 2015
Thank you very much, you saved my life :)Anonymous
November 14, 2015
Issue not fixed. Done with all the above mentioned steps. But getting same error still. :-(Anonymous
November 16, 2015
It worked hassle free for me. Thanks For this good articleAnonymous
January 12, 2016
Great article and nicely explained. ThanksAnonymous
January 12, 2016
This post is still helpful! I just had this exact problem, turns out it was the firewall. Thanks for the great walkthrough!Anonymous
January 21, 2016
Thank you, It works, now I can do my things. Godbless you.Anonymous
January 22, 2016
I read this article and implemented it sucessfully on windows server 2012 R2 with SQL Server 2014.Anonymous
March 03, 2016
Sorry for my lack of experience. But now that the configuration is done, how can access this database? isnt there any ip?Anonymous
March 15, 2016
Thanks for the post is very useful!!!!