I am not so deep into SQL server that I can precisely answer all your questions. So let's begin with what I know for sure.
You are absolutely right. Every software which communicates with other parties over TLS / SSL connections definitely needs a private key which matches its public key, because SSL / TLS exchanges the actual encryption keys by means of asymmetric RSA (or nowadays, EC) encryption. Period.
Please note that the fact that a private key is needed is not due to the fact that there are certificates involved. Rather, it is the principle of asymmetric RSA and EC encryption that there must be a private key matching the public key, regardless of the actual implementation.
The public key and other data (e.g. the a computer or organization name, the signage of trusted root or intermediate authorities, and so on) are usually stored in a certificate which one party presents to another, thereby claiming a certain identity. It is up to the other party to check if the claim is true, i.e. if the party presenting the certificate really is what it pretends to be.
The private key matching a certificate's public key may be stored in unexpected places.
For example, I have generated the SSL certificate for my SQL Server 2012 using
certreq (for details, see here and here). When I issued the final command of the process, that is,
certreq -new -f req.inf test.cer
a certificate was created and automatically imported into the Windows certificate store (I later used SSCM to activate that certificate for the instance in question), and that certificate's private key was put into a file with a cryptic name in the following location:
I am absolutely sure that I do not know all methods to create a certificate for SQL Server's SSL / TLS authentication, but once again, I am also sure that all methods involve creating a private key, and that SQL server needs this key to authenticate against other parties (clients, or (itself acting as a client) servers).
After all, the key point of certificate-based server authentication is that the client checks whether the server can prove that it has the private key which matches the server certificate's public key. If the server does not have that key, the certificate does not belong to that server, and the client rejects to continue the connection.
In my case, for example, SQL Server won't even start if it can't read the private key matching the TLS certificate from the machine key store mentioned above.
To compare the certificate SSL / TLS to other certificates, I would do the following:
a) Identify the SSL / TLS certificate:
Start SSCM (SQL Server Configuration Manager; if you don't have it installed, add it to your installation via SQL Server setup - it is part of the configuration / management tools). In the left pane, expand "SQL Server Network Configuration" and (still in the left pane) right-click "Protocols for xxx" (where xxx is the instance in question). From the context menu, select "Properties".
This should open a dialog box with multiple tabs, among them a tab titled "Certificate". Activate this tab and press the button "View ...". This should open the usual Windows dialog where a certificate's details are presented. In your case, the tab "Details" in this dialog is the interesting one. Among others, it contains the certificate's thumbprint which is unique. Note down that thumbprint.
In my case, the thumbprint is
b) Identify the other certificates
You can query the other certificates like that:
select name, thumbprint from sys.certificates
which (in my case) gives the following result:
Now compare these thumbprints to the SSL / TLS certificate's thumbprint obtained in step a). This clearly shows that the SSL / TLS certificate is not identical to the MS_SQLAuthenticatorCertificate.
By the way, I don't know what
pvt_key_encryption_type_desc from your example actually contains or means, and what those
##MS_... certificates are used for. But once again, SQL Server ultimately needs to know the private keys of the certificates which it uses to authenticate itself against clients (or other servers which insist on the client presenting a certificate).
So I suspect you misunderstood what
NO_PRIVATE_KEY actually means (I had no chance to misunderstand it because I have never seen it until now :-)).
I should probably not write anything more, because the following really is just a guess, so don't take it too serious. However, there are certificate formats which directly include the private key as part of the certificate itself. Perhaps
NO_PRIVATE_KEY means that the respective certificates don't include the private key directly; in that case, it is stored elsewhere, at least for those certificates which SQL Server actually uses to authenticate itself.