-
July 5th, 2016, 04:33 AM
#1
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.
-
July 7th, 2016, 03:26 PM
#2
Re: How to secure sql database server using SSL for an MFC Application ?
Is there a reason to use SQLOLEDB rather than SQL Server Native Client provider?
Victor Nijegorodov
-
July 9th, 2016, 06:09 AM
#3
Re: How to secure sql database server using SSL for an MFC Application ?
Originally Posted by VictorN
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
-
July 9th, 2016, 06:10 AM
#4
Re: How to secure sql database server using SSL for an MFC Application ?
I am not the owner of the code.I have been asked to make these changes
-
July 9th, 2016, 06:31 AM
#5
Re: How to secure sql database server using SSL for an MFC Application ?
So did you try to use SQLNCLI10 or SQLNCLI11 rather than SQLOLEDB?
Victor Nijegorodov
-
July 9th, 2016, 07:26 AM
#6
Re: How to secure sql database server using SSL for an MFC Application ?
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,
-
July 9th, 2016, 11:57 AM
#7
Re: How to secure sql database server using SSL for an MFC Application ?
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?
Victor Nijegorodov
-
July 10th, 2016, 02:53 AM
#8
Re: How to secure sql database server using SSL for an MFC Application ?
Originally Posted by VictorN
SQL server 2008 R2.
-
July 10th, 2016, 03:08 AM
#9
Re: How to secure sql database server using SSL for an MFC Application ?
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.
Last edited by Ndata sks; July 10th, 2016 at 03:11 AM.
-
July 10th, 2016, 04:11 AM
#10
Re: How to secure sql database server using SSL for an MFC Application ?
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
Victor Nijegorodov
-
July 10th, 2016, 05:33 AM
#11
-
July 10th, 2016, 09:52 AM
#12
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|