How to configure SQL Express 2012 to accept remote connections

/, XenApp, XenDesktop/How to configure SQL Express 2012 to accept remote connections

How to configure SQL Express 2012 to accept remote connections

Share this post

When you try to connect to an instance of Microsoft SQL Server 2012 Express from a remote computer, you might receive an error message.

INTRODUCTION

Named SQL instances listen on dynamic ports. This is the function of the Sql Server Browser Service to inform the clients of the actual port. The Sql Browser listens on UDP 1434 and answers all client request with the port number the current instance is using. Sql Server Browser service is required for both TCP and named pipes protocols. SQL Server Browser is used by clients transparently and there is no need for special configuration.

To configure SQL Server 2012 Express to allow remote connections, you must complete these steps:

Update 2015.05.11

If you want to use GPO to configure SQL server Standard or Enterprise to accept remote connections see my new post: How to use GPO to allow SQL 2012 accept remote connections

 

Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.

1. Open SQL Management Studio and right-click server name in the left pane and select Properties

 Figure 1


Figure 1

2. Select Connections in the left pane and make sure that checkbox Allow remote connections to this server is selected as it is shown in Figure 2.

 Figure 2


Figure 2

 

Configure SQL express server to listen on static port.

1. Open SQL Server Configuration Manager and click on “SQL Server Services” in the left pane.

 Figure 3


Figure 3

2. In the center pane, is a column that lists the Process ID for each running service. Look for the PID in the row for SQL Server. Identify the port that that PID is listening on by typing this into a command prompt:

netstat -ano | find /i “PID-Number-Of-SQL-Server”. Based on the details shown in Figure 3 syntax is the following: netstat -ano | find /i “116”. The results are shown in Figure 4.

 Figure 4


Figure 4

3. There is no results from the command executed in step 3 because TCP/IP protocol is disabled and must be enabled. In SQL Server Configuration Manager and click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol and select option Enable.

 Figure 6


Figure 5

4. Restart SQL Server service  and identify the process ID assigned to SQL service.

 Figure 6


Figure 6

5. In the command prompt execute command: netstat -ano | find /i “6524”. The results are shown in Figure 7.

 Figure 7


Figure 7

6.  In SQL Server Configuration Manager and click on SQL Server Network Configuration in the left pane and right-click TCP/IP protocol and select option Properties. Goto IP Address tab and scroll-down to APAll section. Remove value for TCP Dynamic Ports (do not enter Zero 0 !!!) and enter the port 1433 for TCP Port.

 Figure 7


Figure 8

7. Restart SQL Server service, identify new process ID assigned to SQL service and in the command prompt execute command: netstat -ano | find /i “3948”. The results are shown in Figure 9.

 Figure 9


Figure 9

At this stage SQL Express is configured to listen on standard port 1433.

Turn on the SQL Server Browser service.

1. Open SQL Server Configuration Manager and click on “SQL Server Services” in the left pane, right-click SQL Server Browser service and select Properties.

 Figure 10


Figure 10

2. Go to Service tab and for Start Mode option change start type to Automatic.

 Figure 11


Figure 11

3. Click Start button to start SQL Browser service

 Figure 12


Figure 12

4. Confirm that SQL Server Browser service is up and running as it is shown in Figure 13.

 Figure 13


Figure 13

Important note: According to SQL server hardening best practices the SQL Server Browser service should be disabled. This service, which typically isn’t required, responds to requests for SQL Server resources and redirects the caller to the correct port. Keeping the Browser service disabled will remove the redirector as an attack vector, helping to obscure the correct entry ways into your SQL Server components.

Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

Four exceptions must be configured in Windows Firewall to allow access to SQL Server:

  1. A port exception for TCP Port 1433. In the New Inbound Rule Wizard dialog, use the following information to create a port exception:
    • Select Port
    • Select TCP and specify port 1433
    • Allow the connection
    • Choose all three profiles (Domain, Private & Public)
    • Name the rule “SQL – TCP 1433”
  2. A port exception for UDP Port 1434. Click New Rule again and use the following information to create another port exception:
    • Select Port
    • Select UDP and specify port 1434
    • Allow the connection
    • Choose all three profiles (Domain, Private & Public)
    • Name the rule “SQL – UDP 1434
  3. A program exception for sqlservr.exe. Click New Rule again and use the following information to create a program exception:
    • Select Program
    • Click Browse to select ‘sqlservr.exe’ at this location:
[C:\Program Files\Microsoft SQL Server\MSSQL11.<INSTANCE_NAME>\MSSQL\Binn\sqlservr.exe] where <INSTANCE_NAME> is the name of your SQL instance.  
  • Allow the connection
  • Choose all three profiles (Domain, Private & Public)
  • Name the rule SQL – sqlservr.exe
  • A program exception for sqlbrowser.exe Click New Rule again and use the following information to create another program exception:
    • Select Program
    • Click Browse to select sqlbrowser.exe at this location: [C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe]. 
    • Allow the connection
    • Choose all three profiles (Domain, Private & Public)
    • Name the rule SQL – sqlbrowser.exe
  •  

    By | 2016-12-18T19:21:37+00:00 January 23rd, 2014|SQL, XenApp, XenDesktop|102 Comments

    About the Author:

    I’m a Citrix Architect with 17 years experience in Microsoft and Citrix infrastructure. I have been working with Citrix since Metaframe 1.8 and my primary focus is on Server, Desktop and Application virtualisation with a preference for Citrix products. I’m an enthusiast of Citrix XenDesktop and Provisioning Server.

    102 Comments

    1. Marko Jozic April 9, 2014 at 9:06 pm - Reply

      This is actually the first Blog-Post which helped me with my SQL-Express Network Problem.

      I’ve tried to connect MS Access 2010 to my SQL Express 2012 Instance via network but it failed because of the Windows firewall.

      Your tip with the SQL-Browser helped me a lot and solved my Problem.

      Thank you 🙂

      • Andrzej Gołębiowski April 10, 2014 at 10:33 am - Reply

        Thank you for your comment. I’m glad you found this post usefull.

        Andrzej

      • Hrvoje Kevic April 10, 2016 at 10:31 am - Reply

        I second this one. Even thought it was for an SQL 2012, but it was precise and concise as one blog should be.
        Big thank you to Andrzej Gołębiowski!

    2. Jason April 21, 2014 at 3:38 am - Reply

      Very clear & complete instructions. Thank you.

    3. Dave May 16, 2014 at 12:35 pm - Reply

      For my 64 bit SQL Express install on a 64 Bit version of Windows 7 I found that the location of sqlbrowser.exe was not at this location: [C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe]. It took me a few to locate it because it was actually located here: [C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe].

    4. Erika October 8, 2014 at 3:32 pm - Reply

      Excellent instructions! One of the most useful, accurate blog posts I’ve found.

    5. Anne October 18, 2014 at 12:16 pm - Reply

      Hello… I followed all the steps and when I get to netstat -ano | find /i “PID-Number-Of-SQL-Server”, I’m missing one line in the results. I do not get the Established line… can you help?

      • Andrzej Gołębiowski October 20, 2014 at 7:19 am - Reply

        Try to connect to sql server and than execute netstat -ano command.

        • Anne October 20, 2014 at 6:06 pm - Reply

          Thanks… I am connected to SQL server…

          • Anne October 31, 2014 at 6:01 pm - Reply

            Does not work even when connected to SQL….

    6. david November 7, 2014 at 1:12 am - Reply

      wow very clear.. and very helpful

      thanks

    7. Renan November 13, 2014 at 10:57 pm - Reply

      Thank you!!!!!!!!!!

    8. Terry Davidson December 14, 2014 at 3:33 pm - Reply

      Andrew
      Truly a useful explanation of what to do. I have been struggling with the other advice I found on the Iternet but yours was direct and accurate.
      Thanks very much

    9. […] I finished adult adding Firewall manners and following some of a stairs in this glorious configure SQL Express 2012 to accept remote connectors article. […]

    10. CJ January 3, 2015 at 7:25 am - Reply

      excellent article, this is how documentation should be written. nothing has been assumed as a given and all spelled out step by step, a novice can follow it successfully.

      perhaps you can add some additional points for the following topics:
      1) how to specify connections using a specific IP address, as in the case when using a hosted virtual machine not part of your network
      2) how to create SQL users and connect via sql server authentication in the case where the DB server is not part of the domain

    11. Javier January 7, 2015 at 12:56 pm - Reply

      Excellent!! Works perfect! Thanks!

    12. IT researcher January 12, 2015 at 7:07 am - Reply

      What if the windows firewall is disabled ?

      • Andrzej Gołębiowski January 12, 2015 at 10:58 am - Reply

        Check the event log for more information. If you have problem with connecting send me the error and I will try to help you to solve this problem.

    13. Matt C January 13, 2015 at 3:40 pm - Reply

      Absolutely amazing. Thank you for the very thorough instructions. I was beating my head against the wall for weeks! The real kicker for me was the part about adding a firewall inbound rules. That was what was holding me up.

      Thank you!

    14. […] am running SQL server 2012 web edition on Windows Server 2008 R2. I have followed all these steps https://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/. But still unable to connect remotely. How to debug and find why it is not working. Tried […]

    15. Andrew Henson March 10, 2015 at 12:03 am - Reply

      Thanks for posting this, I tried it and it worked first time 🙂

    16. Petar March 26, 2015 at 1:32 pm - Reply

      Thank you, instructions are very clear and easy to follow.

    17. CT April 2, 2015 at 7:52 pm - Reply

      thank you for this article. for those having issues with the netstat -ano command try this: netstat -ano | findstr %PID%

    18. FreeWebProxies.org April 6, 2015 at 5:04 am - Reply

      Thanks for posting this, I tried it and it worked first time!

    19. Vijay Dsouza April 13, 2015 at 12:59 pm - Reply

      Thanks

    20. JRB April 16, 2015 at 6:47 am - Reply

      You did a very good job Andrzej, an excellent article.

    21. Pete May 8, 2015 at 5:58 am - Reply

      Nice Article. very informative. Cheers!!

    22. sahin May 8, 2015 at 8:40 am - Reply

      hi. in my SQL server configuration manager, i cannot see any protocol section under sql server network configuration. there is nothing under sql server network configuration. what should i do? i cannot enable tcp/ip.

    23. Marcos Paulo May 13, 2015 at 10:59 am - Reply

      Excellent job. Thanks a lot. Congrats! PS: SQL Server Express 2012: sqlbrowser.exe @ \Program Files (x86)

    24. Online Odisha May 19, 2015 at 4:24 pm - Reply

      This post is very useful to me. Thanks for this post

    25. Jane June 1, 2015 at 5:31 pm - Reply

      Thanks very much, it is very helpful.

    26. Alfredo June 10, 2015 at 12:02 am - Reply

      hello, excellent tutorial. Perform all the steps above but I failed to connect remotely. If I am in a local network it works perfectly , but when I try from a pc that is not on the local network is down . And probe everything you say on the internet and still not managed to fix it . I have the firewall configured , the router with ports 1433 and 1434 open the TCP / IP configured and enabled . If you have any idea which may be my problem would be very grateful to you for help .

    27. glany June 12, 2015 at 10:20 am - Reply

      Thanks a lot. It helped me

    28. L.N.Vu June 13, 2015 at 5:49 am - Reply

      Very excellent instruction, Thank you Andrzej .

    29. […] Enable remote connections on the instance of SQL Server that you want to connect to from a remote co… […]

    30. Julio del Aguila July 9, 2015 at 3:35 am - Reply

      Excellent, Thanks

    31. Mark July 9, 2015 at 9:20 am - Reply

      Thank you

    32. Ian Dalrymple July 11, 2015 at 6:36 pm - Reply

      Good work, very clean and well done instructions. I wish I had found this a while back and would have saved a lot of time. Thanks for putting this together.

    33. Daniel July 21, 2015 at 6:23 am - Reply

      Thank you very much. This was a huge help.

    34. A July 23, 2015 at 2:05 pm - Reply

      For :
      “A program exception for sqlbrowser.exe Click New Rule again and use the following information to create another program exception:
      Select Program
      Click Browse to select sqlbrowser.exe at this location: [C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe].
      Allow the connection
      Choose all three profiles (Domain, Private & Public)
      Name the rule SQL – sqlbrowser.exe”

      Very Nice……
      Very Good…..
      Excelent……
      Thank For U

    35. Andreas August 6, 2015 at 10:23 am - Reply

      Thank you very much for the beautiful all-encompassing instructions. Being very new to SQL server I’ve been blundering about for days trying to accomplish this task. It works the same on SQL Server 2014.

    36. Joachim Berger August 8, 2015 at 3:32 pm - Reply

      Again it worked for me. Thank you!

    37. Jaison Lucas August 22, 2015 at 8:46 pm - Reply

      Thanks for the post. Been looking to do this for the last 3 days then landed onto this post. worked perfectly as illustrated.

    38. Brian August 27, 2015 at 4:04 pm - Reply

      You’re a gentleman and a scholar. Thanks!

    39. Maxx September 1, 2015 at 3:01 pm - Reply

      You are the star! Thanks a lot for this post. It saved me some days or maybe even weeks!

    40. Ajit September 15, 2015 at 2:36 pm - Reply

      Worked Perfectly. Thank you for the detailed explanation.

    41. Vin September 18, 2015 at 4:29 am - Reply

      Excellent article Andrzej, you just made my day. I was troubleshooting the SQL connection issue since morning and I’m glad that I found your article. Good job, keep it up. All sorted now 🙂

    42. badr October 2, 2015 at 10:55 am - Reply

      just awesome !!!!

    43. john October 4, 2015 at 10:13 pm - Reply

      Hi Andrzej,
      Thank you so much for this detailed documentation. You made my day!
      I had a problem connecting to a named sql server (Widows Server 2008R2/SQL Server 2012) instance hosted in a Virtual machine, which is a complicated environment that is hard to know which part of setting is missing. I’ve searched and tried many other documentation online but no luck. Your detailed documentation solved my problem right away. Your detailed instruction for configuring firewall port and program was most helpful.
      Thank you for your great article which solved my problem.

    44. James October 9, 2015 at 10:50 am - Reply

      Excellent guide. Within 15 minutes got it working and now have remote access. Keep up the good work.

    45. Cato October 9, 2015 at 4:11 pm - Reply

      You show how to configure SQL aerver to use the standard port 1433. I Think you miss a very big point in this guide that you confuse people and not telling them the different in using dynamic and static port in this context. You say you are skilled so show it 🙂
      This is simple, if you use static ports you do not use SQL broweser and do not need to open for it in the fierwall. SQL browser is used when dynamic ports are configured for SQL server. So to me it looks like you are saying that they must open for both and have SQL server browser service running even with static ports, that is not the case. If you open for SQL browser as a program in the firewall you don’t need to add udp port. And what’s up with the netstat? It is a completely unesassary step to perform as you the configuration and the dynamic port in sql configuration manager and if it is setup with static or dynamic from the beginning.
      One thing to mention, if you install SQL server with the default instance it is setup with the static port 1433 with tcp/ip disabled. If you install it with a named instance it is setup to dynamic ports. So this would apply to every new named instances that it will be setup to use dynamic ports.

    46. Yoann October 10, 2015 at 6:26 pm - Reply

      Very well written. Thank you very much.

    47. Lester November 5, 2015 at 10:21 pm - Reply

      is that instructions will allow me to remote access to my sql server over the internet?? thanks..

    48. Manuel Macias November 19, 2015 at 8:09 am - Reply

      Very clear and usefull. This instructions were helpfull for sure. Thank you, congratulations!
      Mexican .net POS developer

    49. lavish November 24, 2015 at 9:16 am - Reply

      i selected mixed mode for authentication, but test connection is successful via sql security method but not by windows authentication method

    50. Leo November 28, 2015 at 7:48 pm - Reply

      Thank you so much for posting this! I didn’t have any luck with the netstat command, but everything else was very clear and spot on. I had it configured in about 10 minutes. Great article!

    51. Winston Malana December 13, 2015 at 5:26 am - Reply

      Hi Mark,

      You just wow us. This post is simply the BEST and I can’t stop my self from giving this compliment. I am running a software development company and your article has been very useful to us. A step by step, clearly demonstrated with each instruction perfectly understandable. Thank you for sharing your knowledge to the world. Please don’t get tired sharing your talent. Kudos to your work Mark. Thanks again- Wins, Manila Philippines.

    52. Venugopala December 17, 2015 at 6:35 am - Reply

      Thank you very much. I was trying to set this and I was missing the Section 6 and 7 IPAll configuration.
      Beautiful explaination

    53. Javier December 29, 2015 at 12:03 pm - Reply

      Thanks,
      Excellent explanation!

    54. Sandip Kumar January 30, 2016 at 3:51 pm - Reply

      Very nice article and really helpful for people who needs connection across local network

    55. Niraj Sevalkar February 4, 2016 at 5:40 am - Reply

      Superb illustration 🙂

      It has helped me to sort out my problem

    56. David Sukola February 11, 2016 at 9:28 pm - Reply

      Great walk through on getting a remote connection for MSSQL 2012 up and running. Works wonderful for us.

    57. Daniel February 18, 2016 at 6:36 pm - Reply

      Great post. It helped me a lot. I had to link the remote server first on destination; next, I had to execute this:

      sp_addlinkedsrvlogin
      @rmtsrvname = N’TELLWARE’,
      @locallogin = N’your local user’,
      @useself = N’False’,
      @rmtuser = N’your remote user’,
      @rmtpassword = N’xxxxxxxx’

    58. […] How to configure SQL Express 2012 to accept remote connections […]

    59. Reza Ghandchi March 28, 2016 at 5:49 pm - Reply

      Thank you so much …
      It helped me a great deal

    60. […] How to configure SQL Express 2012 to accept remote connections […]

    61. Christer May 5, 2016 at 10:27 pm - Reply

      This made my day 🙂

    62. Leo Chen June 15, 2016 at 12:28 am - Reply

      Thank you very much.
      Help me a lots.

    63. […] Enable remote connections on the instance of SQL Server that you want to connect to from a remote co… […]

    64. Andrea Brice July 27, 2016 at 11:49 pm - Reply

      Still working even after all these years. THANK YOU!!!! Clearly written.

    65. Jarel Walker August 17, 2016 at 2:45 pm - Reply

      Thank you so much. I was banging my head against my desk for over a week trying to get a two controller setup working with SQL Express. These instructions worked like a charm!

    66. Ozzy September 2, 2016 at 10:59 am - Reply

      Must say thanks for this. It worked IMMEDIATELY on Windows 10 + SQL Server Management Studio + SQL Server 2012 Local instance. I really expected something to go wrong (as they usually do) but it worked. Something I will have to put more effort into (writing really comprehensive tutorials). Thanks a lot.

    67. Carl September 14, 2016 at 9:43 am - Reply

      Absolutely fantastic post – very clear, helped me untangle the mess I’ve created over the past few months

    68. Harishankar November 4, 2016 at 11:34 am - Reply

      Excellent article, Precise and concise. Appreciate it. Thanks much.

    69. Hemant November 14, 2016 at 7:22 am - Reply

      this helped me solve my problem. Thanks!

    70. Sabir November 15, 2016 at 9:46 pm - Reply

      Thanks a lot you are a life saver.

    71. sachin December 1, 2016 at 9:01 am - Reply

      worked like a charm 🙂

    72. Anton Stein December 7, 2016 at 3:13 pm - Reply

      You are my hero! Thank you so much for this.. I can’t believe it worked.

    73. Khumz December 8, 2016 at 3:34 pm - Reply

      This post worked perfectly for me. Thank you

    74. David Brown December 15, 2016 at 3:56 am - Reply

      Very well written post. I followed all of your steps, but now I am getting the following error:

      Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Cannot generate SSPI context.

      How do I correct that?

    75. Shujauddin Ahmed January 2, 2017 at 11:31 am - Reply

      Thanks a alot.

    76. Miguel January 20, 2017 at 7:55 am - Reply

      Thans so much for the post, It’s a very clear step by step to configure sql express 2012 to accept remote connection.

    77. sgwong January 22, 2017 at 4:44 pm - Reply

      It worked, it worked, it worked!!!
      Thx for the detailed post….it save my day!

    78. Thompson March 30, 2017 at 12:48 pm - Reply

      Unfortunately this has not worked on windows 7 and I don’t know why? please can someone help !!!

    79. Shay Frenkel April 10, 2017 at 3:50 pm - Reply

      Thank you very much for this wonderful post!

    80. Mujtaba April 11, 2017 at 10:05 am - Reply

      Done all but i am not getting ESTABLISHED line shown in fig 7 and fig 9. Any ideas ?

    81. Amar Kumbhar April 20, 2017 at 11:48 am - Reply

      Thanks . Very useful

    82. Raed May 19, 2017 at 6:32 am - Reply

      Thank you very much

    83. Sunit Ranjan Poddar May 22, 2017 at 2:36 pm - Reply

      Hi,
      Your article was really very helpful. Although i’m stuck where we’ve to change the SQL server’s TCP port to 1433 and dynamic port is left blank. I’m getting “windows could not start the sql server on local computer” error while restarting the service.
      Please find the link and see the error that i’m getting –
      http://prntscr.com/farp7e

      When i followed the error in Event Log i found this –
      http://prntscr.com/farq70

    84. Carl Nightingale May 28, 2017 at 1:27 am - Reply

      3 hours spent trying to sort a remote connection tonight until I found this post. Fantastic job, well written & worked first time.

      I can’t help wondering….”Is this really the way MS intended people to spend their Saturday nights? Surely there’s a simpler way?”

      Many thanks,
      Carl

    85. Vasanth July 14, 2017 at 6:08 pm - Reply

      Thanks so much Andrzej Gołębiowski for the article, this is the most clear and concise document. I followed the step by step instruction. Voila, I connected to SQL Server instance.

    86. Nate August 3, 2017 at 3:18 pm - Reply

      This was a huge help! Thanks so much!

    87. Divya Paliwal August 3, 2017 at 8:51 pm - Reply

      It worked.. You are awesome. Thank you thank you thank you 🙂

    88. Pete August 7, 2017 at 6:59 am - Reply

      It’ also work on MS SQL EXPRESS 2014 , Thank you so much

    89. Thakur Das August 8, 2017 at 10:10 am - Reply

      thank you so much it helps me a lot

    90. Alessandro September 8, 2017 at 10:15 am - Reply

      great article ! Thanks !! it helped me a lot

    91. Mark September 20, 2017 at 1:54 am - Reply

      Clear, concise steps that were easy to follow. thank you so much for putting the effort into this blog.

    Leave A Comment

    To protect our website from spam. * Time limit is exhausted. Please reload the CAPTCHA.