I have an SSIS package with a Script Task that places a message on an Azure Service Bus. The SSIS package is running on MS SQL Server 2014, on an Azure VM with Windows Server 2012. This works fine on my development machine, and in a Testing Azure VM that I control. My customer has installed it into their Production Azure VM (which I can’t access), and now it consistently generates the error:
The X.509 certificate CN=servicebus.windows.net is not in the trusted people >store. … at Microsoft.ServiceBus.Messaging.MessageSender.OnSend( …
The Script Task code that fails is:
MessagingFactory factory = MessagingFactory.CreateFromConnectionString(connStr);
MessageSender msgSender = factory.CreateMessageSender(SBQueueName);
msgSender.Send(JsonMsg); // throws the error
After reading other posts I’ve seen on StackOverflow and Microsoft Forums, my understanding is that, the first time this code runs it should download & install the needed certificate automatically. Apparently that is not happening, and I suspect a configuration issue in my customer’s Production environment. Likely either insufficient privileges on the ‘NT Service\MsDtsServer120’ Virtual Account for the SSIS Service, or the firewall is blocking the download of the certificate from the Microsoft certificate authority. Since the Virtual Account was setup with default access rights, I’m guessing the issue is the firewall. What are the likely firewall rule changes needed to allow the certificate to be downloaded? Or am I wrong in suspecting it’s a firewall issue?
I have seen workarounds suggested in other StackOverflow posts including 1) changing the calling ConnectionMode to HTTP or HTTPS, or 2) Manually installing the certificate on the VM, but I would like to understand the issue more fully and resolve this without resorting to these workarounds if possible.
Unfortunately I failed to mention that the package was being executed by a Powershell script. Turns out the SSIS package was inheriting the rights of the account executing the Powershell script, which were insufficient to install the certificate; running the Powershell script as Administrator resolved the issue, and the Certificate needed (Intermediate Certification Authorities > Microsoft IT LTS CA 5 (Issued by Baltimore CyberTrust Root)) correctly installed itself. Before I figured that out, I did also try the workaround suggestion by Tom Sun to manually install the certificate, and indeed that also worked.