The common practice for user management in Azure AD is to create Azure AD Security Groups for users and assign permissions to groups rather than individual users. For example, the common practice for users who need access to Azure MySQL instance would to be add them to an Azure AD Group (say MySqlDb_Users) and then setup this Azure AD Group for access on Azure MySQL Instance.
Make sure to enable Auditing on Server Parameters section (at least Connection audit log event type).
The logs can be saved to Storage Account, Event Hubs or Azure Log Analytics Workspace. We used Log Analytics Workspace for the runbook because it provides rich querying capabilities.
The following Log Analytics Query and the corresponding screenshot shows that individual user activity is tracked in the Audit Logs in spite of them using the Azure AD Group as username to connect Azure MySQL — external_user_s field logs the exact individual user, connection_id_d and thread_id_d carry over the Connection/ThreadId which can be used to tie the user across all log records.
AzureDiagnostics
| where Category == "MySqlAuditLogs"
| order by TimeGenerated desc
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_name_s, error_code_d
Recently, I have had to automate a process to generate csv files and upload them to a certain folder in Azure blob storage. Here is the powershell code that does just that. After some research online, I have put together couple of solutions into one that worked well for me.
In order for this to work, you will need to install Azure powershell module on your machine. This solution assumes you are authenticating using account name and storage account key
#Upload csv files to Azure Blob Storage
$ErrorActionPreference = "Stop"
$acct = "storage-account-name" #Storage Account Name
$key = "storage-account-key" #Storage Account Key
$ContainerName = "container-name" #Container Name
$containerdirectory = "subfolder path within the container"
$localfilepath = "local-file-directory"
#create a context for communicating with azure storage
$ctx = New-AzStorageContext -StorageAccountName $acct -StorageAccountKey $key -Protocol Https
$container = Get-AzStorageContainer -Name $ContainerName -Context $ctx
$container.CloudBlobContainer.Uri.AbsoluteUri
if ($container) {
#use Set-AzStorageBlobContent to upload file
$filesToUpload = Get-Childitem -Path $localfilepath -Filter "*.csv"
ForEach ($x in $filesToUpload) {
$targetPath = $containerdirectory+($x.fullname.Substring($localfilepath.Length)).Replace("\", "/")
Write-Verbose "Uploading $("\" + $x.fullname.Substring($localfilepath.Length)) to $($container.CloudBlobContainer.Uri.AbsoluteUri + "/" + $targetPath)"
Set-AzStorageBlobContent -File $x.fullname -Container $container.Name -Blob $targetPath -Context $ctx -Force:$Force | Out-Null
}
}
Recently, I needed to create a test VM to play around with a POC. I figured why not use TerraForm to recreate a test VM with a pre-set configuration each time rather than having to do it through GUI over multiple click of buttons.
Prerequisites:
Install Terraform on your machine (Will cover the basics on how to do it in another post). However, you have plenty of tutorials online on how to do that. Pretty straight forward.
You will need to do the following step only once. Go to AWS console, create an IAM user with access to deploy EC2 instance. Store the access_key and secret_key in a file named “credentials” without a .txt extension
Create a folder named “.aws” and copy credentials file to this newly created folder. On my machine, I created it under C:/Users/username/.aws/
Steps:
Open Git Bash, navigate to the folder where you will store the terraform code (example: C:\Users\username\Documents\Github\terraform). You can pretty store your code anywhere. Above is just my example.
Create file named variables.tf (You will define the region where you would like to deploy your EC2 instance)
#Variable for AZ
variable "availability_zone" {
type = string
default = "us-east-1a"
}
3. Next, create a file named main.tf (You will define the infrastructure piece here)
provider "aws" {
region = "us-east-1"
}
#AWS Instance
resource "aws_instance" "example" {
ami = data.aws_ami.windows.id
instance_type = "t2.micro"
availability_zone = var.availability_zone
lifecycle {
ignore_changes = [ami]
}
}
#AMI Filter for Windows Server 2019 Base
data "aws_ami" "windows" {
most_recent = true
filter {
name = "name"
values = ["Windows_Server-2019-English-Full-Base-*"]
}
filter {
name = "virtualization-type"
values = ["hvm"]
}
owners = ["801119661308"] # Canonical
}
#EBS Volume and Attachment
#resource "aws_ebs_volume" "example" {
# availability_zone = var.availability_zone
#size = 30
#}
#resource "aws_volume_attachment" "ebs_att" {
# device_name = "/dev/sdh"
#volume_id = aws_ebs_volume.example.id
#instance_id = aws_instance.example.id
#}
4. Once you have the .tf files in place, run the below command to install the necessary plugins. It will download the highlighted folder below.
terraform init
5. After this, run the below statement to show the deployment plan
terraform plan
6. This will allow you to review all the items that terraform is going to create for you. I did have an issue with reaching aws sometimes. Error is shown below as an example:
7. Once everything looks good, run the following to create the EC2 instance. type “yes” upon receiving a prompt
terraform apply
8. If you ever need to destroy the VM you created, run the following:
terraform destroy --target aws_instance.example
I named my VM as example in the code above. You will need to provide the name of your VM that is applicable on your end.
That is all there is to create an EC2 instance using terraform. You can also adjust the code to deploy more than one instances. The above is a very simple example.
My next idea is to use terraform to deploy EC2 instance, and then use something like dbatools or powershell to download and install SQL server on it without ever logging into the VM.
9. If you ever would like to reproduce the above result, you can save the plan using the command below:
terraform apply -out=exampleEC2.plan
#Use the below to apply the saved plan
terraform apply "exampleEC2.plan"