Author: Pulkit Gulati

Pulkit Gulati

List SQL Server Objects

To list out all Databases in SQL Server run the following query under master:

SELECT name FROM master.dbo.sysdatabases

To list out all SQL Server Jobs with Job Owner details use:

SELECT s.name AS JobName, l.name AS JobOwner
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid
WHERE l.name IS NOT NULL
ORDER by l.name

To list out all SQL Server Logins use:

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

To list out all Linked Servers:

select sp.name as login,
       sp.type_desc as login_type,
       sl.password_hash,
       sp.create_date,
       sp.modify_date,
       case when sp.is_disabled = 1 then 'Disabled'
            else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
          on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;
Pulkit Gulati

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,
    tab.name as table_name, 
    col.column_id,
    col.name as column_name, 
    t.name as data_type,    
    col.max_length,
    col.precision
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,
    table_name, 
    column_id;
Pulkit Gulati

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
    jobs:
      - job: arm_loganalytics_deploy
        steps:
              - checkout: self
 
              - task: AzureResourceManagerTemplateDeployment@3
                inputs:
                  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
    jobs:
      - job: arm_hubvnet_deploy
        steps:
              - checkout: self

              - task: AzureResourceManagerTemplateDeployment@3
                inputs:
                  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.

Pulkit Gulati

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.

Pulkit Gulati

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:

$global:creds;
$global:cName;
$global:fName;
$global:lName;
$global:UPN;
$global:alias;
$global:database="db_name";
$global:sAMAccountName;
$global:OU;
$global:pwd;
$global:displayName;
Invoke-Command -ComputerName serverdns.mylab.com  -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();

	try
	{
		runspace.Open();
		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.AddScript(sr.ReadToEnd());

		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 "";
		}
		else
		{
			sr.Close();
			sr.Dispose();
			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 "";
	}
	finally
	{
		runspace.Dispose();
		runspace = null;
	}
}

private SecureString String2SecureString(string password)
{
	SecureString remotePassword = new SecureString();
	for (int i = 0; i < password.Length; i++)
		remotePassword.AppendChar(password[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.

Pulkit Gulati

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.

Pulkit Gulati

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 () {
        $("#dialog").dialog({
            autoOpen: false,
            modal: true,
            width: 400
        });
    });

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

		$("#dialog").dialog("open");
    }
</script>

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>
Pulkit Gulati

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..
	log(TextlogMessage);

}
Pulkit Gulati

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()
                .AddJsonFile("appSettings.json")
                .Build();

            try
            {
                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();
                }

                Console.WriteLine(ResponseString);
                Environment.Exit(0);
            }
            catch (WebException ex)
            {
                if (ex.Status == WebExceptionStatus.ProtocolError)
                {
                    response = (HttpWebResponse)ex.Response;
                    ResponseString = "Some error occured: " + response.StatusCode.ToString();
                }
                else
                {
                    ResponseString = "Some error occured: " + ex.Status.ToString();
                }
            }

        }
    }

}