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!
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!!!
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.
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!
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. :)
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.
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.
+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.
9 ปีที่แล้ว +1
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
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.
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, 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?
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 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!
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!
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).
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.
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.
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 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 :(
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!).
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.
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.
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
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.
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.
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.
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.
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?
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.
Viet Vo The two computers are not in the same Active Directory domain (or they are not domain-joined at all), or you're not using pass-through authentication. This message is a good sign, though, because it means you have configured the firewall correctly!
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.
I have installed sql server 2008, and service pack 3, but I cannot come to the step you show at 3:19. It gives me this i1300.photobucket.com/albums/ag100/thichbanla/Untitledvf_zpsa35563a7.png Can you please help? Thank in advance.
It doesn't appear that any database engine instances are installed, just the client tools. If there actually is a database engine installed (a permanent instance, not LocalDB), I would try a repair as a first step. Otherwise, I'm not sure what the problem is, sorry. You might try asking a question on dba.stackexchange.com (please add more details about the installation process you used) or contact Microsoft support.
Thank you very much for pointing out the problem. That makes me realize that sql server was not installed fully though it always show successful installation message.
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.
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 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?
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.
+Joe Chan I'm not sure exactly what you're doing, but if "outside" the network is the public internet, I would strongly advise against doing that for a number of reasons. That said, since you've got things working remotely on the local network, there's likely an issue with a firewall configuration at some outer level. Unfortunately, I can't really help with that. You'll have to consult the documentation for the device(s) or enlist a network admin's help. Definitely attempt to connect using a fixed port number instead of using the Browser service (i.e., named instances) for simplicity until the issue is identified.
Voluntary DBA Thank you for your reply! I used the default instance and working now. I may still need to figure out the named instance to get through the firewall. Thanks again!
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.
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
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'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.
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 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.
I am able to login from MS Access using ODBC connection remotely(not from the same computer). But I am able to login using SQL Server Authentication. How can I enable windows Authentication in local Network?
It depends on the Windows domain configuration on the two machines. See my answer here: dba.stackexchange.com/questions/24428/log-shipping-setup-across-domains -- especially the MSDN link at the bottom.
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.
Hi Eslam, A quick Google search says this is a Python client-side error message. Try to connect using a different client, such as SSMS or SQLCMD, which may give more detailed error messages. You can also check the logs on the server to see if the connection attempts even hit the instance. That should give you something to start with.
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.
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.
Hello! firstly thanks alot for the Vid, it's cleared some air and answered a couple of questions i had. Apparently, i am working on a desktop application, i have the desktop PC as the machine to host MySql Database server while the laptop as the device i want to connect remotely to the database on MySql server on the desktop PC, on the laptop i just want to do the connection in code as i run my desktop application, question is, how do i do it provided the host is configured as had been in the Vid?
McVersity mONE Hi, the port numbers I gave in this video are for SQL Server, not MySQL. I'm not sure which you meant in your comment, and honestly I'm not sure if those port numbers work for MySQL. In any event, assuming the firewall is correctly configured, check out www.connectionstrings.com for an appropriate connection string to use from the client application.
Voluntary DBA just sliped, i actually meant SQL Server, just that i have been reading+researching on both to find which one works for me the best. Provided i have configured everything like shown in the video above, how can i connect to the database on the configured SQL Server when developing a desktop application on another machine without having contact with the hosting machine? or is it even possible to develop an external application that uses,fetches/manipulates data of a database that resides on a different machine(the host) having the configured SQL Server?
McVersity mONE Okay, I think I'm missing part of your requirements... "without having contact with the hosting machine" -- are you talking about an occasionally-connected client? In other words, someone takes the laptop to use the app, has no network or internet access but is able to enter data, then periodically reconnects to the network to synchronize information with the central database?
Voluntary DBA No, connecting to the machine with the database via internet, meaning the hosting machine has to have internet at all times in order for client with internet fetch data from it
McVersity mONE Okay, for that situation there's no difference between connecting within an intranet versus over the internet except that you'll probably have to open the ports at more than one level.Now having said that, this kind of configuration is strongly discouraged for security reasons because it exposes the SQL Server directly to the outside world, even if traffic encryption is used for a particular application Certainly you don't want to be using SQL Browser, and a non-default port should be used for the database engine instance. Both of those changes would need to be carried through in the firewall rules.A Terminal Services type of architecture will keep the data more secure and provide users better performance because data will only be traveling over the local network. This also gives the solution provider better control over deployment and versioning, which is a huge advantage.Using something like a VPN might also be an option, in which case it would be just as if connecting to SQL Server over the local network.
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 (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
Well, this is clearly a terrible idea... but from an actionable point of view, I can't really help because what you need help with is far beyond the scope of SQL Server. You've got a local connection working so the server is accessible; work a step at a time from an external perspective to figure out where the connection is getting blocked.
Hi Antonio, Having a SQL Server instance exposed directly to the Internet is not a good idea. If you need to access an instance remotely, I would recommend implementing a solution using a VPN or an SSH tunnel depending on what infrastructure is already in place.
Thank you for the quick response... perhaps I should rephrase the question: which tool / software do you recommend and / or the pros use to provide remote sql server dba support to a remote customer / server???
Ah, that's completely different. There are many solutions out there (including the two I previous mentioned), depending on the structure of your relationship with the customer, your needed level of access, whether they want to actively monitor your activities, etc. I've personally used Team Viewer (from both the provider and consumer sides) in the past and it's worked well. It's a good idea to ask the customer what they already use, because that's often the best place to start from their perspective.
Hi, I don't quite understand the question, but you should be able to connect to an instance of SQL Server by the IP address, as long as TCP/IP is enabled in the Configuration Manager (probably requires instance restart) and the network routing allows it.
Hi, I am trying to connect remotely to a SQL 2008 R2 instance (HPTM2_2008R2) running on a Windows 7 laptop. 1. TCPIP is enabled using SQL Config Mgr for this instance 2. Under IPALL, TCP Dynamic ports is 49516, TCP Port is set to 40000 3. SQL Browser, Named Instance and Server Agent were restarted. 4. Router has port forwarding for all incoming traffic on port 40000 to go to the laptop's IP 192.168.0.20, port 40000 5. Router has port forwarding for all incoming traffic on port 1434 to go to the laptop's IP 192.168.0.20, port 1434 6. Firewall Rule set up for %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.HPTM2_2008R2\MSSQL\Binn\sqlservr.exe to SQL Server instance; Domain, Private and Public are checked, Local and Remote Scope set to Any IP, Protocols are for Any and All Local and Remote ports; 7. Firewall Rule set up for all programs that meet SQL Browser condition; Domain, Private and Public are checked, Local and Remote scope set to Any IP, Protocols are for Any and All Local and Remote ports; I can connect to 192.168.0.20\HPTM2_2008R2 using SQL authentication with username and password - no problem. When I substitute 192.168.0.20 with my public IP address from my internet provider using whats-my-ip, I cannot connect. What am I missing here? I even turned off windows firewall but still no luck. Your thoughts would be greatly appreciated.
Hi Yossi, two things: First, if your intent is to use a static port, set the Dynamic Ports setting to 0. I don't remember which setting is actually used if both are set; changing Dynamic to 0 makes it clear which one is supposed to be in use. Restart the database engine service after making this change. Second, check the router port forwarding setting for port 1434 -- this must be set to UDP protocol to allow through the SQL Browser Service requests. The fact you can connect from within your network is a good sign, but it doesn't say anything about whether or not the router settings are working, nor which port SQL Server is actually using.
Voluntary DBA Hi, I set Dynamic ports to 0, but after restarting the server and browser, the dynamic port is reassigned (that's why it's dynamic :-) ) I ran PortQry on the IP address returned from Whats My IP and here are the results: ============================================= Starting portqry.exe -n 12.34.56.78 -e 1434 -p UDP ... Querying target system called: 12.34.56.78 Attempting to resolve IP address to a name... IP address resolved to bas1-thornhill40-1176143386.dsl.bell.ca querying... UDP port 1434 (ms-sql-m service): LISTENING or FILTERED Sending SQL Server query to UDP port 1434... UDP port 1434 (ms-sql-m service): FILTERED portqry.exe -n12.34.56.78 -e 1434 -p UDP exits with return code 0x00000002. ============================================= Starting portqry.exe -n 12.34.56.78 -e 1433 -p TCP ... Querying target system called: 12.34.56.78 Attempting to resolve IP address to a name... IP address resolved to bas1-thornhill40-1176143386.dsl.bell.ca querying... TCP port 1433 (ms-sql-s service): NOT LISTENING portqry.exe -n 12.34.56.78 -e 1433 -p TCP exits with return code 0x00000001. As you can see the UPD port is FILTERED and the default 1433 is NOT LISTENING. Also note that the ip address resolution goes to some router out of my hands. Bell says they do not filter or block anything so I have no idea why it wont get through. Even if I specify the port 1433 with the ip address and server name it still fails with a SQL 10061 error. Any other thoughts?
Yossi Dukes Sorry, my mistake. Set the Dynamic Ports field to (empty), not zero, and then restart the service. I think the problem is that the SQL Server service is using the dynamic port number, not the static port number, and so won't be forwarded properly by the router. Also, I would strongly consider editing your last reply to remove the public internet addresses.
Voluntary DBA Hi again, Ok, I've changed the Dynamic port to blank, restarted the browser, and SQL Server.. I've checked the protocols and the dynamic ip address remained as blank, so this is good as it is now using port 1433 as assigned. I have also verified that the router is showing port 1433 routed to my laptop running SQL server I have also verified that the router is showing port 1434 router to my laptop running SQL server. I am now using my dynamic dns account to ensure if my world-ip address changes by my internet provider, it will get resolved to the correct ip. Thanks for the tip to remove my outside world IP address (although it does change if I restart the router). I have changed the IP addresses to 12.34.56.78 This all being said, the port query is returning the same results as before. UPD Port is FILTERED and 1433 is NOT LISTENING Starting portqry.exe -n 12.34.56.78 -e 1434 -p UDP ... Querying target system called: 12.34.56.78 Attempting to resolve name to IP address... Name resolved to 12.34.56.78 querying... UDP port 1434 (ms-sql-m service): LISTENING or FILTERED Sending SQL Server query to UDP port 1434... UDP port 1434 (ms-sql-m service): FILTERED portqry.exe -n 12.34.56.78 -e 1434 -p UDP exits with return code 0x00000002. ============================================= Starting portqry.exe -n 12.34.56.78 -e 1433 -p TCP ... Querying target system called: 12.34.56.78 Attempting to resolve name to IP address... Name resolved to 12.34.56.78 querying... TCP port 1433 (ms-sql-s service): NOT LISTENING portqry.exe -n 12.34.56.78 -e 1433 -p TCP exits with return code 0x00000001. Any further thoughts / assistance would be greatly appreciated.
Yossi Dukes I thought you were going to use port 40000, but okay (might have been a placeholder). Please try the following steps. If at any time a step doesn't work, you need to figure out why it doesn't work, and fix it, before moving on to the next step. 1. Connect from within your network from another machine using the "," syntax. (Tests TCP/IP is enabled on the correct port, and is allowed through the firewall.) 2. Connect from within your network from another machine using \. (Tests SQL Browser is enabled, and allowed through the firewall.) 3. Connect from outside the network using the public IP with the specific port number as above. (Tests the static TCP port is forwarded.) 4. Connect from outside the network using the public IP with the instance name. (Tests UDP 1434 is forwarded.) I'm not familiar with portqry... I just test using Management Studio. From the configuration you told me, I set up the same scenario on my home network, and I was able to successfully connect using all of the above tests (connecting to home from an RDP session to my work machine). There are many factors, such as router settings, and what the service provider may or may not be doing on their end, that can affect this, so if you're unable to get through these simple tests, I can't reasonably provide further help.
Great Video but I cannot make mine work...read many other sites too. Hoping for another idea. Developer 2012 - Default Instance on a huge desktop; SQLExpress 2014 - Named Instance on a power laptop; Both on the same Domain...wasn't but I added it during the course of "fixing". Inbound Rule: SQL Browser = TCP 1433; -- didn't do UDP cuz desktop is default. Inbound Rule: Sql Server Database Engine (MSSQLSERVER) = applied to SQL Server (MSSQLSERVER) connect: tried every from I can think of for server name/login meaning having the login with and without the servername. note: as is, I cannot even see my Developer "server". I did have a inbound rule set that was pointing to the program, which then I could see, but still couldn't connect....balding rapidly! Is it just due to rules for Developer? Is it domain user versus deskop and laptop user...note: my domain user is different...weird to me. Thanks so much for any thoughts.
Hi Brooks, Please verify that TCP/IP is enabled for the Developer instance, and the instance has been restarted since the setting was changed. For the server name to connect to, it's just the computer name that has the Developer instance installed (I assume you can see this computer on the network from the laptop in some way already). When you try to connect after that, does it return with a "login failed" message immediately, or does it wait for a while and then say "connection timeout"?
Voluntary DBA Thanks for the reply! I am definitely restarting...I did it again to be sure. If windows auth I get a 18452 error and with sql auth I get a 18456 error...and they are immediate. I can see the desktop from the network and I can also see the "desktop" server from the list of network servers when ...
Brooks Cochrum Okay, this is actually good! The messages coming back immediately means the connection is working fine and the firewall is configured correctly. The problem is authentication-related. Have a look at these two docs for possible solutions: technet.microsoft.com/en-us/library/cc645598%28v=sql.110%29.aspx technet.microsoft.com/en-us/library/cc645917%28v=sql.110%29.aspx
William N. N. Nsambu In the Database Properties window, look in the Files area: either increase the sizes of the files manually if auto-growth is disabled, or enable auto-growth and set appropriate growth rates. If that still doesn't work, I'm guessing the database has hit the size limit of either 4 or 10 GB in Express Edition, depending on the version of SQL Server -- a solution in that case is to buy a licenced edition of SQL Server.
Voluntary DBA thanks you so much, my others questions are: 1- how to connect to sql server data base between 2 computer on over LAN using the odbc connection. I tried and I can see the server but can't connect to my database. 2- how to use the select query on my crystal report to only select what I want to show on my crystal report (vb.net). Thanks
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!
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!
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.
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!
3 year and still helps. Thanks for the tutorial and for you time.
I have tried many instruction from the WEB, but only yours working for me. Many thanks.
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. :)
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.
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 Million Times. Works. You got all the steps with firewall. I missed one step.
Hi, thanks for the video. when you createdconmevtion did you already have user e in database ?
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.
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.
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
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.
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
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, 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?
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 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!
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!
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).
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.
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.
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 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 :(
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!).
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.
Really great! Thanks for the info. I liked the thoroughness and calm delivery.
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
Can you prepare video for how to configure alwayson step by step with active directory services
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.
You have no idea how much this helped, thank you very very much!
Immac (RockLeet) You're welcome!
Hello , i want to know if this method can connect two computers on the same lan
thanks alot
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
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.
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.
Thank you so much for this video. Forgot I needed the SQL Browser service active to access my named instance.
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.
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 :)
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.
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?
I want to thank you very much for taking the time to others such as myself.
+Don Harned You're welcome. Thanks for watching. :)
I have connected the same way but not configured. can you please tell me where was the problem
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.
Why I get the error: Login failed. The login is from an untrusted domain and cannot be used with Windows authentication? Please help me!!!
Viet Vo The two computers are not in the same Active Directory domain (or they are not domain-joined at all), or you're not using pass-through authentication. This message is a good sign, though, because it means you have configured the firewall correctly!
Voluntary DBA thanks for replying me.
Viet Vo You're welcome!
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.
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????
I have installed sql server 2008, and service pack 3, but I cannot come to the step you show at 3:19. It gives me this i1300.photobucket.com/albums/ag100/thichbanla/Untitledvf_zpsa35563a7.png
Can you please help?
Thank in advance.
It doesn't appear that any database engine instances are installed, just the client tools. If there actually is a database engine installed (a permanent instance, not LocalDB), I would try a repair as a first step. Otherwise, I'm not sure what the problem is, sorry. You might try asking a question on dba.stackexchange.com (please add more details about the installation process you used) or contact Microsoft support.
Thank you very much for pointing out the problem. That makes me realize that sql server was not installed fully though it always show successful installation message.
Okay, great. I'm glad that was an easy fix. :)
please make a video for connecting remote c# application to SQL server.
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.
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.
Just the information I was looking for - Thank you so much!
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.
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.
+Joe Chan I'm not sure exactly what you're doing, but if "outside" the network is the public internet, I would strongly advise against doing that for a number of reasons. That said, since you've got things working remotely on the local network, there's likely an issue with a firewall configuration at some outer level. Unfortunately, I can't really help with that. You'll have to consult the documentation for the device(s) or enlist a network admin's help. Definitely attempt to connect using a fixed port number instead of using the Browser service (i.e., named instances) for simplicity until the issue is identified.
Voluntary DBA Thank you for your reply! I used the default instance and working now. I may still need to figure out the named instance to get through the firewall. Thanks again!
Pls I want to known can this connection be done via internet
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.
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
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'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
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.
Figured it out - had to change the start property to automatic.
Jeffrey Hughes Yep, if you're trying to connect to a named instance using that name, the Browser service has to be running for it to work.
I have sql2000 and sql2012 how can i connect? please
why does my SSCM in the SQL Server Service Node display "the remote procedure call failed [0x800706be]" can you help me how to solve this
Hi David, please try the solution posted here: social.technet.microsoft.com/Forums/en-US/5782bda0-8d38-437c-ba02-ed5f8b7c0a15
Brilliant. Was struggling with this, but you made it so easy. Thanks very much!
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.
Thank you for this guide. Saved me hours of my time.
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.
Are you able to login remotely using SSMS from the same computer you're trying to use MS Access?
Voluntary DBA now I am able to login remotely using SQL sever authentication. thank you sir
I am able to login from MS Access using ODBC connection remotely(not from the same computer). But I am able to login using SQL Server Authentication. How can I enable windows Authentication in local Network?
It depends on the Windows domain configuration on the two machines. See my answer here: dba.stackexchange.com/questions/24428/log-shipping-setup-across-domains -- especially the MSDN link at the bottom.
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.
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
Hi Eslam,
A quick Google search says this is a Python client-side error message. Try to connect using a different client, such as SSMS or SQLCMD, which may give more detailed error messages. You can also check the logs on the server to see if the connection attempts even hit the instance. That should give you something to start with.
This information helped us out. I'm glad I found this video!
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.
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.
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.
Hello! firstly thanks alot for the Vid, it's cleared some air and answered a couple of questions i had.
Apparently, i am working on a desktop application, i have the desktop PC as the machine to host MySql Database server while the laptop as the device i want to connect remotely to the database on MySql server on the desktop PC, on the laptop i just want to do the connection in code as i run my desktop application, question is, how do i do it provided the host is configured as had been in the Vid?
McVersity mONE Hi, the port numbers I gave in this video are for SQL Server, not MySQL. I'm not sure which you meant in your comment, and honestly I'm not sure if those port numbers work for MySQL. In any event, assuming the firewall is correctly configured, check out www.connectionstrings.com for an appropriate connection string to use from the client application.
Voluntary DBA just sliped, i actually meant SQL Server, just that i have been reading+researching on both to find which one works for me the best.
Provided i have configured everything like shown in the video above, how can i connect to the database on the configured SQL Server when developing a desktop application on another machine without having contact with the hosting machine? or is it even possible to develop an external application that uses,fetches/manipulates data of a database that resides on a different machine(the host) having the configured SQL Server?
McVersity mONE Okay, I think I'm missing part of your requirements... "without having contact with the hosting machine" -- are you talking about an occasionally-connected client? In other words, someone takes the laptop to use the app, has no network or internet access but is able to enter data, then periodically reconnects to the network to synchronize information with the central database?
Voluntary DBA No, connecting to the machine with the database via internet, meaning the hosting machine has to have internet at all times in order for client with internet fetch data from it
McVersity mONE Okay, for that situation there's no difference between connecting within an intranet versus over the internet except that you'll probably have to open the ports at more than one level.Now having said that, this kind of configuration is strongly discouraged for security reasons because it exposes the SQL Server directly to the outside world, even if traffic encryption is used for a particular application Certainly you don't want to be using SQL Browser, and a non-default port should be used for the database engine instance. Both of those changes would need to be carried through in the firewall rules.A Terminal Services type of architecture will keep the data more secure and provide users better performance because data will only be traveling over the local network. This also gives the solution provider better control over deployment and versioning, which is a huge advantage.Using something like a VPN might also be an option, in which case it would be just as if connecting to SQL Server over the local network.
What if it doesnt work?
Very Informative and exactly what I needed
Well done. Clear and concise.
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 (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
Well, this is clearly a terrible idea... but from an actionable point of view, I can't really help because what you need help with is far beyond the scope of SQL Server. You've got a local connection working so the server is accessible; work a step at a time from an external perspective to figure out where the connection is getting blocked.
Thank you so much. This is very clear and easy with this video. It's working.
it worked :D thank you very much, i was struggling for days !
Great video, thanks... but, what about how to connect to a sql server instance via the internet???
Hi Antonio,
Having a SQL Server instance exposed directly to the Internet is not a good idea. If you need to access an instance remotely, I would recommend implementing a solution using a VPN or an SSH tunnel depending on what infrastructure is already in place.
Thank you for the quick response... perhaps I should rephrase the question: which tool / software do you recommend and / or the pros use to provide remote sql server dba support to a remote customer / server???
Ah, that's completely different. There are many solutions out there (including the two I previous mentioned), depending on the structure of your relationship with the customer, your needed level of access, whether they want to actively monitor your activities, etc. I've personally used Team Viewer (from both the provider and consumer sides) in the past and it's worked well. It's a good idea to ask the customer what they already use, because that's often the best place to start from their perspective.
Thank you!
I really appreciate your tutorial. It is super helpful.
Great Explanation, and Great Tutorial
is it possible for vps ? (ipv4 protocol)
Hi, I don't quite understand the question, but you should be able to connect to an instance of SQL Server by the IP address, as long as TCP/IP is enabled in the Configuration Manager (probably requires instance restart) and the network routing allows it.
I solved my problem. thx for reply.
Thank you for the clear explanation.
thank you for a cleared explanation
You saved me bro... really helpful.. thanks man
Hi,
I am trying to connect remotely to a SQL 2008 R2 instance (HPTM2_2008R2) running on a Windows 7 laptop.
1. TCPIP is enabled using SQL Config Mgr for this instance
2. Under IPALL, TCP Dynamic ports is 49516, TCP Port is set to 40000
3. SQL Browser, Named Instance and Server Agent were restarted.
4. Router has port forwarding for all incoming traffic on port 40000 to go to the laptop's IP 192.168.0.20, port 40000
5. Router has port forwarding for all incoming traffic on port 1434 to go to the laptop's IP 192.168.0.20, port 1434
6. Firewall Rule set up for %ProgramFiles%\Microsoft SQL Server\MSSQL10_50.HPTM2_2008R2\MSSQL\Binn\sqlservr.exe to SQL Server instance; Domain, Private and Public are checked, Local and Remote Scope set to Any IP, Protocols are for Any and All Local and Remote ports;
7. Firewall Rule set up for all programs that meet SQL Browser condition; Domain, Private and Public are checked, Local and Remote scope set to Any IP, Protocols are for Any and All Local and Remote ports;
I can connect to 192.168.0.20\HPTM2_2008R2 using SQL authentication with username and password - no problem.
When I substitute 192.168.0.20 with my public IP address from my internet provider using whats-my-ip, I cannot connect.
What am I missing here?
I even turned off windows firewall but still no luck.
Your thoughts would be greatly appreciated.
Hi Yossi, two things:
First, if your intent is to use a static port, set the Dynamic Ports setting to 0. I don't remember which setting is actually used if both are set; changing Dynamic to 0 makes it clear which one is supposed to be in use. Restart the database engine service after making this change.
Second, check the router port forwarding setting for port 1434 -- this must be set to UDP protocol to allow through the SQL Browser Service requests.
The fact you can connect from within your network is a good sign, but it doesn't say anything about whether or not the router settings are working, nor which port SQL Server is actually using.
Voluntary DBA
Hi,
I set Dynamic ports to 0, but after restarting the server and browser, the dynamic port is reassigned (that's why it's dynamic :-) )
I ran PortQry on the IP address returned from Whats My IP and here are the results:
=============================================
Starting portqry.exe -n 12.34.56.78 -e 1434 -p UDP ...
Querying target system called:
12.34.56.78
Attempting to resolve IP address to a name...
IP address resolved to bas1-thornhill40-1176143386.dsl.bell.ca
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
UDP port 1434 (ms-sql-m service): FILTERED
portqry.exe -n12.34.56.78 -e 1434 -p UDP exits with return code 0x00000002.
=============================================
Starting portqry.exe -n 12.34.56.78 -e 1433 -p TCP ...
Querying target system called:
12.34.56.78
Attempting to resolve IP address to a name...
IP address resolved to bas1-thornhill40-1176143386.dsl.bell.ca
querying...
TCP port 1433 (ms-sql-s service): NOT LISTENING
portqry.exe -n 12.34.56.78 -e 1433 -p TCP exits with return code 0x00000001.
As you can see the UPD port is FILTERED and the default 1433 is NOT LISTENING.
Also note that the ip address resolution goes to some router out of my hands. Bell says they do not filter or block anything so I have no idea why it wont get through. Even if I specify the port 1433 with the ip address and server name it still fails with a SQL 10061 error.
Any other thoughts?
Yossi Dukes Sorry, my mistake. Set the Dynamic Ports field to (empty), not zero, and then restart the service. I think the problem is that the SQL Server service is using the dynamic port number, not the static port number, and so won't be forwarded properly by the router. Also, I would strongly consider editing your last reply to remove the public internet addresses.
Voluntary DBA
Hi again, Ok, I've changed the Dynamic port to blank, restarted the browser, and SQL Server.. I've checked the protocols and the dynamic ip address remained as blank, so this is good as it is now using port 1433 as assigned.
I have also verified that the router is showing port 1433 routed to my laptop running SQL server
I have also verified that the router is showing port 1434 router to my laptop running SQL server.
I am now using my dynamic dns account to ensure if my world-ip address changes by my internet provider, it will get resolved to the correct ip.
Thanks for the tip to remove my outside world IP address (although it does change if I restart the router). I have changed the IP addresses to 12.34.56.78
This all being said, the port query is returning the same results as before. UPD Port is FILTERED and 1433 is NOT LISTENING
Starting portqry.exe -n 12.34.56.78 -e 1434 -p UDP ...
Querying target system called:
12.34.56.78
Attempting to resolve name to IP address...
Name resolved to 12.34.56.78
querying...
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
UDP port 1434 (ms-sql-m service): FILTERED
portqry.exe -n 12.34.56.78 -e 1434 -p UDP exits with return code 0x00000002.
=============================================
Starting portqry.exe -n 12.34.56.78 -e 1433 -p TCP ...
Querying target system called:
12.34.56.78
Attempting to resolve name to IP address...
Name resolved to 12.34.56.78
querying...
TCP port 1433 (ms-sql-s service): NOT LISTENING
portqry.exe -n 12.34.56.78 -e 1433 -p TCP exits with return code 0x00000001.
Any further thoughts / assistance would be greatly appreciated.
Yossi Dukes
I thought you were going to use port 40000, but okay (might have been a placeholder).
Please try the following steps. If at any time a step doesn't work, you need to figure out why it doesn't work, and fix it, before moving on to the next step.
1. Connect from within your network from another machine using the "," syntax. (Tests TCP/IP is enabled on the correct port, and is allowed through the firewall.)
2. Connect from within your network from another machine using \. (Tests SQL Browser is enabled, and allowed through the firewall.)
3. Connect from outside the network using the public IP with the specific port number as above. (Tests the static TCP port is forwarded.)
4. Connect from outside the network using the public IP with the instance name. (Tests UDP 1434 is forwarded.)
I'm not familiar with portqry... I just test using Management Studio. From the configuration you told me, I set up the same scenario on my home network, and I was able to successfully connect using all of the above tests (connecting to home from an RDP session to my work machine).
There are many factors, such as router settings, and what the service provider may or may not be doing on their end, that can affect this, so if you're unable to get through these simple tests, I can't reasonably provide further help.
Thank you for your time. Excellent video and excellent demo.
You saved my ass and my year project .Thank you .
This video really helped me out! thank you so much!
Great Video but I cannot make mine work...read many other sites too.
Hoping for another idea.
Developer 2012 - Default Instance on a huge desktop;
SQLExpress 2014 - Named Instance on a power laptop;
Both on the same Domain...wasn't but I added it during the course of "fixing".
Inbound Rule: SQL Browser = TCP 1433; -- didn't do UDP cuz desktop is default.
Inbound Rule: Sql Server Database Engine (MSSQLSERVER) = applied to SQL Server (MSSQLSERVER)
connect: tried every from I can think of for server name/login meaning having the login with and without the servername.
note:
as is, I cannot even see my Developer "server".
I did have a inbound rule set that was pointing to the program, which then I could see, but still couldn't connect....balding rapidly!
Is it just due to rules for Developer?
Is it domain user versus deskop and laptop user...note: my domain user is different...weird to me.
Thanks so much for any thoughts.
Hi Brooks,
Please verify that TCP/IP is enabled for the Developer instance, and the instance has been restarted since the setting was changed. For the server name to connect to, it's just the computer name that has the Developer instance installed (I assume you can see this computer on the network from the laptop in some way already). When you try to connect after that, does it return with a "login failed" message immediately, or does it wait for a while and then say "connection timeout"?
Voluntary DBA Thanks for the reply! I am definitely restarting...I did it again to be sure. If windows auth I get a 18452 error and with sql auth I get a 18456 error...and they are immediate. I can see the desktop from the network and I can also see the "desktop" server from the list of network servers when ...
Brooks Cochrum Okay, this is actually good! The messages coming back immediately means the connection is working fine and the firewall is configured correctly. The problem is authentication-related. Have a look at these two docs for possible solutions: technet.microsoft.com/en-us/library/cc645598%28v=sql.110%29.aspx technet.microsoft.com/en-us/library/cc645917%28v=sql.110%29.aspx
Voluntary DBA thanks for the TNs. I've passed those along to our network consult in an effort to 'allow' the connection somewhere it isn't.
You're welcome. Now someone _else_ can pull their hair out. :)
Nice Help for Network Access Configuration...
perfect .. Its so easy .. you made my day .. thanks alot
How to add space in my database
William N. N. Nsambu In the Database Properties window, look in the Files area: either increase the sizes of the files manually if auto-growth is disabled, or enable auto-growth and set appropriate growth rates. If that still doesn't work, I'm guessing the database has hit the size limit of either 4 or 10 GB in Express Edition, depending on the version of SQL Server -- a solution in that case is to buy a licenced edition of SQL Server.
Voluntary DBA thanks you so much, my others questions are:
1- how to connect to sql server data base between 2 computer on over LAN using the odbc connection. I tried and I can see the server but can't connect to my database.
2- how to use the select query on my crystal report to only select what I want to show on my crystal report (vb.net).
Thanks
William N. N. Nsambu 1 - Have a look here: www.connectionstrings.com/sql-server
2 - Sorry, I don't know anything about Crystal Reports.
Thanks Voluntary DBA you are awesome
William N. N. Nsambu You're welcome.
thanks bro, you save my first work..thanks so much
Thank you, I was struggling to connect 👍
You made my day.
Thanks.