How to allow remote connections to SQL Server Express
Articles,  Blog

How to allow remote connections to SQL Server Express


Hi, everyone. In this demo, I’m going to show
you how to connect remotely to a SQL Server Express or Developer edition
instance. This scenario usually happens when you
want one of your colleagues to remotely connect to your machine. In
order to allow access, there are two levels of security that need
to be changed. First, we have to allow access through Windows, and then we have to configure the SQL
Server instance to allow the protocol being requested.
When you install SQL Server, the edition you specify not only
controls which features are available, but also which protocols are available
by default. In order to allow access to SQL
Express and Developer edition instances, we have to enable TCP/IP which is not
enabled by default. After we’ve enabled TCP/IP in SQL
Server, we have to allow the appropriate ports
through Windows Firewall. Some advice on the internet says to
disable Windows Firewall entirely, but this is not a
security best practice, and it’s not required at all. When you
connect to a SQL Server instance, there are two different types of
instances that you can connect to. First is the default instance, which is
where you only specify the computer name and nothing else. The second type is a named instance where you not only
specify the computer name, but also the instance name. To connect to
a default instance, the only port you have to open is
TCP port 1433. To connect to a named instance, there’s a second service called the
SQL Browser service, which resolves the instance name to a
port number. The SQL Browser service uses UDP port 1434. By default, named instances use dynamic ports, which
means a new port number is assigned every time
the service restarts. This is why the SQL Browser service is
necessary — it’s because the named instance service does not expose a known port number to
the outside directly. As you can see, I have two virtual machines running in
Virtual Box. The SQL Server is running on the one on the right, and we’ll be trying to connect from the
one on the left. Both machines are in an internal network, and while they’re
not in a domain, will still connect using Windows
Authentication. As a demonstration, I’ll show that I can
connect locally on the right side, but I can’t connect remotely on the left
side. The first step is to enable TCP/IP on the SQL Server service. We do this
by first opening SQL Server Configuration Manager, and then in the network configuration node, we select the protocols item for the instance of interest.
Then we right click on TCP/IP and click Enable. We get a warning that the changes we
just made won’t take effect until the next time
the service is started, so let’s do that now. We’ll go to the services node, right click on the database engine
instance, and click Restart. The service is now restarted with TCP/IP enabled, but we still can’t
connect until we configure the Windows Firewall. We’ll go into the Advanced Firewall
configuration, and then to the inbound rules. To create an allow rule for the SQL
Browser service, we’ll go to New Rule, and for this, we’ll want to choose a
specific port number. We’ll select UDP port 1434. We’ll click Next all the way through, and we’ll give the rule a name, and click Finish. Next, we’ll create an allow rule for the
database engine instance itself. We’ll go to new rule, this time we’ll create a custom rule, and
in the Services area, we’ll select the database engine instance
service. Again, we’ll click next all the way through, and give the
rule a name, and click Finish. Now we have all the firewall rules set up, so let’s try to connect
again. And this time, it works. Because the named instance uses dynamic
ports by default, I allowed the service itself and any
ports it’s using through the firewall. Not only does this
allow connection to the database engine instance itself, but it also allows any
other ports SQL Server may use including things like database mirroring. If this is inappropriate for your environment,
you’ll want to use more restrictive policies, but for most people this is OK. If you
have any questions about this video, or if you’d like to suggest topics for
future videos, please leave me a comment below. Thanks for watching!

100 Comments

  • Tom Davidson

    I Followed the instructions in your video but I am still not able to connect.
    I am running Windows 8 on both PC's and SQLEXPRESS 2014.
    As a test I turned the firewall off completely but still unable to connect. Grrrrrrr!!!
    Any ideas?

  • Jeffrey Hughes

    Still can't get it to work – do you have to have SQL Server Browser running?  If so – My option to start the browser is grayed out.

  • H Kobzz

    Great video.. Though I've always opened the TCP port 1433 instead of UDP 1434 and it has always worked using names instances. I'm looking for a video to allow sql connection over the internet, can you make a video for that please.

  • Warda UAE

    hi
    thank you very much for the amazing video,
    can you tell me how can I start sql server agent (SQLEXPRESS) ?

    I uploaded  my website in some.com but I'm sill getting error (The network path was not found )

    Thanks again

  • Isra Ibrahim

    So, using this method, I can make a windows application and let multiple PC's update and retrieve data from the database?
     Because I need to make a database windows application that can be used on 3-4 PC's

  • As ka

    i need to send table in sql server 2008 to another computer now that computer performs some operation and send result to previous computer…is it possible to send and receive the data????

  • Ralph

    Hi, I did the same thing you did but my client computer won't connect to the server I have no idea what i am doing wrong I even disabled the firewall completely, can you private message me, Maybe we can skype and figure it out?

  • Viet Vo

    Why I get the error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication? Please help me!!!

  • Travis Torres

    Thank you this helped a lot.  I've been trying to connect PHP to SQL Express through PDO and kept receiving a Named Instance Error.  Enabling SQL Server Browser did the trick!!!  Thanks again

  • Henry Onovwaka

    Hi, I have tried everything here, but its not working for me! I can connect to the database on the same system using the IP Address but not remotely! Depends on what I try, I see server error 11001, error 6, error 08001. Any link for more help will be great!

  • Victor Villas Bôas Chaves

    Got it working, thank you!

    Tip for other viewers: If you are doing this for the default instance, remember it has no name and you only need to provide the IP adress to connect to it in SQL Management Studio. Do not type MSSQLSERVER, SQLEXPRESS, name of the machine or whatever.

  • Andrew

    Is there any chance of this causing an issue with speed and resources for people that are using the DB Instance on the host itself? I am new to DBA but have to do a lot of it at my new job. Thanks for the video though, very helpful and exactly what I needed.

  • Anthony Griggs

    You are the Man!! Thanks… I really appreciate the walk through as many articles assume working hardware configuration knowledge. I'm just a developer who is used to developing in already configured environments… I've been setting up some environments myself lately… but a lot of times it's hard as so many who instruct you on these processes assume you are familiar with a lot of it!

  • chaari ons

    thnks for the vidéo !!
    When you get connexion between the two sqls server, Is that the change in the first change in the second.??

  • May July

    I really love to know about DBMS. As a developer , I just knew insert,update,delete,procedur,function , indexer. Its help me a lot to get knowledge overall , I hope you can upload more and more , I subscribed you and really appreciate you. 🙂

  • António Bezerra

    Thank you very much! I tried a bunch of tutorials on this and none worked but this one did! Thank you very, very much! 😀

  • jerry77hd

    BRAVO!!!! Well Done… Been battling with this for days then saw your video and it all came clear…. Thank you for everything you do!!!!!

  • Joe Chan

    It works on local network but still not able to remote from outside network. Am I missing somethings? On network I can even use public IP to login. I did port forwarding.

  • mohamed jhasim

    can you help me to connect the database with external network (ie. having data base in my office i want that database to be connected at my home)

  • vijaya bagret

    Could you please help me connecting the SQLcmd utility with MSSQL remotely such that I can fire the query via sqlcmd utility using .bat file?

  • Swapnil Pednekar

    How can I connect to a named instance using static port instead of dynamic port. Also I am not using windows firewall but a seperate firewall where i have done port forwarding for that port. I have used port 1435 for the same. Please help.

  • Muhammad Adnan

    this is amazing .Thank you very much sir. i have a question, can i connect sql server instance from different network?? like through port forward etc method .

  • Hemant Singh Bohra

    I am trying to connect to SQL Server with MS Access remotely. But I am not able to login either using windows authentication or SQL Server authentication.

  • ALMSys, Inc.

    This works great on my network (non domain). I did this on a client's network a couple of years ago and it worked great. They just got a new server (MS 2016 Server) and I tried to set this up again. No go. Everything looks right, but I cannot connect to the server's SQL instance through the Management Studio. I completely dropped the firewall and uninstalled the antivirus on the server (thinking I was being blocked somehow), but still will not connect. I thought maybe it was a Windows permissions thing, but I can browse to that server through Windows Explorer and can see files and folders OK. I tried the connect using SQL authority via a logon I set up within SQL Server, but again, it will not connect. In reviewing the settings, the only thing your video did not cover was making sure that the SQL Server Browser was set to Automatic and started. It is started in this case and all of the settings match your video. This brings me back to my gut telling me that I somehow have a permissions thing going on. Any insights? (The workstation from which I am trying to connect has its firewall dropped.)

  • Faton Beqiri

    Hi this video was great and very helpful but can you tell me if can I use this way of connection to connect to the same database from two same apps in two different PCs.

    Thanks again for the video it was very helpful

  • اسد السنة

    please help me
    i want to connect to sqlserver via internet
    1- i turned off the firewall
    2- i enabled the the dmz in the router , host address is 192.168.1.24 which is the sqlserver lan ip
    3- i open port in the router for both sqlserver and sql server bowser (1433 – 1434)
    4 – i checked the open port via (http://canyouseeme.org) website, and the result is success for 1433 and error for 1434 (i don't know the reason of error )
    5 – i made the necessary configuration for to the tcp protocols
    6- i checked that the server is allowed remote connection

    but failed to connect to the sever via internet
    my LAN ip is
    192.168.1.24
    the instance is default instance
    my public ip is
    95.218.156.146 (for example)
    when i type (192.168.1.24) in the server name text-box i success to connect to the server
    but i failed to connect when i type 195.218.156.146 or 95.218.156.146 ,1433
    note :
    1- when i put the public ip in the browers then the router page is appear (i meand by that .the public ip is correct)
    2- i didn't find (port forwarding ) in my router , but i configure the DMZ and port mapping

    please help me maybe through team-viewer

    thanks a lot in advance
    whatsapp 00966538250570

  • Mark Williams

    But you mention in the video that to connect using the name of the machine (not the instance), all we need is port 1433. Well, if that port is already open, why can I not connect using just the machine name?

  • Bhavana Gupta

    I have never done the set up part. I'm unable to set up the remote connection, it works fine on my local machine in 'windows Authentication' Mode though. Please correct me if I am wrong; My understanding is if I want to set up a remote connection it will be through 'SQL Server Authentication'.
    How will I know the which port number to enter ? Different videos say different port number like 1433, 1434, 14708…
    Your help will be highly appreciated. Thank you in advance.

  • Gaspar

    Congrats for your initiative, it's very useful for lot of people especially to foreigners because you speak smoothly to helping people non native english speakers.

  • Anita Burke

    I just created a named instance, i have no idea what I'm doing, watched a video by sachin samy, very thick accent, just warning ya. I really have no idea what I'm doing, I'm realizing I need another computer. I figured there was some database somewhere to connect to somewhere. anyway, I'm going to learn to write TSQL statements if it kills me

  • Eslam Elnaggar

    Interface Error: Connection to the database failed for an unknown reason.
    even if i used this way the same error appear
    I am using SQL server 2014

  • Paula Cruz Morales

    Hi, I'm trying to connect to sql server express with advanced services 2017.
    I'm developing a project in Visual Studio Professional 2015 and I have an ADO connection…
    The thing is… I was using SQL Server Express (standard) but suddenly the SQL Server Agent stop working.
    The solution I received was installing the SQL Server Express with advanced services… But now it is in my boss' pc and i can't get access to it…. I already deactivate the windows firewall, and set up the tcp/ip ports, but it isn't still working and I don't know what the problem could be? … I think it might be security configuration, because I can't do a ping to my boss pc….
    Otherwise, in my connection string in Visual Studio, I don't know how to fill the data source, because it isn't a server… So I can put the ip address?…
    I hope you can help me … And sorry about my bad English

  • unknownhumanbeing

    Thanks a lot for the amazing video, this is what exactly I needed. Adding firewall to allow specific service is what I missed in my configuration, now it works like charm.

  • mtk2 jr

    Sir can you help >.. i have tried your method above but i doesn't work in my case. I have a sql server installed on my home pc and is all time poered on and sql server running. how can i access and connect to sql server when i'm away from home ( i mean how can i access and connect to my sql server when i'm outside my local network). i know when both pc are connected to the same router that configuration works because you just allow port 1433 on firewall and allow port 1433 on sql configuration manager but the problem comes when i'm not connected to my local network i.e. let say when im away from home. can you please explain .i have searched everywhere some people suggest that i do "portforwarding" on my router to make my pc discoverable on the internet other say i should allow sql server to listen on udp port 1434 but i tried nothing worked.

  • Indika Manauthum

    hello sir, please help me. i want to connect to sql server through the internet without using any software (eg: hamachi) or purchasing static ip from the internet service provider. i was refer the following video but i'm fail. so please help me. Your help will be highly appreciated.
    https://www.youtube.com/watch?v=VOUDdUJ5BLY

  • Eduardo Espinar Franco

    Great job, You helped me a lot with this video, I've searched a lot of videos And I Must tell Your video is the best. Greetings from Ecuador.

Leave a Reply

Your email address will not be published. Required fields are marked *