Enable Remote Connection on SQL Server 2008 Express

336 Comments

1. Robert April 2, 2009

   Thank you so much for publishing “Enable Remote Connection on SQL Server 2008 Express “. It was exactly what I needed.

   Cheers,
   Robert
2. Aju Peter April 7, 2009

   Thanks!. It worked for me. JUST START “SQL SERVER BROWSER” service!.

   There are two things I learned
   1. SQL server 2008 does not have much to do with SQLServerAgent.
   2. ServerBrowser is instrumental in connecting to SQL Server from remote machines (FROM LOCAL MACHINE THIS IS NOT REQUIRED)
3. Matt April 8, 2009

   Thank you very much for putting this tutorial together. It has been very helpful!
4. Ronald April 22, 2009

   Thanks!
5. SGR April 22, 2009

   Thanks!
   This tutorial helped me to connect to SQL Server 2008 Express from MS Access 2003 via ODBC. Now I can use MS Access as a front end to SQL Server 2008 express.
6. Richard April 30, 2009

   Thanks!

   This fixed the problem i had. I have been running SQL Server since October and for some reason i just couldn’t login this morning. Perhaps something else has changed on my PC??

   Richard
7. Jason May 5, 2009

   This tutorial is great!

   Your instructions are thourghly written and should have assisted me in establishing the connection, but I keep getting the following:
   Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)

   Ultimately, I am trying to use my local SQL Server Management Studio to connect to the remote SQL Server 2008 Express instance provided by my web host provider.

   What could I be missing?
8. Sakoda May 7, 2009

   It’s so great and very helpful!
9. linglom May 12, 2009

   Hi, Jason
   There may be a login problem. You should observe error state in the error message. See the post below for more detail.
   Understanding “login failed” (Error 18456) error messages in SQL Server 2005
10. eiad May 23, 2009

    You are a good person. Thanks so much!

    Eiad
11. Jampa June 4, 2009

    Thanx a lot mate, your tutorial helped me a lot…
12. Vadim June 4, 2009

    Thanx man, it was very helpfull for me
    keep the same way
13. Ben June 15, 2009

    Fantastic – you have saved me a lot of time – many thanks!
14. Thurein June 17, 2009

    Thanks for this post. It is really helpful.
15. Azec June 18, 2009

    Hallow.

    I did all you post, and also add ports 1433 + 1434 to firewall… and it is not workin, i tried near 2 days (( nothing changes. it drops 10060 Sql Server Excheption
16. MrGreek June 24, 2009

    Hello.. I have tried for some while now to get my SQL Server Browser running but whit no luck…
    Then I saw your site and i se that I have all ready tried all the things correct.. but when I´m trying to change to Local Service under Built in account… then I get som errors!!
    Can´t start. No active units are associsat with it… (ore somethings like that.. do you have any suggastions? I don´t know what to do now! HELP
17. linglom June 25, 2009

    Hi, Azec
    The error 10060 indicates that the client cannot open a socket to the SQL Server. You can verify the connection to the server by using telnet command with port 1433.

    I think the problem could be a firewall or some hardware is blocking the traffic.

    ---

    Hi, MrGreek
    Can you show the exactly error message?

    What if you specify other user account (domain users, network service)? Does the error message still showing?
18. Geraint June 30, 2009

    Spot on! Three hours of messing around and searching Microsoft’s site. This is the best reference on the web at the moment for enabling remote access on SQL Server 2008.
19. Pooya Zandevakili July 2, 2009

    Thanks so so much 🙂
20. Renee Kraft July 9, 2009

    Thank you so much! Your posting was a huge help.
21. Marius July 21, 2009

    Thank you for both the 2005 and 2008 guide! Helped me with both as we have both at work!
22. ig4 July 23, 2009

    check win server 2008 firewall public profile state =off
    start–>Administrative Tools–>Windows Firewall with Advanced Security, to click Properties,to select Tab “Public Profile” set firewall state =off.
    Don’t need restart machine.
23. jeff August 7, 2009

    excatly what i needed. you are awesome.
24. Engin August 9, 2009

    Thanx to Turkey
25. fer August 15, 2009

    Thanks. It is very helpful.
26. Serdar August 19, 2009

    Thanks so so much to Turkey
27. IS Thakur August 23, 2009

    Thanks a lot for such a valuable info.
28. matthew August 28, 2009

    WOW! thank you, thank you, thank you!!
29. Ken September 8, 2009

    This is such a great write-up. Thanks!! so much for taking the time to pull this together.
30. ali September 14, 2009

    Thank you so much!
31. sayoni October 7, 2009

    Thanks a lot… it solved my issue…..
32. MAsk October 8, 2009

    Very usefull thank you

    Don’t forget to use connection format :
    “SERVER_IP\SQLEXPRESS”
    When asked server adress or creating ODBC connection

    also (for me) in step 6 :
    “Protocols for SQLEXPRESS.” make sure not to be in “native client” section because TCP/IP is runnning by default.
33. rctaubert October 18, 2009

    I have followed your outline above to the letter.

    On WHS server I have set the Firewall…Exceptions…Add Programs:

    SQL Server Error and Usage Reports
    SQL Server Management Studio
    sqlbrowser.exe (C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe)
    sqlservr.exe (C:\Program Files\Microsoft SQL Server\MSSQL.10SQLEXPRESS\MSSQL\Binn\sqlservr.exe)

    I added the port: SQLExpress 1533

    The log shows no indication that the server even knows there is a logon attempt.

    On my laptop using SQL Management Studio I go to the “Connect to Server” dialog box, click the down-arrow on the Server name box…Browse for more names…Network Servers tab.

    In the text box SERVER1\SQLEXPRESS (10.0) appears. I click on it and click Ok.

    I select SQL Server Authentication and enter the sa login and the password I set for this login.

    It will eventually time out with 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: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    What am I NOT doing right????

    On the server everything seems to work just fine.
34. linglom October 19, 2009

    Hi, Rctaubert
    Are you sure that the SQL Server is running on port 1533? By default, it is 1433.

    I would suggest you temporary disable the firewall and retry connect to the SQL Server to see if the problem is at firewall configuration or not.
35. rctaubert October 19, 2009

    I am sorry. I meant 1433. BUT, it turns out it was set to 1578. netstat -an show it was listening on that port.

    If I use Server1\SQLExpress,1578 it works.

    How would I get it back to 1433???
36. Shoikat October 25, 2009

    Thanks.
    It is very simple but very helpful.
37. J.Ackerman October 26, 2009

    @No.2: even the _local_ SQL Server Express installation is dependent from the running SQL Server Browser service. When it’s not running then even on local machines you can not connect.
    Anyway thanks for this help to enable connection!
38. linglom October 27, 2009

    Hi, Rctaubert
    You don’t need to change if it’s working properly. But if you really want, just open SQL Server Configuration Manager -> expand SQL Server Network Configuration -> click on Protocol for your SQL Server -> double click on TCP/IP -> Click on IP Addresses tab. On IPAll section, here you can change port value to the new one by modify the existing value. Then, restart the SQL Server service.
39. Sareesh November 6, 2009

    Thank you for your nice and clean article.
40. Panwar November 19, 2009

    Hi linglom,

    This query is related to SQL server express 2005. By default if we install SQL server express 2005, it does not allow remote access to the created instance. Is there any way to allow remote connection to the created instance without using the UI (e.g. SQL Server Configuration Manager) i.e by changing some registry or invoking some exes? I am creating silent SQL instance (no UI).

    TIA.
41. Jyothsna November 20, 2009

    Hi,

    Thanks a lot for this well explained tutorial along with screenshots. Appreciate your patience.
42. David November 24, 2009

    Thank you!! I was trying to get some work done at home and I couldn’t figure out for the life of me why my sql server was not working (and it was working yesterday too…)
43. jim s November 29, 2009

    Thanks! This was causing me much consternation.
44. Moonheart November 30, 2009

    thx for this solution
    but i have WMI provider error
    plz how can i connect to SQL server 2008 in my PC
45. Gerry December 10, 2009

    Thanks very much for the sql 2008 set up information it was very helpful and enabled remote connection. However, I cannot seem to get Microsoft Access to connect and my host company are using port 1334 instead of 1434 and MS access dosnt seem to allow me to change the port?
    Help wouls be appreciated.
    Regards
    Gerry
46. joyhan December 14, 2009

    Thanks a lot. It is very helpful!
47. imtiaz December 16, 2009

    after your setting cannot my native client and sql service start

    solve my problime
48. Abdiel December 18, 2009

    Thanks! I was about to reinstalll the whole sql 2008.
49. KMBrewer December 19, 2009

    THIS IS THE ONLY FIX FOR THIS ISSUE -PERIOD!

    Thanks so muchmy friend this has really been a headache for me. After 3 install/uninstall attempts nothing was working for me. Again, thanks for the detailed resolution to this anomaly.
    KMB
50. linglom December 30, 2009

    Hi, Moonheart
    Can you show content of the error message?

    ---

    Hi, Gerry
    You can try to connect to the SQL Server using SQL Server Management Studio from a remote PC to see if the SQL Server is configured correctly.
51. ALI December 30, 2009

    Thank you very much… very nice and successful presentation.
52. Orin Hooverson January 3, 2010

    Thank you so much for publishing “Enable Remote Connection on SQL Server 2008 Express “. It was exactly what I needed. Your solution was very clear and to the point. Next time I look to google for help and not just see how many time I can install the same issue.

    Thank you for your time,
    Orin
53. Arun January 8, 2010

    Thanks…
54. NKO January 11, 2010

    thanks! very helpful indeed
55. leo January 15, 2010

    I can enable the sql browser
    btw im seeing two instance. a sqlexpress and mssqlserver
56. Aaron January 18, 2010

    thanks..
57. DJeffer January 27, 2010

    Thanks a lot! Very helpful.
58. gelder February 2, 2010

    Thanks my friend!
59. abhineet komal February 5, 2010

    thanx, u have explained it brilliantly
60. Ulyana Arsov February 5, 2010

    Thank you, this helps a lot, I love you. This only help me while days of researching for the stupid Login fails with that user. Thank you, thank you, thank you 🙂
61. charles February 6, 2010

    Very Good Thanks 🙂
62. Gautam February 7, 2010

    I have installed SQL server 2008 on windows server 2008 R2. windows authentication mode is working but SQL Server authentication mode is not working giving errors login failures error code 18456, I have not any SQL user I don’t know the password of user “sa” I created user in SQL Management studio, user created but could not login as sql server authentication. Please help
63. JVillamil February 7, 2010

    Gautam:
    You need a user in SQLServer to login and the pass off course
64. Gautam February 7, 2010

    MR. JVillamil, I had already created a SQLServer user by using windows authenticated administrator login and this user couldn’t login. Furthermore I am using windows 2008 server R2 and I have also switched-off the firewall of private public network
65. JVillamil February 7, 2010

    Gautam that’s rigth, I’m sorry I should say: change of
66. JVillamil February 7, 2010

    Gautam that’s rigth, you should change of Windows Autentication to SQLServer Autentication to the user and the installation of SQLServer should indistinct in two cases…

    I’m sorry by my english, but I only speak spanish (see you from Mexico)
67. Daniel February 7, 2010

    This was really helpful but unfortunately I still can’t get a connection. I’m trying to use the “ASP.net SQL Server Setup Wizard”. Every time I try to get a list of the databases in SQL Server I get the remote connections error:

    “Failed to query a list of database names from the SQL server. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verifty 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)”.
68. JVillamil February 8, 2010

    Daniel: why no connect by TCP / IP ???
69. Discount Code February 8, 2010

    Thanks a lot.. It was exactly what I was looking for. Much appreciated..
70. Gautam February 8, 2010

    Hi Mr. JVillamil & all, The connection error of sql server authentication is solved by making a change in the registry.By default the windows server 2008 R2 having registry setting for the windows authentication by changing the value of registry key I could solve the problem.
71. linglom February 11, 2010

    Hi, Gautam
    Could you share what registry that you changed?

    About your problem (error code 18456), you should look into the SQL Server’s log which is located in Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. You will see more details on the error message. The example error message should looks similar to this:

    2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8

    You see that there is state number which tell the detail of the error message. Here are some common error states and their descriptions:
    2, 5 -> Invalid userid
    6 -> Attempt to use a Windows login name with SQL Authentication
    7 -> Login disabled and password mismatch
    8 -> Password mismatch
    9 -> Invalid password
    11,12 -> Valid login but server access failure
    13 -> SQL Server service paused
    18 -> Change password required

    Reference: Understanding “login failed” (Error 18456) error messages in SQL Server 2005

    ---

    Hi, Daniel
    Check your SQL Server’s name to see if it is correctly. This wizard always generates the server name as [MachineName]. But if you are using SQL Server express, it should be [MachineName]\SQLEXPRESS.
72. dilip February 18, 2010

    http://localhost:8080/reportserver but i want to use only http://localhost/reportserver but it not connect because port number require but how can connect without port number.
73. JVillamil February 19, 2010

    mhmhmh, you should configurate the IIS to run the ReportServer to the port 80
74. Sinbad1 February 23, 2010

    I can’t thank you enough !
    Excellent walkthrough .
75. PBC February 25, 2010

    Very good explanation. I am still in need of help. At Step 4, the following error message appears:

    “The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.”

    Any advice?

    Thanks …
76. terirog February 26, 2010

    Thanks! Clear and very helpful.
77. Agus March 17, 2010

    Hi, Mr. Linglom
    I think this is very good posting,
    but I have a question how about configuration setting ODBC for Ms. Access in PC Client to connect to the Windows Server 2008?, which is I put the master Database.
    Tks for your answer…
78. GIJack March 17, 2010

    I still cant connect using ipaddress
79. Andy March 19, 2010

    Outstanding. Thank you for the straightforward (and correct!) explanation.

    I’ve been going around in circles on this problem for hours, and Microsoft’s online resources have been unhelpful.
80. Gojko March 19, 2010

    It’s awesome, thnaks….
81. kv April 7, 2010

    thx for the info that was what i was looking for
82. Deepika April 14, 2010

    I have completed till 9th step successfully..but microsoft sql server 2008 management studio does not found in my Start menu. Please solve this query.
83. linglom April 14, 2010

    Hi, Deepika

    You can use Microsoft SQL Server Management Studio from other pc to connect to the SQL Server. Or you can download and install the tool – Microsoft SQL Server Management Studio Express.
84. Mike April 20, 2010

    Thank you for the article and thanks too to linglom. Having to append “\SQLEXPRESS” was not obvious at all. Changing my “Server name” value from “192.168.1.112” to “192.168.1.112\SQLEXPRESS” did the trick for me.
85. Ravi April 20, 2010

    thanks a lot buddy,. it helped me a lot
86. Vibgy Joseph April 22, 2010

    @PBC – Hey I also got the same error “The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.”. I solved it by the following steps…

    1. Start -> Run
    2. Type “services.msc” and click “Ok”
    3. Locate “SQL Server Browser” service
    4. Right click on it and select “Properties”
    5. In “General” tab select “Automatic” in “Startup type”
    6. Goto “Logon” tab
    7. Select “Local System account” in “Log on as” section
    8. Click on “Ok”
    9. Now right click on the service and select “Start”
87. wn April 26, 2010

    Thanks, exactly what I needed.

    Has anyone tested how many simaltaneous users can access the db installed like this for simultaneous w+r access? I know each db can have it’s own issues, but just wondering if there has been any testing / reporting?

    Thanks
88. paramsrikant April 28, 2010

    Thanks a lot. It is very helpful!
89. itl May 6, 2010

    The “,” saved my day!!

    So, if SQL is on different port then default 1433

    SERVERNAME(OR IP)\InstanceNAME,PORT

    ex: SQLSRV01\SQL2008,1588

    thx!!
90. Keren May 17, 2010

    THank you deeply!!! great article – helped a lot!
91. Edward May 17, 2010

    I owe you one! This is sheer gold. Got it running v. quickly.
92. Andy May 19, 2010

    Great article linglom!

    For some reason my SQL Server Management Studio Express had to be given “Server1\SQLExpress,1433” to be able to connect to remote instance.
93. Kepa May 19, 2010

    Thank you very much, is a perfect guide!!
94. RahulR May 20, 2010

    Thanks a Lot! It worked for me!
95. Puroh May 20, 2010

    Thanks.. This is how all guides should be written! Nice work
96. Jan May 31, 2010

    Thank you so much! Been struggling with the port issue for hours.

    So instead of a normal ‘: – then use ‘,’

    Thanks!
97. Derrick June 3, 2010

    thank you so much..

    it help me a lot..

    thanks..
98. Sarmad June 18, 2010

    Thanks a lot man !! it is very Helpfull.
99. Sarath June 21, 2010

    Nice article man. But my issue is a bit different. I have a server in which both SQL server 2005 and 2008 are installed. few databases are in sql server 2005 and few are in sql server 2008.
    I am able to connect to 2005 from my local system but unable to connect to 2008 from local system. Can you please solve this issue.
100. Mehboob June 24, 2010

     Yup,nice article but i had follow all the steps and still i can’t connect remotely to Sql Server 2008 database.
101. Sundar July 1, 2010

     Great Thanks.
102. Robbie Reyes July 1, 2010

     VERY NICE!! EXACTLY WHAT I NEED! THANKS!
103. Dave July 2, 2010

     Thank you, Thank you, Thank you, Thank you, Thank you, Thank you…………….
104. Dams75 July 7, 2010

     Thank you Mask for connection format.
     You just saved me.
105. Cathy Ooi July 8, 2010

     Thanks! This was very helpful. M$ should take note and give us concise simple instructions to their $#$@#$ idiosyncrasies.

     Also much thanks to Bud Aaron for the link. This and Bud Aaron’s link answered simply what I can’t get out of M$ help.

     Wish I’d seen this earlier. Would have saved me days of figuring out what the heck is wrong when I can’t connect remotely. And I can’t find any Listening PORT 1433 on “netstat -na”.
106. Indoril Nerervar July 11, 2010

     “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)”

     The above is the error I recieved and I have expended days on this incredibly annoying issue – I have trawled through forums and Google looking for answers, made changes to my Registry, contacted the rather stupidly named “Microsoft help”, uninstalled and re-installed SQL Server 2008 on numerous occasions and generally been ready to dump my Windows machine in the bin. I eventually arrived at this site, looked at the comments above and found that my SQL Service was not Listening on port 1433!

     This is why people turn away from MS products. I am glad to see their market share dwindle slowly but surely and await the day when they will, end up where the sun doesn’t shine. I hope its sooner rather than later!

     Rant over – btw – thanks to your post and the commentators.
107. Asifali Kannattil July 16, 2010

     Hi Linglom,

     Thank you for your valuable information, I’m able to connect to remote server now. But another problem still exists. I initialized a Publication and Subscription on Local server…its working perfectly. But in the case of subscription for Remote server (Added Remote SQL Server Subscriber from New Subscription Wizard) nothing happens like error and also any updations. May I want to do something in Remote Server for “Replication”?
     Pls help…
108. Asifali Kannattil July 17, 2010

     Hi Linglom,

     I have Two Machines (PC1 and PC2) and installed Sql Server 2005 on both. I initialized a ‘Merge Publication ‘ on PC1; Publication database was “db_Inventory” .I ticked the check boxes “Create a snapshot immediately” and” Schedule a snapshot Agent to run at the following times” and changed the schedule to occur every 5 minutes Daily.
     I want to Subscribe this Publication on a database ‘Master’ which is created on PC2. For that purpose I created a Subscription on PC1 (Subscriber and Subscription database is PC2 and Master. ) successfully.
     I inserted new records to db_Inventory using My Accounting Software that is running on PC1. The problem I’m facing is that the newly inserted records to Publication database are not getting inserted in the Subscribing database Master. But I can insert records to this database by simply changing the Database connection in My Accounting Software (The two databases having same structure).

     For testing purpose, I created a new database Master_B on PC1 and created a new subscription for that db. In this case, all records I inserted to db_Inventory are getting added in Master_B.

     I don’t know how to solve this issue. Help me pls…….
109. Manish Jaiswal July 22, 2010

     This is a great great post…

     Thanks
110. Jmunoz July 23, 2010

     I’m setting Up a SQL Server 2008 R2 on a Windows 2008 Server, and I can’t get connected because of a Windows Firewall issue, any idea?
111. victor July 23, 2010

     thanks from maputo!!!!
112. Pingback: Enable to create ODBC Connection to SQL Server 2008 Express « Network PC Tech Blog July 25, 2010
113. Rakesh July 27, 2010

     Great .. was tensed .. thinking will I be able to do with SQL Server 2008 R2 Express or not .. your clear explaination solved my problem in minutes .. thanks
     But remotly I was forced to use Server IP address. Can it be connected by using server name?

     Thanks
114. Traci July 30, 2010

     Thank you! Saved me a ton of time!
115. Farshad July 30, 2010

     Hi , thanks for your kind help to all programmers like me.
     I have to make a setup that has to be installed on many PCs in different (about 80)cities of my country , so I have to make a script to enable TCP/IP in SQL Express.
     would you please help me if you have such a script to do this job ?
     looking forward to hearing from you.
     lots of thanks
116. Bruno July 30, 2010

     Tks for this very helpful explanation!
     But I still face a problem to find the server when I try the remote access of ODBC.
     The message I get is
     Connection Failure
     SQLState: ‘01000’
     SQL Server Error: 2
     [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen(Connect())
     Connection Failure
     SQLState: ‘08001’
     SQL Server Error: 17
     [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server unavailable or access denied

     Can you help me?

     Thanks again
117. melih August 3, 2010

     Thanks !!!!!
118. Jasper August 3, 2010

     Thank you very much. Very clear explanation and works like a charm!
119. migro August 5, 2010

     I got a named SQL server 2008R2 instance running on a 2008R2 Terminal Server. Mixed authentication is activated because my application connects with user and password via ODBC.

     When I logged in with admin rights, the application can access the database without any problems. When I’m logged in as a normal user I can’t get access to the database. Do You got any suggestions to solve this problem?

     When I establish a new ODBC connection to a SQL server/database running on a separate maschine it works with normal user rights. So I think the problem relies to the local instance of SQL Server 2008R2.

     Thanks in advance for Your help.

     Regards
     michael
120. migro August 5, 2010

     Problem is solved. The SQL Browser service wasn’t running 😉

     Thanks a lot for this article.
121. pankaj August 9, 2010

     Thanks a lot its really nice and helpfull
     Thanks
122. Liberty!!! August 12, 2010

     This also worked for my DEVELOPER Edition of SQL Server 2008.
     THANK YOU!!!
     THANK YOU!!!
     THANK YOU!!!
123. Sandae August 12, 2010

     THANK you!!!
124. sunsetquest August 13, 2010

     Thank you!!!!!
     fyi – I did not need to perform step one.
125. George August 18, 2010

     Thanks, but I still have a problem. I can connect from machine/instance A to machine/instance B (‘green play-button’) with SSMS, but when I connect from machine/instance B to machine/instance A I see a ‘white dot’ instead of the ‘green play button’. Both firewall’s are off and I’m pretty sure the SQL Server Browsers are running and that I enabled TCP/IP on both machines…
126. Rajesh August 20, 2010

     superb…thanks..u increase my challanging
127. Sivawut Srithammawut August 26, 2010

     Thanks you so much.

     This is very clear..

     -Pipe
128. Ilya August 27, 2010

     Thanks! It’s really great manual!
129. Paul August 29, 2010

     Thanks for the guidance, solved my problem in minutes once I started reading through the comments.
130. Paul August 31, 2010

     Thanks a lot…….
131. Jeffrey September 1, 2010

     Thank you so much for this, I was working on this for a few hours trying to get it work. Real life saver.
132. eqirtas September 3, 2010

     Thanks, yes it works but my one more question.

     I want to attach mdf database file for database operations. As i can attach db file on my own local system and it works.

     Is it possible if mdf file is on other systems. and then i can attach for operations??

     I am using sql express with a desktop application in C#

     Regards
     Eqirtas
133. Chanty September 8, 2010

     Thank-you, very much the tutorial is very clear and it worked!!!!
134. bpelhos September 9, 2010

     Very important, just repeating one of the comments:

     Don’t forget to use connection format:
     “SERVER_IP\SQLEXPRESS”
     When asked server adress or creating ODBC connection
135. Jguzman September 11, 2010

     thanks a lot man !!
136. david September 22, 2010

     thanks for posting this, the step-by-step with screenshots were PERFECTO, just what i needed. thank you again for taking the time to publish this!
137. Imran September 22, 2010

     It worked for me.
     Thanks for posting this helpful material.
138. Abraham September 23, 2010

     Worked for me as well. Thnx a lot of the instructions.
139. AD September 30, 2010

     Great help!
140. transportinoradea.ro October 5, 2010

     thanks a lot 😉
141. rodcompo October 6, 2010

     I’ve tried this but still not getting connected. I have a SQL Express R2 instance on my XP desktop I’ve got a laptop with W7 also running a SQLExpR2 instance. I want to connect the laptop to the desktop instance (and vice versa) using the SQL Management Studio but getting no joy. Got the browser service and network protocols running but still can’t see or connect to either instance from laptop or desktop – any ideas please
142. Pingback: multi seat set up... - MediaPortal Forum October 9, 2010
143. linglom October 11, 2010

     Hi, rodcompo
     It could be blocked by Windows firewall, try to disable Windows firewall and reconnect again.
144. kushi October 17, 2010

     Awesomw steps and comments !…. it worked like a magic after 1 week of struggle to connect to my sql server db.

     thaks a ton 🙂
145. Abhay October 18, 2010

     Superb work, This will help me a lot.

     Regards
     Abhay
146. phpbugs October 22, 2010

     I am able to connect from remote machine using the “sa” username. I created another user called “asd” and I am not able to conect to the DB with this user.
     Please help!!!!!!!!!!!!!
147. Leonardo Suarez October 26, 2010

     Hi, Is needed the Sql Server Browse is started, I think its dangerous if you put the DB server in internet. There is another way or I’m just in panic. Thanks
148. phpbugs October 26, 2010

     I got it worked. I deleted the user “asd” and created the user with sysadmin privilege.
149. CCSWebWeaver October 27, 2010

     I followed your instructions and now I can connect my SQL Express 2005 and SQL Express 2008 servers to / from MS Access 2003 database; this opens up a whole world of possibilities for me!

     Thank You for this wonderful well written guide!
150. tobon424 October 27, 2010

     I have 5 computers connecting to a DB
     3 connecting fine, but 2 are not connecting, all computers are winXP Pro SP3

     This is the error message that I get from the two computers I cannot connect:

     Error Number: -2147467259

     SQL 2008 could not open a connection to SQL server [53]

     I know something is blocking the connection, but not sure what it is.

     Why the other computers can connect, all are connected to the same router, switch etc.

     I can see all computer within network, I am able to ping the server from the computer I cannot connected to the DB.

     Thank you
151. Manuel Tenorio October 28, 2010

     Thank you a lot for your excellent post!

     Some of us just don’t born with the DataBase Management gift… whatever that means 🙂
152. balakrishnan October 28, 2010

     Thanks friends, please tell us some more tutorials, Thank you
153. Geo November 3, 2010

     I’m having the same problems I cant connect to the db from any other server. I have checked that SQL browser is running but I cant see it in config manager but I can see it under services? any thoughts SQL 2008 windows sever 2008.
     Thanks in advance for any pointers
154. Bungei November 4, 2010

     Big Thanks for this, my issue was fixed in seconds!
155. phoeniks November 10, 2010

     THANKS for this! You are great!
156. Elham November 10, 2010

     hello and thanks
     but I did all tasks that you said in your posts but it also doesnot work and return Error26.
     My windows is vista.
     what can I do now…??
157. sim November 10, 2010

     Thanks alot! That was really useful.
158. kachwahed November 11, 2010

     Hi, Great thanks for share
     I did all tasks and I still get:
     [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server unavailable or access denied
159. Bob_SA November 11, 2010

     Thanks linglom! Ive used quite a bit of info from your blog over the past few days getting sql2005 express and sql2008 express running concurrently(dont ask) with remote access. Saved me a lot of time!
160. linglom November 12, 2010

     Hi, Elham
     This post could help about error 26 – SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified

     ---

     Hi, Kachwahed
     It seems that you are using ODBC driver. Can you show your connection string?
161. Greg November 14, 2010

     This worked. Thank you!
     Why they disabled TCP, Is it any danger to enable it?
162. Kachwahed November 16, 2010

     Hi, linglom. Thanks for reply
     It’s OK now, I think the issue was in “Listen All” in TCP/IP properties, it should be set to “Yes”, add that in the article.
     Great thanks linglom
     Regards.
163. ahmet November 21, 2010

     i did everything but it didnt solve my problem
164. btaz125 November 26, 2010

     Hi,
     How to config SQL Server 2008 to connect over Internet.
165. victor November 30, 2010

     thank you very much ..
166. Jeff November 30, 2010

     Hi,

     I followed the entire guide. I disable windows firewall, enable DMZ mode for my computer with mssql server.
     I am able to connect from a differnt PC under the same LAN. However when I try to connect from a PC under a different network using SQL Server Management Studio and it doesn’t work and gave me error 26. I followed one of your links on solving error 26 you provided and followed the content listed below:

     ) Make sure your server name is correct, e.g., no typo on the name.
     2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
     3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
     4) Make sure SQL Browser service is running on the server.
     5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

     I am pretty sure I meet the requirements for all 5 points above. For part 3, I am able to ping the server in command prompt, is that good enough?

     Anyways I am totally clueless as what is wrong in my configuration. I would very much appriciate it if you can give me some pointers.
     Thanks!
167. Pingback: CodeEase.com » Blog Archive » Again: Remote connect to SQL Server 2008 Express December 1, 2010
168. Robertas December 1, 2010

     That worked, thanks!
169. Arinola December 2, 2010

     oh thank you thank you soooooooooooomuch. WORKS greatly for me. i am so happy..thanks!!!
170. Eric'H December 2, 2010

     Thank you! Thank you very much!!! : )
171. kyriakos December 2, 2010

     Thank you!!!!!
172. Jeff December 3, 2010

     Hi,

     I followed the entire guide. I disable windows firewall, enable DMZ mode for my computer with mssql server.
     I am able to connect from a differnt PC under the same LAN. However when I try to connect from a PC under a different network using SQL Server Management Studio and it doesn’t work and gave me error 26. I followed one of your links on solving error 26 you provided and followed the content listed below:

     ) Make sure your server name is correct, e.g., no typo on the name.
     2) Make sure your instance name is correct and there is actually such an instance on your target machine. [Update: Some application converts \\ to \. If you are not sure about your application, please try both Server\Instance and Server\\Instance in your connection string]
     3) Make sure the server machine is reachable, e.g, DNS can be resolve correctly, you are able to ping the server (not always true).
     4) Make sure SQL Browser service is running on the server.
     5) If firewall is enabled on the server, you need to put sqlbrowser.exe and/or UDP port 1434 into exception.

     I am pretty sure I meet the requirements for all 5 points above. For part 3, I am able to ping the server in command prompt, is that good enough?

     Anyways I am totally clueless as what is wrong in my configuration. I would very much appriciate it if you can give me some pointers.
     Thanks!
173. Daniel December 3, 2010

     Great work!
     Thanks
174. rick December 4, 2010

     tks 4 details!
175. Arjun December 5, 2010

     It was very helpful. Thanks a lot.
176. mrcity December 5, 2010

     One thing that hung me up for a while was the different “modes” of Windows Firewall — domain, private, and public. In testing, I had only shut off Domain, where I should have disabled the other two. Of course, the best thing to do is to add an exception for the particular port you’re using across all 3 modes.

     @Jeff, perhaps try looking at your TCP/IP configuration in Sql Server Configuration Manager (as described above), but double-click on it to examine the settings. In the “IP Addresses” tab, make sure the correct IP addresses and ports are enabled and set (you probably want to clear “Dynamic Ports” and enter a static port # in “TCP Port” – most people pick 1433). Go to the bottom, to IPAll, clear “Dynamic Ports”, and put in the desired port # in “TCP Port” just like the ones you have enabled above.

     – Stephen
177. Jeff December 7, 2010

     @Stephen

     The TCP/IP config are all set just like you stated, yet no luck.
178. Mayur December 8, 2010

     Thanks a lot … keep up the good work.
179. SRAVS December 21, 2010

     great job!!!!!!!!!!!!!! resolved my issue……..THANKS 🙂
180. nildeen December 27, 2010

     It would be great to expand this great walk-through to include server 2008 firewall setup 🙂 I’ve turned off the domain inbound for now and will need to go figure out the rest later.

     Anyhow, thank for the help!
181. Ivan Lewis December 28, 2010

     Can i connect to sqlserver 2008 express from sql server 2005 express edition?
182. MedMost January 4, 2011

     Thank you very much

     it help to solve my problem

     i think i cant log on server2008 from server2005express
183. Terry January 13, 2011

     Really great..!!!

     I have followed it without any error or mistake.

     Very good.
184. Guido January 13, 2011

     Bravo!!
185. Roman January 18, 2011

     If you follow the above steps, but the remote machine cannot connect, check your firewall software to ensure ports 1433, 1434 are open.
186. Teimoor Khan January 23, 2011

     Thanks A lot to publish this article…….this article would help me in my fyp…….
187. Chuck January 25, 2011

     Thank you. This helped me today.
188. Plal January 27, 2011

     As mentioned in 89. “,” worked for me. Thanks!
189. Ankit February 3, 2011

     hi.. Can we achieve this using script or command prompt??
190. jcy February 17, 2011

     excellent guide, thank you
191. Domino February 17, 2011

     My great thanks!
192. Adam February 18, 2011

     When all else failed your instructions worked.

     FYI – I did have to reboot after following instructions.

     Thanks again.
193. lax February 18, 2011

     Hi thanks in advance.
     There are three machines in LAN.
     Mac 1 contains windows server 2008 + Sql server 2005 + package X accessing local db
     Mac 2 contains windows 7 + Sql server 2005 + Package Y accessing local db
     Mac 3 Contains windows xp

     problem
     from mac 2 and 3 package x is working and retrieving data from x db
     mac 3 access package x and its data, but not y and its data.
194. janerose February 19, 2011

     O M G you are an angle sent down from heaven i want to buy an aeroplane and a jet ski. I have been trying to fix this error for the last 7 days my eyes hurt i was on the verge of jumping from my staircase fml. My stupid teacher who has been teaching this university module using this software for the last flipping 10 years he had no clue and gave no help (he will be egged soon).

     anywayyssss thankkkk youuu so much!!
195. Steve February 22, 2011

     Excellent information. Thanks. You save me hours of work.
196. Mindwarp February 23, 2011

     Excellent! Thank you very much for this information!
197. Ivanov February 23, 2011

     Thanks indeed!
     I did what you told, but had to do a bit more. It was about the ports in the Firewall.
     In the “server” I opened for incoming these ports:
     TCP 1433
     UDP 1434
     And also authorize the sqlserver.exe through the Firewall.
     More on that, here:
     http://msdn.microsoft.com/en-us/library/cc646023.aspx
198. MathewUy March 3, 2011

     Thank You so much for this very useful information,
     GOD Bless people like you who take time and effort in making this.
199. Darshan March 10, 2011

     The tech blog was really useful it was very informative. Thanks a lot!
200. Krzysztof March 10, 2011

     Big thanks. This tutorial is verry helpful, working good 🙂 Greetings from Poland
201. Jared March 19, 2011

     Great post.

     I had the same error as Moonheart in Nov 2009: “Cannot connect to WMI provider.” For anyone else with the error, I solved it with the help of this post: http://msmvps.com/blogs/martinpoon/archive/2009/11/27/sql-server-configuration-manager-cannot-connect-to-wmi-provider-invalid-class-0x80041010.aspx.
202. Nirav March 22, 2011

     Thanks a lot mate…really helped..
203. Suresh March 22, 2011

     Hi, I have installed sql server 2008 on a windows server 2008 system.I would like to access sql server browser to query data from other system but this doesn’t sql server 2008. Is it possible to access like that ? I followed your steps. Thanks.
204. linkoblast March 30, 2011

     Excellent….I was looking for such a good solution…..it saved my day!!
205. Roshani March 31, 2011

     Thanks a lot!!!!!!!!!
     You solved my big problem..I was able to connect to the SQL server on local but was not able to connect from remote machine…

     I changed the server name to IP\SQLEXPRESS

     Thank you
206. Wahaab April 4, 2011

     You were a great help!!
     there are rare articles which address a problem step by step!!
207. Dude April 5, 2011

     You’re super!!this article helps me out from problem connecting to SQL Express 2008. Great thanks…
208. Joy April 13, 2011

     Great Article Superb Thanks to you. i was struggling quite a bit in connecting to the sql server . you saved my day 🙂
209. Krumov April 19, 2011

     Thank You !!!
     This is something that must be given all kudos and admirations. GREAT JOB !!!
210. gleija April 20, 2011

     🙂 thanks a lot!!
211. Suzy May 9, 2011

     Thanks you so much – your document helped me to finally get my remote access working. I had to blank out the dynamic port settings in the TCP/IP settings (in IPall), and add 1433 as specific port.
212. Fourier May 18, 2011

     Congratulations, the secret is the service SQL Server Browser. Thank you very much for your article.
213. Cobesauce May 19, 2011

     Awesome!!! 3 days, whole IT dept. could not solve it. But, I solved it from this once I found you guys!!!
214. taner May 23, 2011

     Great one. Thanks
215. Ramesh May 27, 2011

     Thanks for ur guidance for enable the remote server in SQL Server 2008, Its very helpful for me and I have connected as per ur instruction. Thanks for posting this article.
216. Marie June 9, 2011

     Thank you so much for this detailed post. Perfect!
217. Nimda June 9, 2011

     Thank you. Very helpful
218. Nripin June 15, 2011

     Thanks for the tip mate !!
     Keep up the great Work !!
     Cheers
     Nripin
219. Moeez June 16, 2011

     It is not worked for me. I have as it was written but can not access my instance remotely.
220. AlexF1980 July 26, 2011

     I tried everything listed above, and none of it worked. Then I checked another instance I had running (that I recently upgraded to SQL Server 2008). It turns out that (having setup the firewall correctly first!) changing the “TCP Dynamic Ports” under the IPAll section of the TCP/IP Properties dialogue to 1433 (and a restart of the SQL Server service) allows the remote connection to take place.
221. Marco July 31, 2011

     Thanks for the help!
     This may help too:
     (1)
     Look at the error log:
     Server Management Studio -> Object Explorer -> under you server connection expand Management, expand SQL Server Logs -> Open up Current
     (2)
     Find osql.exe and run this at the command line:
     osql -E -S %SERVER%\%INSTANCE% -Q “sp_grantlogin ‘NT AUTHORITY\NETWORK SERVICE'”
222. devesh August 1, 2011

     nice problem solve trick. thanks ………
223. ravell August 4, 2011

     It did not work for me. I used the link below and it is work fine. Besided the diffrence is changing TCP port to 1433 and removing Dynamic port information.
     http://social.msdn.microsoft.com/Forums/en/sqlexpress/thread/97a0ddf7-d23e-4723-b49f-463943180172
224. Ken August 16, 2011

     Thank you for posting these steps. They helped more than you know.
225. Sandeep August 17, 2011

     Very usefull post…Thanks dude
226. abhi August 23, 2011

     i have window 7 64 bit. Can 32 bit machine will be able to view my reports or i need some special settings as nthg working for me now……rep asap
227. abhi August 23, 2011

     i am not able to start my sqlexpress too? wats d problem in dat????? some error is dere like…..application not responding in timely fashion….???????? i installed sql 2008 properly
228. Emil G August 27, 2011

     When connecting to Microsoft SQL Server and trying to use usually SQL Authentication method you may get error 18456 login failed for user you provided. This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. It sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Check here for more info microsoft sql server error 18456
229. sqlserver August 28, 2011

     You can also check the SQL Authentication online using the quick free tool at:
     http://www.webkeet.com/sqlservertestconnector.aspx
230. JimiSweden August 31, 2011

     Thanks for a great guide!

     //Jimi
     http://newsweb.se
231. DKronen August 31, 2011

     Exactly what I needed

     Thanks
232. Izaks September 12, 2011

     Good one. solved me a huge problem.

     Thanks.
233. jim September 14, 2011

     thx ig4 (post #22)

     “check win server 2008 firewall public profile state =off
     start–>Administrative Tools–>Windows Firewall with Advanced Security, to click Properties,to select Tab “Public Profile” set firewall state =off.
     Don’t need restart machine.”

     that fixed it for me. thanks!
234. Tushar September 23, 2011

     Great article it rocked.
     Thanks so much.
235. Al September 23, 2011

     I’ve followed every step…no problems and then I get step 10. It says ‘connect to SQL server 2008 express’ but I still can’t do this!

     Get the following error message….It’s driving me mad….any advice gratefully received!!!

     TITLE: Connect to Server
     ——————————

     Cannot connect to (local).

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

     For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

     ——————————
     BUTTONS:

     OK
     ——————————
236. Al September 23, 2011

     YESSSSSS – it was the IPAall 1433 problem!!! ok now…phew
237. Peter Olofsson September 27, 2011

     Excellent!
     Thanks
238. Kamlesh October 6, 2011

     Thank you very much…
     It’s really helpful
239. Paul October 10, 2011

     Awesome! Thanks a lot!
240. Soheb October 11, 2011

     Thaaaaaaaaaaaaaaaaaaaaaaaaanxxxxxxxxxx….
241. SeriousM October 12, 2011

     GREAT!! Thank you sooo much :)!!
242. Khalane Rapuleng October 13, 2011

     Great work: It really come for my rescue
243. Brad October 18, 2011

     Great tips….I seem to recall the protocols needing to be enabled but not the browser service
244. Aws October 19, 2011

     that is great. thank you alot 🙂
245. Neeraj October 21, 2011

     it’s really very helpful.Thank you..
246. Farhan November 3, 2011

     I added 1433 to ipall even after that its not working, I have done all steps, 1433 added to firewall sql server browser service is running also..
247. Raja November 16, 2011

     Thanks for valuable Informations
248. Sebastien November 17, 2011

     Thanks for the info. It worked!!
249. Roberto November 18, 2011

     work for me, thanks!
250. Chris J November 22, 2011

     Thank you so much… 2:18 am and you saved me!
251. Farhan November 22, 2011

     I am working on a dedicated server I guess some upper level security is not letting me to open port 1433 so I have to install a software TunnelEX to open port 1433 and after installing this software I am able to connect remotely to SQL SERVER 2008 Express Finally
252. Christine November 22, 2011

     Hiya!

     This has been incredibly useful, however, I’m still falling down at the last hurdle of getting an ODBC connection from a machine that is also running SQL Server 2008 Express. I can register the remote server via SQL Server Management Studio, I’ve changed the port to 1433 in the TCP/IP configuration and then when that didn’t work to 14330 as per someone else’s suggestion. However, the error I’m getting with my ODBC connection is:

     Connection failed:
     SQLState: ‘010000’
     SQL Server Error: 11004
     [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
     Connection failed:
     SQLState: ‘08001’
     SQL Server Error: 6
     [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Specified SQL server not found.

     So the configuration is:

     Machine 1: Windows XP, SQL Server 2008 Express. Remote connectivity enabled.
     Machine 2: Windows XP, SQL Server 2008 Express. Can add Machine 1 to SQL Server Management studio but not set up an ODBC connection. Can connect via ODBC to machine 3
     Machine 3: Windows 7, SQL Server 2008 Standard. Can add Machine 1 to SQL Server Management studio and connect via ODBC to Machine 1

     What could it be on Machine 2 that will stop me connecting to one server via ODBC but not another?
253. Christine November 22, 2011

     Ignore me! I was being an idiot! It’s now working absolutely fine – in the SQL server name I’d used a forward slash instead of a backslash!

     In the immortal words of Homer Simpson – d’oh!!
254. Elnora Kailey November 24, 2011

     Fantastic web site. Lots of helpful info here. I am sending it to a few buddies ans also sharing in delicious. And of course, thanks for your sweat!
255. Dannette Sarcone November 25, 2011

     I’m not positive where you’re getting your information, but great topic. I needs to spend a while studying more or working out more. Thank you for wonderful information I was in search of this info for my mission.
256. Alberto Juliao December 3, 2011

     the comment #89 helped me with the issue starting SQL Browser, I’m new to creating remote connection with databases, However I want to know what specific IP must be set on the connection String, does it need to be the public ip of my local machine? Or the one I assign from the TCP/IP? Please answer as soon as possible.. THANK YOU FOR THIS GUIDE.
257. Josip December 4, 2011

     Hey man, thank you for the detailed informations and step-by-step leading through the problem! It helped me so much that I can’t tell you how happy I am 😀
258. YK December 8, 2011

     Thank you! It helped me! 🙂
259. Alberto Juliao December 9, 2011

     Ok I figured it out, you need to type down your public IP address if you’re connecting from outer network (i.e friend’s home) if you’re connecting from the Server PC then, type your internal IP (192.168..) That’s all. And also what this post is missing is the steps of allowing windows firewall to sqlserver and its ports. Also the creation of accounts and users login that you choose to access the database you want to allow remote connection. Tutorial in this link: http://videotutors.net/sqlserver-remote-access.aspx
260. Mustapha December 10, 2011

     Hi all,
     I have followed steps , until step 10 , when I open Microsoft visual studio management . when I click on connect to , I see only database engine and SQL Server Compact ,but not he SQL Server 2008 Express .
     I d appreciate if someone can help me with this issue.
     Regards
261. Alberto Juliao December 10, 2011

     @Mustapha, there’s only those two features (Database Engine and SQL Server Compact) what he means for “SQL Server 2008 express” is connecting to the core service, which is “Database Engine”, it’s selected by default you just need to type your instance name in the “Server Name”, also “NameInstance”/SQLEXPRESS can work, by default the instance name is your Computer Name.
262. Henry Lafferty December 13, 2011

     Brilliant mate – worked first time :0)
263. Marcelo Castilho December 24, 2011

     Very good!!! It´s all rigth.
264. Kazinski January 2, 2012

     I have followed the steps above a couple of times and I am still having no luck in getting the server to talk from one machine to another.

     I can log on the local machine using Server Management Studio but I can not from a second machine on a local network.

     When I use sqlcmd – > sqlcmd -S \MSSQLSERVER it does not work, but if I use sqlcmd -S I get a sql command prompt.

     I am getting a Connection string is not valid [87] error.

     Any help would be greatly appreciated.
265. Kazinski January 2, 2012

     I am now able to connect. I use the IP address of the server and I am able to connect using a different account I set up. I was trying to log in using my admin account for the sql server and that is failing.

     I think I have 2 problems – the first is a domain name problem. The computers are connected in my home network via workgroup and the 2nd issue is account permissions.
266. Asim Raza January 9, 2012

     This is best.
267. abdullah January 16, 2012

     thank you dear, i appreciate that!
268. Chris January 17, 2012

     Thanks mate!
269. nwaomachux January 18, 2012

     I’d like to ask you, since I do not have Microsoft SQL Server 2008 Management Studio Express installed on the PC that’s running the SQL Server 2008 R2, how do I connect to it from another PC.

     I installed the SQL Server on a Virtual Machine. I’m able to connect to the VM, but the problem is that I followed the above tutorial till I got to the point where I’d have to work further with Microsoft SQL Server 2008 Management Studio Express but I’m not able to finish up because I don’t have it installed.

     I will like to use another program to connect to the server. Please, how do I do it. Thanks.
270. Farhan January 18, 2012

     install a port redirector like TunnelX , It is going to work.
271. Raghava January 20, 2012

     THANKQ VERY MUCH……………….
272. Stan January 22, 2012

     Great write-up. Excellent.
273. ilias January 22, 2012

     Thanks!!
274. Rick February 5, 2012

     Re: SQL Surface Area configuration.

     SQL 2008 Express does have Surface Area configuration. It is now (hidden) in “Facets” (see context menu), last option of the list. Ad hoc remote queries can be enabled there.
275. Wellington February 8, 2012

     Thanks man !! You have helped so far in Brazil.
276. Haza February 12, 2012

     Gracias!!!!!!!!!!!!!!!!!

     Muy bueno =) y muy util

     100% funcional
277. Roopesh February 14, 2012

     Perfect. Somehow i missed to enable tcp/ip and this article helped and saved a lot of time. Nice article.
278. ANkit Roy February 24, 2012

     Thank you It worked for me…
279. Nicola Matarese March 5, 2012

     Very useful tutorial! It worked fine for me.

     Now I have a question:
     is possible to establish a remote connection on SQL SERVER through a VPN using the Windows authentication?

     Thanks!
280. dba March 8, 2012

     thanks alot dude .. it is help me much for my project……
281. Wombat69 March 9, 2012

     This was great, I am migrating a database to another server & wondered why I could not connect from a remote pc, now i have followed your detailed instructions & now it is working.

     Thank you, saved mae a lot of hours
282. cape March 19, 2012

     Thank You very much.. its relly relly really help me!
283. Poetry April 6, 2012

     Great weblog right here! Also your website a lot up very fast! What host are you using? Can I am getting your affiliate link to your host? I want my site loaded up as fast as yours lol
284. Halmeic April 7, 2012

     Trying to do the same thing on windows 7 and MS SQL 2008. The menu tree is different can anyone help?
285. Filme April 9, 2012

     Thank you a bunch for sharing this with all of us you actually understand what you’re talking about! Bookmarked. Please additionally visit my web site =). We can have a link alternate contract between us
286. Tihanyi Zoltán April 13, 2012

     Hi Man !
     Millon thank’s for you! You saved my life 🙂
287. Nio April 17, 2012

     Hi,
     billion thanks for your help.!

     You really saved me.
288. Mickey April 17, 2012

     Nice and clear post. Thanks.
289. Inno April 17, 2012

     I am using a university computer connected to the university network. I don’t have admin rights. I did install SQL Server 2008 myself. SQl Server is running by default but SQL Server Agent and SQL Server Browser are both disabled so I could not start them by following the above instructions. Any way out? Please.
290. hicsel April 17, 2012

     Windows 7 won’t let you install most programs without the correct authorisation, so I take it your using an older OS. Any form of Server setup and configuration usually requires Admin rights. I think your very lucky it let you install SQL Server in the first place. Speak to the IT manager and ask him to elevate you to the required level of access.
     If you still have problems come back on.
291. Ernesto April 18, 2012

     Eres el mejor.
     Gracias.
292. Ranjit April 18, 2012

     Thats a very useful blog dude. Thank You very much
293. Yamini April 19, 2012

     i did all settings which you mentioned above but still have bellowed error for remotely connection to SQLEXPRESS server.
     Error: 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)

     Can anybody giude me please…!
294. Kurd April 20, 2012

     Greate guide, it helpt me aswell 🙂
295. linglom April 25, 2012

     Hi, Yamini

     Here is a checklist that you could do:
     – Check if the SQL Server service is running.
     – Check if there is any firewall blocking the connection between the SQL Server and the remote computer or not.
     – If you are connecting by programming, you should verify the connection string if it corrects or not.

     If you have trouble in any step, let me know.
296. Mubarak April 29, 2012

     Hi guys,

     I’m VS 2008 to upload excel sheet in remote machine. but local machine, it was working fine. and its through the error like below, while I’m uploading excel sheets in applications. please go through below,

     please help me to resolve the issue, I’m in critical situtaion. any help will be greatly appreciated. i did all cahnges in remote machine even I’m installing the SQl 2005 in remote server and enable the port 1433 in firewall, and enable the database engine to local & remote connections. but still the below errors are appear.

     please help me to solve the issue, its my humble request.

     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)
     Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

     Exception Details: System.Data.SqlClient.SqlException: 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)

     Source Error:

     An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

     Stack Trace:

     [SqlException (0x80131904): 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)]
     System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849719
     System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
     System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +4863021
     System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +90
     System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +342
     System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +221
     System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +189
     System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185
     System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +31
     System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +433
     System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
     System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +499
     System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +65
     System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +117
     System.Data.SqlClient.SqlConnection.Open() +122
     Zakat.PhoneBook.Contacts.XLUpload(DataTable dt) in D:\Rahman\Clients\Zakat\Zakat\Zakat\PhoneBook\Contacts.aspx.cs:469
     Zakat.PhoneBook.Contacts.UploadXL() in D:\Rahman\Clients\Zakat\Zakat\Zakat\PhoneBook\Contacts.aspx.cs:244
     Zakat.PhoneBook.Contacts.Button1_Click(Object sender, EventArgs e) in D:\Rahman\Clients\Zakat\Zakat\Zakat\PhoneBook\Contacts.aspx.cs:452
     System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
     System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
     System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
     System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
     System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
     System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

     Wating for your kind reply guys,

     Regards,
     Mubarak
297. Mubarak April 29, 2012

     Dear linglom,

     I’m checking that SQL server is running, and the connection string also working fine in all other modules. but especially the when i import excel sheets in my application, the instance error throws..

     1. can you please giude me more on this and please giude how can i check that, Is ther any firewall blocking the connection between the SQL Server and the remote machine..??

     2. I’m also checking the instance with conenction string like data source=”zak_db01\zakat,1433…etc”. –> but there is no use of it.

     please help me to reolve this issue.

     Anyhelp will be greatly appreciated. I’m in critical situation.

     Thanks
     Mubarak
298. Sethu May 1, 2012

     Great. It was worked fine. SQL Express 2008 R2 – Remote connection issue has been resolved by followed the steps given. Thanks a lot. 🙂
299. Ola May 2, 2012

     A Millions thanks from Norway!!!
300. Pingback: Unable to connect to SQL Server Express from a remote machine. « NetworkPCTech Blog May 8, 2012
301. Mike May 11, 2012

     thanks! it’s very effective…. ^_^
302. Stef May 23, 2012

     hi,
     did everything and still not working… suggestion anyone??

     they changed the server and before it was working and now…
     thank you
     Stef
303. Zahir June 12, 2012

     Thank you very much! This tutorial was very effective, everything works in just couple of minutes.
     Thank you,
     Zahir
304. Amir Yaqoob June 13, 2012

     Different version of SQL server may cause to avoid remote connection:
     I struggled around 4 hours, and it did not work. Finally, I realized that I have some installtions of SqlServer 2005 in addition to Sql Server 2008 Express edition. I gave last try by removing Sql 2005 installtions, and then try to connect with SQL Server 2008 Express instance. and got success.

     Thanks!
305. Daksh Bhatt July 1, 2012

     thanks buddy . . .
     i solve my problem in 5 minutes . . .
306. KW July 6, 2012

     What “nonstandard” happened to me when I was configuring remote MSSQL (maybe someone said it above):
     – my SQL Server process didn’t open on standard 1433 port, but some 50000 (check it in netstat)
     – On client I used Server’s port, not Server Browser’s
     – Windows Firewall by default blocks Server’s port! I disabled it totally until I made it work.
     – pay attention to User name and domain you are using – I used same (domain) user remotely, as I installed server on, and it worked

     First make it work at all costs. Then play with setting ports, firewall and so on.
307. Osmumos July 19, 2012

     This is awsome!!!
     I has sorted me big time!
     Thanks and Best Regards.
308. ivykoii August 20, 2012

     thanks! great tutorial for that error.
309. Mostafa August 21, 2012

     Thanks!

     hi
     thanks a lot for training
310. Christos August 26, 2012

     Very nice!!!! Thanks a lot!!!!
311. Israeli Dude August 28, 2012

     Great Manual! Thank you!!!! 🙂
312. oderu September 4, 2012

     Very helpful! Thank you very much!!!!
313. Pingback: When you cannot connect remotely to an MsSQL server …. - Data Management Center October 2, 2012
314. Dave October 9, 2012

     I have a Sql express server at my h.office.Im running a application from my brance.i have a static ip at my h.offc.
     how i can connect my appl to my h.office sql express?
315. Bill October 13, 2012

     Thanks! Your article just saved my weekend. Had a client on SQLServer Express, who now wanted to access it on a remote networked computer. When it did not initially connect, I figured I’d be at it all weekend trying to find the solution. It is 8:00A.M. Saturday morning and I’m on my way to the golf course. Thanks a million!!
316. Dave October 17, 2012

     Thks Bill.
     Any way i have sort out the issue over the weekend
317. Murphiks October 26, 2012

     A very good statement.
     All but one of the super, nobody faced with connection from Linux hosting via MSSQL or ODBC , port 1433 to Windows server 2008 R2 computer with SQL Express 2008 R2 ?
     All set up correctly with the other computer on Windows server 2003 I’m conecting via XAMPP 1.7.1 and MSSQL.
     host=192.x.x.x,1433
     I no it does not work. Someone tell me or anyone advise? As early as the days of steam no results 🙁
     On linux Hosting is on FreeTDS ver.8.
318. Álvaro October 29, 2012

     Great manual!!! Thanks!!!
319. Steve November 20, 2012

     Many thanks for this post. Broke a log jam for me.
320. Mariola November 23, 2012

     Very helpfill indeed. Thanks a lot.
321. rvfigueroa November 25, 2012

     Just one think, and elemental!!!!

     You need to verify your Windows Firewall in your machine that run your SQLEXPRESS!!!

     You can add and rule to your firewall or directly deactivate for a few momento while you work with connection.

     Regards!
322. shabnam December 19, 2012

     I have 2 sql server in win server 2003. sql server 2000 and sql server 2008 R2 .I want to remote connection to both of them from the client system. but I could remote to sql serevr 2000 but I could not remote to sql 2008 and show this message:
     Cannot connect to SQL-Server-Instance-Name
     An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)”

     what I am doing?? please help me… thanks alot
323. Alberto Juliao December 19, 2012

     @shabnam,
     1. did you follow every step for this tutorial?
     2. If not, for “SQL Server 2008 Express” you go open Microsoft Visual Studio Management, there will be two features (Database Engine and SQL Server Compact)
     3. To manage “SQL Server 2008 express”, go to “Database Engine”, it’s selected by default you just need to type your instance name in the “Server Name”, also “NameInstance”/SQLEXPRESS can do the trick as people said, by default the instance name is your Computer Name.
     4. If SQL is on different port than default 1433
     SERVERNAME(OR IP)\InstanceNAME,PORT
     example: SQLSRV01\SQL2008,1588
     5. If still no luck, please read this post that includes Firewall configuration and TCP/IP config http://videotutors.net/sqlserver-remote-access.aspx
     Merry Christmas.
324. masud January 22, 2013

     Has XP2 but win 7 cann’t login

     Help
325. Ponnusamy January 23, 2013

     Windows 7 OS, I have tried from java to connect MS SQL 2008 server. I am having 2005 also in my machine. Here I was not able to connect 2008 server since 1433 port was assigned to 2005. So I have changed the port in my application to port which is mentioned in SQL Server Configuration Manager ->SQL Server Network Configuration ->Protocols-> TCP/IP properties for All IPs
326. fateme January 25, 2013

     every body teach me a thing is my GOD. thanx alot dear, it was the thing that i realy needed.
327. Krajisejck February 7, 2013

     Tnks a lot…
     =)
328. Ahsan Ashfaq April 25, 2013

     Thanks for such helping post . It really help me . Thanks again .
329. Sarang May 1, 2013

     Thank you so much!!!
330. andriy May 16, 2013

     Thanks a lot!
331. Costin August 25, 2013

     Thank you for information, was very clearly and helpfull!
332. bogser September 13, 2013

     Thanks for this very helpful information.
     After reading this article, it just took me some more minutes to get the external SQL Server connection working !
     Much appreciated.
333. Benjin October 31, 2013

     Cheers mate, now sorted.
334. sajjad December 30, 2013

     Thank you very much.
335. Tomas Sancio January 28, 2014

     Thank you.
     Thank you.
     Thank you.
336. khalid z? March 4, 2014

     thank you very very much, so helpful tuto.
337. Pingback: [RESOLVED]Enabling SQL Server 2008 (R2) access over Network | ASP Questions & Answers July 3, 2014
338. GB January 27, 2016

     Thanks for the blog + suggestions from everyone, which helped me to get connected to ec2 – sql server studio from my local machine.
339. Wagner July 15, 2016

     Muito obrigado, me ajudou muito, você não imagina o quanto. Abraço
340. Barbar January 30, 2019

     big thank you!!!!
341. Jason Miller August 5, 2019

     Thanks for sharing such an useful information with us.
342. Michael April 11, 2024

     Thanks for sharing it, if anyone is looking to develop sports betting app development for any type of betting app then please visit: https://www.apptunix.com/solutions/sports-betting-app-development-company/