locked
SQL Management Studio does not trust the Server Certificate because it says that the CN name does not match the passed value RRS feed

  • Question

  • Hi everyone!

    I´m trying to stablished a secure SSL connection with my developing SQL Server database for an application that I´m programming in .Net.

    I´ve created a self-signed certificate with the MakeCert.exe tool. In the CN name I´ve put the name of my Server (CN="DEVSERVER", it´s not part of a domain) and, in the application, I´ve set TrustServerCertificate=true in the connection string so everything goes right.

    But, when I try to connect with an ecnrypted connection from the SQL Management Studio from outside my LAN (I´ve a dynamic IP so I use a no-ip pointer) it gives me the error:

    A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - the CN name does not match the passed value.) (Microsoft SQL Server, Error: -2146762481)


    It´s because the CN name does not match with the no-ip pointer, but I cannot find an option called "Trust Server Certificate" in the Management Studio like in the connection string of the application...

    I´ve been searching and trying lot of things but with no success... If I make a certificate with the CN name equal to the No-IP pointer the SQL Server does not start with that certificate.. I really don´t know how to solve this issue...

    Any help or idea will be very much appreciated.

    Tomcat.

    Tuesday, November 4, 2008 3:27 PM

All replies

  • Hi Tomcat,

      To get Trust Server Certificate in Management Studio, in the connect window, click the "Options >>" button, and in Additional Connection Properties, type:

    Code Snippet
    TrustServerCertificate=true

     

     

     

     

    Tuesday, November 4, 2008 10:55 PM
  • Thank you very much for your post Dan!

    I´m using SQL Management Studio Express 2005 (to connect to a 2005 SQL Server) and when it opens and show the "Connect to Server" Dialog box, I go to Options>> and there´s no
    Additional Connection Properties... that option isn´t available in Express edition or I´m no finding it?

    Again, thank you very much for your post!

    Tomcat.
    Wednesday, November 5, 2008 10:00 AM
  •  

    Sorry, the additional connection properties part is only available for SQL Server 2008 editions of SSMS.

     

    Jens K Suessmeyer

    Monday, November 10, 2008 4:55 PM
  • Thanks for your reply Jens!

    I don´t know how can I get the SQL Management Studio 2008... I´ve found SQL Management Studio SP2 as the last edition available in the Microsoft´s web site and there is no additional connection properties...

    Thank you very much again!

    Tomcat.
    Monday, November 10, 2008 10:09 PM
  • SSMS 2008 is included with SQL Server 2008, sorry I don´t actually know if there is any way to tweak that permanently for the client or temporary for the connection.

     

    Jens K. Suessmeyer

    Monday, November 10, 2008 10:42 PM
  • Hi everyone and thank you very much for your posts!

    I´ve been working with this topic and I have some conlusions that I want to share with you.


    1- If you want to use SSL encryption with a self-signed certificate in SQL Server 2005 and SQL Management Studio from outside the network of the server, you can´t.


    2- As the SQL Management Studio 2008 hasn´t been delivered as a separate download, you have to install it with the full installation packet of SQL Server 2008. Then, as in the first case, if you try to use SSL encryption with a self-signed certificate in SQL Server 2005 from outside the network of the server you won´t be able to make the connection. It doesn´t make any diference to add in additional connection properties the line: TrustServerCertificate=true. It will say that the CN name does not match the passed value.


    So, if you want to use SSL encryption with SQL Server 2005 and SQL Management Studio you have to do it inside the network of the server or make another type of link (like a VPN) that is secure becasue of the use of, for example, IPSec.


    Please Jens and Dan, tell me if I´m wrong... I don´t realize why Microsoft hasn´t solve this little issue of SSL Encryption on SSMS.


    Thanks again for all the people that had participate in this thread.
    Tuesday, November 11, 2008 10:16 PM