CODEWITHSUNDEEP
c#sql-serverwpfentity-framework
lewpie
lewpie

Reputation: 99

How to use Entity Framework/C# and SQL Server over the internet securely

I am currently developing a C#/WPF/MVVM/EF6 application that allows multiple users in different physical locations to send and receive SMS messages to mobile phones using a third party API.

The database stores all messages, sent and received, and is either updated directly by the user (sending a message) or by a ASP.NET web hook (receiving a message). Users are using SQL authentication and dynamically created connection strings to connect to the database (i.e. pass in a connection string whenever a new DbContext is created). EF is used to query against and display the data.

I'm now encountering what I believe may be a fundamental security issue with my design. The users all need direct access to the SQL database to use Entity Framework methods. After some research, it appears to be very risky to simply expose the SQL Server to the internet. Most suggestions have been to use a VPN or IP White Listing, but unfortunately, neither of those are options. We will have the SQL Server hosted by a third party that will not provide a VPN, and the users will be in different locations frequently so their IPs will not be static.

Are there any additional options to maintain direct SQL Server access with additional security? The only option I am able to think of is to completely change the architecture by creating an API for the application to query against and refactor the code to pull data using the API instead of Entity Framework. Any input would be highly appreciated.

Upvotes: 0

Views: 1313

Answers (2)

Jirayu Methathip
Jirayu Methathip

Reputation: 31

I agree with you to create API and act like database adapter with 2 reasons.

  1. Letting all client store connection string is too risk and difficult in case you want to scale your project. especially Windows application like WPF in your case. So having adapter gain you capability to update and maintain seamlessly.

  2. If clients contact to database directly without IP whitelist meaning you are exposing database to public

An idea that I could imagine is creating a WebAPI and let it dynamically creates SQL user for each clients with limited permissions. Then periodically revoke SQL User every given time (Maybe with Webjob). So client won't have long-lived connection string. And you will need only single endpoint.

What client have to do is asking WebAPI for temporaly connection string. With this solution you will be at least have a chance to validate client's credential, role, ip, version number and etc before give them connection string whenever its connection string was revoked. It still requires API. But client will communicates with database directly with better security in my opinion.

NOTE: Since you are letting clients play with database. Make sure client is always the latest version. I think you can check when client asking for connection string.

Upvotes: 0

carlos chourio
carlos chourio

Reputation: 873

You could create an API that access directly to the database, the api will be exposed over the internet, and you will only define the methods you want to perform on the database (eg CRUD Operations).

The wpf application will no longer be comunicating directly with the database but it will be communicating to the api instead. The api is the one that holds the connection string and is the only one who will have direct access to the db. That way you're not exposing your entire database over the internet.

You could also add Authentication, that way only logged in user could get/insert/modify data from the database, and could also add Authorization for handling user permissions.

Now you should refactor your application to work with the API. Although if you used something like Repository Pattern it will only require a few changes in the repository itself to work properly.

Now your application design has another layer of abstraction which is the API

WPF application → API → Database

The way I consume APIs in my applications is using the HttpWebRequest class . However there are other ways to handle that and this article explains it really well: A Few Great Ways to Consume RESTful API in C#

Upvotes: 2

Related Questions