Enable remote connection to SQL Server 2005 Express

By default, SQL Server 2005 doesn’t allow remote connection so you have to enable it manually. If you try to connect to remote SQL Server without enable remote connection first, you’ll see one of these error messages.

Login failed for user ‘sa’. The user is not associated with a trusted SQL Server connection.

Login failed for 'sa'

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

SQL Server does not allow remote connection

To resolve these problems, you have 2 majors task. One is to enable both SQL Server and Windows authentication mode on SQL Server and the other is to allow remote connection and SQL Server Browser.

Step-by-step enable remote connection to SQL Server 2005 Express

  • Check that you have allowed both SQL Server and Windows Authentication mode (If you’re not use Windows Authentication mode). For instance, user “sa”.
    1. Login to SQL Server using SQL Server Management Studio Express on local SQL Server with Windows Authentication user. This will use Windows account to authenticate to SQL Server.
      Login using Windows authentication mode on local
    2. In Object Explorer, right click on the instance name and select Properties.
      Open SQL Server Properties
    3. On the left, select Security and change Server authentication to SQL Server and Windows Authentication mode. If the choice is already selected, that means you have already allowed SQL Server authentication.
      Change SQL Server authentication mode
    4. There’ll be an information window shows that you need to restart SQL Server to takes effect of the changes.
      SQL Server need restart
    5. Right-click on the instance name again, select Restart to restart SQL Server service.
      Restart SQL Server Service
    6. Select Yes.
      Confirmation
    7. Wait for service stop and start.
      Restarting SQL Server Service
    8. That’s it. You have enabled both SQL Server and Windows authentication so now you can login to SQL Server with your current Windows account or your created account.
  • Check that you have enabled remote connection on SQL Server Surface Area Configuration
    1. Open SQL Server Surface Area Configuration.
      Open SQL Server Surface Area Configuration
    2. Select Surface Area Configuration for Services and Connections.
      Open Surface Area Configuration for Services and Connections
    3. On the left side, expand your SQL Server instance -> Database Engine -> Remote Connections. On the right side, select Local and remote connections -> using both TCP/IP and named pipes.
      Allow remote connections
    4. On the left side, select SQL Server Browser -> Service.
      On the right side, if the startup type is Disable, you need to change to Automatic and click Apply and click Start button to start the service. Then, click OK.
      Start SQL Server Browser
    5. You have finished configure. Now try to login to your SQL Server from remote computer using SQL Server authentication mode.
      Login to remote SQL Server
    6. Login to SQL Server succeeded.
      Login succeeded

Summary

This topic may be found on the Internet in many site or even Microsoft support but the purpose is to rearrange with easier to read and follow with graphic along with each step so it may help you reminder solution faster or even the person who have just start to use SQL Server 2005 Express Edition.

104 Comments

  1. Mohammad Sanati September 10, 2007
  2. linglom September 11, 2007
  3. James November 9, 2007
  4. linglom November 9, 2007
  5. Nigel Stanhope April 14, 2008
  6. sowens May 17, 2008
  7. linglom May 18, 2008
  8. elyas June 20, 2008
  9. Sanjeev July 18, 2008
  10. Vlad July 24, 2008
  11. Nimesh July 25, 2008
  12. Someone July 31, 2008
  13. linglom July 31, 2008
  14. eherzel August 8, 2008
  15. linglom August 10, 2008
  16. eherzel August 11, 2008
  17. linglom August 11, 2008
  18. Sivakumar Sundararajan August 22, 2008
  19. Curtis Sumpter October 28, 2008
  20. Mr Grateful November 18, 2008
  21. HKouts December 19, 2008
  22. Atul Rai December 28, 2008
  23. Web Design January 9, 2009
  24. linglom January 11, 2009
  25. Mouk Kim February 2, 2009
  26. MC February 6, 2009
  27. Jacky February 18, 2009
  28. Eralper February 25, 2009
  29. aman March 16, 2009
  30. linglom March 18, 2009
  31. aman March 18, 2009
  32. jay March 27, 2009
  33. Sebastian Greco April 6, 2009
  34. Nanda kumar May 22, 2009
  35. Aravind May 22, 2009
  36. xman May 25, 2009
  37. Pearl June 1, 2009
  38. Saraboji Jayaraman June 4, 2009
  39. Safuan June 5, 2009
  40. linglom June 11, 2009
  41. dangquang June 29, 2009
  42. JanardhanKR July 8, 2009
  43. moul July 8, 2009
  44. azhar August 24, 2009
  45. al August 25, 2009
  46. linglom September 2, 2009
  47. SL September 5, 2009
  48. Hy Chanhan September 17, 2009
  49. Vishnu October 9, 2009
  50. Rufus October 10, 2009
  51. rctaubert November 6, 2009
  52. linglom November 9, 2009
  53. rctaubert November 9, 2009
  54. aruna November 11, 2009
  55. FM November 13, 2009
  56. aruna November 13, 2009
  57. linglom November 17, 2009
  58. Thuan November 22, 2009
  59. gaul December 11, 2009
  60. linglom December 30, 2009
  61. ABHI January 23, 2010
  62. linglom January 25, 2010
  63. Amar February 18, 2010
  64. linglom February 23, 2010
  65. priya March 16, 2010
  66. emmanuel March 26, 2010
  67. RajeshR April 17, 2010
  68. vrushali April 22, 2010
  69. Nelson Liao April 27, 2010
  70. raynor April 28, 2010
  71. Prabhakar June 9, 2010
  72. Sunil June 17, 2010
  73. linglom June 18, 2010
  74. mike July 2, 2010
  75. ToeToeAung July 13, 2010
  76. Asif Kannattil July 14, 2010
  77. Prashant Nidgunde July 14, 2010
  78. linglom July 15, 2010
  79. Pallavi August 5, 2010
  80. Amit October 28, 2010
  81. ahmet November 22, 2010
  82. George Iyke December 13, 2010
  83. linglom December 18, 2010
  84. Hein January 3, 2011
  85. PAYAS February 16, 2011
  86. jide March 1, 2011
  87. Shobharani March 17, 2011
  88. tarunesh March 24, 2011
  89. linglom March 31, 2011
  90. ali April 15, 2011
  91. BijuKS May 20, 2011
  92. trueman November 18, 2011
  93. Nancee Petruccelli December 1, 2011
  94. Novella Wyrostek December 2, 2011
  95. Hemant December 9, 2011
  96. bryan January 2, 2012
  97. Neha February 15, 2012
  98. Sarvesh March 26, 2012
  99. Raaj May 9, 2012
  100. stpaulchuck December 25, 2012
  101. Michael March 1, 2013
  102. fawad ali May 29, 2015
  103. linglom May 29, 2015

Leave a Reply