SCCM reporting DB was moved to an AG cluster 2014 with SCCM reports locally on standalone server.
Db connectivity between SCCM and AG was fine.
However, SCCM reports created would not connect to the Datasource with the error
the Datasource was connecting to ag01.mydomain.local
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.)
Seems lots of people are experiencing this issue, and logging a case to MS didnt go too far.
Setup
SCCM server with Reporting Role
SCCM DB on a AG called for e.g AG01.mydomain.local with two node cluster where Clustername is Clu1 and Node1 and Node2 are part of the AG cluster.
After lots of searching, wireshark, tracing logs, sql server logs and stumbling on bits and pieces, the solution that worked for us is as below:
Created a Server Auth cert with CN name as AG01.mydomain.local (FQDN of AG)
and SAN with DNS names
ag01
clu1.mydomain.local
clu1
node1.mydomain.local
node1
node2.mydomain.local
node2
References:
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi
https://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/
This is assuming you have a pki infrastructure and can generate proper certs and the CA root certs are on the servers.
Make sure certs created have proper permissions assigned for the SQL service accounts if SQL running under SVC account.
Once you have the appropriate certs as per above.
Follow the article below
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi
And most likely if you copied the Thumbprint as i did from the MMC console then you will need to follow this
https://support.microsoft.com/lo-la/help/2023869/sql-server-fails-to-start-with-error-17182-tdssniclient-initialization
Once the SQL services are running successfully, the issue should get resolved.
Failover was successfully tested and reporting worked fine there after.
Best of Luck
Hope it saves someone sometime... my colleague spent weeks...
Db connectivity between SCCM and AG was fine.
However, SCCM reports created would not connect to the Datasource with the error
the Datasource was connecting to ag01.mydomain.local
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate's CN name does not match the passed value.)
Seems lots of people are experiencing this issue, and logging a case to MS didnt go too far.
Setup
SCCM server with Reporting Role
SCCM DB on a AG called for e.g AG01.mydomain.local with two node cluster where Clustername is Clu1 and Node1 and Node2 are part of the AG cluster.
After lots of searching, wireshark, tracing logs, sql server logs and stumbling on bits and pieces, the solution that worked for us is as below:
Created a Server Auth cert with CN name as AG01.mydomain.local (FQDN of AG)
and SAN with DNS names
ag01
clu1.mydomain.local
clu1
node1.mydomain.local
node1
node2.mydomain.local
node2
References:
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi
https://thesqldude.com/2012/04/21/setting-up-ssl-encryption-for-sql-server-using-certificates-issues-tips-tricks/
This is assuming you have a pki infrastructure and can generate proper certs and the CA root certs are on the servers.
Make sure certs created have proper permissions assigned for the SQL service accounts if SQL running under SVC account.
Once you have the appropriate certs as per above.
Follow the article below
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi
And most likely if you copied the Thumbprint as i did from the MMC console then you will need to follow this
https://support.microsoft.com/lo-la/help/2023869/sql-server-fails-to-start-with-error-17182-tdssniclient-initialization
Once the SQL services are running successfully, the issue should get resolved.
Failover was successfully tested and reporting worked fine there after.
Best of Luck
Hope it saves someone sometime... my colleague spent weeks...
