How do I move this database online?
January 18, 2012 6:56 AM Subscribe
What's the easiest, most secure way to move this SQL database and its browser-based front end online?
I'm the de facto IT person at a nonprofit, but I have very little formal computer education (like many de facto nonprofit IT people, I have an English degree), so I've never done this before. We have a fairly massive, custom-built database of our clients; it's an SQL database (on Microsoft SQL Server Express) with a web-based front end (written in ASP.net, if that matters). Currently, it's hosted on an in-house server that is NOT connected to the rest of the Internet. My boss wants it moved online so our satellite offices can connect to it, and I'm researching ways of doing that. Naturally, I have questions.
1. I'm guessing that the easiest way would be to rent a virtual server from somewhere like Rackspace. Is that accurate, or would there be a cheaper, easier way?
2. What about security? My boss is very concerned about that. Currently, the database has its own password, as does the in-house server it sits on. If we move this to a virtual server on Rackspace, should we take additional security measures?
3. Considering I've got very little background as a server administrator, what am I overlooking? I'm sure I'm overlooking stuff.
Again, please be gentle, genius IT people of the Hive, and remember, I was an English major, so all this is kind of new to me, but it's been added to my official job description, so there's no backing out now!
I'm the de facto IT person at a nonprofit, but I have very little formal computer education (like many de facto nonprofit IT people, I have an English degree), so I've never done this before. We have a fairly massive, custom-built database of our clients; it's an SQL database (on Microsoft SQL Server Express) with a web-based front end (written in ASP.net, if that matters). Currently, it's hosted on an in-house server that is NOT connected to the rest of the Internet. My boss wants it moved online so our satellite offices can connect to it, and I'm researching ways of doing that. Naturally, I have questions.
1. I'm guessing that the easiest way would be to rent a virtual server from somewhere like Rackspace. Is that accurate, or would there be a cheaper, easier way?
2. What about security? My boss is very concerned about that. Currently, the database has its own password, as does the in-house server it sits on. If we move this to a virtual server on Rackspace, should we take additional security measures?
3. Considering I've got very little background as a server administrator, what am I overlooking? I'm sure I'm overlooking stuff.
Again, please be gentle, genius IT people of the Hive, and remember, I was an English major, so all this is kind of new to me, but it's been added to my official job description, so there's no backing out now!
Response by poster: We don't have a VPN. Would that be a better way? Would we still need to host it somewhere, or would it be better to set up a VPN on the server in our building? (And if so, is that pretty easy?)
posted by infinitywaltz at 7:05 AM on January 18, 2012
posted by infinitywaltz at 7:05 AM on January 18, 2012
Best answer: If the public doesn't need to get on it, I'd pursue a course like countrymod suggests. If you have an application that was designed to sit on a local network and you put it out there on an internet-facing server, the security considerations could be myriad.
I don't think there's any shame in saying that this project requires an outside contractor to do safely. I'd get a IT/network/computer support company with some decent references (i.e. not "Dr. Computer's PC support and sandwich shop") or two to quote it.
posted by randomkeystrike at 7:11 AM on January 18, 2012
I don't think there's any shame in saying that this project requires an outside contractor to do safely. I'd get a IT/network/computer support company with some decent references (i.e. not "Dr. Computer's PC support and sandwich shop") or two to quote it.
posted by randomkeystrike at 7:11 AM on January 18, 2012
Sanitize your inputs. You want to be on guard for SQL injections. Google it, it'll be very high on your list of things to secure against.
posted by unixrat at 7:20 AM on January 18, 2012
posted by unixrat at 7:20 AM on January 18, 2012
Is there a reason to stay loyal to this home-built application? There's a lot of solutions out in the world for customer relations management (CRM). You don't say whether your database is purely a souped-up address book or if it has CRM features (like tracking who contacted when, future follow-ups, etc) as well.
If it's solely an address book type of solution then you might be better off just outsourcing your Exchange mail service to someone like Rackspace and using the shared built-in facilities or looking into hosted Sharepoint for some additional stuff. MS jokes aside, they're better equipped to avoid common security issues than you are as a single person.
If your home-grown app has more bells and whistles and you have people accustomed to doing things its way then that's one thing. However if it doesn't then I'd seriously look into transitioning to someone else's product. Odds are good your home-grown application already has a number of things people wish were different. If you're preparing to spend some money and effort on this then this is a good time to look into moving to something where you're not dependent on over-stressed internal resources.
Regardless, I'd encourage you to go look at the resources at NTEN. I see questions similar to yours all the time on the DC-area mailing list and there's always people with similar backgrounds and situations to yours who have recently solved the same problems. There's also a whole lot of up-to-date CRM talk and there's usually coders and solutions providers hanging out to offer support or services.
posted by phearlez at 7:58 AM on January 18, 2012 [2 favorites]
If it's solely an address book type of solution then you might be better off just outsourcing your Exchange mail service to someone like Rackspace and using the shared built-in facilities or looking into hosted Sharepoint for some additional stuff. MS jokes aside, they're better equipped to avoid common security issues than you are as a single person.
If your home-grown app has more bells and whistles and you have people accustomed to doing things its way then that's one thing. However if it doesn't then I'd seriously look into transitioning to someone else's product. Odds are good your home-grown application already has a number of things people wish were different. If you're preparing to spend some money and effort on this then this is a good time to look into moving to something where you're not dependent on over-stressed internal resources.
Regardless, I'd encourage you to go look at the resources at NTEN. I see questions similar to yours all the time on the DC-area mailing list and there's always people with similar backgrounds and situations to yours who have recently solved the same problems. There's also a whole lot of up-to-date CRM talk and there's usually coders and solutions providers hanging out to offer support or services.
posted by phearlez at 7:58 AM on January 18, 2012 [2 favorites]
Response by poster: phearlez: Is there a reason to stay loyal to this home-built application?
Yeah, it wasn't home-built, it was built at great expense as a customized solution for our organization. It was also built in the past couple of years, and is designed not only to provide access to our support staff for ongoing case management but also to generate reports for a number of specific government grants.
odinsdream: If you just need other remote offices to get to it, forget about the public internet. Focus your efforts on making the solution accessible remotely, either with a VPN connection between your two offices
I'm thinking this is probably the best solution; we don't need the public to have access (we don't WANT the public to have access, in fact), just a few satellite offices, some of which are basically just people working out of their homes. I'm thinking VPN would be the best solution.
On that note, does anyone have some beginners' resources for setting up a VPN?
posted by infinitywaltz at 8:18 AM on January 18, 2012
Yeah, it wasn't home-built, it was built at great expense as a customized solution for our organization. It was also built in the past couple of years, and is designed not only to provide access to our support staff for ongoing case management but also to generate reports for a number of specific government grants.
odinsdream: If you just need other remote offices to get to it, forget about the public internet. Focus your efforts on making the solution accessible remotely, either with a VPN connection between your two offices
I'm thinking this is probably the best solution; we don't need the public to have access (we don't WANT the public to have access, in fact), just a few satellite offices, some of which are basically just people working out of their homes. I'm thinking VPN would be the best solution.
On that note, does anyone have some beginners' resources for setting up a VPN?
posted by infinitywaltz at 8:18 AM on January 18, 2012
How technical are your people? How "corporate" is your IT?
Commercial VPN offerings, like Cisco's VPN, will cross off every checklist item your board will come up with. On the other hand, they will cost a lot to implement.
On a much smaller level, I've created private sites and file shares and accessed them remotely using PuTTY and secure (encrypted) tunnels (lots of tutorials online for this sort of thing).
If I were IT at a small business with your situation, I would write some scripts to automate the secure tunnel approach and install them on everyone's computer in the remote office, possibly with some instructions on how to launch the script and a shortcut on their desktop to open the site (launching the script could also be automated). The site would remain essentially internal. The largest security risk would be the employees, assuming the SSH server was configured well and strong passwords or keys were used.
If I were IT at a large, publicly traded corporation, I would go with Cisco's VPN offering because I would want to cover my ass and not get fired the first time something went wrong.
posted by jsturgill at 9:04 AM on January 18, 2012 [1 favorite]
Commercial VPN offerings, like Cisco's VPN, will cross off every checklist item your board will come up with. On the other hand, they will cost a lot to implement.
On a much smaller level, I've created private sites and file shares and accessed them remotely using PuTTY and secure (encrypted) tunnels (lots of tutorials online for this sort of thing).
If I were IT at a small business with your situation, I would write some scripts to automate the secure tunnel approach and install them on everyone's computer in the remote office, possibly with some instructions on how to launch the script and a shortcut on their desktop to open the site (launching the script could also be automated). The site would remain essentially internal. The largest security risk would be the employees, assuming the SSH server was configured well and strong passwords or keys were used.
If I were IT at a large, publicly traded corporation, I would go with Cisco's VPN offering because I would want to cover my ass and not get fired the first time something went wrong.
posted by jsturgill at 9:04 AM on January 18, 2012 [1 favorite]
Response by poster: How technical are your people? How "corporate" is your IT?
They're not technical, and our IT is not corporate. Our "IT department" is basically me (an English major, for the love of god) and our executive director, who is a retired electrical engineer and computer guy who occasionally comes in to help me with stuff I can't figure out for myself or that he can't explain over the telephone.
posted by infinitywaltz at 9:15 AM on January 18, 2012
They're not technical, and our IT is not corporate. Our "IT department" is basically me (an English major, for the love of god) and our executive director, who is a retired electrical engineer and computer guy who occasionally comes in to help me with stuff I can't figure out for myself or that he can't explain over the telephone.
posted by infinitywaltz at 9:15 AM on January 18, 2012
I like the VPN approach -- but before you head down that route, I'd suggest a bit of internal analysis. Are there other applications for your VPN? Shared directories, automatic backup, centralizing data -- and if you can leverage the VPN for more than just this one off access to a server, you may be able to get more support for the project, improve the infrastructure, and get a little more funding to implement.
Also think about security requirements and audits you'll need to pass (or continue to pass).
posted by countrymod at 10:21 AM on January 18, 2012
Also think about security requirements and audits you'll need to pass (or continue to pass).
posted by countrymod at 10:21 AM on January 18, 2012
Ah; I'da called that an application, not a database, but terminology in the modern age on these things is always imprecise. I blame Oracle (really).
I reiterate my suggestion that you look into NTEN's resources. These are people who are dealing with similar staffing and financial solutions to you and will be aware of places to direct you for good services and pricing.
I agree with folks above that a VPN is a good 'solved problem' way to respond to this but it might come with a lot of overhead, depending on what your current network connectivity solutions are. In your current staffing arrangement you don't want to destabilize your entire network architecture in order to provide access to a single application for a few remote folks.
If the goal really is easy above all else and you have a few old machines in a corner you could press into services then set them up with no more software than the web browser, pile them headless in the corner/server room, and put LogMeIn on them for your remote users. Marginally more complicated but pretty well documented: set up a Windows server the users can remote desktop into.
Marginally more complicated on both ends but in the same vein, a SOCKS proxy you run inside your network could do the trick. Your remote users can have a configuration that only uses it to get to that one server inside your network and everything else connects directly.
posted by phearlez at 10:22 AM on January 18, 2012
I reiterate my suggestion that you look into NTEN's resources. These are people who are dealing with similar staffing and financial solutions to you and will be aware of places to direct you for good services and pricing.
I agree with folks above that a VPN is a good 'solved problem' way to respond to this but it might come with a lot of overhead, depending on what your current network connectivity solutions are. In your current staffing arrangement you don't want to destabilize your entire network architecture in order to provide access to a single application for a few remote folks.
If the goal really is easy above all else and you have a few old machines in a corner you could press into services then set them up with no more software than the web browser, pile them headless in the corner/server room, and put LogMeIn on them for your remote users. Marginally more complicated but pretty well documented: set up a Windows server the users can remote desktop into.
Marginally more complicated on both ends but in the same vein, a SOCKS proxy you run inside your network could do the trick. Your remote users can have a configuration that only uses it to get to that one server inside your network and everything else connects directly.
posted by phearlez at 10:22 AM on January 18, 2012
Best answer: I agree with the consensus that VPN is the best solution. It provides the best security and least administrative hassle. That said, if using a VPN creates too much friction for your users or is cost prohibitive, I wanted to point out that this is not your only option.
If you have static IP's at both ends (branch and home), and you have adequate bandwidth and a real firewall at home office, you can place the web server in a DMZ and lock down access by IP (...and port, and if possible protocol. Both ways in and out). Serve the site over HTTPS (create the subdomain secure.whatever.yes and buy a cheap cert) and this is an "it's okay" type solution to your problem which is easier to deploy and works better for users. You can skip hardening the application, but you will still have to harden the web server and firewall. Basically, I am suggesting serve the app out of your office but secure the shit out of the connection.
One caveat is that this is not applicable to people working from home connections, unless they can get a static IP. Home workers should still use a VPN or remote desktop.
On that note, does anyone have some beginners' resources for setting up a VPN?
It's not a good idea to do this yourself if you aren't sure you can do it right. If you don't have the expertise in house, please get a consultant. Only you can decide if the risk here justifies the cost, but if your client data can be exposed then in my opinion it definitely does. Learning how to do something is great, but not with production data.
posted by tracert at 10:42 AM on January 18, 2012
If you have static IP's at both ends (branch and home), and you have adequate bandwidth and a real firewall at home office, you can place the web server in a DMZ and lock down access by IP (...and port, and if possible protocol. Both ways in and out). Serve the site over HTTPS (create the subdomain secure.whatever.yes and buy a cheap cert) and this is an "it's okay" type solution to your problem which is easier to deploy and works better for users. You can skip hardening the application, but you will still have to harden the web server and firewall. Basically, I am suggesting serve the app out of your office but secure the shit out of the connection.
One caveat is that this is not applicable to people working from home connections, unless they can get a static IP. Home workers should still use a VPN or remote desktop.
On that note, does anyone have some beginners' resources for setting up a VPN?
It's not a good idea to do this yourself if you aren't sure you can do it right. If you don't have the expertise in house, please get a consultant. Only you can decide if the risk here justifies the cost, but if your client data can be exposed then in my opinion it definitely does. Learning how to do something is great, but not with production data.
posted by tracert at 10:42 AM on January 18, 2012
Response by poster: Anyone have a ballpark idea on what bringing in a consultant would cost to set up a VPN? I've got a decent-sized budget for this project (send me a MeMail if you need to know exact numbers). This is basically one of those types of things where it needs to be done ASAP, so that any money we don't use now can be diverted elsewhere in the organization's budget.
posted by infinitywaltz at 10:56 AM on January 18, 2012
posted by infinitywaltz at 10:56 AM on January 18, 2012
A VPN is a good solution, but you might also find that a Terminal Server works well too. With a VPN you create a tunnel connection so that remote people access files and resources as if they were on the local network. A terminal server lets them remotely log into a server and access resources, like this database app, once they are logged in. If you work with a local company to set this up you might ask them to compare and contrast these solutions.
posted by dgran at 2:20 PM on January 18, 2012
posted by dgran at 2:20 PM on January 18, 2012
Response by poster: We went with a Cisco-affiliated consulting firm and bought our actual Cisco hardware from TechSoup, which provides low-priced technology to nonprofit organizations. So far, so good; the only real glitch is that our remote control software interferes with Cisco's VPN software, which makes troubleshooting connection issues with our remote users a bit more difficult. ("Yes, I understand that it isn't connecting, but can you read me the actual message? No, just read the message that shows up on the screen. The message that says you aren't connected. Yes, I get that it says you aren't connected, but can you read me the message? Yes, word for word.")
Other than that, it's been a pretty good solution, and I'm really relieved that I didn't go with my initial idea, which was to just stick the application on a rented server somewhere.
posted by infinitywaltz at 1:25 PM on December 31, 2012
Other than that, it's been a pretty good solution, and I'm really relieved that I didn't go with my initial idea, which was to just stick the application on a rented server somewhere.
posted by infinitywaltz at 1:25 PM on December 31, 2012
This thread is closed to new comments.
posted by countrymod at 7:00 AM on January 18, 2012