5

I am developing a .NET Windows application and I need to make requests to a SQL Server instance. How do I secure the authentication data in my code in case someone decompiles my application?

I know there are ways to encrypt the connection string, but if someone decompiles the application, they gain access to the encryption/decryption algorithm and can extract the string.

Currently I’m using a Base64 encryption, but as I said, the customer can simply run my decrypt algorithm to get the plain text.

Improve this question
6
  • 4
    Make the connection string configurable, so the customer must ask you for one. This way you still have control over who is guaranteed and in which conditions.
    – Laiv
    Mar 16 at 23:28
  • 53
    BASE64 "encryption" is not encryption at all. It's simply encoding and should be considered as plain text as it is trivial to decode.
    – Geert Bellekens
    Mar 17 at 7:50
  • 7
    How do your users authenticate themselves?
    – Bergi
    Mar 17 at 10:08
  • 8
    In what context is your app used? If inside some AD domain, then you can just use usual windows authentication to access SQL server - no credentials stored in app. If you need public access, then of course this approach doesn't work.
    – Arvo
    Mar 17 at 12:48
  • 2
    Once you provide someone a copy of your program, assume they will do anything they want with it. If you put the key to the safe inside the program, assume you have handed that key to the customer.
    – CausingUnderflowsEverywhere
    Mar 17 at 14:57

5 Answers 5

Reset to default
31

There are two options I can see:

  1. The "modern" option: As Philip Kendall explains, don't. Do not directly connect to the database from the client. Instead, have a dedicated backend service which does. This is what most modern client/server applications do - web applications in particular, but native applications as well.

  2. The "traditional" option: Use the SQL Server's authentication and authorization mechanisms. That means creating a separate database user account for each user of your application, and setting appropriate permission at the database level for reading and writing (typically by forcing most operations to happen via stored procedures). This, when done correctly, can also be secure, and this is how many systems used to work (in the 90s and earlier, I'd say). However, there are numerous drawbacks to this solution (such as depending on the database server for a lot of functionality, and putting logic into the database), thus most modern systems use option 1.

I'd follow Philip Kendall's answer and create a backend service - the initial setup is fairly simple with modern frameworks.

Improve this answer
5
  • 1
    "a database schema for each user" - then they couldn't share any data, which defeats the purpose of a database server? You'd traditionally have multiple user accounts access the same database, with proper permissions set up for the different accounts in the database.
    – Bergi
    Mar 17 at 10:06
  • 1
    @Bergi: True. I edited my answer.
    – sleske
    Mar 17 at 10:08
  • 1
    I didn't even know it was possible to restrict users to, say, only do selects with their own user ID. Probably a big perf hit on it though.
    – jaskij
    Mar 17 at 14:50
  • 1
    @Bergi on the DB side you can grant permissions to other "schemas" for some operations or have triggers/stored procedures. Obviously is way more complicated then just operating on a single table of data but it can be done.
    – Bakuriu
    Mar 17 at 16:46
  • 6
    @jaskij row level security, in rdbms that support it, is pretty powerful :-) And not a performance hit at all - just an extra condition that can make use of indices normally.
    – Bergi
    Mar 17 at 18:16
73

You don't.

You put your SQL Server behind some sort of (probably web) API and use any of the numerous methods for securing an API. Anything which exists on a client's machine is not secure.

Improve this answer
6
  • 4
    This. Plus, even if your API auth gets compromised, an attacker can only do what the API lets him (so make it as small as possible!). If your database is visible to the outside world and its auth gets compromised, an attacker has everything.
    – Julia Hayward
    Mar 17 at 8:50
  • 22
    @JuliaHayward In theory, you could lock things down so the credentials in the client app have only very limited rights over the database. In practice, we both know that's orders of magnitude harder than restricting things through an API.
    – Philip Kendall
    Mar 17 at 8:53
  • @PhilipKendall: Exactly. See my answer :-).
    – sleske
    Mar 17 at 9:58
  • Is it possible that the database in question is going to be running locally on the client?
    – Blackhawk
    Mar 17 at 16:20
  • 2
    @Blackhawk the user is going to be able to get into a database running on their own machine no matter what you do, so this is a futile exercise. But then they can only really hurt themselves in messing with it, so why worry about it? You can decide you won't offer support if they break something on their own database.
    – Seth R
    Mar 18 at 5:34
4

In general, we should not be trying to provide a direct database connections from a client to a database server; unless the client itself is a database client.

Probable view

So, one option would be to provide a client with an authentication mechanism (maybe using credentials) and let that client talk to a DB service application API (or endpoint) that you host inside your network. Then, your DB service application can verify the credentials and continue to serve the client requests by connecting to the secured database.

Improve this answer
7
  • 3
    note the only reason we do this is because the database usually has completely inadequate security mechanisms. If your database does have adequate security, it's an inefficient design.
    – user253751
    Mar 17 at 9:47
  • 3
    @user253751 I wonder what an "adequate" security mechanism would be that would take less effort in configuring than making an API?
    – Vilx-
    Mar 18 at 1:03
  • 4
    @user253751 it's not the only reason. Even if your DBMS has its own security mechanisms that can technically offer sufficient restrictions, doing so is often a lot more complicated to set up and maintain. There is a lot more potential to make a mistake and give away access you didn't intend. The nice thing about abstracting the access out to an API is that your users can then only do what you've explicitly programmed the API to do. It's a lot easier to get right.
    – Seth R
    Mar 18 at 15:18
  • @SethR well if they are too complicated to set up and maintain, that makes them inadequate, no? (Database users can also only do what you explicitly gave them access to do... it's just easier to give them access by mistake... but it's also easier to do on purpose)
    – user253751
    Mar 18 at 22:37
  • 1
    Most database offerings don't offer support for "reset password" or two-factor authentication or integration with a corporate SSO system or many of the other things that people tend to expect from modern applications and authentication libraries.
    – Zach Lipton
    Mar 19 at 19:38
0

A few ideas about "quick-and-dirty" APIs that could be built without much effort. Note that these will never be as secure as a purpose-built API, and that is the CORRECT way to go. However, in a pinch, maybe these could do.

  1. The API just validates user credentials and then simply passes through SQL and returns the results. Note: While it's tempting to also try and analyze the queries in order to prevent some attacks (like, forbid anything that has the words drop table in it), such methods are rarely successful and usually just offer the illusion of security, while any real attacker can easily figure out ways to circumvent them.
  2. The API validates user credentials and then returns the actual DB connection string. This way the connection string/password is never saved on the client, but the DB connection is direct and more efficient. This is also probably the easiest way to retrofit an existing application.
  3. A CRUD-type API that allows basic operations on any table. This will limit your ability to write crazy SQLs (often found in reports), but those could be offloaded in stored procedures (also callable through the API). I've heard that WSO2 might provide this out-of-the-box but I don't know for sure. I'm also not sure if this would be fundamentally any safer than the other methods, but it certainly would take more effort.
Improve this answer
0

Depending on your needs, the ProtectedData class might be what you want. It encrypts the data using the credentials of the logged in user and it saves it on that machine. So decoding your application does not give up any secrets.

This does require that each user enter the information on each computer. And the data is then available to any other program running on that computer. So it's not super-duper secure. But it is a lot better than having it as a literal in your program or stored as plain text somewhere.

Final note as you're new - security is always a trade-off. More secure == more PITA.

Improve this answer
4
  • ProtectedData doesn't secure against the user accessing the string.
    – user1937198
    Mar 21 at 15:12
  • @user1937198 Right, I said that in my answer. ProtectedData provides some security and that's why I said it's a trade-off and the OP has to decide if this is a good trade-off for their situation.
    – David Thielen
    Mar 21 at 16:19
  • Given the op was explicitly asking for protection against an attacker who is a user who decompiles their app, I don't see how ProtectedData adds any real security to their threat model.
    – user1937198
    Mar 21 at 17:17
  • @user1937198 Fair point. But other answers suggested having credentials per user that they enter. If that approach is taken, then ProtectData MAY be of additional use. So I figured it couldn't hurt to let the OP know of it.
    – David Thielen
    Mar 21 at 17:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.