May 11, 2010 at 9:22 pm
Folks- without going into gory detail as to why, i have a request from our security department to encrypt communications when people connect to our SQL 2005 and 2008 databases through SSMS. From what i've read, it appears i can encrypt ALL communications by downloading a certificate and setting the ForceEncryption property through the Network Configuation section of SSCM. However, in this case, I'd like to encrypt only communications for SSMS - not for any other processes connecting to our instance (ie: Sharepoint).
From what i've read, it appears clients such as SSMS can request encryption but are still allowed to connect if SSL is not available.
Does anyone know how to accomplish this? One thing I was wondering was whether it would be possible to create a TCP endpoint on a port other our existing TCP port (ie: 1433), open access to that port through one of our firewalls (either local on the server or at the network level) and designate that port for SSMS access. Does this make any sense?
Forgive me if this is a stupid question - this is one of many areas of SQL with which i'm not all that familiar. Any and all help would be appreciated. Thanks much in advance.
May 13, 2010 at 1:54 am
Ken,
Basically you would need to use a logon trigger and query sys.dm_exec_sessions for the program_name. Unfortunately though I think you would need to come up with other criteria other than just that they are using SSMS. While you can see the program name in sys.dm_exec_sessions, that name can be configured to anything the user wants. For example in SSMS 2008 there is Additional Connection Parameters tab in the Connect to Server dialog. Someone could very easily specify "Application Name=some other app" and get around your trigger.
Your idea of another endpoint could work depending on your environment. So long as that port is the only one open to wherever the ssms users are on the network you could then check for the endpoint_id. If that is not an option then perhaps you could base the criteria on ip address, loginname, etc.
-Jeremy
May 13, 2010 at 7:54 am
Thanks very much, Jeremy. That's good information. I appreciate the sanity check and suggestions!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply