none
How to Configure Remote Connections To SQL Server 2008 R2 Express

    Question

  • Post written June 10, 2010 and pertains to:

    • SQL Server 2008 R2 Express on both my server and local machines: SQLX_SRV, SQLX_LOC
    • SQL Server 2008 R2 Management Studio on both my server and local machines: MS_SRV, MS_LOC
    • Windows Server 2008 R2 Enterprise installed on a Hyper-V VPS: WS
    • SQL Server Configuration Manager on both my server and local machines: CMGR_SRV, CMGR_LOC
    • Server Manager: SMGR
    • I am connecting to my hosting server via Remote Desktop Connection: RDC

    I installed SQLX_SRV and MS_SRV on my hosting server and SQLX_LOC and MS_LOC on my local development machine.  I am able to use MS_LOC to connect to SQLX_LOC and to use MS_SRV to connect to SQLX_SRV.  However I am not able to use MS_LOC to connect with SQLX_SRV.  Here's what I have done so far:

    • SMGR -> Configuration -> Windows Firewall to turn off the Windows Firewall for Domain, Private and Public profiles.  Obviously I'll change this later, but until I can connect I want to remove as many variables as possible.
    • CMGR_SRV -> SQL Server Services to confirm that both SQL Server (SQLEXPRESS) and SQL Server Browser services were running.
    • CMGR_SRV -> SQL Server Network Configuration -> Protocols for SQLEXPRESS to ENABLE the Shared Memory, Named Pipes, and TCP/IP protocols and DISABLE the deprecated VIA protocol.
    • CMGR_SRV -> SQL Server Network Configuration -> Protocols for SQLEXPRESS -> double click TCP/IP to open the TCP/IP properties dialogue.  On the Protocol tab Enabled: Yes, Keep Alive: 30000, changed Listen All to No.  I've tried it both ways, but I've got six IP addresses on my server and I wanted to configure SQLEXPRESS to listen to only the first and primary IP.  On the IP Addresses tab went to IPALL and cleared the TCP Dynamic Ports field and entered 1433 in the TCP Port field.  For my first and primary IP Address I made sure that Enabled was Yes, I cleared the TCP Dynamic Ports field, and entered 1433 in the TCP Port field.  For all other IP Addresses Enabled was set to No and I cleared both the TCP Dynamic Ports and TCP Port fields.
    • CMGR_SRV -> SQL Server Services -> SQL Server (SQLEXPRESS) right click and Restart.  This of course stopped and restarted my instance of SQLX_SRV enabling the TCP/IP configuration in the previous step to take effect.

    On my server, SQLX_SRV is the only instance of SQL Server running and so it's easy to hard wire it to the default port 1433.

    The instance name for both SQLX_SRV and SQLX_LOC is the default "SQLEXPRESS".  My server machine name is "SERVER1" on the EnglishBrains.com domain.  So the proper local name (local within the context of my remote server as connected via RDC) for my instance of SQLX_SRV would be:

    SERVER1\SQLEXPRESS 

    Note the use of a backslash NOT a forward slash. 

    Of course to connect remotely from my development machine, which is not on the same domain as my hosting server, I would need to specify the domain as well, so the SQL Server name becomes:

    SERVER1.EnglishBrains.com\SQLEXPRESS

    I must also use SQL Server Authentication.  Before I can use such a remote connection, however, there are still several configuration steps required.  So on my server (connected via RDC) I used MS_SRV to connect to SQLX_SRV using SERVER1\SQLEXPRESS for the server name and Windows Authentication.  Once connected I performed the following steps:

    • MS_SRV -> right click the connected parent SERVER1\SQLEXPRESS instance node at the top -> Properties -> Security -> Server authentication: select "SQL Server and Windows Authentication mode".  This will enable connections using either type of authentication.
    • Next, leaving the Server Properties dialogue open, Connections -> check "Allow remote connections to this server" box.
    • Click OK to save these changes and close the Server Properties dialogue.
    • MS_SRV -> Security -> right click Logins and select "New Login...", the Login - New dialogue opens.
    • On the General page Enter a name for your new login
    • Select SQL Server Authorization
    • Enter and confirm a password
    • Uncheck Enforce password expiration
    • Select the default Database and Language
    • On the ServerRoles page Public will be checked, also check SysAdmin.  This is probably not a good idea and I'll uncheck this as soon as I can connect to the SQLX_SRV.
    • On the User Mapping page select the databases you want your new Login to access and enter the Default Schema of "dbo".
    • At the bottom of the User Mapping page you'll see the Database Roles table.  Public will be selected by default.  Also select db_Owner.  Again this is probably not a good idea, and I'll refine this once I can connect.
    • On the Status page confirm that "Grant" is checked under "Permission to connect to database engine" and "Enable" is checked under "Login".
    • Click OK to save all changes and close the Login - New dialogue.

    With these steps completed you should now be able to use MS_SRV to connect to SQLX_SRV using SQL Server Authentication by supplying the name and password for your new Login.  I tried this and it worked fine.  Next I tried to use this same login remotely, that is I went to my local development machine and used MS_LOC to try and connect to SQLX_SRV by using

    SERVER1.EnglishBrains.com\SQLEXPRESS

    and opting for SQL Server Authentication and supplying the name and password of my new login.

    THIS DID NOT WORK??  Instead I get the following error message:

    ---------------------------
    Cannot connect to SERVER1.EnglishBrains.com\SQLEXPRESS.

    A network-related or instance-specific error occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    For help, click:
    http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1

    ------------------------------

    If you follow the suggested help link, you are told:

    The SQL Server client cannot connect to the server.  This error could be caused by one of the following reasons:

    • A specified SQL Server instance name is not valid.
    • The TCP, or named pipes protocols are not enabled.
    • The firewall on the server has refused the connection.
    • The SQL Server Browser service (sqlbrowser) is not started.

    WRONG on all 4 counts!  The instance name IS valid.  Both TCP/IP and Named Pipes protocols are enabled.  The firewall has been shut down, so it is not relevant.  Finally the SQL Server Browser IS started.

    The next thing I tried was to circumvent discovery by the SQL Browser service by using the following syntax to specify the IP address and port directly when specifying the SQL Server name.

    tcp:68.71.134.186,1433

    Using this in the Server Name field I was able to use MS_SRV to successfully connect to SQLX_SRV (using SQL Server Authentication of course) with or without the SQL Browser service running. 

    However when I tried to connect from MS_LOC to SQLX_SRV using this same login (WITH SQL Browser service running just for good measure) it does not work??  I get the following error message:

    ------------------------------

    Cannot connect to tcp:68.71.134.186,1433.

    A network-related or instance-specific error occurred 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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

    ------------------------------


    If you follow the help link you are told:

    "The SQL Server client cannot connect to the server. This error could occur because either the firewall on the server has refused the connection or the server is not configured to accept remote connections."

    However, the firewall has been shut down and the server HAS been configured to accept remote connections! 

    I confirmed that i could indeed Ping to 68.71.134.186  and running NetStat -a |find /i "listening" on the server shows that the server is indeed listening at 68.71.134.186 Port 1433 -- which is why I was able to connect to SQLX_SRV using MS_SRV with tcp:68.71.134.186,1433 .

    IN SUMMARY: Even though I can connect to my SQLEXPRESS instance multiple ways from the server itself, I cannot connect remotely from my development machine! 

    If anyone can help me figure out why I would be very, very grateful!

     

    Thursday, June 10, 2010 3:49 PM

Answers

  • I'm relieved to say that I've found the problem.

    It turns out that my hosting company has a network wide block on ports 1433 and 1434.  So even though I had turned off my Windows Firewall, these crucial ports were still blocked.

    Port 1433 is the default port for SQL Server and port 1434 is used by SQL Browser Service.  So the fix is simple!  If you read my post above you'll see that I had already configured SQL Server Express to use ONLY port 1433 (as opposed to Dynamic Ports).  All I had to do was change Port 1433 to 14330. 

    The configuration steps outlined above are all valid and hopefully this list will be helpful to someone. 

    I don't know how to change the Port for SQL Browser.  This fixed port is used by the network library on both the client and server and so if it were changed (probably a registry entry) the change would have to be made on both ends.

    However, as I explained above its possible to bypass SQL Browser altogether by specifying the port rather than a named SQL Server instance.  This will work fine as long as you've configured the desired instance (and only that instance) to listen to a specific port as I have done.

    You can use a raw TCP syntax:

    tcp:68.71.134.186,14330

    Alternatively you use a DNS syntax:

    SERVER1.EnglishBrains.com,14330

    Note that a comma separates the IP4,Port rather than the colon used in URL syntax.

    By specifying the port you will be able to connect remotely even if the SQL Browser service is turned off.

    I said nothing about Firewall configuration, but obviously you've got to include an incoming allow rule for your SQL Express port (this assumes that you configure SQL Express to use a fixed port instead of a dynamic port as described above.  If you are going to use SQL Browser service to allow the use of named instances then you also need to enable port 1434.  Finally you need to provide incoming allow rules for both: SQLServr.exe and sqlbrowser.exe applications.  In my case the paths on my server are:

    c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLExpress\MSSQL\Binn\SQLServr.exe
    c:\Program Files(x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe

    Note that on my server SQLServr.exe is a 64 bit app, while sqlbrowser.exe is a 32 bit app.

    One final note.  Online tools are available to test ports. 

    For example http://www.ping.eu/port-chk/ 

    has a utility to enter an IP address and then check to see if any given port is open.

    Using this tool would have enabled me to sort out that there was a block on ports 1433 and 1434 even though I had turned off the Windows Firewall.

    I've learned a lot solving this problem and hopefully what I've shared here will help someone else as well.

    Mark

    • Marked as answer by Mark_Century Thursday, June 10, 2010 6:34 PM
    Thursday, June 10, 2010 6:33 PM

All replies

  • I'm relieved to say that I've found the problem.

    It turns out that my hosting company has a network wide block on ports 1433 and 1434.  So even though I had turned off my Windows Firewall, these crucial ports were still blocked.

    Port 1433 is the default port for SQL Server and port 1434 is used by SQL Browser Service.  So the fix is simple!  If you read my post above you'll see that I had already configured SQL Server Express to use ONLY port 1433 (as opposed to Dynamic Ports).  All I had to do was change Port 1433 to 14330. 

    The configuration steps outlined above are all valid and hopefully this list will be helpful to someone. 

    I don't know how to change the Port for SQL Browser.  This fixed port is used by the network library on both the client and server and so if it were changed (probably a registry entry) the change would have to be made on both ends.

    However, as I explained above its possible to bypass SQL Browser altogether by specifying the port rather than a named SQL Server instance.  This will work fine as long as you've configured the desired instance (and only that instance) to listen to a specific port as I have done.

    You can use a raw TCP syntax:

    tcp:68.71.134.186,14330

    Alternatively you use a DNS syntax:

    SERVER1.EnglishBrains.com,14330

    Note that a comma separates the IP4,Port rather than the colon used in URL syntax.

    By specifying the port you will be able to connect remotely even if the SQL Browser service is turned off.

    I said nothing about Firewall configuration, but obviously you've got to include an incoming allow rule for your SQL Express port (this assumes that you configure SQL Express to use a fixed port instead of a dynamic port as described above.  If you are going to use SQL Browser service to allow the use of named instances then you also need to enable port 1434.  Finally you need to provide incoming allow rules for both: SQLServr.exe and sqlbrowser.exe applications.  In my case the paths on my server are:

    c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLExpress\MSSQL\Binn\SQLServr.exe
    c:\Program Files(x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe

    Note that on my server SQLServr.exe is a 64 bit app, while sqlbrowser.exe is a 32 bit app.

    One final note.  Online tools are available to test ports. 

    For example http://www.ping.eu/port-chk/ 

    has a utility to enter an IP address and then check to see if any given port is open.

    Using this tool would have enabled me to sort out that there was a block on ports 1433 and 1434 even though I had turned off the Windows Firewall.

    I've learned a lot solving this problem and hopefully what I've shared here will help someone else as well.

    Mark

    • Marked as answer by Mark_Century Thursday, June 10, 2010 6:34 PM
    Thursday, June 10, 2010 6:33 PM
  • Hi Mark,

    Thank you, thank you, thank you very very very much!!!! This artical helped me a lot and my problem is solved by this.

    Many thanks.

    Edward

    Thursday, February 10, 2011 3:25 PM
  • Absolutely spot on... this is indeed a problem with hosted servers. I even spoke to them and they said they don't provide such services (pathetic really). Any ways this is absolutely brilliant solution. However not all of them have problem with 1433 port as I have another server hosted somewhere else I never had any problem connecting to it. So anyone if they get error 26 despite of all the measures

    a) Correct server name

    b) SQL Browser running

    c) Firewall configured or even switched off ...

    your solution is to switch to a static port instead of dynamic (which is better for security reasons anyway)

    Thanks a lot Mark!

    Sunday, August 21, 2011 9:09 PM
  •  

    One point worth keeping in mind is that if you are using a names instance, and therefore running the SQL Browser service, remember that the firewall on the server needs to have port 1434 open but the catch is that it's not a TCP port, it's a UDP port.  That point had me scratching my head for quite a while!

    It's tempting to think that having the Browser service configured correctly negates the need to switch to a static port but that's not the case.  You'll need it to be static so that you can open 1433 in the firewall as well.

    It's also tempting to open the firewall by authorising sqlserv.exe itself rather than a port but this breaks down the track if you apply a service pack or other update that moves the exe.  however, I have to admit that I am always adding this rule as a shotgun approach to connectivity - not very scientific I know...

    So my personal checklist is;

    a) Correct Server and Instance (if applicable) name.

    b) SQL Browser service running.

    c) Firewall configured for TCP port 1433.

    d) Firewall configured for UDP port 1434.

    e) Firewall configured for sqlserve.exe.  I've never had to configure one for SQLBrowser.exe myself.

     

    While I'm at it, I'll share the most useful link that I've found thus far: http://msdn.microsoft.com/en-us/library/cc646023.aspx#BKMK_dynamic_ports.  That's where I picked up the need for the SQL browser port 1434 to be UDP.

    Thanks for sharing your experience Mark - it was very helpful to me.

     

    Al.

    Tuesday, August 23, 2011 6:32 AM
  •  

    One point worth keeping in mind is that if you are using a names instance, and therefore running the SQL Browser service, remember that the firewall on the server needs to have port 1434 open but the catch is that it's not a TCP port, it's a UDP port.  That point had me scratching my head for quite a while!

    It's tempting to think that having the Browser service configured correctly negates the need to switch to a static port but that's not the case.  You'll need it to be static so that you can open 1433 in the firewall as well.

    It's also tempting to open the firewall by authorising sqlserv.exe itself rather than a port but this breaks down the track if you apply a service pack or other update that moves the exe.  however, I have to admit that I am always adding this rule as a shotgun approach to connectivity - not very scientific I know...

    So my personal checklist is;

    a) Correct Server and Instance (if applicable) name.

    b) SQL Browser service running.

    c) Firewall configured for TCP port 1433.

    d) Firewall configured for UDP port 1434.

    e) Firewall configured for sqlserve.exe.  I've never had to configure one for SQLBrowser.exe myself.

     

    While I'm at it, I'll share the most useful link that I've found thus far: http://msdn.microsoft.com/en-us/library/cc646023.aspx#BKMK_dynamic_ports.  That's where I picked up the need for the SQL browser port 1434 to be UDP.

    Thanks for sharing your experience Mark - it was very helpful to me.

     

    Al.

    Thank you for the simplest way and working way. After this, just used "192.168.1.101\SQLEXPRESS" and "sa" and password anv voilá! It connected.

    Thanks.

    Adelino


    Adelino Araujo

    Friday, August 17, 2012 10:22 PM
  • My two cents to help you out on c# code example to configure the remote sql server express.

    Hope it helps. It works, but you have to be extra carefull to read it all and setup the server configuration and netsh commands; also the port fowarding on the router.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    
    namespace sqlremoteconnection
    {
        class Program
        {
            static void Main(string[] args)
            {
                // this is the local sql server connection
                // 192.168.1.101\SQLEXPRESS
                //
    			// now, the configuration for remote access:
    			
                // activate SQL SERVER BROWSER - set it to start "automatic"; then START UP
                // SQL Server Configuration Manager
                // --> SQL SERVER BROWSER -> properties -> Service -> Start Mode -> automatic -> apply
                // --> Log On -> Start -> Ok
    			
                // netsh firewall set portopening protocol = TCP port = 1433 name = SQLPort mode = ENABLE scope = all profile = CURRENT
                // netsh firewall set portopening protocol = UDP port = 1434 name = SQLPort mode = ENABLE scope = all profile = CURRENT
                // netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = any profile = PUBLIC
                // netsh advfirewall firewall add rule name = SQLPort dir = in protocol = udp action = allow localport = 1434 remoteip = any profile = PUBLIC
    // // VERY IMPORTANT FOR REMOTE ACCESS: you have to add the rules on port fowarding // on the router!!! // TCP: 1433 // UDP: 1434 // or just a personal port like TCP 31433/UDP 31434 // read carefully the netsh commands above // server name: 123.132.24.177\SQLEXPRESS // server name: anyurl.myftp.org\SQLEXPRESS,1433 <<-- regular port // server name: anyurl.myftp.org\SQLEXPRESS // server name: anyurl.myftp.org\SQLEXPRESS,31433 <<-- WOW different PORT here!!! SqlConnection myConnection = new SqlConnection( "user id=sa;" + "password=password_goes_here!;" + "server=anyurl.myftp.org\\SQLEXPRESS,31433;" + //"Trusted_Connection=no;" + "database=database_name_here; " + "connection timeout=30"); try { myConnection.Open(); } catch (Exception e) { Console.WriteLine(e.ToString()); } try { SqlDataReader myReader = null; SqlCommand myCommand = new SqlCommand("select * from mytable", myConnection); myReader = myCommand.ExecuteReader(); while (myReader.Read()) { Console.WriteLine(myReader["tab01_name"].ToString()); Console.WriteLine(myReader["tab01_age"].ToString()); } } catch (Exception e) { Console.WriteLine(e.ToString()); } try { myConnection.Close(); } catch (Exception e) { Console.WriteLine(e.ToString()); } } } }


    Adelino Araujo


    • Edited by Adelino Araujo Saturday, August 18, 2012 1:08 AM netsh was missing the last udp command
    Saturday, August 18, 2012 1:03 AM
  • Another thought on this is to check the services SQL Server and SQL Server Browser are running under as my remote access issues was resolved when set them to be running under a deicated domain account rather than the built in network services account
    Tuesday, April 09, 2013 10:23 AM
  • This post was immensely helpful!
    Wednesday, April 02, 2014 9:12 PM