SSL Connection to database

Question

My application need to establish secure connection with SQL Server 2008. Having enabled 'Force Encryption' in server side, following is my connection string from my C# application.

Initial Catalog=emp_test;Persist Security Info=True;User ID=sa;Password=***;Data Source=172.21.70.94;Provider=SQLOLEDB;Use Encryption for Data=True;Trust Server Certificate=True;

I did not provision any certificate in server - Hence I gave Trust Server Certificate=True, so that self signed server certificate is not validated.

But the connection is not established with following error.

Database error: [DBNETLIB][ConnectionOpen (SECDoClientHandshake()).]SSL Security error.

Without the two attributes related to security, it works fine.

What do I need to change to get this to work?

Answer 1

Using the SqlConnection object gives you two advantages. First, you can ensure the connection string will be built properly because you can use the SqlConnectionStringBuilder class to build it. Second, it's much faster than OLEDB.

To build this connection string ...

Initial Catalog=emp_test;Persist Security Info=True;User ID=sa;Password=***;Data Source=172.21.70.94;Provider=SQLOLEDB;Use Encryption for Data=True;Trust Server Certificate=True; 

... using the SqlConnectionStringBuilder you would write some code like this ...

var builder = new SqlConnectionStringBuilder();
builder.DataSource = "172.21.70.94";
builder.Encrypt = true;
builder.TrustServerCertificate = true;
builder.InitialCatalog = emp_test;
builder.PersistSecurityInfo = true;
builder.UserID = "sa";
builder.Password = "***";

var connection = new SqlConnection(builder.ToString());

... the Encrypt property holds this definition in the .NET Framework ...

Gets or sets a Boolean value that indicates whether SQL Server uses SSL encryption for all data sent between the client and server if the server has a certificate installed.

... the TrustServerCertificate property holds this definition in the .NET Framework ...

Gets or sets a value that indicates whether the channel will be encrypted while bypassing walking the certificate chain to validate trust.

So I would say that this is the safest approach. You can ensure that the .NET Framework is going to build a connection string properly and you can get a good set of definitions surrounding what those properties mean in regards to certificates based on their definitions.

---

Now, since you connect to Oracle too, the best approach there would be to continue to build a OLEDB connection because you don't have much of a choice. But both connections are an IDbConnection and so you just have a factory that builds the right connection and returns an IDbConnection.

This means you get the best of both worlds, the performance and ease of the SqlConnection object and the abstraction of the IDbConnection so that your code doesn't have to change.