Hello Everyone,
Recently one of my client’s BizTalk environment crashed because few important certificates had expired, this caused many in-flight messages to fail and created huge backlog. Finally they acquired and installed valid certificates which resolved the issue.
But this caused downtime for hours, financial loss and unnecessary trouble which could have been avoided by proactive monitoring of the certificate expiry dates.
Monitoring certificates manually could be a hectic task. Some companies maintain excel sheets for recording certificate details like expiry date etc. but this requires constant analysis/check.
Therefore we created a console application which will run on a monthly basis(configurable value) and get information of all the certificates which are about to expire within next 30 days(configurable value).
Code for fetching the certificate information is very simple-
<img class=”alignnone size-full wp-image-245″ src=”https://prashantbiztalkblogs.files.wordpress.com/2016/05/codetofetchcertificateinformation2.jpg” alt=”//Fetching certificates from store My – Local. You can also fetch from Trusted People or publisher etc. //Store Location can also be changed to LocalMachine or CurrentUser //var store = new X509Store(StoreName.My, StoreLocation.LocalMachine); //var store = new X509Store(StoreName.TrustedPeople, StoreLocation.CurrentUser); var store = new X509Store(StoreName.Root, StoreLocation.LocalMachine); store.Open(OpenFlags.ReadOnly); foreach (X509Certificate cert in store.Certificates) { intCertCount++; if (System.DateTime.Today.AddDays(-numberOfDaysBeforeExpiry)
Code-
//Fetching certificates from store My - Local. You can also fetch from Trusted People or publisher etc.
//Store Location can also be changed to LocalMachine or CurrentUser
//var store = new X509Store(StoreName.My, StoreLocation.LocalMachine);
//var store = new X509Store(StoreName.TrustedPeople, StoreLocation.CurrentUser);
var store = new X509Store(StoreName.Root, StoreLocation.LocalMachine);
store.Open(OpenFlags.ReadOnly);
foreach (X509Certificate cert in store.Certificates)
{
intCertCount++;
if (System.DateTime.Today.AddDays(-numberOfDaysBeforeExpiry) < System.Convert.ToDateTime(cert.GetExpirationDateString()))
{
//Fetching Subject, Issuer, Serial Number, Effective Date, Expiration Date and Thumbprint information in tabular form.
certTabularText = certTabularText + "<tr><td>" + intCertCount + ") </td> <td> " + cert.Subject + " </td> <td> "
+ cert.Issuer + " </td> <td> " + cert.GetSerialNumberString() + " </td> <td> " + cert.GetEffectiveDateString()
+ " </td> <td> " + cert.GetExpirationDateString() + " </td> <td> " + cert.GetCertHashString() + " </td> </tr>";
}
}
Later, I formatted the email body in a tabular form and sent out emails to all the concerned people.
Formatting the email body –
//Formatting the Email body certTabularText = "<table style=\"border: 1px solid black; text-align:center;\" > <tr><td> SNo </td> <td> Subject </td> <td> Issuer </td> <td> Serial Number </td> <td> Effective Date </td> <td> Expiration Date </td> <td> Thumbprint </td> </tr>" + certTabularText; strEmailBody = "There are " + intCertCount.ToString() + " certificates found which are about to expire in " + numberOfDaysBeforeExpiry + " days. \r\n \r\n" + certTabularText + "</tr></table>"; sendEmail(strEmailBody);
Sample email-
In this POC I used Gmail SMTP server for sending emails-
SmtpClient SmtpServer = new SmtpClient(appSettings["SMTPHost"].ToString()); mail.From = new MailAddress(appSettings["FROMEmailID"].ToString()); mail.To.Add(appSettings["TOEmailID"].ToString()); mail.Subject = appSettings["Subject"].ToString(); mail.Body = strEmailBody; mail.IsBodyHtml = true; SmtpServer.EnableSsl = true; SmtpServer.DeliveryMethod = SmtpDeliveryMethod.Network; SmtpServer.UseDefaultCredentials = false; SmtpServer.Port = System.Convert.ToInt32(appSettings["SMTPServerPort"]); SmtpServer.Credentials = new System.Net.NetworkCredential(appSettings["Username"].ToString(), appSettings["Password"].ToString()); SmtpServer.Send(mail);
I got below error while sending emails using Gmail Account –
Error Message –
An unhandled exception of type ‘System.Net.Mail.SmtpException’ occurred in System.dll. Additional information: The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required.
Also, if you login to Gmail account you might have received below email saying – “Sign-in attempt prevented.”
Gmail has recently improved sign process to ensure more security. Therefore it blocks sign-in attempts from some apps or devices which do not use modern security standards.
Allowing less secure apps to access your account
GMail starts to block less secure apps: how to enable access again
To solve this we have got three options as suggested in above blog-
- Enable Two-Factor Authentication for the account. As mentioned earlier, accounts with it enabled are not affected by the change. You may need to create an app specific password in the process for the app or service though. Read also: Use 2-Step Verification without mobile app.
- Change the “allow less secure apps” setting to enable. This allows them to connect to the account again.
- Switch to a different service or program.
The option which I chose was to “Allow less secure apps” for the time being.
For this go to below link –Allowing less secure apps to access your account and click on -“Less Secure Apps” as shown below-
This will take you to below page – Select the “Turn On” option for the time being and retry.
I have used App.config file to store all email and other configurable information.
Later I used Windows Task Scheduler to schedule this program to run on monthly once by using below steps.
- Open Task Scheduler
- Go to “Action” -> Create Basic Task – > Give Name and Description -> Click Next
- Select Trigger as “Monthly”
- Select all months and On -First Monday -> Click Next
- Select “Start a program” -> Next
- Browse your program exe -> Next
- Finish
Ensure your Config file is placed in same location.
Attaching the sample code for you usage. Ensure to change the Email Id details in config file.
GetCertificateInfo.zip – Complete Solution
Hope this helps.
Contact Me:-
@Gmail, @Facebook , @Twitter, @LinkedIn , @MSDNTechnet, @My Personal Blog
Contact Me –
Check out my other blogs –
Logic Apps Secure SQL Connection String in Azure Key Vault
While using SQL Connector in Logic Apps we don’t get a straightforward way to fetch/store connection string in Key Vault or in config. By default, the connection string we provide goes and sits in the Configurations as shown in the pics below and is in plaintext format. The regular process to use SQL Connector -…
Save Full File in SQL table Column (varbinary) and read the contents back in t
What if you needed to save full file as-is to a SQL Server table column. I ran into a similar scenario and had to save CSV Files in a table. For this we created a table with a column of type “varbinary” CREATE TABLE [dbo].[TableWithCompleteFiles]([SNo] [int] IDENTITY(1,1) NOT NULL,[FileName] varchar NULL,[FileContent] varbinary NULL) ON [PRIMARY]…
Export SQL Table content as CSV File to a File Location
Recently, I got a request to save the contents of the table to a File. Wroking Command – Above commands will fail with below error – Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1 [Batch Start Line 43]SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off…
Prashant BizTalk And Azure Integration Blogs 
