Connection to SQL Server Best Practices

[stanlyn]

Hi,

Using VFP9sp2 and MSSQL2016.

What is the best practice in connecting a VFP app to a SQL backend while NOT exposing the SQL Server machine to the outside world? The goal is for the SQL server to be completely hidden from the wan. If using a web service on a different machine is acceptable, then what steps needs to be done to get this working from VFP? What special hoops do I need to jump thru to get "cursor adapters", remote views, or pass-thru to work? Are there better ways? Please explain, or white pages/articles.

In the past, we exposed sql server to the wan, but had all ips blocked except for a white list. Now, we are changing it so everyone is on the white list, until they get blacklisted. The public facing Server 2016 IIS machine is secured with a SSL Cert and can host the service, if this is best practice.

Assuming a web service is best, where do I learn how to make all the connections?

Thanks,
Stanley

 

[Olaf Doschke]

That's a bad idea.

To hide a backend via a layer of a web service means, that your endpoints use it and make soap requests. These send over XML, and a cursoradapter can also attach to XML via DataSourceType XML, but then will disregard its DataSource.

Not being capable to use the DataSource in DataSourceType XML means the cursoradapter itself won't fetch XML or send back necessary commands triggered by a TableUpdate() on the cursor.
You instead have to make the web service requests in a separate class for the cursoradapter and provide the XML to it, later creating/providing XMLUpdategrams to go back to MSSQL. This ends up getting as complex as using SQL passthrough with insert/select/update/delete instead of Select-SQL only and TableUpdate() creating the necessary insert/update/delete commands. Cursoradapter has the capability to compute the XML UpdateGram, but that's only one-way, towards the remote backend, it won't apply an UpdateGram you could fetch from MSSQL to the local cursor as a mean of refreshing data without refetching all of it.

In a situation with a WAN with slow connections between affiliate locations, you shouldn't make the cut between application and a central database only available fast for the headquarter location. You either use replication to distribute data, and each location will have its own SQL Server instance it can connect to locally through LAN only, and only replication is done via WAN, or you put the whole application and data on terminal servers in the headquarter and let other locations use the entire software remotely, not just the database server and surely not via the additional hoop of a web service. A third option is hosting the data in a cloud, so every location again connects to it via its standard internet connection to an Azure SQL or similar. You can protect data transfer with SSL connections, but you trust your business data to cloud providers just like any webshop. It wouldn't be unusual, companies do that and outsource their IT hardware to a cloud, not only for having global access, also to spare the hardware administration.

A slow WAN connection shouldn't handle all remote clients requests, that's too much traffic, no matter if that's done with a direct connection not needing coding changes or via a Webservice. When you replicate data with a WAN, only changes are traveling the WAN and you might even limit this when you don't sync any data only necessary locally, replication can be fine adjusted and not just let each replicated node be a 1:1 replication of a master node. And moving to the cloud means making use of your internet connection and fast internet connections are available almost anywhere to make this the viable option.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

Let me explain our scenario a bit more.

1. We have a backend SQL database on a separate machine only visible from the LAN. We do not want any SQL direct access from the WAN.

2. We also have an IIS web server on a different machine that is attached to both the WAN and the same LAN as the SQL server is on. The LAN is only private to the two machines, as the business LAN is on its own LAN and separate from the SQL to IIS lan link.

3. From their desktops, subscription based users will be running a fat client version of our software and will be connecting to the sql backend data indirectly, and hosted at our location, (we are their cloud).

4. The sql data represents about 80GB and far too much for a local copy to be pushed out to all connected users. These clients may be short term clients and we would never want our data on their machines except for a very limited batch where max records for a select statement can be enforced. We only want to fetch data to the workstation based on a select statement, where we will finish its processing via local cursors, while pushing back to the server things like log entries and such.

5. A potential customer will purchase a subscription from our website and be given a link for the fat client software which is downloaded and installed onto their pc along with login credentials. At this point we do not want to install any vpn, ssl certificate, or any other software on their machine. The client must be able to connect to the backend data preferably with sql selects and tableupdate().

6. In past years, we've tried the Remote desktop solutions and had issues, mainly with printing. We are working on a browser version, which is taking far too long, loosing a lot of functionality, and we need this asap. We already have a fat client version that connects to local vfp data, and it won't take much to switch the backend to sql as currently it loads the vfp data into local cursors and displayed from there. This solution provides the exact same look and feel the customer is already accustomed to from our kiosk fat clients.

7. You mentioned above Azure based solutions, which is not an option for us, but wondering how would a connection from the fat client be made to it? Would it be connecting directly to Azure SQL or is there a man in the middle?

8. What do you consider a slow WAN connection speed to be?

9. My questions... with all the above in mind,
a. how is the best and easiest way to connect to sql server data that is only directly available from the LAN?
b. Is there other ways to secure a SQL server that allows direct access to it allowing tableupdate() to work without having to install vpns or some other security device?

Thanks,
Stanley

 

[Nigel Gomm]

just about everyone will advise against exposing a sql server to the internet...

that said if you need a fat vfp client to use standard sql functions (tableupdate and the like) you may well have to.

disable sa account
use a different port for your inward connections (i.e. not the default 1433) and block port 1433 in the firewall
create a trigger (or buy a utility ) to blacklist an ip (and/or its subnet) after n failed login attempts


this turns up in a google search

https://rdpguard.com/mssql-server-protection.aspx

(i know no more than this but seems to address the problem)

i'm in a similar situation so very interested in what others have to say...

n

 

[Olaf Doschke]

I wouldn't redflag opening up a SQL Server, but sure I'd rather use something a cloud provider offers, as they have enough experience in hardening the security of it.

The replication scenarion is aiming for a company WAN with trusted users, yes it's essential to state you have untrusted client users (even if you trust users in general, they aren't having contracts like employees). Still, it's not something to easily solve with a web service interface to the data.

Webservices and APIs are interesting as basisof future development, but nothing to retrofit. The client application should rather be HTML than fat desktop clients.

Bye, Olaf.

 

[stanlyn]

Hi,

just about everyone will advise against exposing a sql server to the internet...

I agree and that is what I'm trying to do, but looks like all the modern vfp tooling (cursoradapters, pass-thru, tableupdate, and such are not fully and easily supported). Actually, I thought web services would be the way to go, but according to Olaf, they are not as they only provide half of it.

How has all the vfp'ers been doing this, with all the sql migration tools and such? I surely thought they've come up with a secure and accepted way of accessing sql server using the vfp toolset by now.

Is everyone only allowing trusted user access?

Realistically, would implementing Nigel's suggestions along with rdpguard provide safety for the sql data and machine?

Thanks,
Stanley

 

[Olaf Doschke]

>Is everyone only allowing trusted user access?

Indeed I have only worked on company software with trusted users so far. But you underestimate what SQL Server offers in restricting the permissions and privileges of connected users. Having a connection to a server doesn't already make you an administrator of it.

You also maybe misunderstand what a connection with "trusted user" means. SQL Server doesn't blindly accept these connections from everyone and trusts users, a trusted user is a user, that is trusted on the OS level from his identity with a Microsoft Windows account (SID), and this is checked with all the security of Windows Login. The Server knows, who's connected and does restrict access the way users are granted or not.

I think your problem goes deeper than that, doesn't it? You want to prevent users from getting at all tables data, but in principle, your application needs that. So you're unsure about how to grant access to a table and yet only to say a rate limit. Limited amount of data per day.

>How has all the vfp'ers been doing this, with all the sql migration tools and such?
I never had that necessity.

When you program web applications you have the situation only your web server has database access, this is mainly how that is encapsulated, you don't have a fat client application. You can do that and only have a cloud database access, but then you typically serve it quite freely and with a database per customer. But since that's only having his data there's not much of a problem, is there?

Bye, Olaf.

 

[stanlyn]

Hi Olaf,

I already know what trusted user access is and how to implement it, with and without AD and/or certificates. I'm also familiar of the power and granularity that sql server has. I was asking how to securely connect a vfp fat client to sql server without exposing sql server and how each of vfp's technologies needs to be setup using best practices to achieve this. I also know that hiding it behind a web server gives us the best protection, but if vfp cannot access it then we go to plan B.

I've already mentioned that our needs is securing sql server as we allow un-trusted users access to its data. That was why I first mentioned using a web service, which you immediately said "bad idea". OK, I get and respect that, but I'm no closer on a direction for solving this problem. In my previous posts above, I described the needed layout is much like what you said "web server only has access, which is where the idea of a web service came from.

Using someone else's cloud is not an option either. So here is what we know,

1. data is hosted in our data center,
2. sql server on separate machine
3. web server on different machine
4. no AD (active directory)
5. allow un-trusted users access
6. will connect on non-standard port
7. authenticate using non standard sql user account
8. sa is disabled
9. super strong password
10. can use a domain lever ssl cert that the web servers are using,
11. can use 3rd party software like Nigel pointed out that blocks attacks
12. would really like to use vfp's tooling, (sql pass-thru and cursor adapters)

I'm really surprised that so few vfp'ers have chimed in, as figured this was old school knowledge that was solved years ago.
Guess not... Just maybe no one is doing this kind of thing.

Anyway, thanks,
Stanley

 

[Olaf Doschke]

You want something along the lines of connecting and not connecting.

5. allow un-trusted users access
6. will connect on non-standard port
7. authenticate using non standard sql user account
8. sa is disabled
9. super strong password
10. can use a domain lever ssl cert that the web servers are using,
11. can use 3rd party software like Nigel pointed out that blocks attacks
12. would really like to use vfp's tooling, (sql pass-thru and cursor adapters)

All these hardening options point out you WANT to connect to the server.

And then you say you want users to only have webserver access. If you don't let users connect to your database, then you don't need all these hardening options as only your webserver has the database access.
But then you want to do a web application, and not a fat client.

A web server can not only provide HTML pages, it can also take in Soap Webservice or REST API request it answers with XML. That type of interface is capable to eventually create Cursors on the VFP fat client side, but not updatable with the simple Tableupdate() option you have with database access. If you want to use such an interface, you have to implement that part of writing back changes yourself.

Bye, Olaf.

 

[clipper01]

Stanley

In the past did something very similar using a DMZ approach ( de-militarized zone ) . This requires an extra hardware firewall , I used Zywall , check their site.
You end up with your SQL server fully visible on the LAN side , but on the WAN side they come in through a separate IP that is translated by the firewall and so is fully secured
You need to set up access rules something like below , so it needs some supplier or techie that is familiar with that , find some-one with Zywall expertise

Access Rules for DMZ ( demilitarized zone )
WAN to DMZ : FTP ( port 21) ;
SQL ( port 1433,1434) to Sql Server
LAN - DMZ : full access
DMZ to LAN : FTP ; netbios to server

 

[Olaf Doschke]

Well, that's also only working for WAN, if you have clients outside your company, do you really want to extend your company WAN to their LAN, too?
The best way to provide data to end customers of any category, be it B2B or even single private end users, is hosting this data or a web application in the internet.

Bye, Olaf.

 

[clipper01]

far as I recall it was something like this
The LAN was subnet 192.168.15.xxx
SQL server sat on subnet 192.168.50.xxx , and nothing else there
192.168.15.xxx had full in/out access to 192.168.50.xxx , but none going the other way , locked by Zywall box
Outsiders were redirected to 192.168.50.xxx for SQL queries so had no exposure to the LAN

Presume you could do a lot of the same kind of firewall access rules with software, but at the time , the Zywall hardware had a lot more capability.
The 'outsider' as far as I recall was the client web site , it was a dental lab , and the dental practices would log in via the web to update orders etc
Think the only 'outsider' allowed by the Zywall box was the IP address of the web host.
But you could of course also permit fat VFP clients, restricted any way needed.

This seemed to lock everything down, outsiders could just post SQL queries, do some FTP , nothing else

 

[stanlyn]

Hi,

You want something along the lines of connecting and not connecting.

This is a simple as I can make it... Ideally, I want our fat vfp client application running on un-trusted users computers somewhere in the world accessing data coming from a non-WAN facing sql server at our data center, while using pass-thru or cursor adapters. Keywords here are "non-WAN" and "pass-thru". If that is not possible, what are good alternatives?

What are the real threats you guys see involved here with exposing sql to the wan?
1. threats to the internal lan,
2. threats to the sql data,
3. threats to uptime and usability (thinking DOS and DDOS)
4. you add another here
5. add some more here...

If the sql is on its own box with nothing else on it, and it is on its own isolated lan segment, and had something like rdpguard blocking abusers, and locking down sql and windows server hardware and software, and hardening the firewall with rules, then why wouldn't that pass for wan facing.

Clipper01, looks like from the configs, the Zywall hardware is only protecting all the other systems on the network, is that true? If so, how is the sql data protected from damage, theft, or whatever else? I would presume via standard sql security and perms. How was it protected from DOS, DDOS, injection and from being hammered (brute force)? I've seen several articles from Microsoft where Microsoft is saying that is secure enough to stand such attacks, (but then if they didn't, it would undermine peoples trust involving sql's security)

Thanks,
Stanley

 

[clipper01]

Stanley, to be honest I did not take it beyond the stages described. I relied on the assistance of the Zywall 'expert' , so if you could find one ( or similar ) , they might offer experience of working in similar scenarios