Tuesday, January 25, 2022

Azure Automation Runbook: Execute SQL Scripts with Managed Identities

In this post, let's see how we can execute SQL Scripts on an Azure SQL Database with Managed Identities using an Azure Automation Runbook.

I am using PowerShell with ADO.NET instead of Invoke-Sqlcmd, because Invoke-Sqlcmd doesn't support Azure AD Authentication (via an AccessToken).

For the purpose of the demo, I have created the following SP on the target database, which we are going to be executing.

CREATE OR ALTER PROC dbo.TestStoredProcedure 
    @Param1 INT,
    @Param2 VARCHAR(100) 
SELECT @Param1 As Data1, @Param2 as Data2

The first step is to create an Automation Account. In Azure, search for Automation Accounts and click on Create.

Create an Automation Account
I have selected a Subscription, Resource Group, Region and given it a name.

Create an Automation Account: Managed Identities
This is an important step. This is where we are selecting Managed Identities for this Automation Account. For now, I am going to uncheck both the options (System assigned and User assigned) and clicking on Review + Create (or you can proceed through the Wizard, you should be fine with the defaults for the purpose of this post).

Once the Automation Account is created, we are all set to go.

System assigned Managed Identity

First, let's have a look at how to execute a SQL Script System assigned Managed Identity. First step is enabling System assigned Managed Identity in the Automation Account as below.

Automation Account: System assigned Managed Identity
Once that's saved, we need to grant this System assigned Managed Identity the access to our database. The name of the System assigned Managed Identity is the name of the Automation Account. 
ALTER ROLE db_datareader ADD MEMBER [aa-demo];
ALTER ROLE db_datawriter ADD MEMBER [aa-demo];
ALTER ROLE db_ddladmin ADD MEMBER [aa-demo];
EXEC sp_addrolemember 'db_datareader', 'aa-demo'
EXEC sp_addrolemember 'db_datawriter', 'aa-demo'
EXEC sp_addrolemember 'db_ddladmin', 'aa-demo'
We can run the above query to create and grant access to our System assigned Managed Identity. Note: The query needs to be executed after connecting to the database using an Azure AD login.

Now I am adding some variables to my Automation Account as follows. One for the Azure SQL Server and the other for the name of the Database.
Automation Account: Variables
Next, I am creating a Runbook in Automation Account
Create a Runbook
I have selected PowerShell 7.1 (preview). And I am adding the following script.
# Reading from variables
$AzureSqlServer = Get-AutomationVariable -Name "AzureSqlServer"
$ApplicationDatabase = Get-AutomationVariable -Name "ApplicationDatabase"
# Getting AccessToken for System assigned Managed Identity
$Resource = "https://database.windows.net/"
$QueryParameter = "?resource=$Resource"
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
    -UseBasicParsing `
    -Uri $Url `
    -Method 'GET' `
    -Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
$AccessToken = $Content.access_token 
# PowerShell/ADO.NET Connected Architecture
$SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
$SqlConnection.ConnectionString = "Server=$AzureSqlServer;Initial Catalog=$ApplicationDatabase;Connect Timeout=30" 
$SqlConnection.AccessToken = $AccessToken 
$SqlCommand = new-object System.Data.SqlClient.SqlCommand("exec dbo.TestStoredProcedure @Param1=100, @Param2='Hello World'"$SqlConnection);
$SqlDataReader = $SqlCommand.ExecuteReader()
$Results = @()
while ($SqlDataReader.Read())
    $Row = @{}
    for ($i = 0; $i -lt $SqlDataReader.FieldCount; $i++)
        $Row[$SqlDataReader.GetName($i)] = $SqlDataReader.GetValue($i)
    $Results += new-object psobject -property $Row
Write-Output $Results
And that's it. Now when I test this, I am seeing the following output:
Using System assigned Managed Identity: Output
That's working. 

User assigned Managed Identity

Now let's have a look at how to get things done using User Assigned Managed Identity. I am going back to our Automation Account and assigning a User assigned Managed Identity. I have already created a User assigned Managed Identity and I am just going to add it in.

Automation Account: User assigned Managed Identity

Sametime I have added another variable to the Automation Account to hold the ClientId/ObjectId the User assigned Managed Identity we just assigned.

Now we need to grant this User assigned Managed Identity the access to our SQL Database. We could follow the same steps as we did above for System assigned Managed Identity. But this time, I am going to go for a different approach, just to show our options.

I am navigating to the SQL Server where our database is in, and setting our User assigned Managed Identity as the Azure AD admin under SQL Server settings.

AAD Admin
Once that's saved, let's modify our Runbook as follows.
# Reading from variables
$AzureSqlServer = Get-AutomationVariable -Name "AzureSqlServer"
$ApplicationDatabase = Get-AutomationVariable -Name "ApplicationDatabase"
$ClientId = Get-AutomationVariable -Name "AzureSqlManagedIdentityId"
# Getting AccessToken for User assigned Managed Identity
# Note: Query Parameter now includes the client_Id
$Resource = "https://database.windows.net/"
$QueryParameter = "?resource=$Resource&client_Id=$ClientId"
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Url = $env:IDENTITY_ENDPOINT + $QueryParameter
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" 
$Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest `
	-UseBasicParsing `
	-Uri $Url `
	-Method 'GET' `
	-Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json 
$AccessToken = $Content.access_token 
# PowerShell/ADO.NET Dicconnected Architecture
$SqlConnection = New-Object System.Data.SqlClient.SQLConnection  
$SqlConnection.ConnectionString = "Server=$AzureSqlServer;Initial Catalog=$ApplicationDatabase;Connect Timeout=30" 
$SqlConnection.AccessToken = $AccessToken 
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand("dbo.TestStoredProcedure"$SqlConnection)
$SqlCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCommand.Parameters.AddWithValue("@Param1", 100) | Out-Null
$SqlCommand.Parameters.AddWithValue("@Param2""Hello World") | Out-Null
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null
Write-Output $DataSet.Tables[0]
Note: When using User assigned Managed Identity we need to pass in the client_id to AccessToken request (this is not required when using System assigned Managed Identity as it will automatically get resolved). And I have changed PowerShell/ADO.NET to use the Disconnected Architecture to show how to use that approach as well.

And now let's test this. I am seeing the following output:

Using User assigned Managed Identity: Output
And that's also working nicely.

Now you can publish the Runbook and schedule it the way you want.

Hope this helps.

Happy Coding.


Thursday, January 20, 2022

Azure Service Bus Simple Request-Reply Pattern

In this post let's have a look at how to implement Simple Request-Reply Pattern with Azure Service Bus.

So first have a look at what this Simple Request-Reply Pattern is. While the Service Buses are used mainly for asynchronous processing, there can be scenarios where the message Publisher/Sender needs to wait for a reply from Consumer/Receiver to the message they sent before proceeding. And that's where the Simple Request-Reply Pattern comes into the picture.

Let's go by an example.

Say a Sender sends the following message into a particular queue and a Receiver is consuming it. The sender populates the Input and expects the Consumer to populate the Output for it to proceed further.

public record ApplicationMessage(string Input)
    public string? Output { getset; }

So how do we achieve this?

ServiceBusMessage has this ReplyTo Property where the Sender can use to set the address of an entity to send its replies to, something like below.


ServiceBusAdministrationClient serviceBusAdministrationClient = new(Configuration.CONNECTION_STRING);

// Temporary Queue for Receiver to send their replies into
string replyQueueName = Guid.NewGuid().ToString();
await serviceBusAdministrationClient.CreateQueueAsync(new CreateQueueOptions(replyQueueName)
    AutoDeleteOnIdle = TimeSpan.FromSeconds(300)
// Sending the message
await using ServiceBusClient serviceBusClient = new(Configuration.CONNECTION_STRING);
ServiceBusSender serviceBusSender = serviceBusClient.CreateSender(Configuration.QUEUE_NAME);
ApplicationMessage applicationMessage = new("John");
ServiceBusMessage serviceBusMessage = new(JsonSerializer.SerializeToUtf8Bytes(applicationMessage))
    ContentType = "application/json",
    ReplyTo = replyQueueName,
await serviceBusSender.SendMessageAsync(serviceBusMessage);

Then after sending the message, Sender needs to look for replies in Queue: replyQueueName.

// Creating a receiver and waiting for the Receiver to reply
ServiceBusReceiver serviceBusReceiver = serviceBusClient.CreateReceiver(replyQueueName);
ServiceBusReceivedMessage serviceBusReceivedMessage = await serviceBusReceiver.ReceiveMessageAsync(TimeSpan.FromSeconds(60));
if (serviceBusReceivedMessage == null)
    WriteLine("Error: Didn't receive a response.");
applicationMessage = JsonSerializer.Deserialize<ApplicationMessage>(serviceBusReceivedMessage.Body.ToString());


From the Consumer, it's easy. It just needs to send the reply to the entity specified in the incoming ServiceBusMessage.ReplyTo.

await using ServiceBusClient serviceBusClient = new(Configuration.CONNECTION_STRING);
ServiceBusProcessor serviceBusProcessor = serviceBusClient.CreateProcessor(Configuration.QUEUE_NAME);
serviceBusProcessor.ProcessMessageAsync += async args =>
    // Message received
    ApplicationMessage applicationMessage = JsonSerializer.Deserialize<ApplicationMessage>(args.Message.Body.ToString());
    WriteLine($"Message Received: {applicationMessage}.\n");
    // Process the message/Update the Output
    applicationMessage.Output = $"Hello {applicationMessage.Input}!.";
    // Sending the reply
    ServiceBusSender serviceBusSender = serviceBusClient.CreateSender(args.Message.ReplyTo);
    ServiceBusMessage serviceBusMessage = new(JsonSerializer.Serialize(applicationMessage));
    await serviceBusSender.SendMessageAsync(serviceBusMessage);
And when we run this, the output would be something like below.
Simple Request-Reply Pattern Output
You can find the sample code through the below link.

More Read,
   Message Routing and Correlation

Hope this helps.

Happy Coding.


Friday, January 14, 2022

Running .NET 6 Isolated Azure Functions in Azure

In this post, let's see how we can run .NET 6 Isolated (Out-of-process) Azure Functions in Azure. 

If you are new to .NET 6 Isolated (Out-of-process) Azure Functions, you can read this post: .NET 6 and Azure Functions.

Let's start off by Creating a Function App. Here you need to select these options.

  • Runtime Stack: .NET
  • Version: 6
Create .NET 6 Function App
Now you can follow the Wizard and create the Function App. Once the Function App is created, we need to deploy a .NET 6 Isolated (Out-of-process) Azure Function App. I am not going to show that step, let's assume we just deployed a sample .NET 6 Isolated (Out-of-process) Azure Function App.

And now, when we navigate into the Function App from Azure, we should be seeing an error, something like this: Microsoft.Azure.WebJobs.Script: Did not find functions with language [dotnet].
Microsoft.Azure.WebJobs.Script: Did not find functions with language [dotnet].
And the reason for this is, by default when Azure creates .NET 6 Function App, it sets FUNCTIONS_WORKER_RUNTIME as dotnet.
In order to run Azure Functions in Isolated (Out-of-process) mode, we need to set FUNCTIONS_WORKER_RUNTIME to dotnet-isolated.

And that should be it. Now the Azure Function is all set up to run in Isolated (Out-of-process) mode.

Hope this helps.

Happy Coding.


Thursday, January 6, 2022

Great Start to the Year: Visual Studio 17.1 Preview 2 is Now Available

It's another year and a newer preview version of Visual Studio 2022 is already out, Visual Studio 2022 version 17.1 Preview 2.  This Preview version got shipped with some very nice features related to,
  • .NET Productivity
  • Git Tooling
  • Code Editor
  • IDE
  • Test tools
  • And more
These include some features that I have been waiting for so long.

.NET Productivity: Go to Definition, Navigate to the Original Source File

This is my favorite of them all and it's a fantastic feature. Now when we do Go to Definition, we are being navigated to the original source file. This is tremendously helpful in situations like we are exploring .NET APIs.

For example, say I need to go to the definition of AddDbContext<TContext>(...)
Go to Definition in Visual Studio 17.1 Preview 2
I can see the full implementation, whereas in prior versions, I am not seeing the method body.
Go to Definition in Earlier Version of Visual Studio

Git Tooling: Enable line-staging support

This one is another favorite. All this time, after doing a change in a file, we could only stage the whole file, not hunks. This feature has been there in most of the other Git tools out there for so many years, but was lacking in Visual Studio Git experience. Now finally, it's arrived.

This feature is not enabled by default, you need to explicitly turn this on. You can do it by navigating into Tools -> Options -> Environment -> Preview Features.
Enable line-staging support
And then we can stage hunks individually if we want to.
Stage Changes/Hunks

Editor: Code Cleanup on Save

Another nice One. With this feature we don't have to do  Ctrl+K, Ctrl+E (code cleanup) explicitly. Visual Studio will do the Code Cleanup on File upon Save automatically. And we can even choose which Profile to be used for the Code Cleanup. This feature again is not enabled by default, you need to explicitely enable it. You can do it by navigating into Tools -> Options -> Text Editor -> Code Cleanup.
Code Cleanup on Save
Aren't these nice?

Read more about all the new features in Visual Studio 17.1 Preview 2,

Happy Coding and Happy New year everyone!