none
"The certificate chain was issued by an authority that is not trusted" when migrating to SQL 2012 RRS feed

  • Question

  • Environment:

    1 Primary Site (USSCCM-Site.domain.com)

    1 CAS (USSCCM-CAS.domain.com)

    SQL 2008 R2 (USSCCM-CAS.domain.com)

    SQL 2012 SP1 CU6 (USSQL12.domain.com)

    Issue:

    We were successfully able to migrate the CAS to the new SQL 2012 server, almost without incident. When attempting to migrate the Site instance however, we are getting errors. Screenshot below.

    Attached is a copy of the log. But below is a highlight of what I think are the errors… It appears that either SQL or SCCM doesn’t like a certificate somewhere, but it is contradicting because the logs say that it has successfully tested connection to SQL. I am lost.

    Logs stating it can connect successfully to SQL

    Machine certificate has been created successfully on server USSQL12.domain.com.        Configuration Manager Setup                10/21/2013 10:20:10 AM               2100 (0x0834)

    Deinstalled service SMS_SERVER_BOOTSTRAP_USSCCM-Site.domain.com_SMS_SQL_SERVER on USSQL12.domain.com.  Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    SQL Server instance [sccmsite] is already running under the certificate with thumbprint[f671be844bf39dec7e7fdd725dc30e225991f28a].       Configuration Manager Setup    10/21/2013 10:20:10 AM         2100 (0x0834)

    INFO: Testing SQL Server [USSQL12.domain.com] connection ...                Configuration Manager Setup    10/21/2013 10:20:10 AM       2100 (0x0834)

    INFO: SQL Connection succeeded. Connection: USSQL12.domain.com SCCMSITE\MASTER, Type: Unsecure                Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: Tested SQL Server [USSQL12.domain.com] connection successfully.  Any preceding SQL connection errors may be safely ignored.            Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: Certificate: 308202FC308201E4A003020102021011BA47041BB0609D4097BC19F5AB96B5300D06092A864886F70D01010B0500301D311B301906035504031312555353514C31322E7063756265642E636F6D3020170D3133313031373139343632345A180F32313133303932343139343632345A301D311B301906035504031312555353514C31322E7063756265642E636F6D30820122300D06092A864886F70D01010105000382010F003082010A0282010100CFAC23CEA8920051C8C24DF4E96D76D9E034931867C4DBF74F8AE863C5BDE6D1EAEAAF363F6B97DEF1D7A1FC292CB870F353D72F04472EBE3D31DCA009BD3F8C58E2AAB69C892C20598306537F5EEAA43FA6DE55D4A784CEB6FD07486AB2CC2DE1A8651648EBC31A5CD918E8ED6E184FC560B3A8B0F76F83E310BBA8C4EB27F46707E3A6377D8DD06C6808146E407EF9DB464F453798B6C1748216665884A7F2CDE03D9DA1CB4E59E67516E4F345755E35450F84F4B039642851EFFA96B22D8E77EC11C01D389989F740923B58799E34FC8F4F19CD55830FA7E786C993A08A1EEDCA87F209268CE9D5E86AC9E2A14668207721D94ACE9FACE3AA55B53507F6BF0203010001A3363034301D0603551D11041630148212555353514C31322E7063756265642E636F6D30130603551D25040C300A06082B06010505070301300D06092A864886F70D01010B05000382010100A0E33BF490B60C2B8A73BF7FA90EBB69D92DA27B439EF0569650F388EBA34B9F382CEF52DAAB543C2924E1B8DC7BEE828FB0C276330B0FF67340CBFA0CC24F47431E5272DC76C7610C290A04411036441E9822FBF8AB52B4BBE43F5FF48074BA420FF690A94D53AFCEF7AC75E2D2723520A9EF64AF06759814AE92D41CEA2F0D6CC8D9E5DEF121234F5DD97A7E886BE55F57DC0B79052A554724E8A0146C08A74AE75672FBD8C8BD6B7FCA82C1CC69906A45128CDDD1BC3985ED9603C16E712FFBAA8AA6878F853367F3E1F69E727DB96864DF6B47EBDA82659036EC82A8B04E77535CEA314D7518D02C401969C77B91C8C210C57AA991A622D679B994AEED3C                Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: Created SQL Server machine certificate for Server [USSQL12.domain.com] successfully.    Configuration Manager Setup 10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: Configuration Manager Setup - Application Shutdown       Configuration Manager Setup    10/21/2013 10:20:10 AM         2100 (0x0834)

    INFO: Running SQL Server test query.    Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: SQL Connection succeeded. Connection: USSQL12.domain.com SCCMSITE\MASTER, Type: Secure                Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: SQL Server Test query succeeded.              Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: SQLInstance Name: sccmsite         Configuration Manager Setup    10/21/2013 10:20:10 AM               2100 (0x0834)

    INFO: SQL Server version detected is 11.0, 11.0.3381.0 (SP1).      Configuration Manager Setup    10/21/2013 10:20:10 AM         2100 (0x0834)

    Logs saying certificate is not trusted

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.        Configuration Manager Setup    10/21/2013 10:20:49 AM                2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection                Configuration Manager Setup    10/21/2013 10:20:49 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection         Configuration Manager Setup    10/21/2013 10:20:49 AM               2100 (0x0834)

    *** Failed to connect to the SQL Server, connection type: CCAR_DB_ACCESS.    Configuration Manager Setup                10/21/2013 10:20:49 AM               2100 (0x0834)

    INFO: SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Secure         Configuration Manager Setup                10/21/2013 10:20:49 AM               2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup    10/21/2013 10:20:52 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.        Configuration Manager Setup    10/21/2013 10:20:52 AM                2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection                Configuration Manager Setup    10/21/2013 10:20:52 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection         Configuration Manager Setup    10/21/2013 10:20:52 AM               2100 (0x0834)

    *** Failed to connect to the SQL Server, connection type: CCAR_DB_ACCESS.    Configuration Manager Setup                10/21/2013 10:20:52 AM               2100 (0x0834)

    INFO: SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Secure         Configuration Manager Setup                10/21/2013 10:20:52 AM               2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup    10/21/2013 10:20:55 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.        Configuration Manager Setup    10/21/2013 10:20:55 AM                2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection                Configuration Manager Setup    10/21/2013 10:20:55 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection         Configuration Manager Setup    10/21/2013 10:20:55 AM               2100 (0x0834)

    *** Failed to connect to the SQL Server, connection type: CCAR_DB_ACCESS.    Configuration Manager Setup                10/21/2013 10:20:55 AM               2100 (0x0834)

    INFO: SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Secure         Configuration Manager Setup                10/21/2013 10:20:55 AM               2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup    10/21/2013 10:20:58 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.        Configuration Manager Setup    10/21/2013 10:20:58 AM                2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection                Configuration Manager Setup    10/21/2013 10:20:58 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection         Configuration Manager Setup    10/21/2013 10:20:58 AM               2100 (0x0834)

    *** Failed to connect to the SQL Server, connection type: CCAR_DB_ACCESS.    Configuration Manager Setup                10/21/2013 10:20:58 AM               2100 (0x0834)

    INFO: SQL Connection failed. Connection: CCAR_DB_ACCESS, Type: Secure         Configuration Manager Setup                10/21/2013 10:20:58 AM               2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup    10/21/2013 10:21:01 AM               2100 (0x0834)

    More logs saying cert is not trusted

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup    10/21/2013 10:21:20 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.        Configuration Manager Setup    10/21/2013 10:21:20 AM                2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection                Configuration Manager Setup    10/21/2013 10:21:20 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection         Configuration Manager Setup    10/21/2013 10:21:20 AM               2100 (0x0834)

    *** Failed to connect to the SQL Server, connection type: CCAR_DB_ACCESS.    Configuration Manager Setup                10/21/2013 10:21:20 AM               2100 (0x0834)

    INFO: Updated the site control information on the SQL Server USSQL12.domain.com.    Configuration Manager Setup                10/21/2013 10:21:39 AM               2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted. Configuration Manager Setup    10/21/2013 10:21:39 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: The certificate chain was issued by an authority that is not trusted.        Configuration Manager Setup    10/21/2013 10:21:39 AM                2100 (0x0834)

    *** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection                Configuration Manager Setup    10/21/2013 10:21:39 AM               2100 (0x0834)

    ERROR: SQL Server error: [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection         Configuration Manager Setup    10/21/2013 10:21:39 AM               2100 (0x0834)

    *** Failed to connect to the SQL Server, connection type: CCAR_DB_ACCESS.    Configuration Manager Setup                10/21/2013 10:21:39 AM               2100 (0x0834)

    CSiteSettings::WriteActualSCFToDatabase: Failed to get SQL connection                Configuration Manager Setup                10/21/2013 10:21:39 AM               2100 (0x0834)

    CSiteSettings::WriteActualSCFToDatabaseForNewSite: WriteActualSCFToDatabase(USA) returns 0x87D20002                Configuration Manager Setup    10/21/2013 10:21:39 AM               2100 (0x0834)

    ERROR: Failed to insert the recovery site control image to the parent database. Configuration Manager Setup                10/21/2013 10:21:39 AM               2100 (0x0834)

    Troubleshooting:

    I have read on a few articles of other people having this issue that states to find the certificate on SQL 2012 that’s being used and export it to the SCCM server – which I’ve done.

    http://damianflynn.com/2012/08/22/sccm-2012-and-sql-certificates/

    http://trevorsullivan.net/2013/05/16/configmgr-2012-sp1-remote-sql-connectivity-problem/

    http://scug.be/sccm/2012/09/19/configmgr-2012-rtm-sp1-and-remote-management-points-not-healthy-when-running-configmgr-db-on-a-sql-cluster/


    -Brad


    Monday, October 21, 2013 3:08 PM

Answers

  • This is a bigger issue than just certificates, unfortunately. I've had to bring in Microsoft support and even they are scratching their heads at some of the items they're seeing. Thanks for the help guys.


    -Brad

    Thursday, October 24, 2013 2:17 PM

All replies

  • So you imported the certificate on the new SQL server again? What certificate have you used? What happens if you bring up its properties? Does it show that it is trusted?

    Torsten Meringer | http://www.mssccmfaq.de

    Monday, October 21, 2013 6:01 PM
  • Torsten,

    How did I know you'd respond? You're a blessing to these forums, sir. I had went to a spot in the registry on the SQL 2012 box and found the certificate thumbprint that the Site Instance is using (can't recall exactly what key it was). I then compared that to the Properties of the Instance in SQL Config Manager to make sure it was the same cert - indeed it is. From there I found that the cert was installed in the Local certificates on the SQL box and also copied it to the Trusted Root CA. I did a standard Export with Private Key to pfx and imported that cert to the SCCM Site server also to the Trusted Root CA path. Error still resides. Completely blowing my mind.

    As an FYI I have also confirmed that the computer accounts for the Site and CAS were both added to the admins group on the SQL server and also that the SQL Service accounts had Read permissions on the certificate.

    Brad


    -Brad

    Monday, October 21, 2013 6:26 PM
  • Hi,

    How about importing certificate in the personal folder under SQL server computer account into SCCM server computer account or SCCM server service account? That certificate is for SQL Server Identification. And you could set the value of the ForceEncryption option to NO. (SQL Server Configuration Manager->SQL Server Network Configuration-> Protocols for <server instance>->Properties)

    Best Regards,

    Joyce Li


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 24, 2013 1:14 AM
    Moderator
  • The SQL cert should be in the Personal store of the computer.

    Torsten Meringer | http://www.mssccmfaq.de

    Thursday, October 24, 2013 7:53 AM
  • This is a bigger issue than just certificates, unfortunately. I've had to bring in Microsoft support and even they are scratching their heads at some of the items they're seeing. Thanks for the help guys.


    -Brad

    Thursday, October 24, 2013 2:17 PM
  • Hi Brad

    I know it was while ago now but did you have any luck in sorting this issue out?

    I'm experiencing the same issue and for the life of me I cannot figure it out.

    Cheers
    Brady

    Wednesday, April 22, 2015 3:28 AM
  • Brady,

    Sorry, I do not recall what was done once Microsoft was brought in. This was a while ago.


    -Brad

    Wednesday, April 22, 2015 12:39 PM