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.
thank you so much!!! I've been reading articles about opening the firewall rule. but none of them worked. Yours is the first one to suggest the database instance. And it worked!
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.
Victor Chaves Using the machine name should work to connect to a default instance under most circumstances, but you're right, using the IP address (plus the port number if it's non-standard) is always a fallback to connect over TCP/IP.
Yes, what I meant to say was to either provide adress or name, never both in case of default instance. I'm saying this because in most tutorials we see someone conecting to a named instance with 192.168.x.y\NAME and by analogy people may try IP\NAME with default instance too.
@@VoluntaryDBA Instead of going for Sql browser, i have used static tcp/ip (set to 62400) for my named instance and created an inbound rule for tcp/ip with the same port. I have a valid sql user authentication. But when i try to connect using ip,port(i.e.,62400) ,i am getting instance specific error... please walk me through to resolve this issue. Any help from @Victor Villas also appreciated.
This was a perfect resolve for my connection issue! I have a Win7 ("server" with 2012) and a WinXP ("client" with 2008) and I was able to figure out on my own how to get 1433-1434 in the firewall listings, to also allow remote access, configure the DB for mix mode, and allow 100 compatibility. But, in all these efforts I could not get past the long logon error message. Searching Google yielded to add sqlbrowser as a firewall rule but that did nothing. Only via your video did I find out the only thing I missed was the custom SQL Server rule. Thank you!!!
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!
Really nice video. Clear, concise, and provided a simple process to follow to allow networked connections. Thank you for posting this, I really appreciate the time/effort you put into this.
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. :)
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.
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
Still valid in 2020 for 2019 SQL Server Express. I had to also enable the SQL Server Browser service to connect from a remote machine. Not sure if that has changed since 2013 or my setup was different for some reason. Just posting here incase you can't connect after following these steps. Double check the SQL Server Browser service is running on (which is on the screen @3:35).
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 .
Definitely! The basic technique in this video shows how to allow access through the *local* firewall hosting the SQL Server instance, which is where most people get stuck. If you can reach the machine over the network and the right ports are forwarded or allowed through, then you should be able to reach the SQL instance.
Perfect video!!! Question...I have a laptop in the intranet connecting perfectly to the SQL server, but when connecting through a VPN I get 'unable to connect to the SQL server' and I can't find why! I set up a port forward for the port and no sucess!
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
Thanks for the answer , i have still one last question. Do you prefer connecting these two PCs with a TCP cable it means peer-to-peer connection or via wireless.
That's a tough question to answer, because I have no idea what your app is designed to do. Generally, wireless is less reliable and more prone to security issues. Enabling connection encryption would be a good idea, especially if you choose to use wireless.
Hi, thanks for the nice tutorial. I have a problem though. I have my sql server on one computer and trying to connect to it from another computer and it doesn't seem to work. I am trying to connect using VB Script in Tia Portal. Any help would be highly appreciated.
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
Warda UAE Assuming the instance is Express edition, SQL Agent cannot be started because that feature is not available in that edition. With regards to your second point, I'm unclear on what the application architecture is -- if you want to provide more details I may be able to help.
I followed this and I cannot connect to the SQL Server. This SQL is hosted on a Windows Server 2016 machine thus, I don't know if there are other additional steps for this. I checked the port by using Test-NetConnection with Powershell and it returns true for the opened port. I don't know what else I'm missing; I tried all sorts of things, my instance is a named instance and I already specified the port to be 1433 under IPAll tab when double clicking the TCP/IP Protocol. I have also enabled "Allow remote connections" under the properties of the SQL Server instance. When I logon, it allows to logon on a different port (5 digit number) and it is my understanding that that logon is only for local access which points to 127.0.0.1. The rest says that it is listening on port 1433 for 'any'. I have tried different ways of logging on but still no go. Let me know if you happen to know what else is needed. Thank you.
Hi Sir, Do I need to add more user guests to the database? Or do I need any additional configuration with my database? Cause I followed you like above but it doesn't work? Thank you!
Voluntary DBA What about updating the windows firewall rules to restrict an ip range from other premises, I am ideally looking to have a main database at hq site with connection to each shop through tcp/ip and a failover mirror at each shop, is this a feasible thing to do?
John Paul Mc Feely If you're talking about Availability Groups, then that's out of my range of expertise; however, I do know there are more ports involved than just the main ones mentioned in this video, so adding other Firewall rules will be necessary. Restricting the incoming IP ranges would be a great start for sure, but I doubt that's sufficient to be completely secure. I would strongly recommend consulting an expert who is in a better position to evaluate your proposed architecture, and also knows how to securely implement Availability Groups... certainly that person isn't me. Sorry.
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.
Andrew Harrison If you're talking about installing SQL Server on the same host as, for example, IIS, then yes, potentially there can be a conflict of resources which can degrade performance. But there's nothing wrong with doing this if the host can handle both (or more) duties at the same time, and there are ways to allocate resources (particularly RAM) to help reduce the conflict. See this blog post for more on that: voluntarydba.com/post/2013/03/19/Is-my-SQL-Servers-memory-over-committed.aspx What you'd want to do is record metrics on how healthy the server is in terms of resource usage and headroom, and then adjust your strategy accordingly. In my experience this kind of setup is usually done to save costs associated with licencing a separate server when the app just doesn't need a lot of horsepower. It also results in less server infrastructure to manage. Having the two servers on the same host can potentially be a security risk because there are more ways for the same host to be compromised. It also limits independent scalability of the application tiers. There are pros and cons to each method. Hope this helps.
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????
As ka You could use something like a Linked Server or an SSIS package to transfer the data. Or possibly using one of the forms of Replication if this is for an ongoing process. All of this is way beyond the scope of this video, though, so if you have a specific issue, please ask a question on dba.stackexchange.com and someone will be happy to help you.
Hi sir, I want to ask if port 1434 is the default with all computers? Because when I installed SQL Express Edition 2019 I got port 8391. And one thing is that you make it simple but I find it difficult :(
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!
Henry Onovwaka All of those error codes indicate that the client can't reach the server over the network. Are you able to successfully ping the server or see other network resources on the server? If not, there's something going on at the network layer and you'll have to talk to your network admin to resolve it. If yes, is TCP/IP enabled in SQL Server Configuration Manager, and was the instance restarted so that setting takes effect?
+mohamed jhasim I would suggest setting up a virtual private network (VPN) between the two locations -- then the server would appear on the "local" network at your home. Unfortunately, I can't help you with the specifics of how to do that; there should be some good tutorials around if you Google it.
chaari ons This method doesn't connect two instances of SQL Server together. What it does is allow a client application (SSMS, IIS, etc.) to connect to a single instance of SQL Server. The configuration changes are done on the server computer. I hope I understood your question correctly; let me know if I misunderstood.
Voluntary DBA I am sorry for the delay, you understood me very well and thank you for the answer. but can you help me solve my problem I am making a website with ql server as my database manager and i want my website to be connected to an application wich is also developped with sql qerver. thank you very much :)
chaari ons If you have a specific question about it, I can probably answer, or at least point you in a direction to get an answer. (Please note that I don't know anything about your applications or infrastructure.)
ok thanks anyway, I am still searching for informations, if i needed your help i will ask ;) thank you very much,it was a pleasure to have this conversation with you.
Ranjeet Kumar Hi Ranjeet, the changes to the firewall are the same for all types of authentication in SQL Server. I only used Windows Authentication in the demo for simplicity.
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.)
Troubleshoot by starting close to SQL and work your way out. Did the service start correctly? Can you connect locally? Can you connect locally using TCP/IP? If you dropped the firewall and that didn't work, I think there's something going on with the local configuration that needs to be fixed first.
Yes. It works fine locally. All of the settings have been checked at least 4 times. I have set this up quite a few times and this is the first time that I have run into a failure. I'll have to dig into this more in depth tomorrow. Perhaps there are other links on the web that cover this issue.
I found a different way to do it. It involved setting the TCP/IP to a specific port on the Protocol's "IPALL" TCP Port property. In this case, I used port 49170 and wiped out the TCP Dynamic Ports property. I then set up an inbound firewall setting to the specific port 49170. Finally, I accessed SQL using the IP address of the server followed by a comma and 49170, such as 192.168.125.5,49170 and that worked perfectly. You are probably right that there was something screwy going on with the server's TCP/IP. This seems to bypass that issue.
@@almsys I did the same configuration. Enabled TCP/IP and given a static port and added an inbound rule for the same. And I am unable to connect through IP,Port. In my case connecting through Sql browser is working. Can we connect to sql server named instance using TCP, SQL Browser (Both ways)? Have you enabled Udp port in your case and allowed custom named instance service along with TCP/IP. Any help from @Voluntary DBA also appreciated.
Excellent video, but even enabling tcp from windows 10, it didn't work. I have a VPS on Hostinger, with Firewall and open port, and it gives the same error, what could it be?
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.
Okay, a couple points here. First, an instance name gets converted to a port number by the SQL Browser service, so what you're asking doesn't really make sense... but you can configure an instance to use a specific static port number in Configuration Manager using the TCP/IP properties page. Second, regardless of whether you're using an external firewall or not, you need to poke a hole at the operating system level, either using the instructions I've presented here, or by disabling the firewall entirely (not recommended!).
What if I create a C# app which uses SQL Server database then I want other users in my network to install and use it on their PCs? Can they still fetch data from my database even if I turn off my PC? Newbie here
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.
Hi Bhavana, make sure you can connect locally using SQL Server Authentication before proceeding with the rest of the steps. Windows Authentication mode can be used, but is more complicated to set up if the server is not domain-joined. To connect remotely, you can either use the port number that SQL Server exposes, or you can specify the instance name in the form: COMPUTERNAME\INSTANCENAME, which will use the SQL Browser service to resolve the port number. My recommendation would be to ensure you can connect locally first, then follow through the steps in the video one by one.
grippnault These steps would allow another computer to be able to connect to a SQL Server instance on your computer. If you're only connecting on the same computer, these steps are unnecessary. I don't remember if Visual Studio 2013 installs a full Express instance or an instance of LocalDB -- if it's the latter, this video does not apply at all.
diego decoder Installing a database engine instance is only necessary on one system.A remote system can then use SQL Server Management Studio (like I demonstrated), or another application, to connect to the server. It isn't necessary to install a database engine instance on the remote system.
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.
H Kobzz Hi, if you try to connect to a named instance locally, it will use Shared Memory first (which effectively bypasses the Browser service) unless you force it to use TCP/IP, in which case it won't connect. To connect remotely, you have to either turn on the Browser service, or know the port number of the instance, or there's something else going on in the network configuration that's impossible for me to know. As for exposing a SQL server directly to the internet, this is rarely a good idea, and I strongly recommend against it. I don't mean to be rude, but if you have to ask how, then you probably shouldn't be going there. To give you some perspective, I would not attempt to set that up myself, because I'm not familiar enough with everything that needs to be done. It's too risky, especially since there are almost always other ways to accomplish whatever it is that needs to be done. I hope that helps.
H Kobzz It really depends on what the business goals and requirements are. Generally speaking, though, some kind of web application (either a front end or a web service) in the middle is a good starting point.
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
isSs Ibrahim Correct. These steps allow access to a SQL Server instance from remote computers, without resorting to compromising other security features in Windows (i.e., by disabling Windows Firewall).
isSs Ibrahim Exactly; for your purposes, the client computers would not need to install an instance of SQL Server. If your application isn't being written using a Microsoft client technology (such as ADO.NET), the client computers may require a small driver install to be able to connect to SQL Server.
isSs Ibrahim Yep, if the version of .NET you're targeting isn't installed by default with the O/S, then you'll have to install it; otherwise, all you need are the app binaries. It would probably take you just a few minutes to whip up a test app that connects to the database to make sure everything works as expected.
All these instructions do is permit inbound connections to the server machine through the operating system-level firewall. As long as the client machine can reach the server on the same ports across whatever network architecture you've got set up, it should work.
The concepts in this video should apply to your situation. After doing that, I would have a look at the MSDN documentation to make sure your sqlcmd command is correct. If you can, construct the command locally where you know it will work, then take it to a remote machine and try it from there with the appropriate modifications.
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?
Hi Mark, Using only the machine name connects to the default (non-named) instance, why is on 1433 by default. Named instances by default are not exposed on 1433, which is why I show setting up a firewall rule on the database engine service, which allows connections in on any port(s) that service opens. If you installed a named instance and created a firewall rule that explicitly allows only 1433 in (instead of the service-based rule as I suggested), you won't be able to connect because the service isn't listening on that port. It's possible to configure a named instance to listen on 1433, but I didn't cover that in the video for the sake of simplicity.
+Voluntary DBA Thank you very much. I followed the workflow, still my issue was not resolved. So turned off the firewall and tried again. Ultimately I found out that on my machine, SQL2014 . for some reason the Sql server browser was disabled. I had to change its start mode to automatic and then I was good to go.
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?
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
Hi Paula, you may want to try asking a question on dba.stackexchange.com including all the details, including whether or not the computers involved are domain-joined -- there are too many variables here for me to try to troubleshoot in the comments. But my initial thought is to start by making sure the client host can reach the server host over the network (try using WireShark on the server if you aren't sure) and work inward from there. You shouldn't have to disable Windows Firewall for this to work.
Hi. Im trying to connect a database table in sql server express to a client software (endicia) but i cannot make it work. Is this possible? How should i do it?.
carlos llanos I would recommend contacting the vendor of the software for support; they should be familiar with how to configure their software to connect to the database for your purposes.
I attempted to install sql server 2008 express service pack 1(x64) i.e part of visual studio 2010 installation .but it failed to install that.& in error msg it shows error code:2068643839 component is not recognized..it returns unexpected value..endofsession...I m using this in windows 8.1.... is ur video's solution can solve this problem????
HI I'm working with MS SQL SERVER 2012 , i followed the instruction of the video but i still unable to establish a remote connection is there any difference ??
There shouldn't be any difference. Assuming you're working with a default install of a database engine instance (not LocalDB), try going through the steps again, as accuracy is important; make sure the SQL Browser service is started, etc. If that doesn't work, or you're using a non-default configuration, I would need a lot more information, possibly beyond what I can troubleshoot in comments here.
It works only if i enter the ip adress of the remote server and the port number in the server name field "ip@,port_number" and only with a windows authetication. i'm using a default configuration(instance_name:MSSQLSERVER) .
An instance name of MSSQLSERVER is a default instance; to connect to it, *don't* specify the instance name -- only use the Windows server name in place of the IP address. If the port number is 1433 (the default), then specifying the port number is optional. This should work if you're able to connect using the IP address. SQL authentication not working is probably a configuration issue in the server settings; it may not have been configured when the instance was installed.
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?
Ralph Could you please tell me more about the scenario? What error are you getting? Are the two computers in a domain? Can you successfully ping the server from the client? Are you using Windows Authentication or SQL Authentication?
Voluntary DBA Hi first of all thank you very much for replying to me so fast, lets start with what I am using in my Environment, my clients use "Microsoft RMS Store Operations" to connect to the SQL Server, previously i have installed SQL server 2005 and it worked perfectly without having to manage any settings but now the database has come to an point where the file group cannot accept more information because of the limitation of 2048MB, the database is 2.8GB and i am surprised it manage to get that far, now i have installed a new Windows server 2008 R2 standard 64x and downloaded SQLexpADV_64.exe and installed it correctly on the server, i managed all the settings in your video precisely the same and i use "SQL Server Authentication" user:SA no PASS to connect to the database, but the real issue starts now, when i go to my clients computer and manage the setting "Data Sources (ODBC)" to make a SQL server connection it refuses to connect regardless of which username i use, when i only select "With windows NT authentication using network login ID" i get to go to the next menu but cannot select the database as i didn't authenticate the "Connect to SQL server to obtain default settings for the additional configuration options" I tried to explain as much as I can but it would be better if i show you through a skype session with viewing screen, if you accept i can PM you my skype name. (All the above are virtual installed on ESXi Host)
Ralph I'm sorry, I don't mean to be rude, but I honestly don't have time to try to troubleshoot a scenario this complex. I would try to solve this step by step: Start by looking at the networking to make sure there is connectivity -- can you successfully ping back and forth between the servers? Then establish you can connect to SQL Server from the *same* machine. It might be helpful to set up a test SQL login for this, because when you try to connect remotely, using SQL Authentication bypasses issues related to domains and Windows Authentication. Once you can connect remotely using the SQL login, you know the networking and firewall is configured correctly and the test user can be removed. Finally, connect remotely using Windows Authentication. If at any point it doesn't work, you have to solve that before moving on to the next step. I hope this helps.
Voluntary DBA Hi, I completely understand, yes i can ping between machines, on the server with the SQL installed I tried to make a SQL server driver through Data Sources and it still doesn't connect, I tried a test username already, I cannot connect on the SQL server 2005 to SQl server 2008, but from the SQl server 2008 i can connect remotely to SQLserver 2005 does this help in anyway?
Hi Voluntary DBA I manage to get a breakthrough on this, what i did is upgrade the SQL server 2005 to 2008 it was a bit tricky but i managed to make it work and the database file can now grow to 10 GB! but I it would be nice if we can still isolate the issue for clean install of a SQL server 2008 R2.
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.
thank you so much!!! I've been reading articles about opening the firewall rule. but none of them worked. Yours is the first one to suggest the database instance. And it worked!
You're welcome!
I have tried many instruction from the WEB, but only yours working for me. Many thanks.
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.
Victor Chaves Using the machine name should work to connect to a default instance under most circumstances, but you're right, using the IP address (plus the port number if it's non-standard) is always a fallback to connect over TCP/IP.
Yes, what I meant to say was to either provide adress or name, never both in case of default instance.
I'm saying this because in most tutorials we see someone conecting to a named instance with 192.168.x.y\NAME and by analogy people may try IP\NAME with default instance too.
@@VoluntaryDBA Instead of going for Sql browser, i have used static tcp/ip (set to 62400) for my named instance and created an inbound rule for tcp/ip with the same port. I have a valid sql user authentication. But when i try to connect using ip,port(i.e.,62400) ,i am getting instance specific error... please walk me through to resolve this issue. Any help from @Victor Villas also appreciated.
3 year and still helps. Thanks for the tutorial and for you time.
This was a perfect resolve for my connection issue! I have a Win7 ("server" with 2012) and a WinXP ("client" with 2008) and I was able to figure out on my own how to get 1433-1434 in the firewall listings, to also allow remote access, configure the DB for mix mode, and allow 100 compatibility. But, in all these efforts I could not get past the long logon error message. Searching Google yielded to add sqlbrowser as a firewall rule but that did nothing. Only via your video did I find out the only thing I missed was the custom SQL Server rule. Thank you!!!
You're welcome!
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!
Really nice video. Clear, concise, and provided a simple process to follow to allow networked connections. Thank you for posting this, I really appreciate the time/effort you put into this.
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. :)
Thank you Million Times. Works. You got all the steps with firewall. I missed one step.
Best video tutorial ever; very good quality of video, clear instructions with demo, good job.
Hope to see more videos from you. Thanks a lot!
You're welcome!
Thank you very much! I tried a bunch of tutorials on this and none worked but this one did! Thank you very, very much! :D
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.
BRAVO!!!! Well Done... Been battling with this for days then saw your video and it all came clear.... Thank you for everything you do!!!!!
I was fiddling about for ages until I saw this. Thank you so much Jon
You have no idea how much this helped, thank you very very much!
Immac (RockLeet) You're welcome!
I want to thank you very much for taking the time to others such as myself.
+Don Harned You're welcome. Thanks for watching. :)
Thank you so much for this video. Forgot I needed the SQL Browser service active to access my named instance.
Really great! Thanks for the info. I liked the thoroughness and calm delivery.
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.
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
Still valid in 2020 for 2019 SQL Server Express. I had to also enable the SQL Server Browser service to connect from a remote machine. Not sure if that has changed since 2013 or my setup was different for some reason. Just posting here incase you can't connect after following these steps. Double check the SQL Server Browser service is running on (which is on the screen @3:35).
Brilliant. Was struggling with this, but you made it so easy. Thanks very much!
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 .
Definitely! The basic technique in this video shows how to allow access through the *local* firewall hosting the SQL Server instance, which is where most people get stuck. If you can reach the machine over the network and the right ports are forwarded or allowed through, then you should be able to reach the SQL instance.
I got it . Thank you :)
Thank you for this guide. Saved me hours of my time.
Perfect video!!! Question...I have a laptop in the intranet connecting perfectly to the SQL server, but when connecting through a VPN I get 'unable to connect to the SQL server' and I can't find why! I set up a port forward for the port and no sucess!
Just the information I was looking for - Thank you so much!
This information helped us out. I'm glad I found this video!
Hi, thanks for the video. when you createdconmevtion did you already have user e in database ?
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
Hi Faton, yes, this technique is about configuring the server to allow incoming connections, so the number of clients could be 1 or it could be 1,000.
Thanks for the answer , i have still one last question. Do you prefer connecting these two PCs with a TCP cable it means peer-to-peer connection or via wireless.
That's a tough question to answer, because I have no idea what your app is designed to do. Generally, wireless is less reliable and more prone to security issues. Enabling connection encryption would be a good idea, especially if you choose to use wireless.
Hi, thanks for the nice tutorial. I have a problem though. I have my sql server on one computer and trying to connect to it from another computer and it doesn't seem to work. I am trying to connect using VB Script in Tia Portal. Any help would be highly appreciated.
Thanks for the video. Could you make a video for sql server security - such as logins, principles, roles. How does it work
Good idea, I'll add it to my list. Thanks.
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
Warda UAE Assuming the instance is Express edition, SQL Agent cannot be started because that feature is not available in that edition. With regards to your second point, I'm unclear on what the application architecture is -- if you want to provide more details I may be able to help.
I followed this and I cannot connect to the SQL Server. This SQL is hosted on a Windows Server 2016 machine thus, I don't know if there are other additional steps for this. I checked the port by using Test-NetConnection with Powershell and it returns true for the opened port. I don't know what else I'm missing; I tried all sorts of things, my instance is a named instance and I already specified the port to be 1433 under IPAll tab when double clicking the TCP/IP Protocol. I have also enabled "Allow remote connections" under the properties of the SQL Server instance. When I logon, it allows to logon on a different port (5 digit number) and it is my understanding that that logon is only for local access which points to 127.0.0.1. The rest says that it is listening on port 1433 for 'any'. I have tried different ways of logging on but still no go. Let me know if you happen to know what else is needed. Thank you.
thank you for a cleared explanation
Thank you so much. This is very clear and easy with this video. It's working.
Can you prepare video for how to configure alwayson step by step with active directory services
Hi Sir,
Do I need to add more user guests to the database?
Or do I need any additional configuration with my database?
Cause I followed you like above but it doesn't work?
Thank you!
Very Informative and exactly what I needed
Great video, will this work outside of an internal network?
John Paul Mc Feely Yes, but I don't recommend exposing a database server directly to the "outside" because it's a big security risk.
Voluntary DBA What about updating the windows firewall rules to restrict an ip range from other premises, I am ideally looking to have a main database at hq site with connection to each shop through tcp/ip and a failover mirror at each shop, is this a feasible thing to do?
John Paul Mc Feely If you're talking about Availability Groups, then that's out of my range of expertise; however, I do know there are more ports involved than just the main ones mentioned in this video, so adding other Firewall rules will be necessary. Restricting the incoming IP ranges would be a great start for sure, but I doubt that's sufficient to be completely secure. I would strongly recommend consulting an expert who is in a better position to evaluate your proposed architecture, and also knows how to securely implement Availability Groups... certainly that person isn't me. Sorry.
Well done. Clear and concise.
You saved my ass and my year project .Thank you .
Thank you for the clear explanation.
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.
Andrew Harrison If you're talking about installing SQL Server on the same host as, for example, IIS, then yes, potentially there can be a conflict of resources which can degrade performance. But there's nothing wrong with doing this if the host can handle both (or more) duties at the same time, and there are ways to allocate resources (particularly RAM) to help reduce the conflict. See this blog post for more on that: voluntarydba.com/post/2013/03/19/Is-my-SQL-Servers-memory-over-committed.aspx What you'd want to do is record metrics on how healthy the server is in terms of resource usage and headroom, and then adjust your strategy accordingly. In my experience this kind of setup is usually done to save costs associated with licencing a separate server when the app just doesn't need a lot of horsepower. It also results in less server infrastructure to manage. Having the two servers on the same host can potentially be a security risk because there are more ways for the same host to be compromised. It also limits independent scalability of the application tiers. There are pros and cons to each method. Hope this helps.
Thank you for your time. Excellent video and excellent demo.
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????
As ka You could use something like a Linked Server or an SSIS package to transfer the data. Or possibly using one of the forms of Replication if this is for an ongoing process. All of this is way beyond the scope of this video, though, so if you have a specific issue, please ask a question on dba.stackexchange.com and someone will be happy to help you.
Hi sir,
I want to ask if port 1434 is the default with all computers?
Because when I installed SQL Express Edition 2019 I got port 8391.
And one thing is that you make it simple but I find it difficult :(
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!
Henry Onovwaka All of those error codes indicate that the client can't reach the server over the network. Are you able to successfully ping the server or see other network resources on the server? If not, there's something going on at the network layer and you'll have to talk to your network admin to resolve it. If yes, is TCP/IP enabled in SQL Server Configuration Manager, and was the instance restarted so that setting takes effect?
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)
+mohamed jhasim I would suggest setting up a virtual private network (VPN) between the two locations -- then the server would appear on the "local" network at your home. Unfortunately, I can't help you with the specifics of how to do that; there should be some good tutorials around if you Google it.
Hello , i want to know if this method can connect two computers on the same lan
thanks alot
Nice Help for Network Access Configuration...
I really appreciate your tutorial. It is super helpful.
thanks bro, you save my first work..thanks so much
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.??
chaari ons This method doesn't connect two instances of SQL Server together. What it does is allow a client application (SSMS, IIS, etc.) to connect to a single instance of SQL Server. The configuration changes are done on the server computer. I hope I understood your question correctly; let me know if I misunderstood.
Voluntary DBA I am sorry for the delay, you understood me very well and thank you for the answer.
but can you help me solve my problem I am making a website with ql server as my database manager and i want my website to be connected to an application wich is also developped with sql qerver.
thank you very much :)
chaari ons If you have a specific question about it, I can probably answer, or at least point you in a direction to get an answer. (Please note that I don't know anything about your applications or infrastructure.)
ok thanks anyway, I am still searching for informations, if i needed your help i will ask ;) thank you very much,it was a pleasure to have this conversation with you.
chaari ons You're welcome.
it worked :D thank you very much, i was struggling for days !
Great Explanation, and Great Tutorial
You saved me bro... really helpful.. thanks man
Thank you, I was struggling to connect 👍
Hi,
It's a fine Tutorial to learn but i have some concerned in the case if i want to connect with SQL Authentication, then what can i do for this.
Ranjeet Kumar Hi Ranjeet, the changes to the firewall are the same for all types of authentication in SQL Server. I only used Windows Authentication in the demo for simplicity.
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.)
Troubleshoot by starting close to SQL and work your way out. Did the service start correctly? Can you connect locally? Can you connect locally using TCP/IP? If you dropped the firewall and that didn't work, I think there's something going on with the local configuration that needs to be fixed first.
Yes. It works fine locally. All of the settings have been checked at least 4 times. I have set this up quite a few times and this is the first time that I have run into a failure. I'll have to dig into this more in depth tomorrow. Perhaps there are other links on the web that cover this issue.
I found a different way to do it. It involved setting the TCP/IP to a specific port on the Protocol's "IPALL" TCP Port property. In this case, I used port 49170 and wiped out the TCP Dynamic Ports property. I then set up an inbound firewall setting to the specific port 49170. Finally, I accessed SQL using the IP address of the server followed by a comma and 49170, such as 192.168.125.5,49170 and that worked perfectly. You are probably right that there was something screwy going on with the server's TCP/IP. This seems to bypass that issue.
Weird. Glad you got it working!
@@almsys I did the same configuration. Enabled TCP/IP and given a static port and added an inbound rule for the same. And I am unable to connect through IP,Port. In my case connecting through Sql browser is working.
Can we connect to sql server named instance using TCP, SQL Browser (Both ways)? Have you enabled Udp port in your case and allowed custom named instance service along with TCP/IP. Any help from @Voluntary DBA also appreciated.
Excellent video, but even enabling tcp from windows 10, it didn't work.
I have a VPS on Hostinger, with Firewall and open port, and it gives the same error, what could it be?
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.
Okay, a couple points here. First, an instance name gets converted to a port number by the SQL Browser service, so what you're asking doesn't really make sense... but you can configure an instance to use a specific static port number in Configuration Manager using the TCP/IP properties page. Second, regardless of whether you're using an external firewall or not, you need to poke a hole at the operating system level, either using the instructions I've presented here, or by disabling the firewall entirely (not recommended!).
What if I create a C# app which uses SQL Server database then I want other users in my network to install and use it on their PCs? Can they still fetch data from my database even if I turn off my PC? Newbie here
Thank you, Very much for your kindness
Thanx! its awesome i was able to connect my app easily after I did all of this steps
You're welcome!
This video really helped me out! thank you so much!
perfect .. Its so easy .. you made my day .. thanks alot
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.
Hi Bhavana, make sure you can connect locally using SQL Server Authentication before proceeding with the rest of the steps. Windows Authentication mode can be used, but is more complicated to set up if the server is not domain-joined. To connect remotely, you can either use the port number that SQL Server exposes, or you can specify the instance name in the form: COMPUTERNAME\INSTANCENAME, which will use the SQL Browser service to resolve the port number. My recommendation would be to ensure you can connect locally first, then follow through the steps in the video one by one.
You made my day.
Thanks.
Is this going to allow me to connect to a SQL Server 2014 Express Database via Visual Studio 2013?
grippnault These steps would allow another computer to be able to connect to a SQL Server instance on your computer. If you're only connecting on the same computer, these steps are unnecessary. I don't remember if Visual Studio 2013 installs a full Express instance or an instance of LocalDB -- if it's the latter, this video does not apply at all.
Okay, thank you for responding.
Pls i want to ask if you want to set up remote connection do u have to get the sql server express installed on two systems
diego decoder Installing a database engine instance is only necessary on one system.A remote system can then use SQL
Server Management Studio (like I demonstrated), or another application, to
connect to the server. It isn't necessary to install a database engine instance on the remote system.
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.
H Kobzz Hi, if you try to connect to a named instance locally, it will use Shared Memory first (which effectively bypasses the Browser service) unless you force it to use TCP/IP, in which case it won't connect. To connect remotely, you have to either turn on the Browser service, or know the port number of the instance, or there's something else going on in the network configuration that's impossible for me to know. As for exposing a SQL server directly to the internet, this is rarely a good idea, and I strongly recommend against it. I don't mean to be rude, but if you have to ask how, then you probably shouldn't be going there. To give you some perspective, I would not attempt to set that up myself, because I'm not familiar enough with everything that needs to be done. It's too risky, especially since there are almost always other ways to accomplish whatever it is that needs to be done. I hope that helps.
Thanks a lot... Can you recommend an alternate method if I want to connect to a sql server over internet?
H Kobzz It really depends on what the business goals and requirements are. Generally speaking, though, some kind of web application (either a front end or a web service) in the middle is a good starting point.
You are Brilliant Sir!!!
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
isSs Ibrahim Correct. These steps allow access to a SQL Server instance from remote computers, without resorting to compromising other security features in Windows (i.e., by disabling Windows Firewall).
That's awesome! I wouldn't need to install sql server on the other computers that will be using remote access right?
isSs Ibrahim Exactly; for your purposes, the client computers would not need to install an instance of SQL Server. If your application isn't being written using a Microsoft client technology (such as ADO.NET), the client computers may require a small driver install to be able to connect to SQL Server.
I will be using c# to build the application, I guess I would need to install .net framework on the devices but that's about it right?
isSs Ibrahim Yep, if the version of .NET you're targeting isn't installed by default with the O/S, then you'll have to install it; otherwise, all you need are the app binaries. It would probably take you just a few minutes to whip up a test app that connects to the database to make sure everything works as expected.
Will this work if the other computer is in a different network with different IP?
All these instructions do is permit inbound connections to the server machine through the operating system-level firewall.
As long as the client machine can reach the server on the same ports across whatever network architecture you've got set up, it should work.
Thank you!! This is very helpful
please make a video for connecting remote c# application to SQL server.
thanks! plain and simple. works fine
Great, its worked for me. thanks a lot.
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?
The concepts in this video should apply to your situation. After doing that, I would have a look at the MSDN documentation to make sure your sqlcmd command is correct. If you can, construct the command locally where you know it will work, then take it to a remote machine and try it from there with the appropriate modifications.
thank you for this intention , it is help to solve my problem
Thanks a lot. You just saved my day :)
Big thanks man , you are the best
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?
Hi Mark,
Using only the machine name connects to the default (non-named) instance, why is on 1433 by default. Named instances by default are not exposed on 1433, which is why I show setting up a firewall rule on the database engine service, which allows connections in on any port(s) that service opens. If you installed a named instance and created a firewall rule that explicitly allows only 1433 in (instead of the service-based rule as I suggested), you won't be able to connect because the service isn't listening on that port. It's possible to configure a named instance to listen on 1433, but I didn't cover that in the video for the sake of simplicity.
thank you, work like a charm
I did it! Thank you very much!
Dante Sparda :)
+Voluntary DBA Thank you very much. I followed the workflow, still my issue was not resolved. So turned off the firewall and tried again. Ultimately I found out that on my machine, SQL2014 . for some reason the Sql server browser was disabled. I had to change its start mode to automatic and then I was good to go.
I have connected the same way but not configured. can you please tell me where was the problem
Pls I want to known can this connection be done via internet
thanks a lot .....it works great for me .....god bless u
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?
Got it! Thanks
The SQL Browser Service was not running.
Thank you so much, I tried this and it really helped me. hehe
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
Hi Paula, you may want to try asking a question on dba.stackexchange.com including all the details, including whether or not the computers involved are domain-joined -- there are too many variables here for me to try to troubleshoot in the comments. But my initial thought is to start by making sure the client host can reach the server host over the network (try using WireShark on the server if you aren't sure) and work inward from there. You shouldn't have to disable Windows Firewall for this to work.
Voluntary DBA HI... We were in different subnetworks... But the agent is still down... What we are going to do is to install the standard edition
Hi. Im trying to connect a database table in sql server express to a client software (endicia) but i cannot make it work. Is this possible? How should i do it?.
carlos llanos I would recommend contacting the vendor of the software for support; they should be familiar with how to configure their software to connect to the database for your purposes.
Sir its working fine on same network. But when I try to access it from another network its not connecting. What should I do for that????
Thank you! Great help!
I have sql2000 and sql2012 how can i connect? please
excellent video!
Thank you so much for this!
I attempted to install sql server 2008 express service pack 1(x64) i.e part of visual studio 2010 installation .but it failed to install that.& in error msg it shows error code:2068643839 component is not recognized..it returns unexpected value..endofsession...I m using this in windows 8.1....
is ur video's solution can solve this problem????
I M Akki No, sorry, you'll have to get support from Microsoft on that one.
HI I'm working with MS SQL SERVER 2012 , i followed the instruction of the video but i still unable to establish a remote connection is there any difference ??
There shouldn't be any difference. Assuming you're working with a default install of a database engine instance (not LocalDB), try going through the steps again, as accuracy is important; make sure the SQL Browser service is started, etc. If that doesn't work, or you're using a non-default configuration, I would need a lot more information, possibly beyond what I can troubleshoot in comments here.
It works only if i enter the ip adress of the remote server and the port number in the server name field "ip@,port_number" and only with a windows authetication.
i'm using a default configuration(instance_name:MSSQLSERVER) .
An instance name of MSSQLSERVER is a default instance; to connect to it, *don't* specify the instance name -- only use the Windows server name in place of the IP address. If the port number is 1433 (the default), then specifying the port number is optional. This should work if you're able to connect using the IP address. SQL authentication not working is probably a configuration issue in the server settings; it may not have been configured when the instance was installed.
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?
Ralph Could you please tell me more about the scenario? What error are you getting? Are the two computers in a domain? Can you successfully ping the server from the client? Are you using Windows Authentication or SQL Authentication?
Voluntary DBA Hi first of all thank you very much for replying to me so fast, lets start with what I am using in my Environment, my clients use "Microsoft RMS Store Operations" to connect to the SQL Server, previously i have installed SQL server 2005 and it worked perfectly without having to manage any settings but now the database has come to an point where the file group cannot accept more information because of the limitation of 2048MB, the database is 2.8GB and i am surprised it manage to get that far, now i have installed a new Windows server 2008 R2 standard 64x and downloaded SQLexpADV_64.exe and installed it correctly on the server, i managed all the settings in your video precisely the same and i use "SQL Server Authentication" user:SA no PASS to connect to the database, but the real issue starts now, when i go to my clients computer and manage the setting "Data Sources (ODBC)" to make a SQL server connection it refuses to connect regardless of which username i use, when i only select "With windows NT authentication using network login ID" i get to go to the next menu but cannot select the database as i didn't authenticate the "Connect to SQL server to obtain default settings for the additional configuration options" I tried to explain as much as I can but it would be better if i show you through a skype session with viewing screen, if you accept i can PM you my skype name. (All the above are virtual installed on ESXi Host)
Ralph I'm sorry, I don't mean to be rude, but I honestly don't have time to try to troubleshoot a scenario this complex. I would try to solve this step by step: Start by looking at the networking to make sure there is connectivity -- can you successfully ping back and forth between the servers? Then establish you can connect to SQL Server from the *same* machine. It might be helpful to set up a test SQL login for this, because when you try to connect remotely, using SQL Authentication bypasses issues related to domains and Windows Authentication. Once you can connect remotely using the SQL login, you know the networking and firewall is configured correctly and the test user can be removed. Finally, connect remotely using Windows Authentication. If at any point it doesn't work, you have to solve that before moving on to the next step. I hope this helps.
Voluntary DBA Hi, I completely understand, yes i can ping between machines, on the server with the SQL installed I tried to make a SQL server driver through Data Sources and it still doesn't connect, I tried a test username already, I cannot connect on the SQL server 2005 to SQl server 2008, but from the SQl server 2008 i can connect remotely to SQLserver 2005 does this help in anyway?
Hi Voluntary DBA I manage to get a breakthrough on this, what i did is upgrade the SQL server 2005 to 2008 it was a bit tricky but i managed to make it work and the database file can now grow to 10 GB! but I it would be nice if we can still isolate the issue for clean install of a SQL server 2008 R2.