Securing SQL Azure db that is directly called by multiple desktop applications

Question

I have inherited the support of a SQL server database hosted in Azure.

There are 5 - 10 WPF applications installed on individual users' desktops which access this database.

The users all have personal PCs (ie. not centrally managed by the organisation) with dynamic IPs because of residential broadband.

The application connects to the database directly with an ADO connection.

There is a single user name and strong password (circa 40 characters) used by all instances of the application.

The unencrypted credentials are only stored in a password safe, not GitHub or anywhere else etc.

The connection string is strongly encrypted and that is what is stored in the app.config and decoded in the working memory of the application at runtime.

The database has TLS security turned on (ie. encrypted at rest). PII data is stored in the database as well as bank accounts details (nb. financial details are stored in additionally encrypted columns)

Database access has been granted by white-listing all IP addresses:

Is this setup acceptable from a security point of view? If not, what needs to change or what can be done to improve it?

This question is similar to two other posts I've found, Is Azure SQL secure when using it for a desktop program? and How can I allow unknown users to access my SQL (Azure) DB?

I believe my question is different enough to these because the answers there basically fall into two camps 1). 'whitelist all IPs' and 2). 'introduce a middle tier'.

Whilst I'm experienced at building WebAPI services, I do not see that introducing a middle webservice tier using HTTPS fronting the database necessarily makes it more secure (tell me if I'm wrong).

Ideally I'd like to keep the current architecture for simplicity but satisfy myself that the setup is acceptable from a security angle.

You can use the ip whitelisting but as per your point its dynamic ips so it better to enable MFA /service principal / managed identity security for authentication and accessing programmatically. — Rahul Shukla
– Rahul Shukla, Commented Apr 20, 2021 at 4:17
@RahulShukla - do you mean passing the individual windows user credentials to the database somehow and have these granted access individually in the database, instead of the current single application username and password approach?? — Frank Ray
– Frank Ray, Commented Apr 20, 2021 at 8:49
Azure sql server provides option for MFA and service principal authentication which you can see option in learn.microsoft.com/en-us/azure/azure-sql/database/…. — Rahul Shukla
– Rahul Shukla, Commented Apr 20, 2021 at 8:59

Jesús Hernández · Accepted Answer · 2021-04-20 00:24:01Z

1

The problem is that your Azure SQL Database has a public endpoint <server_name>.database.windows.net. So by allowing any IP access to the database, you´re removing the firewall rules security layer and your database is exposed to malicious attacks. This is not considered a security best practice. That´s why this "middle tier" solution is always proposed to address problems with dynamic IPs.

You can find more information and possible solutions (Private Endpoint, VPN S2S) here: https://learn.microsoft.com/en-us/azure/azure-sql/database/security-best-practice#minimize-attack-surface

answered Apr 20, 2021 at 0:24

Jesús Hernández

3942 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Frank Ray Over a year ago

Hi @Jesús, thanks for commenting. What I still can't get my head around, is even if I put a webapi in the middle of the two, lock down the database firewall to just the IP address of the webservice host, isn't the fact that the webservice facing the internet with effectively every IP address whitelisted still the same attack surface??? Please help me understand this if you can.

Jesús Hernández Over a year ago

Not much of an expert on the middle tier solution, but I guess you should provide extra security for this layer too, and a way to accept requests originating only from your user´s desktops. Another solution could be to isolate your database in a virtual network and use a Point-to-Site VPN on the desktops (I am assuming that the users work in your company and they are not customers).

Frank Ray Over a year ago

Thank you @Jesús, I think a VPN solution is the best approach if not introducing a middle tier. That way point to point comms can be secured and the whitelisting of all IPs removed lieu of IPs on the VPN.

Collectives™ on Stack Overflow

Securing SQL Azure db that is directly called by multiple desktop applications

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related