Migration of on-premises Access DB to AWS SQL Server DB
Allow geographically distributed users to access reliable and secure DB on AWS through
VB.NET app integrated into Microsoft Excel.
Allow geographically distributed users to access reliable and secure DB on AWS through VB.NET app integrated into Microsoft Excel.
Need for a solution:
The architecture the client had was designed to use the application within a LAN. Users can access the application database only when they are within the local area network. no access to users outside the LAN was provisioned. This architecture was secured until the LAN does not interact with outer networks and also it was safe from any intrusions from the internet. The application used plain password authentication which is not at all recommended or considered as a best practice in developing software. Since the application was used within the company's private network, it was considered to be of low vulnerability. But it also constrained the users to access it only within the network.
When we assessed the system there was no password policy which leads to an easy dictionary attack. Moreover, should the network interact with the internet, the risk is very high. And it happened with the client that the system got compromised and the hacker was successful in accessing the application database. Since there was no provision to account for the compromised user, the client was unable to discover the user whose account was hacked to gain access. The client got the data recovered, but it couldn't estimate the amount of risk the breach could have posed.
The client approached us and after reviewing all the details we planned to host it onto AWS Cloud, which not only has a strong network but is also affordable for small to large organizations.
We hosted the server onto the AWS Cloud and blocked the unnecessary ports access through a firewall. To account user actions we suggested migrating the database from Access to SQL SERVER. It was tedious and expensive to re-develop the application layer with a user accountability module to log user actions from the application layer. It was concluded after a perusal that it would be affordable to use SQL LOGIN, a DB level authentication for application users that is independent of application code. We converted application users to DB users with appropriate roles and access to database tables.
With minor changes to application code and SQL Login in place for each user, we not only had a strong authentication with customized password policy but also accountability of user actions. For users spread across the geography, we hosted it on AWS Cloud in a secured network.