dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

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

  1. #1
    Join Date
    Jul 2016
    Posts
    7

    Question 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.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,592

    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

  3. #3
    Join Date
    Jul 2016
    Posts
    7

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

    Quote Originally Posted by VictorN View Post
    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

  4. #4
    Join Date
    Jul 2016
    Posts
    7

    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

  5. #5
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,592

    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

  6. #6
    Join Date
    Jul 2016
    Posts
    7

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

    Quote Originally Posted by VictorN View Post
    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,

  7. #7
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,592

    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

  8. #8
    Join Date
    Jul 2016
    Posts
    7

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

    Quote Originally Posted by VictorN View Post
    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.

  9. #9
    Join Date
    Jul 2016
    Posts
    7

    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.

  10. #10
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,592

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

    Quote Originally Posted by Ndata sks View Post
    ...
    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

  11. #11
    Join Date
    Jul 2016
    Posts
    7

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

    Quote Originally Posted by VictorN View Post
    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

  12. #12
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    19,592

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

    Quote Originally Posted by Ndata sks View Post
    Didn't understand the link given above
    Sorry, the wrong link!
    I meant this one:
    http://dba.stackexchange.com/questio...-2012-database
    Victor Nijegorodov

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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)