Desktop App with SQL Server security issues

Question

I'm in a discussion with Information Security team about a legacy Desktop Application accessing a SQL Server database directly using LINQtoSQL.

The Information Security team insists that there are many security vulnerabilities in a Desktop application and a SQL Server with open port to network communication.

As a +10 experience developer I can only list this risks involved:

1. Unsigned Application: company's authority can't be certified allowing attacker to create a copy of the app with malicious codes.
2. Unobfuscated code: the attacker can decompile the app change its code an inject malicious codes.
3. Open port: can be exploited with an stolen SQL SERVER user + password to Data leak

But I know how we can mitigate all this issues instead of spending the enormous effort to migrate the whole desktop app (+8 years old) to (intranet) web app.

Do you guys have further arguments against this client server architecture?

Answer 1

Unsigned Application: company's authority can't be certified allowing attacker to create a copy of the app with malicious codes.

Keep in mind an attacker could do the same with a web application if it isn't properly secured with TLS.

Unobfuscated code: the attacker can decompile the app change its code an inject malicious codes.

Obfuscated code is a relatively small hurdle for somebody who is already motivated enough to reverse engineer it. I'm not really sure what would be changed or injected in this case.

Open port: can be exploited with an stolen SQL SERVER user + password to Data leak

Your users likely have access to credentials for the database, whether they are hardcoded in the app or user provided.

I think the real issue is that the database is directly exposed, and your infosec team is probably worried about access controls and auditability. A web application abstracts away the database and allows for business logic to be put in place. You don't want users to, for example, either maliciously or accidentally, delete the entire database, modify all records, add questionable/unvalidated data etc. Or maybe users should have these permissions, but you want a detailed log of who, what, and when.

While some/all of these things may be possible with the database's built-in controls, it will likely be much more complex to manage and more error-prone. I think it depends how much effort you are willing to spend configuring a database versus creating a web application. It also depends on the trust level of the users who have access.

This question and the answers discusses this issue in much further detail.

Also, a desktop application is not sufficient to employ controls and validations, as it is under control by the [untrusted] user. Client-side logic can be modified or bypassed entirely with a direct connection to the server or by changing the application. Credentials and other "secret" information buried in a client-side application can almost always be recovered. If it's encrypted, it can easily be grabbed out of memory when it is used. Or, if network comms to the database aren't encrypted, it could be sniffed there. For these reasons, any important logic must always be validated on some sort of server backend.

Answer 2

It sounds like your desktop app has a shared database (presumably located on a LAN, or even the Internet) that your users connect to? If so, that's not ideal - DB servers should generally be hidden behind other software, not directly exposed to untrusted users - but it's not inherently disastrous if the DB is secured correctly. "Secured correctly" here means things like:

• Per-user access privileges. If you're on a Windows domain or other Active Directory/LDAP service, this can be handled by having the app authenticate with the users' Active Directory account (at least for MS SQL Server; not sure which if any others support this). The opposite would be having all instances of the app use the same credentials to access the database; THAT would be a security disaster because there's no way to prevent an attacker from reading those credentials out of the app and using them to connect directly to the DB and execute queries maliciously.
• Minimal user privileges. Users should only have access to their own data, or to data that is intended to be shared with them. Sometimes this is achieved with per-table access controls, but if user data is co-mingled in a given table, then you probably need row-level security. Ideally, don't let users execute arbitrary queries at all; restrict them to a set of stored procedures that can only do things the user is authorized to do. This is a good idea for protection against SQL injection anyhow. The opposite of this is all users have database admin / owner privileges, able to take full control over the database if they ever access it through any means other than the client app.
• Strong user authentication. Connecting to the database should require credentials that are not reused elsewhere and can't easily be stolen, spoofed, or guessed. Windows authentication is kind of a mixed bag here; it's better than a lot of home-rolled authentication systems but that doesn't make it good, and your ability to harden it is limited (mostly, turn off support for legacy LANMAN/NTLM versions or weak remote password protocols such as MS-CHAPv2... but fundamentally, there's still some risk of things like pass-the-hash attacks being used to impersonate another user). The opposite of this would be a system where, even if each user has their own account, there is no secure way to prevent one person from using another's account (for example, because they share passwords).
• Harden the server. Even if the DB server must be exposed to the network, you can and should disable or firewall off most other remote connectivity to the DB server host. Disable functionality in the DB that you don't need, and ensure the server itself runs on its host with the minimal privileges it needs. You also might be able to restrict network visibility to only certain networks, rather than to everybody at a given company or (worse) on the whole Internet. Also, make sure that the DB server software and its host OS are kept up to date, and restrict who is allowed to physically access the server or log into the host OS directly, and especially restrict access to privileged accounts. The opposite of this is a server sitting somewhere public - possibly at somebody's desk - where every employee (or similar) can log in either locally or remotely; even if they in theory don't have access to the database when doing so, they could try to exploit a local elevation-of-privilege vulnerability to gain access.
• Ensure the connection is secure. Require connecting to use a secure protocol, such as TLS or at least SMB with encryption and integrity, and ensure that attackers can't gain access to the private key used by the DB server to secure connections. Ideally, you want forward secrecy (recent versions of TLS promote or even require this) and authenticated encryption (ditto). The client must authenticate the server using a cryptographic key (typically a certificate), rather than simply relying on the hostname or IP address. The opposite would be connecting over plain text, where credentials and data could be sniffed or even tampered with by a network attacker, or a client that doesn't authenticate the server before authenticating itself sending the user's credentials to a network attacker by accident.

There are a bunch of additional resources you can read to learn about securing SQL Server (some of which can be applied to other databases as well). For example, https://learn.microsoft.com/en-us/sql/relational-databases/security/securing-sql-server?view=sql-server-ver16 and https://learn.microsoft.com/en-us/sql/relational-databases/security/sql-server-security-best-practices?view=sql-server-ver16