December 13, 2007 at 9:02 pm
Hi guys,
We're developing a VB.Net application that is using data stored in SQL Server. It's end user is a government department. They are not willing to host the server. So, another company with whom we're loosly affiliated is hosting the SQL Server for us on their box (that they are incidentally also using to host a GIS application).
From our development machines in our office, we can reach the server quite happily over the internet using a connection string in the form:
"Provider=SQLOLDB;Initial Catalog=DatabaseName;Data Source=xxx.xxx.xxx.xxx,1500;User ID=xxx; Password=xxx;"
As you can see, port 1500 is being used for this connection.
We've just tried installing a demo of the application on a user's desktop in the government department offices (where the internet is completely locked down) and it can't "see" the SQL Server. The government helpdesk dudes have suggested connecting through their proxy.
Can someone tell me in baby steps what needs to be done to achieve this? What information does who need to give to who? What changes needs to be made where?
December 13, 2007 at 9:26 pm
The proxy needs to know to forward port 1500 to the outside IP. I believe by default this is not the case with most proxy servers.
Be sure their firewall also allows 1500 to pass through.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 13, 2007 at 10:12 pm
I can't tell them anything about how to set up their proxy, unfortunately. This being a government department a) they won't make any changes for me, b) their helpdesk can only tell me to "use the proxy" and gave me a proxy URL.
The thing to get is that it's the application that's behind the proxy, NOT the SQL Server.
Any ideas?
(And sorry, I'm not a DBA, I have no sysadmin knowledge, I'm a data analyst, so I can normalise things up the wazoo, but when it comes to ports and proxies, it's all Greek to me!)
December 14, 2007 at 9:08 am
Andrew,
I think that you are really going to have to work with their networking group to make sure that traffic is allowed out on the required port.
If this is an application that they want to work, then they may have to bend a little.
Sincerely,
Dan B
December 14, 2007 at 9:22 am
Unless they plan on running the application from that area (i.e. hosting the application in that office where internet access is locked down), you might not get a lot of cooperation.
While convenient, setting up a government user's machine as a test server (running IIS) is going to be a tall order (especially if the IT dept doesn't think it's a good idea).
You may need to host the demo externally and allow those users to connect to it (via a VPN etc...). Or - ask the IT folks where you could put a copy of this app you want to demo so that the users CAN access it and it can see the server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2007 at 9:25 am
On another note - it's so very reassuring to see that these IT guru's at the government agency think it's perfectly ok to open an unencrypted port to a SQL server with (apparently) confidential data....
Steve - here's another one to add to the 1/2 million servers exposed to the world....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2007 at 10:24 am
I think you need to get the SQL Server into the internal network. Anything else is not only a bad idea, but probably a big problem.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 14, 2007 at 11:57 am
Not sure I agree, too many unknown variables.
I would take a well run, well maintained SQL Server machine that was remote over a poorly run, un-maintained SQL Server machine that was inside the network.
Also, there is the option of encrypting the connection over SSL - if allowed by the database host, and the performance overhead isn't a concern. Or, making the connection out from the app to the db server via VPN.
Is the data confidential? I don't see any indication that it is.
Ideally, I agree with you both. It is definitely better to have as secure an environment as possible. However, if you are not in control of the environment then your role is limited to that of advisor and you have to work with what you get or work somewhere else. A tough choice.
-Dan B
December 14, 2007 at 12:30 pm
I hear you that you're not in charge of the environment. However, as an advisor as you described it, it would be within my purview (or obligation in my mind) to point out the large security hole this presents. That by itself is a gateway into essentially anything that agency has back there (behind the firewall). It's not just a way to get to/modify/insert/delete data: anyone finding this could take control of it, and use it to do anything else they want to any other server THAT server sees.
you don't need SQL injection in that case - there's nothing preventing direct SQL queries...
Pointing that out is one thing. Them not doing anything about it is no longer your responsibility, but at least you pointed that out....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 14, 2007 at 12:53 pm
A fair point, but in this case, the agency wants a hole outbound, not inbound. Their risk is limited to loss or exposure of whatever data is stored in the database, which may or may not be of consequence.
The real risk is absorbed by the other company that is currently hosting the database server with a port open to the world. Certainly they could, at a minimum, take steps to limit the source IPs that can see the server. I wonder if they are behind a firewall or IPS...
Regardless, your point on obligation is important. I suppose that, if you as DBA decide the potential risk is too great, it does become your responsibility to walk away. If the system is compromised and you are involved, your name would likely come up - and not to say "Well, Dan warned us..." 🙂
Sincerely,
-Dan B
December 14, 2007 at 3:21 pm
Thanks for all the feedback guys.
Concerning security, the data for this application is not confidential, in fact it's all a re-hash of data that is publically available anyhow.
There's already been one round of security review, and the result of that was that the IT support sections for the government department in question was not willing to take on the maintenance of the database. Frankly, having had to deal with these guys, I'm relieved, as they seem to have an internal culture of "No" rather than "How?" (The most important security requirement was to make sure no-one could steal the box!)
If it was a web application, then we could have hosted both the application and the database, but it's not, it's a VB.Net Windows Forms application.
In the meantime, I've asked the sponsor of the project to simply get the government IT support guys to offer us[/] a solution, rather than having us guess what they are willing to do.
This is Queensland in Australia. It would appear that English history still pervades local culture, as I'm clear that I'm dealing with Charles Dicken's "Circumlocution Office"! Much talk, but no-one's brave enough to act!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply