MSSQLSERVICE not starting. error 1067

I have an SQL cluster hosting databases for SCCM, SCOM, and WSUS. I wanted to configure encryption so requested a certificate from an internal CA with a subject name of the SQL cluster virtual server. on the nodes of the SQL server there was a self signed certificate created automatically for SCCM. following the instruction in most of the blogs, I replaced the thumbprint of the certificate in the registry to use the thumbprint of the new certificate. the moment the thumbprint was changed the SQL service stopped and the cluster resource for SQL went down. I replaced the thumbprint on both the nodes and still the service does not come up. I replaced the old thumbprint and restarted but the service still does not start. I did a SQL server repair but no luck. the error I get is : unexpected service failure error 1067. now all of my services are down. what can be done to bring back the service to a running state. SQL version I am running on the cluster is 2012 sp1. it a active/passive cluster hosting databases for all my services. I changed the thumbprint as the application catalog role in SCCM was not working and I found some blogs talking about sql encryption and this messed it up for me.
Aamer-Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You likely have not transferred the private key of the certificate.

Such undertakings should only be done in a test environment with good backups.
you would need to use the osql command line to try and revert what you did.

the certificate is not part of the user/service certificate store.......
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
try to undo all what you set and reboot nodes
did you make these changes on both nodes?
did you try to do fail over to  2nd node?
did you reboot boxes?
what errors do you see in event viewer?
in cluster error log?
more details is better
--
Are you using the cluster service as domain login with local admin rights on the both nodes?


what blogs instructions did you use?

did you see this one?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/01a0191f-88d4-4e67-8017-fd26a1dec1fc/ssl-thumbprint-registry-setting-for-sql-server-2008-clustered-on-windows-server-2008?forum=sqlsetupandupgrade
Daniel_PLDB Expert/ArchitectCommented:
Hello,

I suggest you locate your new certificate in certmgr.msc for a computer account and grant SQL Server service account's private key permission to that certificate. You can do this by right clicking on the certificate, choose All tasks then Manage private keys...
In the new window just add your SQL Server service account's permission to the certificate. Then restart your SQL Server.

I don't have any certificate to demonstrate you steps in my own environment, therefore I just paste an image from one of the msdn blogs (reference to the blog is below).

certmgr.msc image
http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/28/inf-permissions-required-for-sql-server-service-account-to-use-ssl-certificate.aspx

As others stated before, please provide us with SQL Server logs, or at least check what's in the logs.

Regards,
Daniel

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.