CODEWITHSUNDEEP
sql-serverazuresecurityencryptiondatabase-connection
Frank Ray
Frank Ray

Reputation: 73

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

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:

enter image description here

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.

Upvotes: 1

Views: 269

Answers (1)

Jesús Hernández
Jesús Hernández

Reputation: 394

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

Upvotes: 1

Related Questions