How to secure sql database server using SSL for an MFC Application ?

I am using Microsoft OLEDB Provider for SQL Server to create connection.I followed below steps to make my connection SSL secured

step 1. I installed self signed certificate on server and attach it with SQL server(MSSQLSERVER->properties->certificate) and turn on Force encryption to "Yes".
Step 2. I exported the certificate and installed on client machine.(vie MMC->add/remove snap in)
step 3. Added "Use encryption for data=True" in connection string.
step 4. In SQL Server client network utility(on client machine), turn Force protocol encryption to "yes"
step 5. My conncetion string sample : CString SSlcon_string = "provider=SQLOLEDB;Use Encryption For Data=True;TrustServerCertificate=No;server=MyServer;Database=MyDatabase";

Now my observation :
It is able to create SSL connection if server has certificate otherwise it is not creating SSL connection(not failing, creating unsecure connection)
regardless of any settings. i.e it doesn't bother about whether the client has certificate or not, client has marked the force protocol encryption to yes or no.It is not even validating even a single condition on client side.

https://msdn.microsoft.com/en-us/library/ff649255.aspx I followed exactly this procedure but couldn't get desired result.

Requirment : The SSL connection should be created only if the client and server has same certificate(certificate should be validated by client and server ), otherwise the connection itself should fail.

Is there a reason to use SQLOLEDB rather than SQL Server Native Client provider?

Quote:

---

Originally Posted by VictorN

Is there a reason to use SQLOLEDB rather than SQL Server Native Client provider?

---

Actually i am working on a legacy product code developed a long back(around 1992).
So i need to make these changes to that system.

Thanks

I am not the owner of the code.I have been asked to make these changes :)

So did you try to use SQLNCLI10 or SQLNCLI11 rather than SQLOLEDB?

Quote:

---

Originally Posted by VictorN

So did you try to use SQLNCLI10 or SQLNCLI11 rather than SQLOLEDB?

---

I tried with SQLNCLI11 and got the same result, but i don't wanna chance initial implementation(SQLOLEDB). Is there any limitation with SQLOLEDB ?? Why are you enforcing to use SQL Native Library?

I mean if it is not feasible with SQLOLEDB then surely will try to upgrade but if it is feasible then there is not point in switching because it will a lot of rework Right?

Thanks,

I don't know...
Some years back we had some problems with SQLOLEDB provider after moving from SQL Server 2008 to SQL Server 2008 R2. And all these problems disappeared after we chose to use SQLNCLI10 provider...
You could find some more or less links/discussions about these both, like:
https://social.msdn.microsoft.com/Fo...rationservices
http://www.progtown.com/topic377471-...ersqlncli.html
http://dbaspot.com/sqlserver-server/...-sqloledb.html

BTW, what version of SQL Server are you using?

Quote:

---

Originally Posted by VictorN

I don't know...
Some years back we had some problems with SQLOLEDB provider after moving from SQL Server 2008 to SQL Server 2008 R2. And all these problems disappeared after we chose to use SQLNCLI10 provider...
You could find some more or less links/discussions about these both, like:
https://social.msdn.microsoft.com/Fo...rationservices
http://www.progtown.com/topic377471-...ersqlncli.html
http://dbaspot.com/sqlserver-server/...-sqloledb.html

BTW, what version of SQL Server are you using?

---

SQL server 2008 R2.

I added "Use Data For Encryption=True" in connection string and then installed the server certificate on client machine.If i install it in personal folder of certificate store then it is throwing SSL Security error but if i install it in trusted root certification authorities folder then it is working I can see it is able to establish SSL connection. Its working !!!

I went through the link given by you in above comment and found "SSL work with SQL Native CLient (ODBC and OLEDB) but not with SQLServer ODBC or SQLOLEDB" but it seems working fine with me.Also https://social.msdn.microsoft.com/Fo...rationservices it is mentioned that "Be aware that Microsoft has announced that OLE DB will be deprecated in SQL Server 2012 for SQL Server relational database access in favor of the SQL Server Native Client ODBC driver (http://social.msdn.microsoft.com/For...8-7a357d3d780f). But today, tools like SSIS use OLE DB heavily so you'll need to stick with OLE DB until the toolset provides first-class ODBC support."

Not sure how it is working for me.I need to try it with SQL Server 2012 and later version.
Can you please provide me some details how to use SQL SQLNCLI10 or 11.
Thanks in advance.

Quote:

---

Originally Posted by Ndata sks

...
Not sure how it is working for me.I need to try it with SQL Server 2012 and later version.
Can you please provide me some details how to use SQL SQLNCLI10 or 11.
Thanks in advance.

---

I don't know because used SQL Server 2012 only for some simple tests.
But have a look at Codeproject.com/Forums/1647/C-Cplusplus-MFC.aspx

Quote:

---

Originally Posted by VictorN

I don't know because used SQL Server 2012 only for some simple tests.
But have a look at Codeproject.com/Forums/1647/C-Cplusplus-MFC.aspx

---

Didn't understand the link given above :(

Quote:

---

Originally Posted by Ndata sks

Didn't understand the link given above :(

---

Sorry, the wrong link! :sick:
I meant this one:
http://dba.stackexchange.com/questio...-2012-database