Enable Remote Connection on SQL Server 2008 Express

Last time, I wrote an article show how to enable remote connection on SQL Server 2005 Express. Now SQL Server 2008 Express is released for a while, it doesn’t allow remote connection on default installation as on SQL Server 2005 Express. So you have to enable it manually. If you’re trying to connect to SQL Server 2008 Express remotely without enable remote connection first, you may see these error messages:

  • “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: 28 – Server doesn’t support requested protocol) (Microsoft SQL Server)”

    Server doesn't support requested protocol
  • “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)”

    Error Locating Server/Instance Specified
  • “Cannot connect to SQL-Server-Instance-Name
    Login failed for user ‘username‘. (Microsoft SQL Server, Error: 18456)”

    Login failed for user 'sa'

To enable remote connection on SQL Server 2008 Express, see the step below:

  1. Start SQL Server Browser service if it’s not started yet. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer.
  2. Enable TCP/IP protocol for SQL Server 2008 Express to accept remote connection.
  3. (Optional) Change Server Authentication to SQL Server and Windows Authentication. By default, SQL Server 2008 Express allows only Windows Authentication mode so you can connect to the SQL Server with current user log-on credential. If you want to specify user for connect to the SQL Server, you have to change Server Authentication to SQL Server and Windows Authentication.

Note: In SQL Server 2008 Express, there isn’t SQL Server Surface Area Configuration so you have to configure from SQL Server Configuration Manager instead.

Step-by-step to enable remote connection on SQL Server 2008 express

  1. Open SQL Server Configuration Manager. Click Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.
    SQL Server Configuration Manager
  2. On SQL Server Configuration Manager, select SQL Server Services on the left window. If the state on SQL Server Browser is not running, you have to configure and start the service. Otherwise, you can skip to step 6.
    SQL Server Browser Service
  3. Double-click on SQL Server Browser, the Properties window will show up. Set the account for start SQL Server Browser Service. In this example, I set to Local Service account.
    Set Startup Account
  4. On SQL Server Browser Properties, move to Service tab and change Start Mode to Automatic. Therefore, the service will be start automatically when the computer starts. Click OK to apply changes.
    Set Start Mode to Automatic
  5. Back to SQL Server Configuration Manager, right-click on SQL Server Bowser on the right window and select Start to start the service.
    Start SQL Server Browser Service
  6. On the left window, expand SQL Server Network Configuration -> Protocols for SQLEXPRESS. You see that TCP/IP protocol status is disabled.
    Protocols for SQL EXPRESS
  7. Right-click on TCP/IP and select Enable to enable the protocol.
    Enable TCP/IP protocol
  8. There is a pop-up shown up that you have to restart the SQL Service to apply changes.
    Need to Restart SQL Server Service
  9. On the left window, select SQL Server Services. Select SQL Server (SQLEXPRESS) on the right window -> click Restart. The SQL Server service will be restarted.
    Restart SQL Server Service
  10. Open Microsoft SQL Server Management Studio and connect to the SQL Server 2008 Express.
    Open Microsoft SQL Server Management Studio
  11. Right-click on the SQL Server Instance and select Properties.
    Open Server Properties
  12. On Server Properties, select Security on the left window. Then, select SQL Server and Windows Authentication mode.
    Change Authentication to SQL Server and Windows Authentication
  13. Again, there is a pop-up shown up that you have to restart the SQL Service to apply changes.
    Need to Restart SQL Server Service
  14. Right-click on the SQL Server Instance and select Restart.
    Restart SQL Server Service
  15. That’s it. Now you should be able to connect to the SQL Server 2008 Express remotely.

333 Comments

  1. Robert April 2, 2009
  2. Aju Peter April 7, 2009
  3. Matt April 8, 2009
  4. Ronald April 22, 2009
  5. SGR April 22, 2009
  6. Richard April 30, 2009
  7. Jason May 5, 2009
  8. Sakoda May 7, 2009
  9. linglom May 12, 2009
  10. eiad May 23, 2009
  11. Jampa June 4, 2009
  12. Vadim June 4, 2009
  13. Ben June 15, 2009
  14. Thurein June 17, 2009
  15. Azec June 18, 2009
  16. MrGreek June 24, 2009
  17. linglom June 25, 2009
  18. Geraint June 30, 2009
  19. Pooya Zandevakili July 2, 2009
  20. Renee Kraft July 9, 2009
  21. Marius July 21, 2009
  22. ig4 July 23, 2009
  23. jeff August 7, 2009
  24. Engin August 9, 2009
  25. fer August 15, 2009
  26. Serdar August 19, 2009
  27. IS Thakur August 23, 2009
  28. matthew August 28, 2009
  29. Ken September 8, 2009
  30. ali September 14, 2009
  31. sayoni October 7, 2009
  32. MAsk October 8, 2009
  33. rctaubert October 18, 2009
  34. linglom October 19, 2009
  35. rctaubert October 19, 2009
  36. Shoikat October 25, 2009
  37. J.Ackerman October 26, 2009
  38. linglom October 27, 2009
  39. Sareesh November 6, 2009
  40. Panwar November 19, 2009
  41. Jyothsna November 20, 2009
  42. David November 24, 2009
  43. jim s November 29, 2009
  44. Moonheart November 30, 2009
  45. Gerry December 10, 2009
  46. joyhan December 14, 2009
  47. imtiaz December 16, 2009
  48. Abdiel December 18, 2009
  49. KMBrewer December 19, 2009
  50. linglom December 30, 2009
  51. ALI December 30, 2009
  52. Orin Hooverson January 3, 2010
  53. Arun January 8, 2010
  54. NKO January 11, 2010
  55. leo January 15, 2010
  56. Aaron January 18, 2010
  57. DJeffer January 27, 2010
  58. gelder February 2, 2010
  59. abhineet komal February 5, 2010
  60. Ulyana Arsov February 5, 2010
  61. charles February 6, 2010
  62. Gautam February 7, 2010
  63. JVillamil February 7, 2010
  64. Gautam February 7, 2010
  65. JVillamil February 7, 2010
  66. JVillamil February 7, 2010
  67. Daniel February 7, 2010
  68. JVillamil February 8, 2010
  69. Discount Code February 8, 2010
  70. Gautam February 8, 2010
  71. linglom February 11, 2010
  72. dilip February 18, 2010
  73. JVillamil February 19, 2010
  74. Sinbad1 February 23, 2010
  75. PBC February 25, 2010
  76. terirog February 26, 2010
  77. Agus March 17, 2010
  78. GIJack March 17, 2010
  79. Andy March 19, 2010
  80. Gojko March 19, 2010
  81. kv April 7, 2010
  82. Deepika April 14, 2010
  83. linglom April 14, 2010
  84. Mike April 20, 2010
  85. Ravi April 20, 2010
  86. Vibgy Joseph April 22, 2010
  87. wn April 26, 2010
  88. paramsrikant April 28, 2010
  89. itl May 6, 2010
  90. Keren May 17, 2010
  91. Edward May 17, 2010
  92. Andy May 19, 2010
  93. Kepa May 19, 2010
  94. RahulR May 20, 2010
  95. Puroh May 20, 2010
  96. Jan May 31, 2010
  97. Derrick June 3, 2010
  98. Sarmad June 18, 2010
  99. Sarath June 21, 2010
  100. Mehboob June 24, 2010
  101. Sundar July 1, 2010
  102. Robbie Reyes July 1, 2010
  103. Dave July 2, 2010
  104. Dams75 July 7, 2010
  105. Cathy Ooi July 8, 2010
  106. Indoril Nerervar July 11, 2010
  107. Asifali Kannattil July 16, 2010
  108. Asifali Kannattil July 17, 2010
  109. Manish Jaiswal July 22, 2010
  110. Jmunoz July 23, 2010
  111. victor July 23, 2010
  112. Rakesh July 27, 2010
  113. Traci July 30, 2010
  114. Farshad July 30, 2010
  115. Bruno July 30, 2010
  116. melih August 3, 2010
  117. Jasper August 3, 2010
  118. migro August 5, 2010
  119. migro August 5, 2010
  120. pankaj August 9, 2010
  121. Liberty!!! August 12, 2010
  122. Sandae August 12, 2010
  123. sunsetquest August 13, 2010
  124. George August 18, 2010
  125. Rajesh August 20, 2010
  126. Sivawut Srithammawut August 26, 2010
  127. Ilya August 27, 2010
  128. Paul August 29, 2010
  129. Paul August 31, 2010
  130. Jeffrey September 1, 2010
  131. eqirtas September 3, 2010
  132. Chanty September 8, 2010
  133. bpelhos September 9, 2010
  134. Jguzman September 11, 2010
  135. david September 22, 2010
  136. Imran September 22, 2010
  137. Abraham September 23, 2010
  138. AD September 30, 2010
  139. transportinoradea.ro October 5, 2010
  140. rodcompo October 6, 2010
  141. Pingback: multi seat set up... - MediaPortal Forum October 9, 2010
  142. linglom October 11, 2010
  143. kushi October 17, 2010
  144. Abhay October 18, 2010
  145. phpbugs October 22, 2010
  146. Leonardo Suarez October 26, 2010
  147. phpbugs October 26, 2010
  148. CCSWebWeaver October 27, 2010
  149. tobon424 October 27, 2010
  150. Manuel Tenorio October 28, 2010
  151. balakrishnan October 28, 2010
  152. Geo November 3, 2010
  153. Bungei November 4, 2010
  154. phoeniks November 10, 2010
  155. Elham November 10, 2010
  156. sim November 10, 2010
  157. kachwahed November 11, 2010
  158. Bob_SA November 11, 2010
  159. linglom November 12, 2010
  160. Greg November 14, 2010
  161. Kachwahed November 16, 2010
  162. ahmet November 21, 2010
  163. btaz125 November 26, 2010
  164. victor November 30, 2010
  165. Jeff November 30, 2010
  166. Robertas December 1, 2010
  167. Arinola December 2, 2010
  168. Eric'H December 2, 2010
  169. kyriakos December 2, 2010
  170. Jeff December 3, 2010
  171. Daniel December 3, 2010
  172. rick December 4, 2010
  173. Arjun December 5, 2010
  174. mrcity December 5, 2010
  175. Jeff December 7, 2010
  176. Mayur December 8, 2010
  177. SRAVS December 21, 2010
  178. nildeen December 27, 2010
  179. Ivan Lewis December 28, 2010
  180. MedMost January 4, 2011
  181. Terry January 13, 2011
  182. Guido January 13, 2011
  183. Roman January 18, 2011
  184. Teimoor Khan January 23, 2011
  185. Chuck January 25, 2011
  186. Plal January 27, 2011
  187. Ankit February 3, 2011
  188. jcy February 17, 2011
  189. Domino February 17, 2011
  190. Adam February 18, 2011
  191. lax February 18, 2011
  192. janerose February 19, 2011
  193. Steve February 22, 2011
  194. Mindwarp February 23, 2011
  195. Ivanov February 23, 2011
  196. MathewUy March 3, 2011
  197. Darshan March 10, 2011
  198. Krzysztof March 10, 2011
  199. Jared March 19, 2011
  200. Nirav March 22, 2011
  201. Suresh March 22, 2011
  202. linkoblast March 30, 2011
  203. Roshani March 31, 2011
  204. Wahaab April 4, 2011
  205. Dude April 5, 2011
  206. Joy April 13, 2011
  207. Krumov April 19, 2011
  208. gleija April 20, 2011
  209. Suzy May 9, 2011
  210. Fourier May 18, 2011
  211. Cobesauce May 19, 2011
  212. taner May 23, 2011
  213. Ramesh May 27, 2011
  214. Marie June 9, 2011
  215. Nimda June 9, 2011
  216. Nripin June 15, 2011
  217. Moeez June 16, 2011
  218. AlexF1980 July 26, 2011
  219. Marco July 31, 2011
  220. devesh August 1, 2011
  221. ravell August 4, 2011
  222. Ken August 16, 2011
  223. Sandeep August 17, 2011
  224. abhi August 23, 2011
  225. abhi August 23, 2011
  226. Emil G August 27, 2011
  227. sqlserver August 28, 2011
  228. JimiSweden August 31, 2011
  229. DKronen August 31, 2011
  230. Izaks September 12, 2011
  231. jim September 14, 2011
  232. Tushar September 23, 2011
  233. Al September 23, 2011
  234. Al September 23, 2011
  235. Peter Olofsson September 27, 2011
  236. Kamlesh October 6, 2011
  237. Paul October 10, 2011
  238. Soheb October 11, 2011
  239. SeriousM October 12, 2011
  240. Khalane Rapuleng October 13, 2011
  241. Brad October 18, 2011
  242. Aws October 19, 2011
  243. Neeraj October 21, 2011
  244. Farhan November 3, 2011
  245. Raja November 16, 2011
  246. Sebastien November 17, 2011
  247. Roberto November 18, 2011
  248. Chris J November 22, 2011
  249. Farhan November 22, 2011
  250. Christine November 22, 2011
  251. Christine November 22, 2011
  252. Elnora Kailey November 24, 2011
  253. Dannette Sarcone November 25, 2011
  254. Alberto Juliao December 3, 2011
  255. Josip December 4, 2011
  256. YK December 8, 2011
  257. Alberto Juliao December 9, 2011
  258. Mustapha December 10, 2011
  259. Alberto Juliao December 10, 2011
  260. Henry Lafferty December 13, 2011
  261. Marcelo Castilho December 24, 2011
  262. Kazinski January 2, 2012
  263. Kazinski January 2, 2012
  264. Asim Raza January 9, 2012
  265. abdullah January 16, 2012
  266. Chris January 17, 2012
  267. nwaomachux January 18, 2012
  268. Farhan January 18, 2012
  269. Raghava January 20, 2012
  270. Stan January 22, 2012
  271. ilias January 22, 2012
  272. Rick February 5, 2012
  273. Wellington February 8, 2012
  274. Haza February 12, 2012
  275. Roopesh February 14, 2012
  276. ANkit Roy February 24, 2012
  277. Nicola Matarese March 5, 2012
  278. dba March 8, 2012
  279. Wombat69 March 9, 2012
  280. cape March 19, 2012
  281. Poetry April 6, 2012
  282. Halmeic April 7, 2012
  283. Filme April 9, 2012
  284. Tihanyi Zoltán April 13, 2012
  285. Nio April 17, 2012
  286. Mickey April 17, 2012
  287. Inno April 17, 2012
  288. hicsel April 17, 2012
  289. Ernesto April 18, 2012
  290. Ranjit April 18, 2012
  291. Yamini April 19, 2012
  292. Kurd April 20, 2012
  293. linglom April 25, 2012
  294. Mubarak April 29, 2012
  295. Mubarak April 29, 2012
  296. Sethu May 1, 2012
  297. Ola May 2, 2012
  298. Mike May 11, 2012
  299. Stef May 23, 2012
  300. Zahir June 12, 2012
  301. Amir Yaqoob June 13, 2012
  302. Daksh Bhatt July 1, 2012
  303. KW July 6, 2012
  304. Osmumos July 19, 2012
  305. ivykoii August 20, 2012
  306. Mostafa August 21, 2012
  307. Christos August 26, 2012
  308. Israeli Dude August 28, 2012
  309. oderu September 4, 2012
  310. Dave October 9, 2012
  311. Bill October 13, 2012
  312. Dave October 17, 2012
  313. Murphiks October 26, 2012
  314. Álvaro October 29, 2012
  315. Steve November 20, 2012
  316. Mariola November 23, 2012
  317. rvfigueroa November 25, 2012
  318. shabnam December 19, 2012
  319. Alberto Juliao December 19, 2012
  320. masud January 22, 2013
  321. Ponnusamy January 23, 2013
  322. fateme January 25, 2013
  323. Krajisejck February 7, 2013
  324. Ahsan Ashfaq April 25, 2013
  325. Sarang May 1, 2013
  326. andriy May 16, 2013
  327. Costin August 25, 2013
  328. bogser September 13, 2013
  329. Benjin October 31, 2013
  330. sajjad December 30, 2013
  331. Tomas Sancio January 28, 2014
  332. khalid z? March 4, 2014
  333. GB January 27, 2016
  334. Wagner July 15, 2016

Leave a Reply

%d bloggers like this: