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:
- Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.
- Configure SQL server to listen on static port
- Turn on the SQL Server Browser service.
- Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.
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
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
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
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
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 5
4. Restart SQL Server service and identify the process ID assigned to SQL service.
Figure 6
5. In the command prompt execute command: netstat -ano | find /i “6524”. The results are shown in 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 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
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
2. Go to Service tab and for Start Mode option change start type to Automatic.
Figure 11
3. Click Start button to start SQL Browser service
Figure 12
4. Confirm that SQL Server Browser service is up and running as it is shown in Figure 13.
Figure 13
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:
- 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”
- 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
- 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:
- 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
I hope you will find this post useful. Feel free to add your comments and rate this article.
[yasr_visitor_votes size=”medium”]
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 🙂
Thank you for your comment. I’m glad you found this post usefull.
Andrzej
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!
Very clear & complete instructions. Thank you.
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].
Excellent instructions! One of the most useful, accurate blog posts I’ve found.
Thank you
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?
Try to connect to sql server and than execute netstat -ano command.
Thanks… I am connected to SQL server…
Does not work even when connected to SQL….
wow very clear.. and very helpful
thanks
Thank you!!!!!!!!!!
You are welcome 🙂
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
[…] I finished adult adding Firewall manners and following some of a stairs in this glorious configure SQL Express 2012 to accept remote connectors article. […]
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
Excellent!! Works perfect! Thanks!
What if the windows firewall is disabled ?
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.
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!
[…] 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 […]
Thanks for posting this, I tried it and it worked first time 🙂
Thank you, instructions are very clear and easy to follow.
thank you for this article. for those having issues with the netstat -ano command try this: netstat -ano | findstr %PID%
Thanks for posting this, I tried it and it worked first time!
Thanks
You did a very good job Andrzej, an excellent article.
Nice Article. very informative. Cheers!!
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.
Excellent job. Thanks a lot. Congrats! PS: SQL Server Express 2012: sqlbrowser.exe @ \Program Files (x86)
Thanks for update. I corrected my mistake with sqlbrowser location.
This post is very useful to me. Thanks for this post
Thanks very much, it is very helpful.
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 .
Thanks a lot. It helped me
Very excellent instruction, Thank you Andrzej .
[…] Enable remote connections on the instance of SQL Server that you want to connect to from a remote co… […]
Excellent, Thanks
Thank you
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.
Thank you very much. This was a huge help.
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
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.
Again it worked for me. Thank you!
Thanks for the post. Been looking to do this for the last 3 days then landed onto this post. worked perfectly as illustrated.
You’re a gentleman and a scholar. Thanks!
You are the star! Thanks a lot for this post. It saved me some days or maybe even weeks!
[…] Viac informácií nájdete tu: https://blog.citrix24.com/configure-sql-express-to-accept-remote-connections/#14 […]
Worked Perfectly. Thank you for the detailed explanation.
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 🙂
just awesome !!!!
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.
Excellent guide. Within 15 minutes got it working and now have remote access. Keep up the good work.
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.
Very well written. Thank you very much.
is that instructions will allow me to remote access to my sql server over the internet?? thanks..
Very clear and usefull. This instructions were helpfull for sure. Thank you, congratulations!
Mexican .net POS developer
i selected mixed mode for authentication, but test connection is successful via sql security method but not by windows authentication method
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!
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.
Thank you very much. I was trying to set this and I was missing the Section 6 and 7 IPAll configuration.
Beautiful explaination
Thanks,
Excellent explanation!
Very nice article and really helpful for people who needs connection across local network
Superb illustration 🙂
It has helped me to sort out my problem
Great walk through on getting a remote connection for MSSQL 2012 up and running. Works wonderful for us.
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’
[…] How to configure SQL Express 2012 to accept remote connections […]
Thank you so much …
It helped me a great deal
[…] How to configure SQL Express 2012 to accept remote connections […]
This made my day 🙂
Thank you very much.
Help me a lots.
[…] Enable remote connections on the instance of SQL Server that you want to connect to from a remote co… […]
Still working even after all these years. THANK YOU!!!! Clearly written.
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!
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.
Absolutely fantastic post – very clear, helped me untangle the mess I’ve created over the past few months
Excellent article, Precise and concise. Appreciate it. Thanks much.
this helped me solve my problem. Thanks!
Thanks a lot you are a life saver.
worked like a charm 🙂
You are my hero! Thank you so much for this.. I can’t believe it worked.
This post worked perfectly for me. Thank you
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?
Thanks a alot.
Thans so much for the post, It’s a very clear step by step to configure sql express 2012 to accept remote connection.
It worked, it worked, it worked!!!
Thx for the detailed post….it save my day!
Unfortunately this has not worked on windows 7 and I don’t know why? please can someone help !!!
Hi,
Can you please provide more details ?
Andrzej
Thank you very much for this wonderful post!
Done all but i am not getting ESTABLISHED line shown in fig 7 and fig 9. Any ideas ?
Thanks . Very useful
Thank you very much
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
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
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.
This was a huge help! Thanks so much!
It worked.. You are awesome. Thank you thank you thank you 🙂
It’ also work on MS SQL EXPRESS 2014 , Thank you so much
thank you so much it helps me a lot
great article ! Thanks !! it helped me a lot
Clear, concise steps that were easy to follow. thank you so much for putting the effort into this blog.
thank you for this, it solved my issue
THANKS MAN YOU ARE THE BAST
When I delete Dynamic port to change TCP/IP to port 1433 then I can’t restart MSSQLSERVER service…any idea?
Excellent document, worked like a charm! Thanks for posting this!!!
Thank you. I’m glad to hear that
Thanks .. Great.. Sir, i am new to server configuration. but still i achieved by following these steps. Spoon fed … Thanks again..
THANK YOU THANK YOU THANK YOU! Just saved me at a client’s office as I am implementing SQL Ex 2017.
Thanks a lot brother. This is how a blog post should be. Covered everything related to the topic.
Thanks for this detailed post. Just a note in case this helps someone. I was trying to set up a DSN on another system as a test. After making all of the changes in your post I got further, but received a ‘an existing connection was forcibly closed by the remote host’ error. I had to exit the ODBC Manager and start over again and everything worked great.
Excellent!!
Even after 6 and a half year this is by far the best and easiest to understand guide out there! Thank you so much, you’re “paper” helped a lot!
Super Thanks!
Excelent explanation!!! After 6 years is still here and I could configure SQLEXPRESS 2019 based on it.
Congratulations!
Still relevant and extremely helpful! Thank you!