I'm planning for a small hosted ASP.NET application which uses a Microsoft SQL Server 2008 database. One of the users which uses the application doesn't want his data to be stored in a remote database and has requested to store his rows of the database on his on premise database server.
First, is this possible with SQL server? And if it is, is this even safer if the application is still running on a remote server and the data is used there (application has to load and transfer data from on premise server to remote server, so the data could potentially be loaded by anyone)?
Best Answer
Yes this is possible, but is not a good idea. You can theoretically run a web application anywhere and punch firewall holes so that the web application can access a database on prem. This opens up a whole lot of issues as the database will have to be on a secure network segment and can wreck havoc on security policies.
You can make this more secure by using SSL for the database connection and use strict access lists on the firewalls, use Site2Site VPN and so forth but you are accessing the database over wide area network which can affect other users.
But in any case you will have to re-route all traffic from this customer to a different server, which will make the application more complex so it's probably easier to install the application on-prem for this customer.