March 29, 2011 at 9:37 am
Hi All,
The company that i work for use an external company to host our live web and database servers.
We currently administer these databases using RDP or PC Anywhere but the request has been made that we allow connections via management studio.
I am aware of the increased security risk that this would bring but i have never worked with such a set up. I was hoping to get feedback fro mmore experienced DBA's about whether allowing these connections could ever be seen as a viable option?
Kind Regards.
March 29, 2011 at 9:54 am
Yeah, it can work. But you have to use SQL logins as well as expose the server through the firewall. From a security standpoint, I don't like it. But is it viable? Yes. Lots of people are doing it successfully, but with an elevated risk.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2011 at 10:13 am
Thanks for the quick reply, i'll take your perspective on board.
March 29, 2011 at 10:27 am
Out of interest; would you see it as a significant increase in risk to allow connections via enterprise manager considering that we already allow access via RDP?
March 29, 2011 at 10:39 am
DBANewbie (3/29/2011)
Out of interest; would you see it as a significant increase in risk to allow connections via enterprise manager considering that we already allow access via RDP?
You must have AD set up for RDP. I guess it can work remotely (although when I've done this type of remote connection I've always had to use SQL logins). It's not SSMS that concerns me, it's the type of login.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 2:01 am
We use mixed authentication currently anyway so there are some SQL Server logins already in use.
Thanks for your replies so far, i keen to learn as much as i can before making this decision one way or the other.
Am i right to assume that data transferred between the remote server and my machine running SSMS within our network would be unencrypted by default? I know that there are encryption options available within SQL Server so i intend to spend some time researching the various options available.
March 30, 2011 at 5:36 am
DBANewbie (3/30/2011)
We use mixed authentication currently anyway so there are some SQL Server logins already in use.Thanks for your replies so far, i keen to learn as much as i can before making this decision one way or the other.
Am i right to assume that data transferred between the remote server and my machine running SSMS within our network would be unencrypted by default? I know that there are encryption options available within SQL Server so i intend to spend some time researching the various options available.
Encryption is outside my knowledge I'm afraid to say. I've only worked a bit with database encryption. I haven't worked with encrypting connections. If we're looking in that direction I can try to track down someone else to help.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 5:57 am
DBANewbie (3/30/2011)
Am i right to assume that data transferred between the remote server and my machine running SSMS within our network would be unencrypted by default?
I suppose that you have a VPN in place and you have to start a VPN client (or the VPN is configured directly on the network appliances) to connect to the remote site. If this is the scenario, the connection is already encrypted and there's no reasonable need for further encription layers IMHO.
-- Gianluca Sartori
March 30, 2011 at 6:00 am
BTW, if you still want to set up an encrypted connection with SQL Server, you will have to enable SSL.
This page on MSDN could be a good place to start looking:
http://msdn.microsoft.com/en-us/library/ms189067.aspx
Hope this helps
Gianluca
-- Gianluca Sartori
March 30, 2011 at 6:13 am
Using PC Anywhere doesn't necessarily mean there's a VPN or SSL tunnel set up. You might want to check on that to make sure connections really are already encrypted.
The thing to consider about SSMS connections, above and beyond encrypting them, is the domain trust issues and the firewall setup. Depending on how security is configured on the database source, you might not be able to connect to SQL via SSMS no matter how hard you try.
I know you said someone else hosts the servers, but do you actually own the servers (as in this is just an off-site server facility that does this for multiple clients) or is your company renting space on someone else's boxes? If the former, you have far more flexibility in dictating your security needs than in the later situation.
Verify that VPN / SSL tunnel setup before you do anything else. Then follow the link recommended by Gianluca. After reading that, if you need more assistance, give us a few more details and your questions.
March 30, 2011 at 6:24 am
Thanks to you all for your imput so far; it's greatly appreciated.
With regards to the VPN; the answer is that i don't know. I will speak with our Network support team and enquire about the connections to the hosting site.
I can connect via PC Anwhere and RDP without having to start up a VPN Client but this may happen behind the scenes without my knowledge.
I'll speak with the support team and post the results.
Thanks again.
March 30, 2011 at 7:30 am
We don't currently have a VPN or an SSL set up for connection betweens our network and the hosted servers. The network administrator has confirmed that if we are to allow connections via SSMS he would set this up.
In answer to your previous question; we do indeed own the servers and the company act as an off-site server facility. In previous corresposnance with them they have been happy to make any security changes that have been requested (they recently opened up the neccessary ports to allow us to connect using RDP as an alternative to PC Anywhere).
Thanks again to each of you for your help.
March 30, 2011 at 9:04 am
I have read through the article on using SSL for data encryption within SQL Server that was suggest earlier in this thread; as well as doing some reserach on some associated topics.
If i set the ForceEncryption option to No (because i only want to use encryption with connections from SSMS) the article says that encryption can be requested by the client.
I was just wondering if anyone could point me to a decent resource detailing how i would be able to configure all connections from SSMS to use encryption.
I'm not sure that i have explained my question very well, please feel free to ask me for clarification.
Thanks.
March 30, 2011 at 9:10 am
Start here:
Enable SSL Encryption for SQL Server Instance
MSDN Blog on Encrypted Connections
You can also google "sql 2005 encrypted connection" (without the quotes) for more articles.
March 30, 2011 at 9:42 am
I knew I could get some help in here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply