I'm making a medium sized application to be distributed privately to several Churches. It connects to an Azure database but it does not have any server managing the information (apart from the SQL server). The application is highly dependent on the information stored in the databases as very little data is saved client side (just the user's personal settings, product key).

But I have a dilemma. The information stored on the server is fairly confidential as it saves individuals' private information (such as demographics, personal and identifiable information), and I want to be able to connect the application to the database without the connection string being easily accessible, especially allowing all copies of the application to have raw and unrestricted access to the database. What's the best way to accomplish this? We currently don't have the resources to run a local server 24/7.

Are there any online solutions out there?

  • 2
    Just as a quick note, you can run a virtual machine for under 10 dollars a month on many diffetent providers, and the costs become even less if you use something like azure to host your application. Considering that you already have a sql server DB, are you sure that you don't have the resources to do what you need? – gcali Oct 16 at 6:40
  • 4
    Depending on the country, you need to know which privacy laws are applicable to your industry (religion/churches) and the kinds of data your system stores (personally identifiable info like names, birth dates, etc) and confidential information specific to each organization. – Greg Burghardt Oct 16 at 12:29
  • Would a web app be better here? – GrandmasterB Nov 1 at 5:20
  • 1
    Your question title and the body of the question ask different things. Are you asking if it's "save to put SQL statements in C#" or are you asking how to "connect the application to the database without the connection string being easily accessible"? – Greg Burghardt Nov 2 at 11:40
  • Are you planning to use MySQL hosted on Azure, or an Azure SQL Database i.e. SQL Server cloudified? – Michael Green Nov 9 at 2:52

The "we don't have the resources" line is a red flag to me.

You're either going to pay to do it right, or you'll have to pay for doing it wrong. I know which I'd pick.

While your use case is fairly broad, which makes it hard to give you a simple answer, I would strongly recommend against giving each application rights to the database directly. A better approach would be to build an API server (possibly hosted on Azure as well) that acts as go-between and only exposes the functions needed rather than open SQL queries.

If that's too much still, then I would recommend a Windows-auth based solution where you have explicitly defined Windows users and roles in SQL Server and lock them down to the minimum set of rights required. That's not a great approach but probably the best you can do without a proper architecture.

You'll also want to make sure you're encrypting your data at rest and in flight and rotating keys and passwords regularly. You probably don't have resources to actually manage and monitor this stuff either which means when you do get hacked you'll have no idea and can't respond to it if you did.

Good luck.

  • 3
    And I'd suggest that paying for doing it wrong is going to be much more expensive in the long term than paying to it right in the short term. Especially when the OP has indicated that the DB will hold sensitive data. – Peter M Nov 1 at 11:35

It's generally not safe to connect directly to an SQL server if the application is running on the client's machine.

If the client has the SQL connection string, then they can run whatever SQL their user has permission to run. And generally SQL permissions are not fine grained enough to have the kind of control you want for an application.

The usual solution is to add an API layer between your app and the database, as others have noted this is much cheaper than running the database, so it shouldn't be a resource issue.

If you really really want to connect directly though, you could possibly use stored procedures to implement an API layer.

I will start by saying I agree that security is generally better handled by an application brokering between the client and database. This help by giving you "security in depth".

However I disagree with some of the previous answers. It is possible to limit or restrict the permissions and privileges users have at database level.

Aside from granting select, insert, update, delete privileges, your app could use different schemas & views or preferrably stored procs to limit or restrict what they can do. If your app can only use those features you are less exposed. The concept is to grant the minimal priviliges necessary. DO NOT give SA or DBO privs to an application or service acct. Ever.

The difficult bit will be that Azure requires a SQL login/password. If users share the same SQL login, that will limit your ability to change passwords, and your application will need to handle which users can perform specific functions. If you want users to have seperate logins and passwords at SQL level this will become a maintenance overhead for you.

I'm not an expert with Azure, but you can also limit/exclude connections by IP address/range. If your client sites can be identified by IP that could give you an added level of security.

But the sad reality is users can bring a db server to it's knees with nothing more than a select query. So you do not want your users to ever know what that SQL login/password is, because if they can connect, they can select.

In a corporate environment this might be acceptable, but in the wild wild web.. this is hacker news waiting to happen.

I highly reccomend you go for security in depth, build some kind of middle tier/broker and keep users at arms-length from your db.

Basically if you are giving raw access you're just asking for SQL injections - the number one security issue online. So basically stop right there or just don't pretend you have any security at all.

As far as having a test DB try a file system based one like HSQL - then you can just have the thing on your machine for development and test purposes. As others here have pointed out you really, really need an API that defines valid actions after that - sanitising raw inputs, encoding them into valid actions only then actually performing the action.

Go have a look at https://www.owasp.org/index.php/Main_Page for more on all the various security issues one might want to consider for any application.

  • 3
    SQL injections can be trivially avoided. The real issue here, as you seem to imply (you really need an API...), is that the moment the SQL server can be opened via the connection string, any and all SQL statements can be run on that open connection, limited only by the permissions of the SQL server user. – Zev Spitz Nov 1 at 6:06
  • SQL injections can certainly be avoided - but that doesn't mean that they are! The sort of framework you link to is exactly what should be but doesn't get used. I probably wouldn't want to give any external party any indication of the inner workings of the system in any case. – cyborg Nov 1 at 9:09
  • 2
    SQL injection is not something you just ask for, -- it depends on your own badly written code which building SQL statements from user input that can be easily corrupted. But this doesn't seem to be related to the OP's question. It is perfectly possible, and even trivial, to build an application that is not vulnerable to SQL injection, but where the DB will happily accept any statement from anyone with a valid connection string (subject to permissions defined on SQL server) because it depends on raw SQL access to the database. Hence, an API that defines valid actions is needed. – Zev Spitz Nov 1 at 17:34

Using your database as your server is not the correct way to protect your data, in addition to tightly coupling your design with this database implementation.

you would typically have an application service layer consume database calls, (preferably as stored procedures) and implement business logic at that layer. Then comes a presentation layer (preferably an API layer) where it would decouple clients from the business engine behind it.

typically this API layer will consume some authentication service, to ensure this client has been authenticated, and is authorized to view this information they are seeking (using this API)

back to the original client->database architecture, with SQL embedded into clients, this is bad for the following reasons:

  1. your clients can be used to compromise your database and gain access to all data
  2. you cannot change your database objects without rolling out changes to all clients
  3. if you have a minor correction to your query, you have to roll it out to all clients

conclusion:

  • keep your database
  • build stored procedures wrapping table access
  • build an application layer to consume stored procedure layer
  • build an API layer to consume application layer
  • build/use an authentication service
  • API layer will consume authentication services
  • have your client applications access only this API layer

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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