Latest / DevQuestions with Tim Corey / 006 How Do I Encrypt a Connection String? How Do I Protect My Settings in a Desktop Application?

Transcript

006 How Do I Encrypt a Connection String? How Do I Protect My Settings in a Desktop Application? · DevQuestions with Tim Corey

• 0:00
  SPEAKER_00: Welcome to the Dev Questions Podcast with Tim Corey.
• 0:03
  SPEAKER_00: Join us each episode as we tackle the questions you are asking about a career in software development, understanding the industry, and new technology.
• 0:12
  SPEAKER_00: If you're just starting out or you want to grow stronger as a developer, this is the place to get your questions answered.
• 0:19
  SPEAKER_00: Now here's your host, expert developer and online educator Tim Corey.
• 0:25
  SPEAKER_01: How do we secure or encrypt our connection string?
• 0:28
  SPEAKER_01: You see, we store our connection strings in app.config or web.config or app settings.json file depending on the project type, but that's a text file in any of those cases.
• 0:41
  SPEAKER_01: So if we give an application to a user, or if a user has access to our machine, they can read that connection string, which may have a login name and password to our database.
• 0:53
  SPEAKER_01: So how do we secure that?
• 0:54
  SPEAKER_01: How do we protect our database when we give access to users to use our application?
• 1:01
  SPEAKER_01: So that's a question that comes up a lot.
• 1:03
  SPEAKER_01: So let's talk about it.
• 1:04
  SPEAKER_01: Now, my name is Tim Corey, and my goal is to make learning C sharp easier.
• 1:09
  SPEAKER_01: And one of the ways I do that is by taking time out to talk through these questions because getting the answers to these questions will allow you to move forward as a developer.
• 1:20
  SPEAKER_01: So let's talk today about connection string security.
• 1:25
  SPEAKER_01: So connection strings are the information you need to connect to a database to get information.
• 1:32
  SPEAKER_01: Most applications use a database of some type because most applications need data.
• 1:39
  SPEAKER_01: Well, that connection to the database usually we don't just give away to anybody.
• 1:45
  SPEAKER_01: Because imagine if we had, let's say, a retail store where we had prices and quantities in stock and all the rest.
• 1:53
  SPEAKER_01: Well, what if we gave away our access to our database?
• 1:57
  SPEAKER_01: Obviously, someone could come in there and change the price of an item.
• 2:01
  SPEAKER_01: I would love to do that.
• 2:02
  SPEAKER_01: That's too expensive.
• 2:03
  SPEAKER_01: Let's cut it in half.
• 2:05
  SPEAKER_01: That's the new price.
• 2:06
  SPEAKER_01: Now let's go ahead and check out.
• 2:07
  SPEAKER_01: You see, that would be chaos.
• 2:09
  SPEAKER_01: We wouldn't want that.
• 2:10
  SPEAKER_01: So we have to protect our data in some way.
• 2:14
  SPEAKER_01: Also, if we capture any information about users, I mean, you've heard about data breaches.
• 2:20
  SPEAKER_01: That's because someone got into a database that shouldn't have.
• 2:23
  SPEAKER_01: We don't want that.
• 2:24
  SPEAKER_01: We don't want to be the next news headline about a data breach because of something we did.
• 2:30
  SPEAKER_01: So, how do we protect ourselves against this type of attack?
• 2:36
  SPEAKER_01: Which really what we're doing is giving away our credentials.
• 2:39
  SPEAKER_01: We don't want that.
• 2:41
  SPEAKER_01: So let's start by talking about that connection string in our settings file.
• 2:48
  SPEAKER_01: That connection string allows our application to talk to the database as someone.
• 2:55
  SPEAKER_01: It says, I am saying I am this person, and here is how, here's my rights.
• 3:01
  SPEAKER_01: Here, here's what I'm saying, either my login name and password, or I'm saying whoever's logged in, that's me.
• 3:09
  SPEAKER_01: Okay, so those are the two different kinds of generally, the two different kinds of connection strings.
• 3:15
  SPEAKER_01: One is username and password, one is the logged in user's credentials.
• 3:20
  SPEAKER_01: There's not they're not typed out.
• 3:22
  SPEAKER_01: We just say, hey, use that person who's logged in, use their credentials, use their authentication.
• 3:28
  SPEAKER_01: They've already logged in, so we know that we can say we are that person.
• 3:32
  SPEAKER_01: That's called a trusted connection.
• 3:35
  SPEAKER_01: Now, ideally, you want to use a trusted connection because that way you're using letting Windows authenticate them.
• 3:44
  SPEAKER_01: Once they're authenticated into Windows, then they can have the rights that they should already have.
• 3:51
  SPEAKER_01: But a lot of times we don't want to give access to our database to 100, 200, 500, 1000 people or more, depending on how many people are using your application.
• 4:04
  SPEAKER_01: In this case, we typically use a username and password and say this application is going to log in with these credentials.
• 4:14
  SPEAKER_01: But if someone got those credentials, they can log into that database directly and possibly do whatever they wanted to.
• 4:24
  SPEAKER_01: Now, the the first thought that comes up, and it's a good thought, is let's encrypt that connection string.
• 4:32
  SPEAKER_01: So that even though those credentials are exposed, they're not really because they're encrypted, so you wouldn't know what those credentials are, therefore it's safe.
• 4:44
  SPEAKER_01: The problem with that is that with encryption, there has to be a two a person on each end.
• 4:54
  SPEAKER_01: There's the person that has the credentials and is going to use them, and the other end that's going to accept them.
• 5:00
  SPEAKER_01: So what's going to happen is if you encrypt those credentials, you're encrypting them with the application, the application on their machine.
• 5:13
  SPEAKER_01: So they control one end of that encryption.
• 5:16
  SPEAKER_01: So even though that encryption happened, you can decrypt that because you control that end of the transaction.
• 5:28
  SPEAKER_01: It's a little complicated.
• 5:29
  SPEAKER_01: Okay?
• 5:30
  SPEAKER_01: I encryption is messy.
• 5:32
  SPEAKER_01: It's difficult.
• 5:33
  SPEAKER_01: It's way above my head.
• 5:36
  SPEAKER_01: But what's happening is with encryption, if you control one end where you're encrypting or decrypting data, then you can control the data.
• 5:47
  SPEAKER_01: Because what's going to happen is you're going to decrypt that connection string and then send the actual credentials onto the database server.
• 5:56
  SPEAKER_01: But somewhere in memory on that machine is that decrypted password.
• 6:03
  SPEAKER_01: So it's still there.
• 6:05
  SPEAKER_01: Yes, it's harder to get to, and that may be good enough.
• 6:09
  SPEAKER_01: But the reality is it's still there in unencrypted form at some point.
• 6:15
  SPEAKER_01: The user can listen to their own traffic.
• 6:18
  SPEAKER_01: Therefore, they could even capture across the wire.
• 6:22
  SPEAKER_01: So there's a lot of ways you can get that decrypted password off of the machine you control.
• 6:30
  SPEAKER_01: So encryption's not really good enough.
• 6:34
  SPEAKER_01: It does add a level of security, but it's not really full security.
• 6:40
  SPEAKER_01: Okay?
• 6:41
  SPEAKER_01: It's like locking all your doors and leaving all your windows open.
• 6:47
  SPEAKER_01: The house is locked up, but you can still get in.
• 6:51
  SPEAKER_01: Okay?
• 6:51
  SPEAKER_01: So what we want to do instead is talk about the actual credentials.
• 6:57
  SPEAKER_01: Because those credentials probably shouldn't be administrative.
• 7:02
  SPEAKER_01: Now that's that's the easiest way of going about it.
• 7:05
  SPEAKER_01: When you're especially on a developer machine, we get used to the concept of just make the credentials administrator.
• 7:11
  SPEAKER_01: That way, there's no roadblocks in place.
• 7:14
  SPEAKER_01: It just works.
• 7:15
  SPEAKER_01: Everything can be done because everything is allowable.
• 7:20
  SPEAKER_01: But that's not a great idea.
• 7:22
  SPEAKER_01: When you're going to give credentials to a user, you want to give them as the least credentials possible.
• 7:29
  SPEAKER_01: There's a reason why they're not an administrator on your network.
• 7:33
  SPEAKER_01: Because you don't want to give them access to the rest of your servers.
• 7:36
  SPEAKER_01: Why would you give them access to your full database?
• 7:39
  SPEAKER_01: So instead, you want to lock those credentials down.
• 7:43
  SPEAKER_01: Now I have a video on this channel about stored procedures.
• 7:47
  SPEAKER_01: You might want to check that out because it's relevant to this video.
• 7:50
  SPEAKER_01: Because in there, I talk about how you can lock down your SQL Server to say, only run the stored procedures that I allow.
• 8:00
  SPEAKER_01: And that's it.
• 8:01
  SPEAKER_01: You can't list the tables in the database.
• 8:03
  SPEAKER_01: You can't query the database.
• 8:05
  SPEAKER_01: You can't run queries that aren't approved.
• 8:09
  SPEAKER_01: The only thing you can do is execute these stored procedures.
• 8:13
  SPEAKER_01: Now, that's restrictive.
• 8:16
  SPEAKER_01: It's not as fun and open and easy as you might want.
• 8:23
  SPEAKER_01: In fact, it won't work with Entity Framework because, and this is one of the reasons why I don't use Entity Framework.
• 8:29
  SPEAKER_01: Entity Framework really wants to have access to even modify your tables.
• 8:35
  SPEAKER_01: But at the very least, it wants to have access to do a select star, insert into, update, delete data in all of your tables.
• 8:46
  SPEAKER_01: Well, that's pretty much admin rights in my book.
• 8:50
  SPEAKER_01: That's all of my data and any manipulation you want to do.
• 8:54
  SPEAKER_01: But if you lock it down as store procedures, that says you can only do what you're already allowed in the application.
• 9:03
  SPEAKER_01: And that's the other thing to remember.
• 9:05
  SPEAKER_01: The user is using your application.
• 9:09
  SPEAKER_01: You're giving them permission to your database in a limited fashion through the application.
• 9:15
  SPEAKER_01: So if you lock their credentials down to only do what the application is allowed to do anyway, then you're pretty close to no different, even if they got the credentials and tried to use them directly.
• 9:31
  SPEAKER_01: Now there are some differences.
• 9:33
  SPEAKER_01: For instance, if you do data validation on your client and not in the database, then that could be an issue.
• 9:41
  SPEAKER_01: Because they could do things in the store procedures that they would directly that they couldn't do through the app.
• 9:48
  SPEAKER_01: But that window is a lot narrower.
• 9:51
  SPEAKER_01: Is it perfect?
• 9:53
  SPEAKER_01: No, it's not.
• 9:55
  SPEAKER_01: And that's why when we're talking about desktop applications, and let's kind of you know break apart just desktop and talk about it first.
• 10:06
  SPEAKER_01: If we're talking about desktop applications, you might not want to give that desktop application direct access to your database because you're putting a connection string on there that gives the user at least a little bit more access than you want.
• 10:28
  SPEAKER_01: Now, again, I would encourage you to use that lockdown to only those store procedures, and at least it's practically the same as if they used the app.
• 10:39
  SPEAKER_01: But even so, you might want to consider not having direct database access and instead having an API.
• 10:49
  SPEAKER_01: Because let's talk about web.
• 10:52
  SPEAKER_01: We talked about desktop, and desktop apps run on the client's machine.
• 10:58
  SPEAKER_01: The client has access to the app.config or app settings.json file, whether it's a.NET framework or.NET Core project, they have access directly to that file.
• 11:08
  SPEAKER_01: And even if you put the file into the application, which kind of negates the purpose of the file, even if you do that, they can still get access to that data.
• 11:19
  SPEAKER_01: So the user controls their own machine.
• 11:22
  SPEAKER_01: But with a web application, the user does not control the machine.
• 11:28
  SPEAKER_01: Instead, an administrator does.
• 11:31
  SPEAKER_01: If you have people on your web application server that shouldn't be there, you've already lost a security battle.
• 11:41
  SPEAKER_01: Because if they have access to the server, it's kind of over.
• 11:44
  SPEAKER_01: You kind of already have people inside the house that you're saying, yeah, but you can't come in the bathroom.
• 11:50
  SPEAKER_01: Okay?
• 11:51
  SPEAKER_01: They're already in the house.
• 11:52
  SPEAKER_01: There's already a problem.
• 11:54
  SPEAKER_01: They can already take stuff.
• 11:56
  SPEAKER_01: So what you want to do instead is you want to make sure that if it's on a web server, that you just lock down a server like you normally would.
• 12:06
  SPEAKER_01: And then it can be in clear text in that app settings.json file or web.config file.
• 12:14
  SPEAKER_01: And it's okay, it's safe because you have all the safety around it.
• 12:19
  SPEAKER_01: Now, again, I would still encourage you to put that layer of security up another level and lock that down so it's not an administrator, it just has access to the store procedures or whatever in order to do its job.
• 12:35
  SPEAKER_01: Because don't forget, we also have other attacks that come through your application.
• 12:40
  SPEAKER_01: For example, hey, cross-site scripting or you have SQL injection.
• 12:46
  SPEAKER_01: There's a fun one.
• 12:47
  SPEAKER_01: If you have SQL injection and you haven't protected your database correctly or your inputs correctly, then the user could say something like, okay, you know, semicolon delete from this table.
• 13:01
  SPEAKER_01: Well, if you're calling store procedures, that won't work.
• 13:05
  SPEAKER_01: So you're a little more safe there, or a lot more safe, actually.
• 13:10
  SPEAKER_01: So you're kind of reducing those risks as much as possible.
• 13:13
  SPEAKER_01: So don't just do one thing, try and do as many as possible.
• 13:17
  SPEAKER_01: But at the end of the day, if you're on a server, you're more secure.
• 13:23
  SPEAKER_01: You're almost totally secure.
• 13:26
  SPEAKER_01: So therefore, you don't have to worry as much.
• 13:30
  SPEAKER_01: It's not as big a deal.
• 13:32
  SPEAKER_01: Therefore, I definitely wouldn't recommend encrypting your connection string.
• 13:37
  SPEAKER_01: It just adds overhead to your application that slows your application down without appreciable benefits.
• 13:44
  SPEAKER_01: Okay, so that's my thoughts on encrypting connection strings.
• 13:49
  SPEAKER_01: I rarely, if ever, do it.
• 13:51
  SPEAKER_01: I'm focused more on securing that database connection string, uh securing those credentials, and making sure they're only uh have as much permission as necessary.
• 14:04
  SPEAKER_01: And I think through do I need an API instead to further protect this?
• 14:09
  SPEAKER_01: For example, in the Timco Retail Manager series, that's a playlist here on YouTube, um, I'm going through and building an application that's got a WPF, a desktop application front end.
• 14:22
  SPEAKER_01: And I said, I really don't want to have direct database connectivity, so I'll create an API in between.
• 14:29
  SPEAKER_01: That allows me to add more user interfaces and it allows me to secure my credentials in a much safer manner.
• 14:37
  SPEAKER_01: Now the WPF application only has access to the API and only if the user authenticates.
• 14:44
  SPEAKER_01: So that really raises that level of security.
• 14:48
  SPEAKER_01: Something to think about, okay?
• 14:50
  SPEAKER_01: So that's how you secure connection strings.
• 14:53
  SPEAKER_01: I'd love to know your questions if you have any on this topic or any other topic you want to see me cover, either in code format or in this dev questions format.
• 15:03
  SPEAKER_01: Thanks for watching, and as always, I am Tim Corey.
• 15:06
  SPEAKER_00: Thank you for joining us for this episode of Dev Questions.
• 15:09
  SPEAKER_00: Tim is committed to making it easier for you to become a developer.
• 15:12
  SPEAKER_00: If you would like to help make more content like this possible, please like, subscribe, rate, and share Dev Questions.
• 15:19
  SPEAKER_00: You can also send your questions to questions at IamTimCorey.com.
• 15:24
  SPEAKER_00: Until next time, remember, you are too smart and your time too valuable to waste it making all the mistakes Tim did.
• 15:32
  SPEAKER_00: When you're ready to learn to think and code like a professional developer, head over to imtimcore.com and enroll in a course.