Port 1433 and .NET 3.5 connection to SQL server

Question

I have a public SQL server (2008) running on port 1433.  When I connect to it remotely thru a .NET 3.5 Windows forms application using:

SQLConnection =

New System.Data.SqlClient.SqlConnection("Server=" & ServerName & ";" & _
"uid=" & UserID & ";" & _
"pwd=" & UserPassword & ";" & _
"Max Pool Size=200;")

Is this connection information available and easy to read for anyone running a packet sniffer?  I believe IT people like to call this "clear text"?

If it is, will setting up my SQL 2008 server to run SSL ensure packet information is encrypted?  If so, does anyone have links on how to setup SQL 2008 to use our SSL certificate (we've obtained for our server) and what changes will need to happen in my Connection string, if any?

Thanks, Rob.

Answer 1

Nevermind, I was able to determine that this code does NOT generate any "clear text" userID and/or password in the network packets -- loaded up a packet sniffer and ran some tests.  All sensitive information is actually encrypted in the database so not worried about it being visible there.  All good from that stand point.

But still plan to either move to SSL SQL or do Web Services thru SSL and take a big performance hit -- but we'll close 1433.  Unfortunately VPN is not viable, public servers.

So for anyone else worried about userID/Password showing as clear text, it doesn't.

Rob.

Answer 2

I am not sure I understand. When you say "move to SSL SQL", what are you refering to? Are you talking about implementing TDE, partial database encryption, or file level encryption? Or are you talking about something else altogether?

Also, even if your information in your code was clear text, you could still implement encryption on the network side of the server. You could implement something like IPSec which could encrypt all traffic on the network interface of the server. You would just need to be careful of how it is setup though, because if done wrong it could drop connectivity to your clients and/or other servers.

---

Joie Andrew "Since 1982"

Answer 3

MS did some work on 2005 to make sure that uid and pwd are always encrypted. I have a feeling that Books Online for 2005 has some information about this new feature (and possibly also BOL 2008).

---

Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi

Answer 4

Tibork,

Agree (using VS 2008 .NET 3.5), I've captured packet data with a sniffer while testing aspects of my applications, SQL text and SQL return data is visible (as in easy to read), but pwd and userID are not.  Not worried about return data since anything that is sensitive is encrypted in the database -- I can see the encrypted data via the packet sniffer so that's validated.

Joie,

No, not using TDE, do my own encryption in code before writing data, code is obfuscated prior to deploy.  I don't know much about IPSec but was warned not to use it -- but I thought (and could be wrong) that IPSec requires both client and server configuration?  In a public environment I have no such options to configure IPSec on client side. 

I've already purchased an SSL cert (EV) from a CA that I use for our web site.  As I understand it, I can use the same SSL cert for our SQL 2008 servers (same sub domain) and close up port 1433.  I just have to modify the connection string in my application code and set SSL = True.  Obviously SSL will come with a performance penalty and I'll test just how much of a penalty, but so be it. 

Rob.

Answer 5

   A quick word of warning. While the login and password may not be transmitted in plaintext, the self-signed certificate used to obfuscate the SQL login credentials is only effective against passive man-in-the-middle (MitM) attacks, but it is ineffective against active MitM.  Given that in your scenario SQL Server is facing the Internet, I would strongly recommend using SSL and forcing the encryption in the communication.  For more detailed information I recommend the following links:

* Encrypting Connections to SQL Server (http://msdn.microsoft.com/en-us/library/ms189067.aspx)

* Certificate for SQL Server 2005 (http://blogs.msdn.com/sql_protocols/archive/2005/12/30/508311.aspx)

  An additional suggestion for the client code. Instead of constructing the connection string by string concatenation, I would suggest using the SqlConnectionStringBuilder class, it will not only make the code cleaner and easier to read, it will also provide you with an easy way to modify settings on the connection string (including adding encryption).

  I hope this information helps,
 -Raul Garcia
  SDE/T
  SQL Server Engine

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 6

Raul,

That's the plan to move to SSL.  Thank you for the links.  I'll look into SqlConnectionStringBuilder.

I'm running SQL 2008 -- was hoping I could use our SSL EV we purchased from GoDaddy  SQL server is located on the same computer as web server.  Based on your link I "should" be able to do this.  FQDN is set however, my client applications reference it via IP -- switching to SSL will my client applications be required to use FQDN for SQL connections?

Thanks, Rob

Answer 7

Raul,

No love getting my CA UCC SSL certificate to appear in the dropdown under Network Protocols for my instance (MYSQLSERVER).  Does my SQL instance name need to be the same as the FQDN my certificate was provisioned for?

In fact, if I select ForceEncryption = Yes, restart SQL services, I can still connect remotely without using SSL connection???  Even with port 1433 open, shouldn't ForceEncryption = Yes prevent ANY connections that are not encrypted?  I've checked the SQL Logs and I see "A self-generated certificate was successfully loaded for encyption" - not what I wanted, but it is what I expected to see given that my SSL certificate doesn not show up in the "Protocols for MSSQLSERVER".

So it looks like ForceEncryption = Yes just doesn't work with a "self-generated" certificate -- is this "by design"?

Rob