How to connect to a SQL Anywhere database running in the cloud?

[fvestjens]

A new customer is running our database server in the cloud on a MS Azure machine. I tried to connect to the database from another MS Azure machine using ODBC using the following settings in the ODBC driver

• Connect to a running database in a cloud
• Host: servername.cloudapp.net
• Port: 8443
• Database name: DCFusion

Port 8443 But for some reason I'm not able to connect. I even tried it from my local PC using ODBC with the same settings.

• Are these settings correct?
• Is there something special that needs to be setup on the database side?
• Is there something special that needs to be done on the client side?
• Should it be possible to ping the database from within Sybase Central?
• Is there a way to check if everything on the server/client side is correctly setup?

TIA,

Frank

[Breck_Carter]

What is the exact error message you are getting? ..."not able to connect" is not informative, since there are many different explanations for that.

Have you checked to see if there is any firewall software on your computer that is preventing the port from being used?

Port 8443 is often used as an alternative for port 443 which is usually used for https... are you expecting to communicate via https whilst using ODBC?

[fvestjens]

I had the service running on the default port 2638. I'm able to connect to the server on port 8443 using remote desktop.

I changed the service to use the same port 8443 and then the error I get is

Connection failed: Connection error: Timeout occurred while waiting for connection response.

When the service was running on 2638 and I used 2638 as the port number in the ODBC settings I get error

Connection failed: Database server not found.

Btw I have no settings in the Network tab or Security tab set.

The settings in the service are:

-ti 0 -x tcpip(port=8443) -c 1G "E:\\Test\\DCFusion.db"

[Breck_Carter]

Remote desktop is a GUI application, ODBC is a data transmission application, what makes you think they can share the same port? 🙂

Try going back to 2638.

Then show us the exact client-side connection string you are using...

...it probably should look something like this: ENG=DCFusion; DBN=DCFusion; UID=whatever; PWD=whatever; LINKS=TCPIP(HOST=123.123.123.123:2638;DOBROAD=NONE); Driver=SQL Anywhere 17;

(Note that you don't need to mess around with DSNs if you just add the DRIVER= parameter to the connection string)

[Vlad]

how can you change the DB port to 8443, if there is the remote desktop server, who has been "bound" to the port already.

plus it is unsafe to forward / open DB ports to the world wide. SqlAnywhere is constantly (hopefully) improved in terms of security and stability, but still... this is a bad practice. At least, I'd suggest to open the VPN tunnel to your cloud machine.

I shouldn't google for you, but here is the start: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/nsg-quickstart-portal

[Breck_Carter]

...or use the Encryption (ENC) connection parameter.

[Vlad]

That's true, but I am always afraid of exposing any backend port. Regardless if it is encrypted or not. It is easy to start trying to use it. Web servers always have some layer before the application server. E.g. nginx or Apache HTTPd.

It is like opening your HTTPS (sounds secure, isn't it) port of your router, so you can configure it from the outside.

For those kind of things, I prefer either SSH-tunneling, or VPN.

Of course, if VPN server fails, nothing in this world will save you 😞

By the way, I didn't think about it, but maybe Relay Server is a good candidate for such a kind of configuration.

[fvestjens]

I installed SQL Central on the application server to be able to log what is wrong.

This is the response I get.

Tue Aug 21 2018 09:23:47
09:23:47 Attempting to connect using:
UID=usr;PWD=********;DBN=DCFusion_Test;CON=SQL_DBC_a36ba27280;LOG=C:\\Users\\FVESTJ~1\\AppData\\Local\\Temp\\3\\testcon2127381434744064064.tmp;ASTART=NO;Host=amsdcf01.cloudapp.net:2638
09:23:47 Attempting to connect to a running server...
09:23:47 Attempting TCPIP connection (no sasrv.ini cached address)
09:23:48 Looking for no name
09:23:48 Trying to find server at address 51.145.157.243:2638
09:23:53 TCP/IP link, function connect(), error code 10060
09:23:53 A HOST value was specified, skipping LDAP check
09:23:53 Failed to connect over TCPIP
09:23:53 Not attempting to autostart server
09:23:53 Cannot connect to server

[Breck_Carter]

You are experiencing a connection problem, like one thousand million folks before you.

...it may seem like every problem is different, but it's not that bad.

...there are probably only 100 million different problems 🙂

=====

Please show us the exact options used to start the server.

Your connection string might need a SERVER= value, and a DRIVER= value.

The host exists, at least as far as DNS is concerned, but a simple ping (see below) gives a timeout (that may not be an issue).

A dbping test (see below) probably finds the host, but not the server; I took a guess at DCFusion_Test being the dynamically-assigned SQL Anywhere server name, and that is probably not correct.

===== ping test

ping amsdcf01.cloudapp.net

Microsoft Windows [Version 10.0.17134.228]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\\Users\\Breck>ping amsdcf01.cloudapp.net

Pinging amsdcf01.cloudapp.net [51.145.157.243] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

===== dbping test

REM -d   Test connecting to the database
REM -m   Connect via ODBC
REM -o   Write diagnostic messages to a file

"%SQLANY17%\\Bin64\\dbping.exe"^
  -c"SERVER=DCFusion_Test; DBN=DCFusion_Test; UID=dba; PWD=sql; LINKS=TCPIP(HOST=amsdcf01.cloudapp.net:2638); DRIVER=SQL Anywhere 17;"^
  -d^
  -m^
  -o "C:\\temp\\dbping_messages.txt"

PAUSE

SQL Anywhere Server Ping Utility Version 17.0.9.4803
Loaded ODBC driver ODBC32.DLL
Ping database failed -- [SAP][ODBC Driver][SQL Anywhere]Database server not found

[Breck_Carter]

There is no better protection than being unable to connect 🙂

[fvestjens]

The exact settings are:

-ti 0

-x tcpip(port=2638)

-c 1G

"E:\\Test\\DCFusion_TEST.db"

[Breck_Carter]

The following dbping (see below) used dblib instead of ODBC, and the -z option to increase the level of diagnostics.

The error message "TCP/IP link, function connect(), error code 10060" is strange (to me)... perhaps the following link will help:

https://support.microsoft.com/en-us/help/191143/10060-connection-timed-out-error-with-proxy-server-o...

A Google search for 10060 yields MANY suggestions involving registry fixes ( egad! 🙂

HOWEVER, dbping kept going and eventually resulted in the generic "Database server not found".

I would bet (a small amount of) money on the reason being a firewall not allowing port 2638. Perhaps you could pick another port... but use the SAME value on both server and client, and DO NOT pick a value actively used by something else.

Here is a link to Ground Zero for port assignments: https://www.iana.org/assignments/service-names-port-numbers/service-names-port-numbers.txt

49152 is a popular choice, as the first of many open Wild West ports 🙂

REM -d   Test connecting to the database
REM -m   Connect via ODBC
REM -o   Write diagnostic messages to a file
REM -z   Include diagnostics

"%SQLANY17%\\Bin64\\dbping.exe"^
  -c"SERVER=DCFusion_TEST; DBN=DCFusion_TEST; UID=dba; PWD=sql; LINKS=TCPIP(HOST=amsdcf01.cloudapp.net:2638);"^
  -d^
  -o "C:\\temp\\dbping_dblib_messages.txt"^
  -z

PAUSE

SQL Anywhere Server Ping Utility Version 17.0.9.4803
Attempting to connect using:
UID=dba;PWD=********;DBN=DCFusion_TEST;ServerName=DCFusion_TEST;LINKS='TCPIP(HOST=amsdcf01.cloudapp.net:2638)'
Attempting to connect to a running server...
Trying to start TCPIP link ...
TCP using Winsock version 2.2
My IP address is 192.168.0.30
My IP address is ::1
My IP address is 127.0.0.1
    TCPIP link started successfully
Attempting TCPIP connection (no sasrv.ini cached address)
Looking for server with name dcfusion_test
Trying to find server at address 51.145.157.243:2638
TCP/IP link, function connect(), error code 10060
A HOST value was specified, skipping LDAP check
Sending broadcast to find server
Using broadcast address of: 51.145.157.243:2638
Resending
Resending
Resending
Resending
    Server not found (no reply received)
Failed to connect over TCPIP
Removing failed address from sasrv.ini cache
Not attempting to autostart server
Cannot connect to server
Ping database failed -- Database server not found.

[Vlad]

ahahaha, you are right, yeah. Here is the list of servers where SqlAnywhere was found. Who knows, maybe the port 2638 dba/sql is still open:

https://www.shodan.io/search?query=Server%3A+SQLAnywhere

In Canada there are 9. Maybe one of them is yours.

[Breck_Carter]

That shodan page seems to be displaying HTTP ports 8080, HTTPS, etcetera... perhaps they are all HTTP servers running inside SQL Anywhere (like this forum, and Foxhound when folks put it out on the web with HTTPS).

There are hackers out there that continuously test all 65,000 ports on each ip address, but I'm not sure what they do when they find an open port 2638... try to login, perhaps? do a DOS attack? move on to something easier?

[fvestjens]

We solved the issue by using the internal ip addres of the servers to connect to each other. That way we don't need to connect using the cloud setting but we can use connecting to a database on another server.

That seems to work.

Thanks for all the input

[Breck_Carter]

It's amazing what the right IP address can accomplish 🙂

By "internal" I presume you mean 192.whatever, or was it some "real" but "secret" IP address different from the one you were using 51.145.157.243?

[fvestjens]

Yes, they have internal IP numbers on which both servers can conect to each other and the 51. are the public numbers