How to: Configure Express to accept remote connections


The information in this posting is superseded by the following KB Article:

914277 How to configure SQL Server 2005 to allow remote connections;EN-US;914277


Some people have been having issues when trying to make remote connections
to SQL Express.  This document will hopefully clarify most of the issues
around remote connections.

First, networking protocols are disabled by default in SQL Server Express.
Thus, if someone simply installs Express and chooses all the defaults, SQL
Server Express will only be able to have connections originating on the
local machine where SQL Server is installed.

To enable SQL Server Express to accept remote connections we need to perform
the following steps:

STEP 1: Enabling TCP/IP

First we must tell SQL Server Express to listen on TCP/IP, to do this
perform the following steps:

1. Launch the SQL Server Configuration Manager from the “Microsoft SQL
Server 2005 CTP” Program menu
2. Click on the “Protocols for SQLEXPRESS” node,
3. Right click on “TCP/IP” in the list of Protocols and choose, “Enable”

STEP 2: To Browse or not to Browse

Next, we have to determine if we want the SQL Browser service to be running
or not.  The benefit of having this service run is that users connecting
remotely do not have to specify the port in the connection string.  Note: It
is a security best practice to not run the SQLBrowser service as it reduces
the attack surface area by eliminating the need to listen on an udp port.

OPTION A: If you want to always specify a TCP port when connecting (Not
using SQL Browser service) perform the following steps else skip these

1.      Launch the SQL Server Configuration Manager from the “Microsoft SQL
Server 2005 CTP” Program menu

2.      Click on the “Protocols for SQLEXPRESS” node

3.      Click on the “TCP/IP” child node

4.      You will notice an entry on the right panel for “IPAll”, right click
on this and select, “Properties”

5.      Clear out the value for “TCP Dynamic Ports”

6.      Give a TcpPort number to use when making remote connections, for
purposes of this example lets choose, “2301”

At this point you should restart the SQL Server Express service.  At this
point you will be able to connect remotely to SQL Express.  A way I like to
check the connection is my using SQLCMD from a remote machine and connecting
like this:


The “,” in the server name tells SQCMD it’s a port.

So you’ve tried this and still get an error.  Take a look at Step 3, this
should address the remaining issue.

OPTION B:  If you want to use SQL Browser service perform these steps:

            You will need to make this registry key change if you are using the April 
            CTP or earlier versions:

            To enable sqlbrowser service to listen on the port 1434, the following 
            registry key must be set to 1

            HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\SQL 

            Next, restart the sqlbrowser service.

      1. Start the SQL Browser Service

STEP 3: Firewall..?

      At this point you should be able to remotely connect.  If you still
can’t chances are you have a firewall configured on the computer where SQL
Express is running.  The instructions below are for Windows XP SP2’s
firewall settings.

      To enable the firewall to allow SQL Server Express traffic:

1.      Launch the Windows Firewall configuration tool from the control

2.      Click the Exceptions Tab

3.      Click the “Add Programs.” button and select “sqlservr.exe” from the
location where you install SQL Server Express

You should be able to remotely connect.  Note, you can get more restrictive
by just specifying the port number that will be allowed (used best when
configured with Option A).

Note: If you chose to use the SQL Browser service, you must also add
sqlbrowser service executable to the exception list as it listens on udp
port 1434.

Comments (84)

  1. Recently, we answered a lot of customer questions about how to make a successful connection to SQL…

  2. David says:


  3. FrankQin says:

    Sql server 2005 express, remote connect

  4. DlanorOk says:

    A last recomendation is to add the SQL Browser executable file to the Firewall Exceptions, when you really need to use it.

  5. damacroguy says:

    THANK YOU…THANK YOU…THANK YOU! As a newbie to VB and SQ, I wrote a timeclock app for a standalone computer and never had any connection issues to contend with until I had to install my app in a P2P environment with 2 PC’s. After lots of Google-ing I landed here and this information put it all into perspective and got me up and running.

  6. Zooba Chat says:

    Got a £60 per 1/2 hour personal engineer booked?? CANCEL IT!!! If you like me and many others have rented

  7. 毁于随 says:


  8. SRLTeam says:

    When you create your DB Dude project and try to build and deploy the project to the client’s sandbox

  9. When you create your DB Dude project and try to build and deploy the project to the client’s sandbox

  10. When you create your DB Dude project and try to build and deploy the project to the client’s sandbox

  11. myozaw says:

    Thanks for Error: 26. I was trying to remote access my sqlexpress from another computer. But, eventually, I found out this blog and my problem was solved. 🙂


  12. groker says:

    I went up to step 2 and could connect through the command line and run queries (like sp_databases).  However, I cannot connect using Sql Server Management Studio.  Both of these connections were attempted through the same client to the same server using <ipaddress>sqlexpress .  Have you experienced this problem and do you have a solution?


  13. groker says:

    Solution: I was including the port in the ipaddress ( … the database name should use the format,2301 .   For those of us who have never used ports with sql server connections, this should help the learning curve.

  14. sqlexpress says:

    To groker:

    It’s best to post questions to the Forum since it is more targeted for question/answer exchanges.

    I’ve not seen this issue where SQLCmd works but Management Studio does not. When posting this question to the forums you should include the entire error message; the first part of the message will likely be the general "remote connections are not enabled" which isn’t true in your case, so it’s likely that the last part of the message which is in parenthasis will have more useful informaiton.

    It’s also worth reading through the many useful posts about SQL connectivity troubleshooting provided by the SQL Protocols team on thier blog. Here are the posts related to remote connections:


  15. Ashfaq Maniar says:

    Thank you so much.

    I was trying for the last 2 days to connect from a Remote machine through the VB Application but failed.

    With your help, my application is a success. Many thanks for your guidance.

  16. davidbotero says:

    Hello, I followed the steps, but I still get an error:

    HResult 0x2AF9, Level 16, State 1

    TCP Provider: No such host is known

    Can anyone help me?


  17. sqlexpress says:

    Response to davidbotero –

    Please post technical questions to the SQL Express forum on MSDN:


  18. Configuring SQL Server Express 2005 for Remote Access when SQL Server does not allow remote connections

  19. sohbeti says:

    kameralı sesli sohbet girişi – video klip izle

  20. John Terry says:

    I am having issues with the Windows XP firewall.  I have made the exceptions for sqlsrver.exe and sqlbrowser.exe as well as udp ports 1433 and 1434.  The connection works fine as long as the fire wall is turned off.

    Any ideas?

  21. tekzalim says:

    Very Good Site Thanks You

  22. tekzalim says:

    thanks You !

  23. tekzalim says:

    Turkiyenin en kaliteli motosiklet sitesi.

  24. tekzalim says:

    Turkiyenin en kaliteli Rüya Tabir sitesi.

  25. Syed Zaidi says:

    Thanks! it is an excellent article and helped me alot.

  26. Jorge says:

    don't forget you also have to add a firewall rule to SQL Browser or else you would not be able to connect the first time

  27. Peg says:

    I'm on Vista.  I have performed the following steps, but a user on my network CANNOT connect to my SQL Server 2008 Express Instance (specifying the port as in your example).  I CAN connect to my instance:

    1.  Sql Server Configuration manager:

           SQL Server network Configuration:

      a) Enable NP (Named Pipes)

    b) Enable TCP/IP

    Properties in TCP/IP:

    Dynamic Port (removed "0")

                           Set Port to:  2309 (for IP1 through IPall)

           Restarted SQLExpress for settings to take effect.

    2.  SSMS:

           a) Set Security, authentication to:  SQL Server and Windows Authentication Mode (set sa password).

           b) Security, Logins:  verified the "sa" account was in the list

    3.  Firewall:  Added to exception list, leaving the "scope" as the default for testing:

           a) SqlServer.exe

           b) SqlBrowser.exe

  28. Dr. Web Works says:

    Very nice info. Thank you for your post.

  29. Ajit Chahal says:

    Thanks..! excellent worked for me after i added windows firewall exception for TCP port 2301.

  30. JayB says:

    Step 1>Install MS SQL server 2008

    Step2>During Istallation Always Select MIXED User connection;

    Step3>after successful installation of data base Login as Local User.and create Database..then in side Database select security

    and then right click on login and create new Login.

    Step4>give new User name and Select SQL Authentication..and give password then deselect USE MUST CHANGE PASSWORD AT NEXT LOGIN

    Step5>Go to USER MAPPING on that form and select database wihich u want to assign to that User.and below that assign

    read,write,backup permission.and then Click "ok" to finish.

    Step6>NOW open SQL Server Configuration Manager->SQL Server Network Configuration->Protocols for <Instance Name>->Enable Shared

    Memory & TCP/IP and Double Click on TCP/IP then got to IPaddress TAB->in here ScrollDown and look "IP ALL"there give TCP Port

    "1433" then "ok".

    Step7>Now Go to SQL NATIVE Client xx.x Configuration->Client Protocols->Enable Shared memmory and TCP/IP->Double click on Client

    Protocols and Default Port =1433

    Step8 Optional Client side>Go to Aliases and create new name=xyz,)port no=1433,Protocol=TCP/IP,Server=Ip Add

    ( or DomainNameInstanceName(JOI-MIMSSQLSERVER)

    Step9>do Step 6 & 7 in client Side.

    Note:-Remember Do all settings of SQL Server Configuration Manager in 32-bit version.ull see don't worry.

    Use this Connection string ->

    cnn = New SqlConnection("Data Source=<Alias name/ipaddress,port>;Network Library=DBMSSOCN;Initial Catalog=DATAbaseNAME;User


  31. sql server says:

    If you need to check whether your sql server is accessible online, you can try using the following tool online:

  32. saurav gautam says:

    What does remote connection refre to.. DOes it mean sharing on sql server database over the LAN or over the internet as well… I have configured SQL server to allow access over the lan (computers connected to a common router can share database with each other).. Now i want to connect it over the internet.. while connecting i used to use ip-… What will be the ip to connect it over the internet.. Or is that possible??

  33. Alex GM says:

    Very useful… nice and clean.

  34. MPerdomo says:

    Muchas gracias. Funciona a la perfeccion

  35. 90 folder says:

    In the 90 folder (HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server90), i found three items.

    In reference to option b, should any of these items be changed or any files added.

    Those three are

    Default (value not set)



  36. it worked says:

    thaaaaaaaanks so much 😀 they regkey solved my problem

  37. Nag says:

    it worked like a charm..!! thanks again.

  38. cv örnekleri says:

    Very nice info. Thank you for your post. <a href="">cv örnekleri</a>

  39. Mohit says:

    It worked. Thanks very much you saved my day…

  40. Changes to server properties and settings may affect the performance, security, and availability of this SQL Server instance. Before making any such changes, consult the product documentation. says:

    Changes to server properties and settings may affect the performance, security, and availability of this SQL Server instance. Before making any such changes, consult the product documentation.

  41. Padam Sonar says:

    Very nice article brother it works now in my system (Win 7 & XP). However, i'm trying to use webservice using IIS 6.0 in windows 7. I'm able to add web reference but after adding i'm not able to access my database sql express 2005. it says Login Failed: database is read-only.

    Please help me with this if any one know. i'll be very greatfull to you guys.

  42. lewis says:

    I really hope your the best software development company in UK and providing the quality services to people.<a href="">crm solution</a>

  43. shrikant says:

    Cannot connect server in sql server 2005 so error is a network-related or instance specific error occured 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:namedpipesprovider error:40 could not open a connection to sql server) microsoft sql server error 2 so plz help me

  44. Daniel says:

    Worked for me on SQL Express 2014 and saved a lot of time over talking to the DB guys, thanks!

  45. Daniel says:

    Also worth noting that in order to connect with a sql authentication the database settings must be set to allow Sql Server Authentication mode in Database Properties -> Security -> Server Authentication.