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)
SQL Server 2008: The server was not found or was not accessible.

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…

SQL Server 2008: Server Properties

… and open the Server Properties.

SQL Server 2008: Server Properties - Connections

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).

SQL Server 2008: Protocols for MSSQLServer

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.

SQL Server 2008: TCP/IP 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.

Microsoft Windows 7 Firewall settings

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.

Microsoft Windows 7 Firewall with Advanced Security

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 πŸ™‚

New Inbound Rule Wizard - Protocols and Ports

New Inbound Rule Wizard - Protocols and Ports

New Inbound Rule Wizard - Action

New Inbound Rule Wizard - Profile

New Inbound Rule Wizard - Name

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):

 

Comments (143)

  1. Oliver Bradley says:

    I see red X’s instead of your screenshots.

  2. Behtash Moradi says:

    it is very good article.Thank you indeed.

  3. Gerhard says:

    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.

    cheers

  4. Walzenbach says:

    @Oliver >> do you still have issues seeing the pictures?

    @Gerhard >> Great tip! Thanks very much!

  5. Grant says:

    Thank you!! This is a very good article and it sorted my problem out. So much for the technet and MSDN online help.

  6. Vadim says:

    How to enable remote connections in SQL Server 2008 into Windows Server 2008 R2 Core?

  7. Salim says:

    thank u so much …it helps me lot..keep up ur good work.

  8. JayB says:

    Step 1>Install MS SQL server 2008

    Step2>During Istallation Always Select MIXED User connection;

    Step3>after successful installation of data base Login as Local User.and create Database..then in side Database select security

    and then right click on login and create new Login.

    Step4>give new User name and Select SQL Authentication..and give password then deselect USE MUST CHANGE PASSWORD AT NEXT LOGIN

    Step5>Go to USER MAPPING on that form and select database wihich u want to assign to that User.and below that assign

    read,write,backup permission.and then Click "ok" to finish.

    Step6>NOW open SQL Server Configuration Manager->SQL Server Network Configuration->Protocols for <Instance Name>->Enable Shared

    Memory & TCP/IP and Double Click on TCP/IP then got to IPaddress TAB->in here ScrollDown and look "IP ALL"there give TCP Port

    "1433" then "ok".

    Step7>Now Go to SQL NATIVE Client xx.x Configuration->Client Protocols->Enable Shared memmory and TCP/IP->Double click on Client

    Protocols and Default Port =1433

    Step8 Optional Client side>Go to Aliases and create new Alias.eg-Alias name=xyz,)port no=1433,Protocol=TCP/IP,Server=Ip Add

    (192.168.1.1) or DomainNameInstanceName(JOI-MIMSSQLSERVER)

    Step9>do Step 6 & 7 in client Side.

    Note:-Remember Do all settings of SQL Server Configuration Manager in 32-bit version.ull see don't worry.

    Use this Connection string ->

    cnn = New SqlConnection("Data Source=<Alias name/ipaddress,port>;Network Library=DBMSSOCN;Initial Catalog=DATAbaseNAME;User

    ID=SQLCreatedUSERname;Password=Pass;")

  9. Duncan says:

    Thank you so much for this.  Saved me at midnight for a launch tomorrow!

  10. Hussain says:

    I didn't try your solution yet, but realy I want to thank you very much for your explanation.

  11. Roshan says:

    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 πŸ™‚

  12. oracle boy says:

    what kind o microsoft IDIOT mind this ?

    what for ???

    this way Everybady open ALL !

    IDIOT !!!

  13. kenan says:

    have you can do this automaticly with code

  14. Hunk RE says:

    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.aspx

  15. Syed Irfan says:

    Thanks you dear this is very helpful document i have deployed its successfully.

  16. Dan P says:

    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!

  17. Livingston Chesterfeld says:

    This is not a good article.  How can I connect to the server in question and configure it's properties when the problem is connecting to that server!??!  I appreciate the effort but everyone really sucks at helping.  

  18. Angel says:

    Thank You very much for providing for solving this dangerous error…….

  19. Angel says:

    Thank You very much for providing solution to solve this dangerous error…….

  20. Rami says:

    Thanks alot, that what I needed, so nice.

  21. Mohon says:

    Nice post. It helped me. I was facing problem for firewall

  22. zugutak says:

    This is very clever. It works for me.

    Thanks a lot.

    zugutak

  23. a says:

    ("Data Source=<Alias name/ipaddress,port>;Network Library=DBMSSOCN;Initial Catalog=DATAbaseNAME;User

    ID=SQLCreatedUSERname;Password=Pass;")

    what if thus is the error

  24. Jules says:

    Thanks!!!  Worked like a charm and your instructions were so easy.

  25. Raj says:

    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.

  26. Hari Krishna B says:

    Your article is very helpful to me… Thanks a lot

  27. Mark Chappell says:

    Nice article Daniel.

    Is there anything specific to do for the iPad 2 platform using a standard SQL Server app?

    Mark@En1gmaEngine.co.uk

  28. Rachel Fonseca says:

    All the details are a great help. Thank you!

  29. qntmfred says:

    Depending on your networking setup, you might need to set up port forwarding at the router too

  30. Ashiq says:

    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.

  31. prakash says:

    This article is a nice one, but i dont know where configuration manager is located in SQL Server 2012……help me please?  

  32. Taran says:

    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 advance

  33. Rachel says:

    What if you still can't get onto it after doing all the steps? Thanks

  34. Safi says:

    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.

  35. Mike says:

    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!

  36. Moses says:

    So after this what next. I was expecting to see what will happen after uploaded to a web server.

  37. hardik says:

    it is realy very very very usefull.

  38. Sukumar Reddy says:

    Thank You Very Much, This article helped me a lot

  39. Bill says:

    Very good.  Used it twice this week:-)

  40. Nilesh Mohite says:

    I Solved this connection issue on XP and Vista But On 7 your article help me ! Thanks Pro!

  41. 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.

  42. Hemant says:

    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?

  43. Pax says:

    @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 πŸ™‚

  44. rykk says:

    Thanks resolved problem on Windows 8 and SQL Server 2012

  45. Lewis says:

    It was the $%^ Windows Firewall! Thanks man

  46. shine says:

    i am still nt able to connect to other PC…bt d other pc is able to connect…wat could be d problem?..do reply

  47. shine says:

    also dat my pc is windows 8 n d other pc is windows 7..is dis a problem for connection??

  48. Karsten says:

    Thanks a lot for this article. I missed the tcp access here. Thumbs up for you.

  49. Serge says:

    Thanks, man. A very helpful walkthrough.

  50. Anurag Das says:

    Hello, I was indeed facing that same problem. Your article helped you. Thanks a lot sir

  51. Joey says:

    Thanks! Enabling TCP/IP did it for me.  Great article!

  52. Ken says:

    This was very helpful.  The step by step approach was great and it worked for me.  Thank you.

  53. faizal(fas) says:

    very nice article…..it is very helpfull

  54. I loved this one! Thank you for sharing! says:

    I loved this one! Thank you for sharing!  Cheryl Matrasko

  55. David says:

    Thanks for this article – most helpful indeed!

  56. Sergio Cormio says:

    Thank you very much! It was very useful in our project from Argentina.

  57. Matt Hoffmann says:

    Thank You so much for this. Great, clear and concise article. Really appreciate posts like this.

  58. Cinash says:

    Worked like a charm! Thanks a lot πŸ™‚

  59. Nel Garrata says:

    @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?

  60. Steve Boyd says:

    Thanks so much Daniel. Easy to follow instructions that even I could understand.

  61. Anil Gopie says:

    Daniel, excellent stuff there, helped me A LOT !!!

  62. Thanks, this is exactly what I have been looking for. It worked.

  63. Shabir Mehraj says:

    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 advance

  64. Happy says:

    Fantastic article.  This absolutely solved my problem.  

  65. Sai Rani D says:

    really the setting worked

    thanks for the info.

  66. Niels Quinteros Santana says:

    MUchas gracias es excelente la explicaciΓ³n, me ha ayudado mucho los felicito

  67. Chris says:

    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

    Chris

  68. Satish says:

    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.

  69. Ashfaq says:

    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 idea

  70. Shaz says:

    Thank you so much . you saved my day…:D

  71. Neel says:

    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..

  72. jasdeep says:

    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.

  73. Varinder Sandhu says:

    Very usefull article. Thanks for sharing.

  74. Janet says:

    Thank you so much.. simple and wonderful explanation given for the resolution. Excellent work. God Bless.

  75. Chamroeun says:

    It's very good, thanks you so much.

  76. Dominic says:

    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.

  77. Joo Mo says:

    This was s great help to me.

    Thanks a lot.

  78. Anna says:

    Hello, I tried all of this and still have the problem. I have windows 8, I don't understand the problem.

  79. aalia lyon says:

    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 lyon

  80. Balakrishna says:

    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.

  81. Mungai Kiarie says:

    Thanks for your tutorial it really helped.God bless you Daniel

  82. Amit Gupta says:

    Very very helpful and well defined. Keep it up

  83. David says:

    To the point directions,  easy to follow

  84. siva says:

    hi,  i have only server ip address , how to hack the sql database server.

  85. Joshua Kohl says:

    You Rule! Thank you so much!  I went through all of these steps and finally succeeded on the last one!

  86. Singh says:

    Many thanks. This was most helpful.

  87. Good work says:

    Thanks Daniel. What a detailed & wonderful article!

    Regards,

    Tony

  88. Robin Mason says:

    Thanks very much – It worked first time and saved hours

  89. Gladson says:

    Very informative and helpful. Thank you!

  90. suloveoun says:

    This article can fixes my issues. Recommend for who is same problem.

  91. Norm Feaster says:

    Thank you very much! The firewall configuration did the trick!

  92. Krishan Murari says:

    Very nice article. Saved alot of my time. Thanks Daniel!

  93. santosh dhoot says:

    Thanks you dear this is very helpful document i have deployed its successfully.

  94. Hasan says:

    Very useful article.

    Many thanks to you.

    Cheers.

  95. Richard L. says:

    Thank you so much for this guide πŸ™‚

    It has resolved my issue.

  96. Gary Kuipers says:

    Does not work on Win 8, still cannot telnet IP 1433 or connect with manager (Error 26)

  97. Kamrul Hasan says:

    Thanks you very much. its works for me.

    nice solution

  98. MH says:

    I believe you incorrectly stated results in your success. You cannot access SQL remotely with these settings, only from the DOMAIN. You have not allowed public access to SQL you would need to check the box for PUBLIC. Correct?

  99. Mahesha says:

    Thanks!

    If you still have problems, start up SQL Server Browser.

  100. dingx says:

    Thank you so much!

    This save me from half day struggling…

  101. Jim Redmond says:

    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.

  102. Prachi says:

    Really thanx a lot…!!! Nice article.. It has helped me !!

  103. tectist says:

    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="http://www.tectist.com/databases-online-training.html " >DATABASES training courses online</a>

  104. Ravi says:

    Thanks a lot Daniel for you great article.

  105. Ahmad says:

    Thank you Dear Mr.Daniel, it was very to the point problem solving, best of lack.

  106. Peter B says:

    This has been very helpful.

    Thanks a million.

    The P

  107. Cheryl Mabatid says:

    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?

    Thanks

  108. Sumit says:

    Hello,

    This was very helpful Thanks

  109. Lisa says:

    The last suggestion – create inbound rule was needed in my case.

    Thank you

  110. TheAZAndyman says:

    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.  

  111. david sol says:

    thanks a lot ..it's very interesting.

  112. Bilal Subhani says:

    Thank you very much.

    My problem is solved. Easy Steps and easy to capture the issue..

  113. venantius kardiyanto says:

    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.com

  114. Cath says:

    Awesome!!! Thank you… you're a lifesaver!

  115. Nadesh Thevar says:

    SQL Server Configuration Manager – TCP/IP was disabled.

    Thanks

    Very nice article

  116. Rumo says:

    Nice job. Thank you, this saved me! πŸ™‚

  117. Faizee says:

    Man, very helpful article. Helped me a lot…..Thank you !

  118. Bob Lippold says:

    Thank you!  The FireWall instructions got me past my issue.

  119. Dinesh Vishe says:

    Telnet sqlserver port => check telnet port is open

  120. ravi says:

    how to do this in visual studio 2013?

  121. Subodh S says:

    Thanks Daniel for sharing these screenshots.

  122. Selva_MSN says:

    It helped me to fix an issue I was having with a new application installed recently by an external vendor, specialized in SQL :((

    They did not know what to do…

    Thanks god I found this awesome article!!!

    Keep up the good work

    Regards

  123. Shiva says:

    Thank you so much, its a great article with very good step by step visual explanation

    cheers

  124. Tauseef Kaldane says:

    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 instances

  125. Ram S says:

    This Article has worked wonders for me.

    Only extra step was to "RESTART SERVER"

  126. Arvind Sharma says:

    great Suggestion.

    Cheers……….!

  127. Manny P. says:

    Thank you for the concise tutorial. I am now able to connect remotely with SSMS!

  128. Mukesh the leo says:

    Thank You for this nice article. Please keep on posting such good articles.

  129. Dave says:

    Daniel it appears that your images have become detached from the page (red Xs) but thanks for the article – it helped me solve a problem with connecting to a second server instance on the same box – I changed the port # to another unused number and it worked on the local server but I couldn't connect from outside until I set an Inbound Rule.

    Thanks for that pointer!

    Cheers.

  130. Jason Mortiere says:

    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.

  131. Darsha Hobson says:

    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

    ——————————

  132. Hrishi says:

    Well explained…Thanks for the article.

  133. ThienLong says:

    Great instruction: work fine and detail. Thanks a lot.

  134. Bradley says:

    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.

    cheers

  135. Steven Nguyen says:

    Thank you very much, you saved my life πŸ™‚

  136. Sandeep says:

    Issue not fixed. Done with all the above mentioned steps. But getting same error still.  πŸ™

  137. Devinder Bawa says:

    It worked hassle free for me. Thanks For this good article

  138. Gary says:

    Great article and nicely explained. Thanks

  139. Kenneth says:

    This post is still helpful! I just had this exact problem, turns out it was the firewall. Thanks for the great walkthrough!

  140. No_ID says:

    Thank you, It works, now I can do my things. Godbless you.

  141. Zain says:

    I read this article and implemented it sucessfully on windows server 2012 R2 with SQL Server 2014.

  142. José says:

    Sorry for my lack of experience.

    But now that the configuration is done, how can access this database?

    isnt there any ip?

  143. Gib says:

    Thanks for the post is very useful!!!!