February 2, 2017 at 10:51 am
Hello,
We are working on a migration project. I need to have an ODBC DSN connection available to Microsoft AX deployment.
I have opened the ports of the local firewall.
I am using the public IP for the server.
The network guys have opened the firewall at the router.
I've created a sql server login.
I've made the login a system admin
The protocols are enabled.
The server is configured for remote connections.
SQL Server browser is enabled and running.
When on a pc from outside our network, I try to create a system ODBC DSN, I am using for the server name tcp:xx.xxx.xx.xx,1433.
I use sql server authentication and hit next.
I then try to change the default database. I am getting an error message.
"Connection failed. SQLState '08001'. SQL Server Error: 258 The TCP provider wait operation timed out."
Does anyone have a thought or an idea of why we can't query our server over the internet?
Thanks.
Things will work out. Get back up, change some parameters and recode.
February 2, 2017 at 11:17 am
WebTechie - Thursday, February 2, 2017 10:51 AMHello,We are working on a migration project. I need to have an ODBC DSN connection available to Microsoft AX deployment.
I have opened the ports of the local firewall.
I am using the public IP for the server.
The network guys have opened the firewall at the router.
I've created a sql server login.
I've made the login a system admin
The protocols are enabled.
The server is configured for remote connections.
SQL Server browser is enabled and running.When on a pc from outside our network, I try to create a system ODBC DSN, I am using for the server name tcp:xx.xxx.xx.xx,1433.
I use sql server authentication and hit next.
I then try to change the default database. I am getting an error message."Connection failed. SQLState '08001'. SQL Server Error: 258 The TCP provider wait operation timed out."
Does anyone have a thought or an idea of why we can't query our server over the internet?
Thanks.
Couple of things to try would be -
On that same PC have you tried pinging the server?
Did you try a telnet session using the same port?
Sue
February 2, 2017 at 12:26 pm
WebTechie - Thursday, February 2, 2017 10:51 AMHello,We are working on a migration project. I need to have an ODBC DSN connection available to Microsoft AX deployment.
I have opened the ports of the local firewall.
I am using the public IP for the server.
The network guys have opened the firewall at the router.
I've created a sql server login.
I've made the login a system admin
The protocols are enabled.
The server is configured for remote connections.
SQL Server browser is enabled and running.When on a pc from outside our network, I try to create a system ODBC DSN, I am using for the server name tcp:xx.xxx.xx.xx,1433.
I use sql server authentication and hit next.
I then try to change the default database. I am getting an error message."Connection failed. SQLState '08001'. SQL Server Error: 258 The TCP provider wait operation timed out."
Does anyone have a thought or an idea of why we can't query our server over the internet?
Thanks.
Another thing to try would be can you connect from inside the network using the same server name and performing the same steps?
It could be something blocking it on the other end.
If it fails internally as well, I would double check that you have TCP/IP connections turned on in the SQL configuration manager.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 2, 2017 at 12:28 pm
Sue,
I was able to ping the server from the pc.
I am testing telnet now.
Thanks.
Things will work out. Get back up, change some parameters and recode.
February 2, 2017 at 1:33 pm
I tried connecting inside the network with the same public IP. That didn't work.
I checked all of the TCP protocols and all are enabled.
I am thinking there is something with the network and the firewall. I've asked the system admins to check for me.
Thanks.
Things will work out. Get back up, change some parameters and recode.
February 2, 2017 at 1:46 pm
WebTechie - Thursday, February 2, 2017 1:33 PMI tried connecting inside the network with the same public IP. That didn't work.I checked all of the TCP protocols and all are enabled.
I am thinking there is something with the network and the firewall. I've asked the system admins to check for me.
Thanks.
Absolutely agree. You did a great job of already hitting the "usual" issues with this that aren't network related.
Maybe look at doing a network trace and see where things are breaking down. Or hopefully the sysadmin can at least trace and see if you are even hitting the network at all.
Sue
February 3, 2017 at 8:23 am
WebTechie - Thursday, February 2, 2017 10:51 AMDoes anyone have a thought or an idea of why we can't query our server over the internet?Thanks.
Um, just one thing...
If you can connect to it over the internet (which is what you want), what's to prevent all sorts of random hacking scripts from all over the world trying to connect to your database?
I hope your sa password is very strong (or sa is disabled) and that there's no data of value in there.
Last time I saw a SQL server open on the internet, it was getting roughly 20 login attempts a second from various IP addresses that were not in my country.
I strongly recommend you sort out a VPN or similar for the connections you need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2017 at 8:55 am
Sue_H - Thursday, February 2, 2017 1:46 PMWebTechie - Thursday, February 2, 2017 1:33 PMI tried connecting inside the network with the same public IP. That didn't work.I checked all of the TCP protocols and all are enabled.
I am thinking there is something with the network and the firewall. I've asked the system admins to check for me.
Thanks.
Absolutely agree. You did a great job of already hitting the "usual" issues with this that aren't network related.
Maybe look at doing a network trace and see where things are breaking down. Or hopefully the sysadmin can at least trace and see if you are even hitting the network at all.Sue
As Sue suggested, have you tried running a network trace?
you can do this from the command prompt with "tracert <ip address>" and you can see where the hops are going.
And possibly a dumb question, but do you have your local firewall set to allow outgoing connections to the appropriate ports?
Although, as GilaMonster pointed out, this is a big security risk making it visible to the outside world. Might want to talk to your Microsoft AX vendor and see if there is another solution such as backing up the DB and dropping it onto AZURE?
Also, this is probably another dumb question, but are you sure that is the correct port? I think (but could easily be wrong) by default SQL 2008 R2 and higher likes to use dynamic ports instead of static ones. Did you configure it for a static port?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 3, 2017 at 2:43 pm
bmg002 - Friday, February 3, 2017 8:55 AMAlso, this is probably another dumb question, but are you sure that is the correct port? I think (but could easily be wrong) by default SQL 2008 R2 and higher likes to use dynamic ports instead of static ones. Did you configure it for a static port?
Default instance uses 1433 in all versions (unless configured otherwise), named instances use dynamic ports.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply