Connecting to remote SQL database through internet

Hello,

I’d like to set up an authentication system (log in) for a winforms application.  There will be about 500 users or so in remote locations.  The database will be a SQL Server accessible via port forwarding thru a firewall.  As I’ve read on how to do this I either don’t fully understand the process or there’s a security hole.  Here’s the environment summary:

Server accessible over internet:
SQL Server 2014
Tables:
      Users
      Logins
      Several Others readable and writable depending on permissions

Server Database Logins:
      AppAdmin, dbOwner
      AppUserManager, read/write permissions on User Table, read on Logins table
      AppUser, read/write on Users table (to change PW every so often), write on Logins, write /read various tables
      AppTemp, read user Table

Main Application:
      WinForm .Net
      Includes AppTemp credentials in deployment

UserManager App:
      WinForm .Net
      Includes AppTemp credentials

AirCode:

Main App:
      Open Log In Form
      Retrieve User Row (salt, PW Hash, permissions, Encrypted AppUser Credentials) based on what is entered in the User Name Text Box using the AppTemp database user.
      Compare PW hash (with salt) to make sure PW is correct
      If Correct decrypt AppUser credentials retrieved with the AppTemp and use those to open new connection to DB for operations

I guess what I’m worried about is ‘hardcoding’ the AppTemp connection string as anybody could decompile and have free access to the user table at least, And then could decrypt the AppUser credentials and have free reign….What am I missing in this process to make it secure?

I read about creating a web service or middle tier logic to manage the DB traffic but I have no experience in doing that so would prefer to avoid it if possible.

Thanks for thoughts.
zipnoticAsked:
Who is Participating?
 
btanExec ConsultantCommented:
Suggested methods for client
http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/devwin32/multiwritingwebbasedclients_xml.html
One example is build the data broker or the appl server that sit between web client and database. See the below.
http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/devwin32/multithestructureoftheapplicationserver_xml.html
Th above is based on RAD studio tool though.
0
 
nociSoftware EngineerCommented:
The security depends on the API you expose.
If exposing the full database API anything is searchable and modify-able.


the weblogin will shield that rich data environment by something YOU control.
otherwise you will have to put a server (program) with not a that rich API between
the users and the database.
0
 
zipnoticAuthor Commented:
I'm not understanding your comment at all.  I have no API exposed.  The database itself would be "exposed."  There is no weblogin as this is not a browser based application.

Thank You
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
nociSoftware EngineerCommented:
The database access also is an API... just like a web service or what ever method you (let) design  to access something.
That API is wel described in all kinds of manuals. Only missing details would be database, identity  & credentials to access.
0
 
btanExec ConsultantCommented:
Avoid having your database exposedto Internet directly, just fronted by firewall and straight to it is not going to secure. It is subjected to various Internet based attack esp brute force against your login and even DDOS to bring down your services.

Suggest you have a tier approach, having the web and app server to process request.  So have the expert mentioned earlier.
Enforce all remote access to be via VPN to secure the traffic in transit and (seriously) built in 2FA rather than a simple password based authentication.

Against DDOS, it will be tough going to catch up by pumping up resource like memory or bandwidth. They will not suffice against surged and persistent traffic coming with waves of attack. Consider service like Cloudflare, Incapsula, Nexusguard or Akamai. They have WAF capable inspection to scrutinise and block SQL based injection attack.

Without getting the infrastructure setup right, the app and DB setting will not be protected effectively. You need a collective approach and get your network and ops teams to bring this forward.
0
 
zipnoticAuthor Commented:
I did some reading on N-Tier structure.  It didn't really strike me as designed to be more secure, just easier to manage for updates and changes in the future.  That would be overkill for this project.  What about N-Tier do you think would make it more secure?

VPN is not going to be an option.  

I agree about getting Network people involved but I was hoping this could stay inhouse.  Web App/Dev would have to be contracted out and of course would be a nightmare.
0
 
btanExec ConsultantCommented:
You are right in reading the separation between tiers is not mainly done because of security but so that you can easier understand and manage the application. It makes it possible to make changes on one layer without many changes to the other layer, i.e. you could scale the database or even change the underlying database without much impact to the application layer.

And related, this separation can be helpful for security. E.g. Clearly defined interfaces between the tiers make it easier to audit the application/db. You could also better detect and deter possible attacks as you actually know the parts interacting with each corresponding tier's part. Rolling of compliance and policy in each tier can be on context to what is needed to protect in that realm. User access and rights will be allowed according to go for granular access.
0
 
nociSoftware EngineerCommented:
Also if you insert your "broker" in between the client and the database (and it doesn;t need to be a Web application).
that broker can also do rate-limitting, filtering, marshalling of arguments (is a text field realy containing text, a number field just numbers, is the currency field holding a valid currency, etc. etc.)
the additional functions that you CAN put in a "broker" and cannot be done on the DB API.  And that aids security. BTW, Security is a process not a method.

There is no need to build a Web application, it can be any kind of services. Think Application Firewall here. With knowledge about the specific data being transfered and to what amount this is valid.
0
 
btanExec ConsultantCommented:
actually if the DB is having a loadbalancer, you may want to explore further its capability - like this case in netscalar.
Data security and threat control – Polices can be applied at the user level to
control access to back-end database servers. NetScaler inspects all SQL
transactions and can perform SQL protocol validation and data access control.
Data access events can be audited and logged to meet compliance requirements.
Intent is to save you coding and enforce policy via the LB which does scaling & inspection
https://www.citrix.com/content/dam/citrix/en_us/documents/products-solutions/scaling-and-optimizing-microsoft-sql-server-with-citrix-netscaler.pdf
0
 
zipnoticAuthor Commented:
So N-Tier is a better mouse trap.  I'm still stuck on the original problem.  How do I authenticate with a 'broker' , 'middle tier', or whatever you want to call it.  I'm not against writing something like this.  I wouldn't get funds to use citrix or anything like that so I'm stuck creating it.  How do I take a remote WinForm app and authenticate over the Internet against a broker residing on a server?
0
 
zipnoticAuthor Commented:
I'm sorry but those links were unhelpful.  I would be interested in a link for a walkthrough or simple example of middle tier application that can SECURELY connect with a REMOTE winforms application.  Perhaps using TCP sockets but I would be willing to learn to use JSON.  My main concern is creating the connection that can be secured with at least a username and password.

Thank You
0
 
btanExec ConsultantCommented:
Provably there will be more examples out there but thought you may be interested in this example that uses a Windows Forms client to connect over the Internet to your WCF service. Will look out more.
https://msdn.microsoft.com/en-us/library/ff648163.aspx
0
 
zipnoticAuthor Commented:
I guess the WCF link you pointed to is a starting point.  Honestly I was hoping for something a little more straight forward to help me build my first system.  I never used WCF before.  A quick search didn't come up with too much for my scenario (winforms to internet to DB).  Everything seems to focus on http/webapp to DB security.  EE is forcing me to close the question so unless you were able to find something or have specific advice I'm going to close this out.
0
 
btanExec ConsultantCommented:
Probably it is to divide your question into smaller parts (functionality and modules) and work on various in separate question allowing various experts contribution. Another is to put into Gigs projects or Live requests with more involved expert advice on your problem.
0
 
btanExec ConsultantCommented:
For author advice
0
 
zipnoticAuthor Commented:
Does not really help with original problem but I have to close question.
0
 
btanExec ConsultantCommented:
You may request to delete the question away if deemed not useful or re-ask a new question if required.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.