SQL server 2008R2 default encryption?

Question

I was wondering if SQL server 2008 R2 has any default security if you're using it via ODBC or SQL Server Management Studio?

I was reading about SSL here https://technet.microsoft.com/en-us/library/ms189067(v=sql.105).aspx

The issue is forcing encryption right now will break a lot of things and would take a while. I was wondering if there is anything that's set up to encrypt by default to buy some time while we figure ssl out.

We have hundreds of users and some people designed their own database to pull from this one. It would be a huge task with limited amount of time. I have a trusted cert it's just force encryption plan fell out of place. So I guess right now nothing's encrypted - am just trying to see if anything exist by default.

Answer 1

SQL Server communicates using TDS, or Tabular Data Stream, protocol. By default, TDS is not encrypted.

Encryption can be negotiated between the client and the server at time of connection. Essentially, you enable encryption at the server, then roll it out by making the necessary changes at the client end. Since encryption is not forced at this point you are simply reducing the visibility of some data over the wire. Security is about layers of protection; encrypting communications, even if only some communication, is still a valuable layer in the security stack.

Once you have all clients configured to use TDS encryption, you configure the server to only accept encrypted connections. At that point, all communication between the server and its clients will be encrypted.

Answer 2

Simply turning on Force Encryption=Yes via

right-click SSCM-->SQL Server Configuration Manager-->
    SQL Server Network Configuration-->Protocols

...shouldn't break things and will encrypt connections as long as the client supports it.

A self-signed cert will be used, which is not as secure as a certificate issued from a trusted authority, but is more secure than no encryption at all. You could turn it on and provision a cert when you get time.

Exchanging data requires communication between two or more entities. Encrypting the data exchanged requires support on both sides to work. There is no magic that can make it work on the SQL Server side without it also working on the client(s).