Linglom.com

Enable Remote Connection on SQL Server 2008 Express

280 Responses to “Enable Remote Connection on SQL Server 2008 Express”

1. Robert Says:
   April 2nd, 2009 at 3:15 am

   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 Says:
   April 7th, 2009 at 7:59 pm

   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 Says:
   April 8th, 2009 at 10:45 am

   Thank you very much for putting this tutorial together. It has been very helpful!

4. Ronald Says:
   April 22nd, 2009 at 1:47 pm

   Thanks!

5. SGR Says:
   April 22nd, 2009 at 6:33 pm

   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 Says:
   April 30th, 2009 at 8:54 pm

   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 Says:
   May 5th, 2009 at 2:09 pm

   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 Says:
   May 7th, 2009 at 9:47 am

   It’s so great and very helpful!

9. linglom Says:
   May 12th, 2009 at 9:30 am

   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 Says:
    May 23rd, 2009 at 8:39 am

    You are a good person. Thanks so much!

    Eiad

11. Jampa Says:
    June 4th, 2009 at 4:42 am

    Thanx a lot mate, your tutorial helped me a lot…

12. Vadim Says:
    June 4th, 2009 at 3:12 pm

    Thanx man, it was very helpfull for me
    keep the same way

13. Ben Says:
    June 15th, 2009 at 6:05 pm

    Fantastic – you have saved me a lot of time – many thanks!

14. Thurein Says:
    June 17th, 2009 at 5:40 pm

    Thanks for this post. It is really helpful.

15. Azec Says:
    June 18th, 2009 at 10:25 pm

    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 Says:
    June 24th, 2009 at 4:19 pm

    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 Says:
    June 25th, 2009 at 9:20 am

    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 Says:
    June 30th, 2009 at 7:23 pm

    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 Says:
    July 2nd, 2009 at 12:25 am

    Thanks so so much

20. Renee Kraft Says:
    July 9th, 2009 at 5:52 am

    Thank you so much! Your posting was a huge help.

21. Marius Says:
    July 21st, 2009 at 4:29 pm

    Thank you for both the 2005 and 2008 guide! Helped me with both as we have both at work!

22. ig4 Says:
    July 23rd, 2009 at 5:23 am

    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 Says:
    August 7th, 2009 at 3:11 am

    excatly what i needed. you are awesome.

24. Engin Says:
    August 9th, 2009 at 7:10 am

    Thanx to Turkey

25. fer Says:
    August 15th, 2009 at 2:08 pm

    Thanks. It is very helpful.

26. Serdar Says:
    August 19th, 2009 at 5:28 pm

    Thanks so so much to Turkey

27. IS Thakur Says:
    August 23rd, 2009 at 7:14 pm

    Thanks a lot for such a valuable info.

28. matthew Says:
    August 28th, 2009 at 2:23 am

    WOW! thank you, thank you, thank you!!

29. Ken Says:
    September 8th, 2009 at 8:10 am

    This is such a great write-up. Thanks!! so much for taking the time to pull this together.

30. ali Says:
    September 14th, 2009 at 3:36 pm

    Thank you so much!

31. sayoni Says:
    October 7th, 2009 at 11:56 am

    Thanks a lot… it solved my issue…..

32. MAsk Says:
    October 8th, 2009 at 7:00 pm

    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 Says:
    October 18th, 2009 at 9:48 pm

    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 Says:
    October 19th, 2009 at 8:12 am

    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 Says:
    October 19th, 2009 at 8:24 am

    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 Says:
    October 25th, 2009 at 10:54 pm

    Thanks.
    It is very simple but very helpful.

37. J.Ackerman Says:
    October 26th, 2009 at 11:41 pm

    @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 Says:
    October 27th, 2009 at 10:33 am

    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 Says:
    November 6th, 2009 at 3:30 pm

    Thank you for your nice and clean article.

40. Panwar Says:
    November 19th, 2009 at 12:28 am

    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 Says:
    November 20th, 2009 at 10:43 am

    Hi,

    Thanks a lot for this well explained tutorial along with screenshots. Appreciate your patience.

42. David Says:
    November 24th, 2009 at 12:31 pm

    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 Says:
    November 29th, 2009 at 4:48 am

    Thanks! This was causing me much consternation.

44. Moonheart Says:
    November 30th, 2009 at 3:54 pm

    thx for this solution
    but i have WMI provider error
    plz how can i connect to SQL server 2008 in my PC

45. Gerry Says:
    December 10th, 2009 at 3:21 am

    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 Says:
    December 14th, 2009 at 3:15 pm

    Thanks a lot. It is very helpful!

47. imtiaz Says:
    December 16th, 2009 at 9:42 pm

    after your setting cannot my native client and sql service start

    solve my problime

48. Abdiel Says:
    December 18th, 2009 at 11:24 pm

    Thanks! I was about to reinstalll the whole sql 2008.

49. KMBrewer Says:
    December 19th, 2009 at 2:50 am

    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 Says:
    December 30th, 2009 at 8:49 am

    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 Says:
    December 30th, 2009 at 5:40 pm

    Thank you very much… very nice and successful presentation.

52. Orin Hooverson Says:
    January 3rd, 2010 at 9:00 am

    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 Says:
    January 8th, 2010 at 1:36 pm

    Thanks…

54. NKO Says:
    January 11th, 2010 at 3:53 pm

    thanks! very helpful indeed

55. leo Says:
    January 15th, 2010 at 5:10 pm

    I can enable the sql browser
    btw im seeing two instance. a sqlexpress and mssqlserver

56. Aaron Says:
    January 18th, 2010 at 12:11 am

    thanks..

57. DJeffer Says:
    January 27th, 2010 at 1:19 am

    Thanks a lot! Very helpful.

58. gelder Says:
    February 2nd, 2010 at 1:44 am

    Thanks my friend!

59. abhineet komal Says:
    February 5th, 2010 at 1:18 pm

    thanx, u have explained it brilliantly

60. Ulyana Arsov Says:
    February 5th, 2010 at 7:15 pm

    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 Says:
    February 6th, 2010 at 5:36 am

    Very Good Thanks

62. Gautam Says:
    February 7th, 2010 at 12:35 am

    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 Says:
    February 7th, 2010 at 1:53 am

    Gautam:
    You need a user in SQLServer to login and the pass off course

64. Gautam Says:
    February 7th, 2010 at 7:59 am

    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 Says:
    February 7th, 2010 at 10:27 am

    Gautam that’s rigth, I’m sorry I should say: change of

66. JVillamil Says:
    February 7th, 2010 at 10:29 am

    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 Says:
    February 7th, 2010 at 8:34 pm

    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 Says:
    February 8th, 2010 at 1:51 pm

    Daniel: why no connect by TCP / IP ???

69. Discount Code Says:
    February 8th, 2010 at 10:47 pm

    Thanks a lot.. It was exactly what I was looking for. Much appreciated..

70. Gautam Says:
    February 8th, 2010 at 11:09 pm

    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 Says:
    February 11th, 2010 at 10:34 am

    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 Says:
    February 18th, 2010 at 3:12 pm

    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 Says:
    February 19th, 2010 at 1:41 am

    mhmhmh, you should configurate the IIS to run the ReportServer to the port 80

74. Sinbad1 Says:
    February 23rd, 2010 at 10:40 am

    I can’t thank you enough !
    Excellent walkthrough .

75. PBC Says:
    February 25th, 2010 at 2:29 am

    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 Says:
    February 26th, 2010 at 5:53 am

    Thanks! Clear and very helpful.

77. Agus Says:
    March 17th, 2010 at 1:33 pm

    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 Says:
    March 17th, 2010 at 10:55 pm

    I still cant connect using ipaddress

79. Andy Says:
    March 19th, 2010 at 5:47 am

    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 Says:
    March 19th, 2010 at 9:27 pm

    It’s awesome, thnaks….

81. kv Says:
    April 7th, 2010 at 12:24 am

    thx for the info that was what i was looking for

82. Deepika Says:
    April 14th, 2010 at 1:26 am

    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 Says:
    April 14th, 2010 at 12:33 pm

    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 Says:
    April 20th, 2010 at 6:55 am

    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 Says:
    April 20th, 2010 at 6:07 pm

    thanks a lot buddy,. it helped me a lot

86. Vibgy Joseph Says:
    April 22nd, 2010 at 7:18 pm

    @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 Says:
    April 26th, 2010 at 6:50 pm

    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 Says:
    April 28th, 2010 at 4:08 pm

    Thanks a lot. It is very helpful!

89. itl Says:
    May 6th, 2010 at 9:34 pm

    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 Says:
    May 17th, 2010 at 6:07 pm

    THank you deeply!!! great article – helped a lot!

91. Edward Says:
    May 17th, 2010 at 10:59 pm

    I owe you one! This is sheer gold. Got it running v. quickly.

92. Andy Says:
    May 19th, 2010 at 9:27 am

    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 Says:
    May 19th, 2010 at 3:51 pm

    Thank you very much, is a perfect guide!!

94. RahulR Says:
    May 20th, 2010 at 6:37 pm

    Thanks a Lot! It worked for me!

95. Puroh Says:
    May 20th, 2010 at 11:55 pm

    Thanks.. This is how all guides should be written! Nice work

96. Jan Says:
    May 31st, 2010 at 8:04 pm

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

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

    Thanks!

97. Derrick Says:
    June 3rd, 2010 at 3:22 pm

    thank you so much..

    it help me a lot..

    thanks..

98. SQL Server 2008 R2 Express remotely - IT Consultant Says:
    June 7th, 2010 at 8:30 am

    [...] Server 2008 R2 Express remotely Jun.06, 2010 in Tweaks and Tricks If you’re trying to connect to SQL Server 2008 Express remotely without enable remote connection first, you may see these error [...]

99. Bud Aaron Says:
    June 16th, 2010 at 2:38 am

    The post above is very complete but falls down at step 7. You need to click properties and enable specific IP addresses. Check here for details:
    http://videotutors.net/SQLServer2008RemoteAccess.aspx

100. Sarmad Says:
     June 18th, 2010 at 4:46 am

     Thanks a lot man !! it is very Helpfull.

101. Sarath Says:
     June 21st, 2010 at 8:45 pm

     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.

102. Mehboob Says:
     June 24th, 2010 at 11:25 pm

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

103. Sundar Says:
     July 1st, 2010 at 1:02 pm

     Great Thanks.

104. Robbie Reyes Says:
     July 1st, 2010 at 10:06 pm

     VERY NICE!! EXACTLY WHAT I NEED! THANKS!

105. Dave Says:
     July 2nd, 2010 at 3:18 am

     Thank you, Thank you, Thank you, Thank you, Thank you, Thank you…………….

106. Dams75 Says:
     July 7th, 2010 at 6:37 pm

     Thank you Mask for connection format.
     You just saved me.

107. Cathy Ooi Says:
     July 8th, 2010 at 5:15 pm

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

108. Indoril Nerervar Says:
     July 11th, 2010 at 10:26 pm

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

109. Asifali Kannattil Says:
     July 16th, 2010 at 7:29 pm

     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…

110. Asifali Kannattil Says:
     July 17th, 2010 at 1:22 pm

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

111. Manish Jaiswal Says:
     July 22nd, 2010 at 5:17 pm

     This is a great great post…

     Thanks

112. Jmunoz Says:
     July 23rd, 2010 at 6:31 am

     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?

113. victor Says:
     July 23rd, 2010 at 8:03 am

     thanks from maputo!!!!

114. Enable to create ODBC Connection to SQL Server 2008 Express « Network PC Tech Blog Says:
     July 25th, 2010 at 10:39 am

     [...] while trying to make an ODBC connection to your SQL Server, take a look at the steps on this post. It helped [...]

115. Rakesh Says:
     July 27th, 2010 at 5:29 am

     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

116. Traci Says:
     July 30th, 2010 at 7:29 am

     Thank you! Saved me a ton of time!

117. Farshad Says:
     July 30th, 2010 at 3:23 pm

     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

118. Bruno Says:
     July 30th, 2010 at 8:43 pm

     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

119. melih Says:
     August 3rd, 2010 at 4:17 pm

     Thanks !!!!!

120. Jasper Says:
     August 3rd, 2010 at 5:46 pm

     Thank you very much. Very clear explanation and works like a charm!

121. migro Says:
     August 5th, 2010 at 6:52 am

     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

122. migro Says:
     August 5th, 2010 at 5:43 pm

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

     Thanks a lot for this article.

123. pankaj Says:
     August 9th, 2010 at 4:17 pm

     Thanks a lot its really nice and helpfull
     Thanks

124. Liberty!!! Says:
     August 12th, 2010 at 2:14 am

     This also worked for my DEVELOPER Edition of SQL Server 2008.
     THANK YOU!!!
     THANK YOU!!!
     THANK YOU!!!

125. Sandae Says:
     August 12th, 2010 at 8:49 am

     THANK you!!!

126. sunsetquest Says:
     August 13th, 2010 at 10:36 pm

     Thank you!!!!!
     fyi – I did not need to perform step one.

127. George Says:
     August 18th, 2010 at 7:47 pm

     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…

128. Rajesh Says:
     August 20th, 2010 at 5:28 pm

     superb…thanks..u increase my challanging

129. Sivawut Srithammawut Says:
     August 26th, 2010 at 8:04 pm

     Thanks you so much.

     This is very clear..

     -Pipe

130. Ilya Says:
     August 27th, 2010 at 5:20 pm

     Thanks! It’s really great manual!

131. Paul Says:
     August 29th, 2010 at 7:21 pm

     Thanks for the guidance, solved my problem in minutes once I started reading through the comments.

132. Paul Says:
     August 31st, 2010 at 5:33 pm

     Thanks a lot…….

133. Jeffrey Says:
     September 1st, 2010 at 3:36 am

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

134. eqirtas Says:
     September 3rd, 2010 at 6:52 pm

     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

135. Chanty Says:
     September 8th, 2010 at 6:23 am

     Thank-you, very much the tutorial is very clear and it worked!!!!

136. bpelhos Says:
     September 9th, 2010 at 4:12 pm

     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

137. Jguzman Says:
     September 11th, 2010 at 10:12 am

     thanks a lot man !!

138. david Says:
     September 22nd, 2010 at 1:27 am

     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!

139. Imran Says:
     September 22nd, 2010 at 7:58 pm

     It worked for me.
     Thanks for posting this helpful material.

140. Abraham Says:
     September 23rd, 2010 at 8:09 pm

     Worked for me as well. Thnx a lot of the instructions.

141. AD Says:
     September 30th, 2010 at 9:10 pm

     Great help!

142. transportinoradea.ro Says:
     October 5th, 2010 at 1:51 pm

     thanks a lot

143. rodcompo Says:
     October 6th, 2010 at 2:13 am

     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

144. multi seat set up... - MediaPortal Forum Says:
     October 9th, 2010 at 4:23 pm

     [...] a betting man… I'd say you haven't configured MS SQL Express to allow for remote connections Enable Remote Connection on SQL Server 2008 Express | Linglom's blog How to: Configure Express to accept remote connections – SQL Server Express WebLog – Site Home – [...]

145. linglom Says:
     October 11th, 2010 at 9:07 am

     Hi, rodcompo
     It could be blocked by Windows firewall, try to disable Windows firewall and reconnect again.

146. kushi Says:
     October 17th, 2010 at 3:14 pm

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

     thaks a ton

147. Abhay Says:
     October 18th, 2010 at 2:26 am

     Superb work, This will help me a lot.

     Regards
     Abhay

148. phpbugs Says:
     October 22nd, 2010 at 2:36 pm

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

149. Leonardo Suarez Says:
     October 26th, 2010 at 4:52 am

     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

150. phpbugs Says:
     October 26th, 2010 at 10:30 am

     I got it worked. I deleted the user “asd” and created the user with sysadmin privilege.

151. CCSWebWeaver Says:
     October 27th, 2010 at 5:25 am

     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!

152. tobon424 Says:
     October 27th, 2010 at 10:46 pm

     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

153. Manuel Tenorio Says:
     October 28th, 2010 at 10:45 am

     Thank you a lot for your excellent post!

     Some of us just don’t born with the DataBase Management gift… whatever that means

154. balakrishnan Says:
     October 28th, 2010 at 3:53 pm

     Thanks friends, please tell us some more tutorials, Thank you

155. Geo Says:
     November 3rd, 2010 at 6:04 pm

     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

156. Bungei Says:
     November 4th, 2010 at 9:38 pm

     Big Thanks for this, my issue was fixed in seconds!

157. phoeniks Says:
     November 10th, 2010 at 1:41 pm

     THANKS for this! You are great!

158. Elham Says:
     November 10th, 2010 at 5:33 pm

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

159. sim Says:
     November 10th, 2010 at 7:02 pm

     Thanks alot! That was really useful.

160. kachwahed Says:
     November 11th, 2010 at 5:31 am

     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

161. Bob_SA Says:
     November 11th, 2010 at 8:43 pm

     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!

162. linglom Says:
     November 12th, 2010 at 9:49 am

     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?

163. Greg Says:
     November 14th, 2010 at 5:36 pm

     This worked. Thank you!
     Why they disabled TCP, Is it any danger to enable it?

164. Kachwahed Says:
     November 16th, 2010 at 1:27 am

     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.

165. Connection Issue - RaGEZONE - MMORPG server development forums Says:
     November 19th, 2010 at 1:43 am

     [...] Connection Issue Maybe your MSSQL Server doesn't accept remote connections. Enable Remote Connection on SQL Server 2008 Express | Linglom's blog See this link, and make sure, the server is accepting [...]

166. ahmet Says:
     November 21st, 2010 at 11:26 am

     i did everything but it didnt solve my problem

167. btaz125 Says:
     November 26th, 2010 at 2:57 pm

     Hi,
     How to config SQL Server 2008 to connect over Internet.

168. victor Says:
     November 30th, 2010 at 2:11 pm

     thank you very much ..

169. Jeff Says:
     November 30th, 2010 at 4:45 pm

     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!

170. CodeEase.com » Blog Archive » Again: Remote connect to SQL Server 2008 Express Says:
     December 1st, 2010 at 3:45 am

     [...] steps, the first part you can check here. our steps almost the same as [...]

171. Robertas Says:
     December 1st, 2010 at 8:08 pm

     That worked, thanks!

172. Arinola Says:
     December 2nd, 2010 at 1:50 am

     oh thank you thank you soooooooooooomuch. WORKS greatly for me. i am so happy..thanks!!!

173. Eric'H Says:
     December 2nd, 2010 at 8:55 am

     Thank you! Thank you very much!!! : )

174. kyriakos Says:
     December 2nd, 2010 at 4:39 pm

     Thank you!!!!!

175. Jeff Says:
     December 3rd, 2010 at 7:35 am

     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!

176. Daniel Says:
     December 3rd, 2010 at 11:24 am

     Great work!
     Thanks

177. rick Says:
     December 4th, 2010 at 2:00 am

     tks 4 details!

178. Arjun Says:
     December 5th, 2010 at 3:26 am

     It was very helpful. Thanks a lot.

179. mrcity Says:
     December 5th, 2010 at 11:42 pm

     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

180. Jeff Says:
     December 7th, 2010 at 8:01 am

     @Stephen

     The TCP/IP config are all set just like you stated, yet no luck.

181. Mayur Says:
     December 8th, 2010 at 1:08 pm

     Thanks a lot … keep up the good work.

182. SRAVS Says:
     December 21st, 2010 at 5:02 pm

     great job!!!!!!!!!!!!!! resolved my issue……..THANKS

183. nildeen Says:
     December 27th, 2010 at 5:15 am

     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!

184. Ivan Lewis Says:
     December 28th, 2010 at 7:51 pm

     Can i connect to sqlserver 2008 express from sql server 2005 express edition?

185. MedMost Says:
     January 4th, 2011 at 10:19 pm

     Thank you very much

     it help to solve my problem

     i think i cant log on server2008 from server2005express

186. Terry Says:
     January 13th, 2011 at 4:25 pm

     Really great..!!!

     I have followed it without any error or mistake.

     Very good.

187. Guido Says:
     January 13th, 2011 at 9:27 pm

     Bravo!!

188. Roman Says:
     January 18th, 2011 at 2:24 am

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

189. Teimoor Khan Says:
     January 23rd, 2011 at 3:14 am

     Thanks A lot to publish this article…….this article would help me in my fyp…….

190. Chuck Says:
     January 25th, 2011 at 11:42 pm

     Thank you. This helped me today.

191. Plal Says:
     January 27th, 2011 at 12:25 pm

     As mentioned in 89. “,” worked for me. Thanks!

192. Ankit Says:
     February 3rd, 2011 at 5:03 pm

     hi.. Can we achieve this using script or command prompt??

193. jcy Says:
     February 17th, 2011 at 9:48 am

     excellent guide, thank you

194. Domino Says:
     February 17th, 2011 at 7:16 pm

     My great thanks!

195. Adam Says:
     February 18th, 2011 at 12:04 pm

     When all else failed your instructions worked.

     FYI – I did have to reboot after following instructions.

     Thanks again.

196. lax Says:
     February 18th, 2011 at 12:33 pm

     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.

197. janerose Says:
     February 19th, 2011 at 1:38 am

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

198. Steve Says:
     February 22nd, 2011 at 2:04 am

     Excellent information. Thanks. You save me hours of work.

199. Mindwarp Says:
     February 23rd, 2011 at 3:57 am

     Excellent! Thank you very much for this information!

200. Ivanov Says:
     February 23rd, 2011 at 1:52 pm

     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

201. MathewUy Says:
     March 3rd, 2011 at 11:53 pm

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

202. Darshan Says:
     March 10th, 2011 at 1:02 pm

     The tech blog was really useful it was very informative. Thanks a lot!

203. Krzysztof Says:
     March 10th, 2011 at 4:35 pm

     Big thanks mr. Nipawit Luangaroon. This tutorial is verry helpful, working good Greetings from Poland

204. Jared Says:
     March 19th, 2011 at 2:37 am

     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-0×80041010.aspx.

205. Nirav Says:
     March 22nd, 2011 at 1:04 pm

     Thanks a lot mate…really helped..

206. Suresh Says:
     March 22nd, 2011 at 7:07 pm

     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.

207. linkoblast Says:
     March 30th, 2011 at 10:46 am

     Excellent….I was looking for such a good solution…..it saved my day!!

208. Roshani Says:
     March 31st, 2011 at 4:00 pm

     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

209. Wahaab Says:
     April 4th, 2011 at 1:25 am

     You were a great help!!
     there are rare articles which address a problem step by step!!

210. Dude Says:
     April 5th, 2011 at 3:44 pm

     You’re super!!this article helps me out from problem connecting to SQL Express 2008. Great thanks…

211. Joy Says:
     April 13th, 2011 at 11:03 am

     Great Article Superb Thanks to you. i was struggling quite a bit in connecting to the sql server . you saved my day

212. Krumov Says:
     April 19th, 2011 at 4:54 am

     Thank You !!!
     This is something that must be given all kudos and admirations. GREAT JOB !!!

213. gleija Says:
     April 20th, 2011 at 10:13 pm

     thanks a lot!!

214. jay bacalla Says:
     May 7th, 2011 at 6:31 pm

     thanks alot guys, this is what i realy want..

215. Qattan Says:
     May 8th, 2011 at 3:14 pm

     THAAAAAAAAAAAAAAAAAAAANNNNNNNNNNNNNNKKKKKKKKKKKSSSSSS

216. Suzy Says:
     May 9th, 2011 at 5:24 am

     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.

217. Fourier Says:
     May 18th, 2011 at 10:19 am

     Congratulations, the secret is the service SQL Server Browser. Thank you very much for your article.

218. cv service Says:
     May 18th, 2011 at 8:21 pm

     Ok thanks! really impressive i must admit

219. Cobesauce Says:
     May 19th, 2011 at 2:46 am

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

220. taner Says:
     May 23rd, 2011 at 7:52 pm

     Great one. Thanks

221. Rob Says:
     May 25th, 2011 at 9:43 am

     Installed 2008 express with Microsoft defaults … only Microsoft would release a product with the defaults that leave the product inaccessable.

222. Ramesh Says:
     May 27th, 2011 at 4:07 pm

     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.

223. Marie Says:
     June 9th, 2011 at 1:40 am

     Thank you so much for this detailed post. Perfect!

224. Nimda Says:
     June 9th, 2011 at 4:31 pm

     Thank you. Very helpful

225. Nripin Says:
     June 15th, 2011 at 7:57 pm

     Thanks for the tip mate !!
     Keep up the great Work !!
     Cheers
     Nripin

226. Moeez Says:
     June 16th, 2011 at 4:58 pm

     It is not worked for me. I have as it was written but can not access my instance remotely.

227. AlexF1980 Says:
     July 26th, 2011 at 7:48 pm

     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.

228. Marco Says:
     July 31st, 2011 at 2:12 am

     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’”

229. devesh Says:
     August 1st, 2011 at 2:20 am

     nice problem solve trick. thanks ………

230. ravell Says:
     August 4th, 2011 at 1:04 pm

     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

231. Ken Says:
     August 16th, 2011 at 5:35 am

     Thank you for posting these steps. They helped more than you know.

232. Sandeep Says:
     August 17th, 2011 at 5:43 pm

     Very usefull post…Thanks dude

233. abhi Says:
     August 23rd, 2011 at 4:43 pm

     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

234. abhi Says:
     August 23rd, 2011 at 4:51 pm

     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

235. Emil G Says:
     August 27th, 2011 at 2:18 am

     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

236. sqlserver Says:
     August 28th, 2011 at 4:28 pm

     You can also check the SQL Authentication online using the quick free tool at:
     http://www.webkeet.com/sqlservertestconnector.aspx

237. JimiSweden Says:
     August 31st, 2011 at 5:43 am

     Thanks for a great guide!

     //Jimi
     http://newsweb.se

238. DKronen Says:
     August 31st, 2011 at 10:26 pm

     Exactly what I needed

     Thanks

239. Izaks Says:
     September 12th, 2011 at 5:35 pm

     Good one. solved me a huge problem.

     Thanks.

240. jim Says:
     September 14th, 2011 at 11:20 pm

     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!

241. Tushar Says:
     September 23rd, 2011 at 1:04 am

     Great article it rocked.
     Thanks so much.

242. Al Says:
     September 23rd, 2011 at 2:50 am

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

243. Al Says:
     September 23rd, 2011 at 2:57 am

     YESSSSSS – it was the IPAall 1433 problem!!! ok now…phew

244. Peter Olofsson Says:
     September 27th, 2011 at 6:30 pm

     Excellent!
     Thanks

245. Kamlesh Says:
     October 6th, 2011 at 11:02 pm

     Thank you very much…
     It’s really helpful

246. Paul Says:
     October 10th, 2011 at 9:57 am

     Awesome! Thanks a lot!

247. Soheb Says:
     October 11th, 2011 at 12:52 pm

     Thaaaaaaaaaaaaaaaaaaaaaaaaanxxxxxxxxxx….

248. SeriousM Says:
     October 12th, 2011 at 4:07 pm

     GREAT!! Thank you sooo much !!

249. Khalane Rapuleng Says:
     October 13th, 2011 at 6:38 pm

     Great work: It really come for my rescue

250. Brad Says:
     October 18th, 2011 at 7:12 am

     Great tips….I seem to recall the protocols needing to be enabled but not the browser service

251. Aws Says:
     October 19th, 2011 at 4:34 pm

     that is great. thank you alot

252. Neeraj Says:
     October 21st, 2011 at 5:48 pm

     it’s really very helpful.Thank you..

253. Farhan Says:
     November 3rd, 2011 at 7:28 pm

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

254. Raja Says:
     November 16th, 2011 at 5:54 pm

     Thanks for valuable Informations

255. Sebastien Says:
     November 17th, 2011 at 1:58 am

     Thanks for the info. It worked!!

256. Roberto Says:
     November 18th, 2011 at 11:54 pm

     work for me, thanks!

257. Chris J Says:
     November 22nd, 2011 at 3:18 pm

     Thank you so much… 2:18 am and you saved me!

258. Farhan Says:
     November 22nd, 2011 at 5:41 pm

     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

259. Christine Says:
     November 22nd, 2011 at 7:22 pm

     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?

260. Christine Says:
     November 22nd, 2011 at 7:50 pm

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

261. Elnora Kailey Says:
     November 24th, 2011 at 10:46 am

     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!

262. Dannette Sarcone Says:
     November 25th, 2011 at 8:11 pm

     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.

263. Alberto Juliao Says:
     December 3rd, 2011 at 6:49 am

     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.

264. Josip Says:
     December 4th, 2011 at 2:54 am

     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

265. YK Says:
     December 8th, 2011 at 10:34 am

     Thank you! It helped me!

266. Alberto Juliao Says:
     December 9th, 2011 at 12:01 pm

     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

267. Mustapha Says:
     December 10th, 2011 at 3:28 am

     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

268. Alberto Juliao Says:
     December 10th, 2011 at 12:34 pm

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

269. Henry Lafferty Says:
     December 13th, 2011 at 6:20 pm

     Brilliant mate – worked first time :0)

270. Marcelo Castilho Says:
     December 24th, 2011 at 1:41 am

     Very good!!! It´s all rigth.

271. Kazinski Says:
     January 2nd, 2012 at 10:51 am

     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.

272. Kazinski Says:
     January 2nd, 2012 at 11:11 am

     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.

273. Asim Raza Says:
     January 9th, 2012 at 2:41 am

     This is best.

274. abdullah Says:
     January 16th, 2012 at 12:39 am

     thank you dear, i appreciate that!

275. Chris Says:
     January 17th, 2012 at 11:25 am

     Thanks mate!

276. nwaomachux Says:
     January 18th, 2012 at 10:50 pm

     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.

277. Farhan Says:
     January 18th, 2012 at 11:12 pm

     install a port redirector like TunnelX , It is going to work.

278. Raghava Says:
     January 20th, 2012 at 1:35 pm

     THANKQ VERY MUCH……………….

279. Stan Says:
     January 22nd, 2012 at 9:30 pm

     Great write-up. Excellent.

280. ilias Says:
     January 22nd, 2012 at 10:07 pm

     Thanks!!

Leave a Reply