Android Question MSSQL RDC Connection settings

[jkhazraji]

if you have a SQL database residing on a remote website (NOT local) and you have the MS SQL Server address= 'dbService.mssql.site.com' and user name ='defaultuser' and password='Anypass' what will be the lines in config.properties file to achieve an RDC connection through 'RunRLC.bat' to run the server. I do not want the server to run on a local machine since my database is remote and opening the server port is a heck of problem.
My confusion is really that: If I want my android app to connect to a remote database why would I use a database that resides on a PC. How would the app find the address?

 

[aeric]

I think you are using the old RDC.
Why don't you use jRDC2?

 

[jkhazraji]

I think in this case, the server should be hosted on the machine of the remote database and run from there, which is almost impossible.?

 

[aeric]

I think in this case, the server should be hosted on the machine of the remote database and run from there, which is almost impossible.?

I don't get you.
Where is your database hosted? Is it in a shared hosting or a VPS?
Do you want to separate the database server and the jRDC server?

 

[jkhazraji]

I don't get you.
Where is your database hosted? Is it in a shared hosting or a VPS?

My database is hosted in a shared hosting (free plan) (www.somee.com)

Do you want to separate the database server and the jRDC server?

Yes.

 

[aeric]

My database is hosted in a shared hosting (free plan) (www.somee.com)

You can't use RDC or jRDC2 in shared hosting. You don't have access to install Java and run command prompt.

Your options are:
1. Develop your web service in ASP.NET / PHP (or other language) that can be hosted in shared hosting
2. Upgrade to paid VPS server (to use jRDC2) - there are some cheap VPS available
3. Host in your local machine

 

[jkhazraji]

You can't use RDC or jRDC2 in shared hosting. You don't have access to install Java and run command prompt.

Thanks a lot for your time and patience ? ?

Your options are:
1. Develop your web service in ASP.NET / PHP (or other language) that can be hosted in shared hosting

I did that already and succeeded.

2. Upgrade to paid VPS server (to use jRDC2) - there are some cheap VPS available

That is the real point but can you show me some cheap ones?

3. Host in your local machine

That was also within my reach

But I think hosting on local machine is only for test or debug purposes not real life apps

 

[aeric]

can you show me some cheap ones?

I use hostinger ($3.95/mo) but it is Linux server where you can install MySQL/Mariadb (or maybe PostgreSQL)

 

[jkhazraji]

I use hostinger ($3.95/mo) but it is Linux server where you can install MySQL/Mariadb (or maybe PostgreSQL)

Yes I am using it too, but I think it can not host a MS SQL database only MySql/MariaDB and the alike.

 

[aeric]

Yes I am using it too, but I think it can not host a MS SQL database only MySql/MariaDB and the alike.

You may try to install Express (free) version

SQL Server on Linux FAQ - SQL Server

This article provides answers to frequently asked questions (FAQ) about SQL Server running on Linux.

docs.microsoft.com

 

[aeric]

I use hostinger ($3.95/mo) but it is Linux server where you can install MySQL/Mariadb (or maybe PostgreSQL)

Hostinger also offer Windows VPS starting from $26/month

 

[aeric]

Hostinger also offer Windows VPS starting from $26/month

I think they are no longer offering Windows VPS plan.

 

[Albert Kallal]

Yes I am using it too, but I think it can not host a MS SQL database only MySql/MariaDB and the alike.

Just keep in mind that while MANY web sites offer very low cost SQL server or MySQL server plans? VERY few allow direct external connections to the database. The reason of course is security. years ago, I installed SQL express (actually it was even the older free SQL server (CTE) edition. I then opened up a ports on my home router. In less then 30 minutes I was seeing internet bots and attempted logons:

user=sa; password = 1234
user=sa;password =  password
etc. etc.

That was in 2004!!!!! Today, there are 10x or 100x times the number of bots that scan for open SQL,, or MySQL ports on the internet.
So while tons of plans allows and has the web hosted site to connect to the database that is also hosted? Well, sure, that connection occurs ON the web site to the database - and that occurs local behind the firewalls. This setup thus does not allow some external connect via open ports from the wild and crazy internet to connect.

Quite a few providers 10+ years ago did allow say ODBC or external connections - now most don't. So you get around this by building + setting up a web service on the web site which then in turn can connect to and talk to the database.

I use jdbc direct all the time to SQL server on my local network - it is nice and great since I don't need any web server or anything at all. I run just the jtds driver, and thus from Android/B4A, I direct connect. However, for this kind of setup to work off site, or say to a database on a web server? Well then you need some kind of bridge or software running on that site that exposes the data via web methods - no direct connection to the database is allowed.

edit: now having stated the above? well, hosted sql server is now coming back into voge again. SQL Azure is one such example. In these cases, they get around the open ports by limited the incoming connection to a KNOWN IP address (like say your company network). So in these cases, if you VPN into that company network - even while on the road, your IP to sql server will appear to come from the company ID address - and thus you can use that hosted instance of SQL server. So, while i stated that wide open ports and ODBC connections to hosted sql server is long gone? Well, it is and has made a big comeback due to SQL Azure, and now other companies offering just database servers and services without even having a web site hosting plan.


R
Albert

 

[Bladimir Silva Toro]

Hello @jkhazraji

Well the most practical solution in your case is the use of jRDC which you can do through a VPS, you can get cheap VPS on Linux and install SQL Server, I have tried it using Ubuntu 18 and SQL Server 2014 Express.

I personally use the DigitalOcean VPS (https://www.digitalocean.com/pricing/calculator/), the lowest price is 5 dollars a month. View photo.

Another solution that you can use with the hosting you have would be to use WebServices sharing data through JSON.

Cordially

Bladimir Silva Toro

 

[jkhazraji]

Just keep in mind that while MANY web sites offer very low cost SQL server or MySQL server plans? VERY few allow direct external connections to the database. The reason of course is security. years ago, I installed SQL express (actually it was even the older free SQL server (CTE) edition. I then opened up a ports on my home router. In less then 30 minutes I was seeing internet bots and attempted logons:

user=sa; password = 1234
user=sa;password =  password
etc. etc.

That was in 2004!!!!! Today, there are 10x or 100x times the number of bots that scan for open SQL,, or MySQL ports on the internet.
So while tons of plans allows and has the web hosted site to connect to the database that is also hosted? Well, sure, that connection occurs ON the web site to the database - and that occurs local behind the firewalls. This setup thus does not allow some external connect via open ports from the wild and crazy internet to connect.

Quite a few providers 10+ years ago did allow say ODBC or external connections - now most don't. So you get around this by building + setting up a web service on the web site which then in turn can connect to and talk to the database.

Thanks for you valuable approach, I agree with you that opening ports on a home machine is very vulnerable to bots attacks. This is a price to pay in order to connect to you remote SQL database. Building a web service is a reasonable solution that I used recently by developing and publishing an ASP.NET file to do the task.

I use jdbc direct all the time to SQL server on my local network - it is nice and great since I don't need any web server or anything at all. I run just the jtds driver, and thus from Android/B4A, I direct connect. However, for this kind of setup to work off site, or say to a database on a web server? Well then you need some kind of bridge or software running on that site that exposes the data via web methods - no direct connection to the database is allowed.

Yes but for Android solutions, the use of local network will greatly limit them, so if you want a user to communicate with your app in this case, you need to open a port again.

edit: now having stated the above? well, hosted sql server is now coming back into voge again. SQL Azure is one such example. In these cases, they get around the open ports by limited the incoming connection to a KNOWN IP address (like say your company network). So in these cases, if you VPN into that company network - even while on the road, your IP to sql server will appear to come from the company ID address - and thus you can use that hosted instance of SQL server. So, while i stated that wide open ports and ODBC connections to hosted sql server is long gone? Well, it is and has made a big comeback due to SQL Azure, and now other companies offering just database servers and services without even having a web site hosting plan.


R
Albert

I agree. SQL Azure is available directly from Visual Studio IDE and you can run your app and database connection with a secure Microsoft account but of course it is paid.

 

[jkhazraji]

Hello @jkhazraji

Well the most practical solution in your case is the use of jRDC which you can do through a VPS, you can get cheap VPS on Linux and install SQL Server, I have tried it using Ubuntu 18 and SQL Server 2014 Express.

I personally use the DigitalOcean VPS (https://www.digitalocean.com/pricing/calculator/), the lowest price is 5 dollars a month. View photo.

View attachment 112152

Another solution that you can use with the hosting you have would be to use WebServices sharing data through JSON.

Cordially

Bladimir Silva Toro

Thanks a lot for your reply. Well, the site you mentioned offers reasonable price. For now, I prefer to use the second solution. I am using the free plan of a site which also has paid plans for MSSQL hosting and VPS. The task of VPS is still to protect your IP and put the port out of reach to the others.
Regards

 

[Albert Kallal]

Yes but for Android solutions, the use of local network will greatly limit them, so if you want a user to communicate with your app in this case, you need to open a port again.

Actually, no, I don't. My phone/app is communicating with SQL server exposed on that network.

And those instances of SQL server being exposed on that network is a requirement for all computers to run on that network. That applies to MySQL, or any database server. I mean, if the database server is not exposed to the local network, then how can any of the workstations run their software such as accounting systems etc. that consume their data from that database. So on a LAN (local area network), then in my experience, such database servers have ALWAYS been open to any connection from any workstation.

In the case then of adopting the jtds drivers and directly connecting from Android? I did not have to open up anything, or configure anything different then the typical need + requirement that ANY database server would and does require.

And I can EASY use the app off site (but i don't need to). How? Why of course you run a VPN. After all, all a VPN is allow workers on the road or at home to simply become part of that network. Once you do this, (connect with VPN), then zero changes to my B4A software is required. The IP address of the SQL server simply works. So I can connect with great ease. As long as the phone has some kind of VPN software then using WI-fi or the data plan? The connection to the database server works seamless.

So sure, while the database server needs ports open? Well, they quite much do in all cases if any other computers, workstations etc. on that network is going to use that instance of sql server.

So, I just wanted to point out that no extra or specials ports have to be open for such direct connections. But of course in all cases from workstations say using Excel to pull data from SQL server or whatever? Well, yes, that database has to be exposed to the network. So having to open ports applies to any and all database servers, and if no such ports are open, then most desktop software not going to be able to use that sql server instance on that network.

I have found that if SQL server is up and running on that network, and you using SQL server logons (as opposed to windows authentication logons), then the jtds drivers with B4A work seamless, and NO additional configuration of the network, or the server or anything is required. All that is required is that B4A running on the phone be part of that network and it can use that IP address of the SQL server in question. And as noted, if you have VPN setup (and most companies do), then if you have a supported VPN client on your android? Launch that first - connect. Now my B4A app again connects directly to the SQL server - no extra ports, or even any setup on the phone side, or the server side + network is or will be required. The only requirement is that your phone is part of the LAN - and a VPN will solve this requirement.

Having stated all the above? I only used a direct connection since the direct connection time I use is VERY small, and often not even once a day. I have a sqlite local database, and my software works against that. I then wrote a sqlite to sql server sync set of routines (they handle PK/FK and the issues of autonumber PK keys between phone side and server side. New updates, new records added sql server side thus sync down to client side, and any new updates, changes, additions on the client side are synced up to sql server. So this is a bi-directional sync. i find that the sync takes less then 2 seconds, and as noted, I only need to do this once a day, or even once every 2-3 days. So perhaps you mean the phone has to open its connection to talk to the sql server - but my application is not constantly doing this - and for apps that require a constant pull of information from a server? Then jtds direct is a VERY bad idea, and is not all that reliable. So in this case - yes, having a port open not really the problem, but having a open direct connection all the time is MOST certainly a problem. They time out - many other issues.

As noted, hosted SQL systems often restrict by IP address. So you in those cases have to use a VPN to appear that your connecting from your work IP address/location.

R
Albert

 

[jkhazraji]

Actually, no, I don't. My phone/app is communicating with SQL server exposed on that network.

And those instances of SQL server being exposed on that network is a requirement for all computers to run on that network. That applies to MySQL, or any database server. I mean, if the database server is not exposed to the local network, then how can any of the workstations run their software such as accounting systems etc. that consume their data from that database. So on a LAN (local area network), then in my experience, such database servers have ALWAYS been open to any connection from any workstation.

In the case then of adopting the jtds drivers and directly connecting from Android? I did not have to open up anything, or configure anything different then the typical need + requirement that ANY database server would and does require.

And I can EASY use the app off site (but i don't need to). How? Why of course you run a VPN. After all, all a VPN is allow workers on the road or at home to simply become part of that network. Once you do this, (connect with VPN), then zero changes to my B4A software is required. The IP address of the SQL server simply works. So I can connect with great ease. As long as the phone has some kind of VPN software then using WI-fi or the data plan? The connection to the database server works seamless.

So sure, while the database server needs ports open? Well, they quite much do in all cases if any other computers, workstations etc. on that network is going to use that instance of sql server.

So, I just wanted to point out that no extra or specials ports have to be open for such direct connections. But of course in all cases from workstations say using Excel to pull data from SQL server or whatever? Well, yes, that database has to be exposed to the network. So having to open ports applies to any and all database servers, and if no such ports are open, then most desktop software not going to be able to use that sql server instance on that network.

I have found that if SQL server is up and running on that network, and you using SQL server logons (as opposed to windows authentication logons), then the jtds drivers with B4A work seamless, and NO additional configuration of the network, or the server or anything is required. All that is required is that B4A running on the phone be part of that network and it can use that IP address of the SQL server in question. And as noted, if you have VPN setup (and most companies do), then if you have a supported VPN client on your android? Launch that first - connect. Now my B4A app again connects directly to the SQL server - no extra ports, or even any setup on the phone side, or the server side + network is or will be required. The only requirement is that your phone is part of the LAN - and a VPN will solve this requirement.

Having stated all the above? I only used a direct connection since the direct connection time I use is VERY small, and often not even once a day. I have a sqlite local database, and my software works against that. I then wrote a sqlite to sql server sync set of routines (they handle PK/FK and the issues of autonumber PK keys between phone side and server side. New updates, new records added sql server side thus sync down to client side, and any new updates, changes, additions on the client side are synced up to sql server. So this is a bi-directional sync. i find that the sync takes less then 2 seconds, and as noted, I only need to do this once a day, or even once every 2-3 days. So perhaps you mean the phone has to open its connection to talk to the sql server - but my application is not constantly doing this - and for apps that require a constant pull of information from a server? Then jtds direct is a VERY bad idea, and is not all that reliable. So in this case - yes, having a port open not really the problem, but having a open direct connection all the time is MOST certainly a problem. They time out - many other issues.

As noted, hosted SQL systems often restrict by IP address. So you in those cases have to use a VPN to appear that your connecting from your work IP address/location.

R
Albert

It is interesting that you can achieve your own solutions on a local network, but what kind of services do you usually run with a phone that is connected to a LAN ? How could you serve your clients who are not on the same LAN as you?

 

[Albert Kallal]

How could you serve your clients who are not on the same LAN as you?

You are 100% correct. This class of solutions and problems thus only makes sense for a company location. Information about parts, projects etc. I mean every single workflow station in both offices and on the floor? The worksheets have bar codes on them. With B4A in less then one hour - I cobbled together a bar code scan - you point it at that sheet laying on that table? Poof - like magic I have all the project information and details displayed on my phone. And I did not even have to setup a web server or anything for this to work.

So sure, for a WAN (wide area), such ideas and concepts don't make sense. For for a local environment - say a typical business with people doing tasks in both office and on the floor? From shipping to all kinds of examples? Really amazing solutions can be cooked up - and often in just a few hours - since no web services or a web server has to be setup here. So, of limited use? Yup - I'll admit that.

But then again, there are 100's of use cases for such setups - and they are beyond easy to cook up with B4A. Once I have that simple connection to the production servers running SQL server? Then that information can be pulled and displayed. Often with just a FEW lines of B4A code. It is limited, but then again, it also beyond ease to do this.

So, for a general phone app - wide area use? Nah - not much good and in fact a bad idea. But for a local network and business? Testing proof of concepts? Beyond amazing and beyond easy.

Regards,
Albert D. Kallal
Edmonton, Alberta, Canada