While working with IoT and website deployment and testing, we will be in need of certificates. We end up using OpenSSL to generate strong certificates (mostly Microsoft blogs will guide us on how to do that). Most of these blogs will guide us on how to get that done.
Recently working on a web deployment to IoT edge containers, I was trying to secure the website with a self signed certificate. It was working all fine locally. But strangely when deployed to container, the container starts and shuts down immediately.
After a bit of good old trouble shooting and isolating the problem, I realized that it works from my Windows machine and not Mac machine! to be specific when the certificates were generated from Mac container version of my website didn’t load. Why?
Windows had OpenSSL by default. Whereas my Mac had LibreSSL by default and the encryption on that didn’t help.
Face -palm moment..
“Ensure that you have proper libraries for SSL before you dockerize and copy certificates to your container.”
Though the blog shows that path, that is not ideal from a security standpoint. But I wanted to troubleshoot, why wouldn’t the Kestrel hosted website not work inside the container. So was trying to isolate the problem.
It is quite a known pattern to use Azure Stream Analytics (ASA) to create data pipelines to store ingress IoT data to an output location be it a SQL Server or EventHub or Azure Storage etc. This is all the more important when push ASA to the edge and use that as a data transformation and storage orchestration engine esp. with SQL Server on local on premises being used like a Historian. This seems to a straight-forward process but here comes a small catch and technical/product limitation of Azure. It took us a few precious hours to understand this aspect.
Short Answer (if you have scrolling like me):
“If you want to connect ASA with SQL server, ensure that you have a trusted CA certificate with proper certificate chain installed in the SQL server VM”.
For the patient ones who need the backstory 🙂 read along..
What were we doing?
We were trying to wire up an Azure IoT edge module with a SQL server on a VM! This seemed quite easy as per the documentation but I ended up with a curious certificate error.
As a troubleshooting step, I tried to create this on ASA on cloud and connect with the same SQL server on VM to rule out any Edge VM certificate issues. This should be quite quite simple if we follow this blog.
Set Force Protocol Encryption Client Setting to Yes
For secure connectivity, ensure that the client and server both require encryption. Also ensure that the server has a verifiable certificate, and that the TrustServerCertificate setting on the client is set to FALSE.
Created self signed CA certificates and installed them as well. But then still the issue seems to be coming back and back.
Finally, we found out from Microsoft product team that we need proper CA certificates with certificate chain from well known authority to make ASA and SQL work together.
One requirement for SQL server on VM as output to work is that the SQL server needs to be configured with an SSL certificate issued by a trusted CA. There is no workaround with this. You can’t use a self signed certificate or use TrustServerCertificate=True and change SQL Server settings.
1- Regarding SSL Certificate – Make sure to use the DNS based FQDN for the CN. Here are the full requirements listed.
2- SSL Setup in the VM – Follow steps here. If using SQL 2016 , Also put the certificate’s thumbprint in the registry key mentioned in the “Wildcard Certificates” section.
Now for me who is just doing a dev setup and doesn’t have the luxury of client CA certificates, there are quite limited options.
For IoT Edge, I used a custom .NET code with SQL DB client to communicate with SQL server VM using the TrustSeverCertificate = True flag in connection string for dev code until I get a CA cert.
But for Azure Stream Analytics PaaS service, we can’t enter connection string. So there is no way to enter TrustSeverCertificate=TRUE during development. Sure seems like a restriction.
One another way which is to use services like Let’s Encrypt and generate a chained certificate for your use temporarily. Something which I am yet to try. I think that should work.
If you have used that and worked, please let me know in the comments.