Prashant BizTalk And Azure Integration Blogs

Search
Skip to content
  • BizTalk
    • BizTalk Migration
    • BizTalk High Availability & Disaster Recovery
    • ESB Toolkit
  • My YouTube Channel
  • Contact Me
  • About Me

Monthly Archives: May 2016

BizTalk, BizTalk Automation

Get Certificate Information(about to expire) programatically using C#

May 24, 2016 Prashant Singh Leave a comment

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&#8221; 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-

SampleEmail.png

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.”

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-

  1. 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.
  2. Change the “allow less secure apps” setting to enable. This allows them to connect to the account again.
  3. 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-

AllowingLessSecureApps

This will take you to below page – Select the “Turn On” option for the time being and retry.

TurnON-AccessForLessSecureApps.JPG

I have used App.config file to store all email and other configurable information.

App.config File.png

Later I used Windows Task Scheduler to schedule this program to run on monthly once by using below steps.

  1. Open Task Scheduler
  2. Go to “Action” -> Create Basic Task – > Give Name and Description -> Click NextCreateBasicTask
  3. Select Trigger as “Monthly”
  4.  Select all months and On -First Monday -> Click NextScheduleToRunOnFirstMondayOfEveryMonth.JPG
  5. Select “Start a program” -> Next
  6. Browse your program exe -> NextBrowseProgram.JPG
  7. Finish

Ensure your Config file is placed in same location.

ConfigFileIsPresentAtSameLocation.JPG

Attaching the sample code for you usage. Ensure to change the Email Id details in config file.

GetCertificateInfo.zip – Complete Solution

Program.cs

App.config

Hope this helps.

Contact Me:- 

@Gmail, @Facebook , @Twitter, @LinkedIn , @MSDNTechnet, @My Personal Blog 

Contact Me –

  • YouTube
  • Mail
  • Facebook
  • Twitter
  • Link
  • WordPress

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 -…

by Prashant Singh May 31, 2023

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]…

by Prashant Singh May 20, 2023

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…

by Prashant Singh May 20, 2023
Advertisement
Allowing less secure apps to access your accountCertificates about to expire in next 30 daysFormatting Email BodyGet Certificate about to expire programatically using C#Get Certificate Information programatically using C#he SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required.Sending emails using GMailSign-in attempt prevented.Using Task Scheduler to run a ProgramWindows Task Scheduler

My Integration Experiences – BizTalk And Azure Integration

Search

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 22 other subscribers

Categories

  • Azure Data (1)
  • Azure Integration Services (1)
    • Logic Apps (1)
  • BizTalk (17)
    • BizTalk Automation (6)
    • BizTalk High Availability & Disaster Recovery (1)
    • BizTalk Migration (3)
  • ESB Toolkit (2)
  • Powershell (2)
    • Desired State Configuration(DSC) (2)
  • SQL (3)

Top Posts & Pages

  • Logic Apps Secure SQL Connection String in Azure Key Vault
  • BizTalk Server High Availability and  Disaster Recovery Options
  • Welcome to Prashant BizTalk and Azure Integration Blogs
  • PROGRAMMATICALLY Change service account's password for Windows Services, iis app pools and scheduled tasks - C#
  • BizTalk Automated migration Tool
  • BizTalk Server Migration from 2010 to 2013 R2- Why & How
  • Error - "Microsoft .NET Framework 3.5 Service Pack 1 is required"- Specify an alternate source path
  • Step by Step Guide to Install & Setup ESB Toolkit and Management Portal (Standalone Machine)
Follow Prashant BizTalk And Azure Integration Blogs on WordPress.com

Connect with me

  • Facebook
  • Twitter
  • LinkedIn
  • YouTube

Recent Posts

  • Logic Apps Secure SQL Connection String in Azure Key Vault
  • Save Full File in SQL table Column (varbinary) and read the contents back in t
  • Export SQL Table content as CSV File to a File Location
  • SSMS – Export Table and its data as SCRIPT
  • DSC Open Firewall Ports and Enable existing Firewall Rules

Archives: Old Blogs

  • May 2023 (5)
  • March 2023 (1)
  • February 2023 (1)
  • July 2022 (2)
  • November 2021 (1)
  • August 2020 (2)
  • July 2020 (1)
  • June 2020 (1)
  • August 2017 (1)
  • May 2016 (1)
  • April 2016 (1)
  • September 2015 (1)
  • August 2015 (2)
  • March 2015 (6)

Blog Stats

  • 102,088 hits

Contact Me

Bangalore, India
+919538426060
Create a free website or blog at WordPress.com.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • Prashant BizTalk And Azure Integration Blogs
    • Already have a WordPress.com account? Log in now.
    • Prashant BizTalk And Azure Integration Blogs
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...