Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am connecting to a Windows 2008 R2 Server with SQL Server 2008 R2 Express installed. I am connecting through a remote connection, not on the same LAN. I am connecting to the SQL Server 2008 R2 Express through VS2010. This is simply a database connection made remotely. Is this remote connection secure? If so, how? Does SSL have anything to do with it? When connecting remotely, how are the passwords that are used to log in remotely to the SQL Server secure?

share|improve this question

2 Answers 2

No, the connection is not secure in terms of people being able to sniff the packets, and see the data (or some of it) being returned. You'll need either certificates, IPSEC, VPN, or something similar.

If you are logging into the SQL Server using windows credentials that should be more secure - AD. However, since it's not on the same LAN, it sounds like you might be using a SQL login, which is inherently less secure that a Windows domain account.

share|improve this answer

SQL Server offer privacy (ie. are encrypted) and are tamper proof (ie. signed) if configured so. The protection is done indeed using SSL (actually is TLS, details...). See Encrypting Connections to SQL Server and How to: Enable Encrypted Connections to the Database Engine for details, including the steps to enforce it.

The question about passwords is a completely different topic. There are two authentication modes supported by SQL Server: Windows and SQL Authentication. If your connection string specifies Trusted Connection=True, or Trusted connection=SSPI then you use Windows authentication. If you specify a user and a password User ID=Carpenter;Password=secret then you use SQL Authentication.

Windows authentication will use NTLM or Kerberos, neither of which require the password to be sent over the connection. But if you use SQL Authentication then the password will be sent over the connection during the handshake. If the connection is not protected by an SSL tunnel, then the password is in clear text and can be snooped by anybody.

As long as you enforce SSL (the articles linked show step-by-step how to do it) you are safe.

share|improve this answer
    
Starting in SQL 2005 the packet with the password is actually encrypted via a self signed cert and sent via SSL so that the password isn't sent via clear text any more. –  mrdenny Mar 23 '11 at 18:40
    
@mrdenny: The connection SSL tunnel can be established using a self signed cert, if the client is configured to accept it. But I'm not aware of any 'special' encryption for the password packet if no SSL tunnel is established. –  Remus Rusanu Mar 23 '11 at 19:03
    
@Remus, My info is from Rick Byham (BOL team) which he was kind enough to hunt down and provide me for my book. I'll email you what he sent me (it won't fit in here). –  mrdenny Mar 24 '11 at 8:00
    
BOL documentation - msdn.microsoft.com/en-us/library/ms189067(v=SQL.90).aspx. I've confirmed that behavior with packet traces myself from when SQL Server 2005 first came out. There's also this thread: social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/…. Also, technically in SQL Server 7 and 2000 the password is encrypted on the wire, it's just that the algorithm is trivially exploited. –  K. Brian Kelley Mar 24 '11 at 9:08
1  
That's what happens when you leave the product team for a few years. Now that I've proven Remus wrong I can retire. :) –  mrdenny Mar 24 '11 at 23:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.