How to Restrict Database for One User in SQL Server 2008 R2?

What's the safest way to get this data to the outside?

• I have a database table with sensitive (read as personal, non-financial) information inside our office locked-down network that I need to expose to users on a website. What's the best way for me to expose this data without exposing us to unnecessary risk? I have a 200MB-ish table of 200k-ish rows of data associated with users, that we want to expose to users on a website so that users can edit their data without us having to be involved - although we will still be involved in updating as well, so it'll be a two-way sync. The problem is that the data in question is currently kept within a database server that's within our office network, which is locked down (and appropriately so). This is the start of us likely doing this with more data and databases that are currently internal-only. Nothing that will likely be in the tens of gigabytes of size, but I wouldn't be surprised to get a gigabyte-sized table get involved in the future. All solutions seem to be revolving around Azure at this point, as the company wants to start expanding to use Azure in other things as well (outside of me who spends his days in Linux, everyone else here is MS born and bred). The database in question is a SQL Server 2008 R2 instance. My idea was to use a virtual network to extend our internal network to include our Azure account, and then write a web API hosted within Azure to access the database so that we can lock down precisely what data we can grab from it (locked down SQL user that can only access a particular view, and all interactions happening with stored procedures). Then open up just port 443 on this Azure web API instance/virtual network, encrypt the transfer, and lock down access to that point to only come from a non-public IP from the website in question, which will be the only IP that will need to access this data. There's some pushback on this idea though - I think because of opening 443 on the Azure virtual network. Our senior dev wants to use SQL Sync Agent to synchronize the table up to Azure over an encrypted connection, which then would be in the cloud and exposed, but since the data is encrypted up to the cloud (and synced), we can query the database directly from the website over a secure connection. With the SQL Sync Agent, no additional ports would need to be opened, but the database table would sit out on the cloud. My background is in web, so I tend to just want to make APIs out of everything. I feel like I'm right in this situation (or at the very least both of our options are valid), but our senior dev is a smart guy, and his resistance to my method means I end up feeling like I'm missing something obvious in both sides of this. Am I?

• Answer:

  Caveat: I know nothing about Azure or Microsoft technology. Also if you're dealing with regulations like HIPAA, PCI-DSS, etc. different rules apply and I am not speaking to any of that. I would not poke a hole into your office network for this, even if it's really locked down. That's not a great architecture from a performance, reliability, or security standpoint. I would either move or replicate the data that is needed into a database instance inside of Azure. Yes, having the database "in the cloud" adds some risk, but it removes other risk, and if you do it correctly it should be adequately secure. I'd personally be more worried about viruses coming in on employee laptops and getting at your internal network than people getting at a properly-secured database in Azure. Keep in mind "the cloud" is how the vast majority of new technology companies are building their stuff, and the direction many older companies are moving. It's not inherently insecure. You do need people who know what they're doing to configure the systems and write the code. But you need that either way, if you're going to be opening up an application to the world.