List all tables with column details SQL Server

The below query can be run in a database on SQL Server to list all tables with column details.

SELECT schema_name(tab.schema_id) as schema_name, as table_name, 
    col.column_id, as column_name, as data_type,    
FROM sys.tables as tab
    INNER JOIN sys.columns as col
        on tab.object_id = col.object_id
    LEFT JOIN sys.types as t
    on col.user_type_id = t.user_type_id
ORDER BY schema_name,

Automate ARM templates deployment with Azure DevOps pipelines

In continuation to my previous post, I’m deploying the Hub and Spoke model using the ARM templates from Azure Repo using Azure pipeline. For this example, I’m only showing 2 ARM templates deployment for Log Analytics and Hub Vnet. The rest can be done in the same way.

Create a Service Connection in your Azure Project as shown below:

Below is the yaml file that I’m using to create multi-stage pipeline, where each stage is running a job:

name: $(BuildDefinitionName)_$(date:yyyyMMdd)$(rev:.r)
trigger: none
pr: none
stages :        
  - stage: arm_loganalytics_deploy
      - job: arm_loganalytics_deploy
              - checkout: self
              - task: AzureResourceManagerTemplateDeployment@3
                  deploymentScope: 'Resource Group'
                  azureResourceManagerConnection: 'AzureVSE'
                  subscriptionId: '1bcd68af-e392-4b66-9558-697bd7e8dc91'
                  action: 'Create Or Update Resource Group'
                  resourceGroupName: 'azhubspoke-rg'
                  location: 'Japan East'
                  templateLocation: 'Linked artifact'
                  csmFile: '$(System.DefaultWorkingDirectory)/loganalytics-workbook/loganalytics.json'
                  deploymentMode: 'Incremental'
  - stage: arm_hubvnet_deploy
      - job: arm_hubvnet_deploy
              - checkout: self

              - task: AzureResourceManagerTemplateDeployment@3
                  deploymentScope: 'Resource Group'
                  azureResourceManagerConnection: 'AzureVSE'
                  subscriptionId: '1bcd68af-e392-4b66-9558-697bd7e8dc91'
                  action: 'Create Or Update Resource Group'
                  resourceGroupName: 'azhubspoke-rg'
                  location: 'Japan East'
                  templateLocation: 'Linked artifact'
                  csmFile: '$(System.DefaultWorkingDirectory)/hub-vnet/hub-vnet.json'
                  deploymentMode: 'Incremental'

You can also validate the parameters of ARM template using the following parameter below csmFile in yaml file e.g.:

csmParametersFile: '$(System.DefaultWorkingDirectory)/hub-vnet/hub-vnet.parameters.json'

Run the pipeline and verify the results.

Create Azure Hub and Spoke model using ARM templates

The Hub and Spoke model is a popular Architecture for Teams who are migrating their Workloads to Cloud environment incrementally and still keep some workloads on-prem. Following are the main components that make-up Hub and Spoke model:

  1. Hub Virtual Network that holds your common components like VPN or Express Route Gateway, Azure Firewall, Azure Bastion Host etc. These components can be common to different environments like Dev, Staging, Prod etc. for better cost management.
  2. Spoke Virtual Network which have isolated workloads. These can hold VMs or other PaaS services like App Service that connect to On-Prem network via the Hub network gateway transit. There can be any number of Spokes.
Example Hub and Spoke Architecture

The benefits of hub and spoke configuration include cost savings, overcoming subscription limits and workload isolation.

Another example from the MS docs I found useful is as shown below:

I’m going to create the architecture shown above using ARM templates. You can find the templates for different components here. I’ve broken down the combined template into multiple templates that can be run in the following order by deploying them as Custom templates in Azure Portal and will be created in an existing Resource Group:

  1. Log Analytics workbook
  2. Hub (includes vpn gateway, firewall, bastion)
  3. Spoke1
  4. Spoke2
  5. Vnet Peerings
  6. Azure Sentinel
  7. Azure KeyVault

MS docs URL shared above contains more details about these components. Breaking down the templates into separate components gives you more control in creating a automated flow using say Azure pipelines. You can also later add or remove components easily as per your requirement.

Other options can be using Azure Blueprints for creating a minimal architecture and building from there on.

A similar architecture can created using Terraform as per MS docs here. But while running Terraform seems to be erroneous in Cloud shell and sometimes becomes unresponsive as per my experience.

Invoke PowerShell with parameters from C#

In this example, I’m using PowerShell script to connect to Exchange Server and create a mailbox. The parameters include the credentials that’ll be passed from C# while calling the script.

Below is the PowerShell script:

Invoke-Command -ComputerName  -Credential $creds -ScriptBlock {
$s = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://{serverdns}/PowerShell/ -Authentication Kerberos -Credential $using:creds
Import-PSSession $s
$securePassword = ConvertTo-SecureString $using:pwd -AsPlainText -Force
new-mailbox -UserPrincipalName $using:UPN -Alias $using:alias -Database $using:database -Name $using:cName -SamAccountName $using:sAMAccountName -OrganizationalUnit $using:OU -Password $securePassword -FirstName $using:fName -LastName $using:lName -DisplayName $using:displayName -ResetPasswordOnNextLogon $true

The C# code below will invoke the above PowerShell script:

using System.Management.Automation;
using System.Management.Automation.Runspaces;
using System.Security;

public string CreateMailbox(Int64 requestID, int sID, string cName, string firstName, string lastName, string displayName, string UPN, string alias, string exchangeDatabase, string userName, string OU, string accountPassword)
	Uri uri = new Uri(exch.ExchangeServer);
	SecureString secureExchangePassword = String2SecureString(exch.ExchangePassword);

	PSCredential credentials = new PSCredential(exch.ExchangeUserName, secureExchangePassword);

	Runspace runspace = RunspaceFactory.CreateRunspace();

	Collection<PSObject> results = new Collection<PSObject>();

	PowerShell powershell = PowerShell.Create();

		powershell.Runspace = runspace;

		//Change the Path to the Script to suit your needs
		System.IO.StreamReader sr = new System.IO.StreamReader("Script\\MailboxScript.ps1");

		powershell.Runspace.SessionStateProxy.SetVariable("creds", credentials);
		powershell.Runspace.SessionStateProxy.SetVariable("cName", cName);
		powershell.Runspace.SessionStateProxy.SetVariable("fName", firstName);
		powershell.Runspace.SessionStateProxy.SetVariable("lName", lastName);
		powershell.Runspace.SessionStateProxy.SetVariable("UPN", UPN);
		powershell.Runspace.SessionStateProxy.SetVariable("alias", alias);
		powershell.Runspace.SessionStateProxy.SetVariable("database", exchangeDatabase);
		powershell.Runspace.SessionStateProxy.SetVariable("sAMAccountName", userName);
		powershell.Runspace.SessionStateProxy.SetVariable("OU", OU);
		powershell.Runspace.SessionStateProxy.SetVariable("password", accountPassword);
		powershell.Runspace.SessionStateProxy.SetVariable("displayName", displayName);

		results = powershell.Invoke();
		if (powershell.Streams.Error.Count > 0)
			StringBuilder sb = new StringBuilder();
			foreach (ErrorRecord er in powershell.Streams.Error)
				sb.Append(er.ToString() + "       ");

			LogFactory.LogError("RequestID:'" + requestID.ToString() + "' ID:'" + sID.ToString() + "' Error:  On creating exchange account(PowerShell). Reason: " + sb.ToString());
			return "";
			return String.Concat("CN=" + cName + ",", OU);
	catch (Exception ex)
		LogFactory.LogError("RequestID:'" + requestID.ToString() + "' ID:'" + sID.ToString() + "' Error:  While creating exchange account. Reason: " + ex.Message.ToString());
		return "";
		runspace = null;

private SecureString String2SecureString(string password)
	SecureString remotePassword = new SecureString();
	for (int i = 0; i < password.Length; i++)

	return remotePassword;

This C# code can be called from an Application passing the required Exchange Server information and required Parameter values. The using statements in the C# code mentions the required Nuget packages to be installed.

Cannot access files with Umlauts in virtual directory IIS

In case you have a file in the virtual directory with filename containing non-English characters like Umlauts e.g. รถ. When we try to access the path with it is becomes inaccessible, but the files with only English characters are accessible.

You can try the following IIS settings, first one is

Request Filtering:

  1. Open the iis, double click the ‘Request Filtering’ icon
  2. In the ‘File Name Extension’ right click->Edit Feature Settings…’ the file ‘web.config’
  3. Check the option ‘Allow double escaping’ (this option is unchecked by default)
  4. Repeat all above 3 steps for the ‘default website’ (or whatever you have given the name to your site)
  5. Re-start IIS.

UrlScan under ISAPI:

One of the possible causes could be you’re using UrlScan extension for IIS which is visible under ISAPI filters. It is applied to all sites by default. In our case, removing UrlScan for the site facing issue resolved the issue.

Use jQuery UI dialog yes no

For this example, I’m using jQuery version 3.2.1. The javascript method popupConfirm() can be called on the click of a button event or onchange event of a drop-down. The no-close class is to remove the x button from top-right of the dialog box.

< link href="../Scripts/jquery-ui.css" rel="stylesheet" />
< script src="../Scripts/jquery.js"></script>
< script src="../Scripts/jquery-ui.js"></script>
< style>
	.no-close .ui-dialog-titlebar-close{
		display: none;
< /style>
<script language="JavaScript">   
    $(document).ready(function () {
            autoOpen: false,
            modal: true,
            width: 400

    function popupConfirm() {
			buttons: {
				"Yes": function () {
					$("#message").text('You clicked Yes');
				"No": function () {
					$("#message").text('You clicked No');
			dialogClass: "no-close"


Use the following html for the dialog and message as below.

< div id="dialog" title="Confirmation Required" >
  Are you sure?
< /div >
< div id="message" style="color: red;" >

< /div >

Use onchange method of a drop-down e.g.

< select name = 'types' size='1' onchange='popupConfirm();'> < /select> 

Check email count on mailbox with C#

Below is an example of .Net core 3.1 Console App that is reading a mailbox on Exchange and checking the count of mails. If the mail count cross a certain threshold, it’ll check for an e-mail older than 2 hours to send an alert.

Such an example can be used to schedule alerts or log information if you’re tracking mails in a mailbox.

Install Nuget package Microsoft.Exchange.WebServices to use ExchangeService class.

using Microsoft.Exchange.WebServices.Data;

public static void Main(string[] args)
	string TextlogMessage;
	string Subject = "";
	string sUsername = ConfigurationManager.AppSettings.Get("UserName");
	var section = ConfigurationManager.GetSection("secureAppSettings") as NameValueCollection;
	string vCount = ConfigurationManager.AppSettings.Get("Count");
	int Count = Convert.ToInt32(vCount);
	string sPassword = ConfigurationManager.AppSettings.Get("Password");
	string sDomain = ConfigurationManager.AppSettings.Get("Domain");
	TimeZoneInfo INDIAN_ZONE = TimeZoneInfo.FindSystemTimeZoneById("India Standard Time");
	DateTime indianTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, INDIAN_ZONE);
	DateTime comparedate = indianTime.AddHours(-2);
	DateTime MailDateTime = comparedate;
	ExchangeService exchange = new ExchangeService(ExchangeVersion.Exchange2013_SP1);
	exchange.Credentials = new WebCredentials(sUsername, sPassword, sDomain);
	exchange.Url = new Uri(ConfigurationManager.AppSettings.Get("ExchangeUrl"));
	Folder inbox = Folder.Bind(exchange, WellKnownFolderName.Inbox);
	var oSendemail = 0;
	TextlogMessage = "Start With count:" + inbox.TotalCount;

	if (inbox.TotalCount > Count)

		Console.WriteLine("Count " + inbox.TotalCount);

		ItemView view = new ItemView(1);

		view.OrderBy.Add(ItemSchema.DateTimeReceived, SortDirection.Ascending);
		var findResults = exchange.FindItems(WellKnownFolderName.Inbox, view);

		foreach (Item item in findResults.Items)

			Console.WriteLine("Test: " + item.DateTimeReceived + " %" + comparedate);
			DateTime newdate = TimeZoneInfo.ConvertTimeToUtc(item.DateTimeReceived);

			int Dresult = DateTime.Compare(newdate, comparedate);
			if (Dresult < 0)
				Console.WriteLine("Alert " + "Sub" + item.Subject + newdate + " %" + comparedate);
				oSendemail = 1;
				MailDateTime = newdate;
				Subject = item.Subject;


		if (oSendemail > 0)
			Program p = new Program();
			//To Do Send Email Logic..
			p.SendEmail(exchange, oSendemail, inbox.TotalCount, TextlogMessage);
			TextlogMessage = TextlogMessage + " EmailTime: " + MailDateTime + "CompareTime " + comparedate + "  Alert Sent Subject : " + Subject;



	//To Do logging..


Call .Net core API from Console with App Bearer token

In the following example, we’re using a .Net Core 3.1 Console App that will call API with POST request that requires Authentication with a bearer token in Authrorization Header. The token is generated by passing credentials to another API endpoint.

For more details on how to use appSettings.json file in Console App, check this post.

using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Text;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;

namespace ConsoleApp1

    class Credentials
        public string username { get; set; }
        public string password { get; set; }

    class Token
        public string token { get; set; }

    class Program
        static void Main(string[] args)
            string ResponseString = "";
            HttpWebResponse response = null;
            HttpWebResponse response2 = null;
            IConfiguration Config = new ConfigurationBuilder()

                var baseURL = Config.GetSection("baseURL").Value;
                var request = (HttpWebRequest)WebRequest.Create(baseURL + "/token");
                request.Accept = "application/json"; //"application/xml";
                request.Method = "POST";

		//Get credentials from config.
                var dusername = EncryptionService.Decrypt(Config.GetSection("credentials")["username"]);
                var dpassword = EncryptionService.Decrypt(Config.GetSection("credentials")["password"]);

                Credentials cred = new Credentials()
                    username = dusername,
                    password = dpassword

                var myContent = JsonConvert.SerializeObject(cred);

                var data = Encoding.ASCII.GetBytes(myContent);

                request.ContentType = "application/json";
                request.ContentLength = data.Length;

                using (var stream = request.GetRequestStream())
                    stream.Write(data, 0, data.Length);

                using (response = (HttpWebResponse)request.GetResponse())
                    ResponseString = new StreamReader(response.GetResponseStream()).ReadToEnd();

		//Get the token from the /token end-point and call another end-point.
                Token token = JsonConvert.DeserializeObject<Token>(ResponseString);

                var request2 = (HttpWebRequest)WebRequest.Create(baseURL + "/ProcessData");
                request2.Accept = "application/json"; //"application/xml";
                request2.Method = "POST";
		//Pass token in Authorization Header.
                request2.Headers["Authorization"] = "Bearer " + token.token;

                using (response2 = (HttpWebResponse)request2.GetResponse())
                    ResponseString = new StreamReader(response2.GetResponseStream()).ReadToEnd();

            catch (WebException ex)
                if (ex.Status == WebExceptionStatus.ProtocolError)
                    response = (HttpWebResponse)ex.Response;
                    ResponseString = "Some error occured: " + response.StatusCode.ToString();
                    ResponseString = "Some error occured: " + ex.Status.ToString();



Use config file in .Net core Console App

For this example, I’m using a Console App created in .Net Core 3.1 using Visual Studio 2019.

Add a json file to your Project and name it appSettings.json, it could like like below:

  "credentials": {
    "username": "xxxx",
    "password": "xxxx"
  "URL": ""

Install the following Nuget packages in your Project:

<PackageReference Include="Microsoft.Extensions.Configuration" Version="5.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Binder" Version="5.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables" Version="5.0.0" />
<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="5.0.0" />

The versions could vary depending on the time you’re adding these packages.

In your Program.cs file, add the namespace:

using Microsoft.Extensions.Configuration;

In the Main method, add the following code:

class Program
    static void Main(string[] args)
		IConfiguration Config = new ConfigurationBuilder()
		var URL = Config.GetSection("URL").Value;
		//Assuming you're using Encrypted values in configuration.
		var dusername = EncryptionClass.Decrypt(Config.GetSection("credentials")["username"]);
        var dpassword = EncryptionClass.Decrypt(Config.GetSection("credentials")["password"]);