Saturday, February 12, 2022

T-SQL Commands to set cross subscription Azure SQL Server DB replication

 Setting replication between Azure SQL databases are very important as it helps in recovering your application during any disaster. Same subscription replication is straight forward as it can be done from Azure Portal itself on the database Replica page. 

In case of you would need to replicate your database to a SQL Server in a different subscription then the best way to do is the T-SQL commands . Below is the step by step to create cross subscription SQL replication.

GEO Replication Configuration 

Follow the below steps to configure GEO replication (make sure the public access is enabled while executing the below steps) 

1) Create a privileged login/user on both primary and secondary to be used for this setup: 

a. Connect to your primary Azure SQL Server and create a login and a user on your master database using the below script: 

--Primary Master Database 

create login GeoReplicationMigUser with password = '<password>' 


create user GeoReplicationMigUser for login GeoReplicationMigUser

alter role dbmanager add member GeoReplicationMigUser

Get the created user SID and save it:  


select sid from sys.sql_logins where name = 'GeoReplicationMigUser'  


 b. On the primary database create the required user as below:  


-- primary user database 

create user GeoReplicationMigUser for login GeoReplicationMigUser

alter role db_owner add member GeoReplicationMigUser



 c. Connect to your secondary server and create the same login and user while using the   same SID you got from point A:  


--Secondary Master Database 

create login GeoReplicationMigUser with password = <password>, sid=0x010600000000006400000000000000001C95H41B95D9C78CFRA8578FACE37E5A 

create user GeoReplicationMigUser for login GeoReplicationMigUser; 

alter role dbmanager add member GeoReplicationMigUser



2) Make sure that both primary and secondary Azure SQL servers firewall rules are configured to allow the connection (such as the IP address of the host running SQL Server Management Studio). 


3) Log in with the created user to your primary Azure SQL server to add the secondary server and configure GEO replication, by running the below script on the primary master database: 



-- Primary Master database 


alter database DBName add secondary on server[]


4) To verify the setup, access your Azure portal, go to your primary Azure SQL database, and access Replicas blade as below: 



You will notice that the secondary database has been added and configured. 


Note: before moving to the next steps make sure your replica has completed the seeding and is marked as "readable" under replica status (as highlighted below):   



·       Remove Replication



             REMOVE SECONDARY ON server [secondarydb]


Azure VMSS VM Instance Status using powershell - Get-AzureRmVmssVM

 The below powershell script helps you to check the status of VM Instances inside a Virtual Machine Scale Sets. This will check the Status whether the Instance is Running or Deallocated/Stopped as some actions will not be allowed on a deallocated instance while performing on VMSS.

For Instance, I'm checking if the status is Running and then do any operation on the instance.

Get-AzureRmVmssVM -ResourceGroupName $nameprefix  -VMScaleSetName $key | foreach { $Id = $_.InstanceId; $vmssvm = Get-AzureRmVmssVM -ResourceGroupName $nameprefix  -VMScaleSetName $key -InstanceView -InstanceId $_.InstanceId; if($vmssvm.Statuses[$vmssvm.Statuses.Count-1].Code.Contains("running")){ Write-Host "vmid: " $vmssvm.Statuses[$vmssvm.Statuses.Count-1].Code}}

Friday, January 28, 2022

Pass List of users and get Azure AD Signin logs

 Pass List of users and get Azure AD Signin logs. You can loop through AD users in case of no list of users to pass. Note that this gives logs for last 30 days and if you want previous logs then need to get from Security Centre Audit Logs.


$Headers = "Email`tInteractive`tLastLogon" >><folderpath>\ADUsersSignIns.csv

#$SetDate = (Get-Date).AddDays(-1);

#$SetDate = Get-Date($SetDate) -format yyyy-MM-dd 

foreach($line in Get-Content <folderpath>\ADUsers.txt) 


     $UPN = $line



     #$LoginTime = Get-AzureAdAuditSigninLogs -filter "userprincipalname eq '$UPN' and createdDateTime gt 2022-01-21T00:30:00.0Z" -top 1 | select CreatedDateTime, UserPrincipalName, IsInteractive

     $LoginTime = Get-AzureAdAuditSigninLogs -filter "userprincipalname eq '$UPN'" -top 1 | select CreatedDateTime, UserPrincipalName, IsInteractive

     $NewLine = $UPN + "`t" + $LoginTime.IsInteractive + "`t" + $LoginTime.CreatedDateTime

     $NewLine >><folderpath>\ADUsersSignIns.csv



     $NewLine = $UPN + "`t" + "" + "`t" + "Too Many Requests"

     $NewLine >><folderpath>\ADUsersSignIns.csv




Monday, December 6, 2021

Powershell script to export certificate from Azure Keyvault

 # Replace these variables with your own values

$vaultName = "<key vault name>"

$certificateName = "<certificate name>"

$pfxPath = "<folder path>\<certname>.pfx"

$password = "<exportpassword>"


$cert = Get-AzKeyVaultCertificate -VaultName $vaultName -Name $certificateName


$pfxSecret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $cert.Name -AsPlainText



$pfxUnprotectedBytes = [Convert]::FromBase64String($pfxSecret)

$pfx = New-Object Security.Cryptography.X509Certificates.X509Certificate2Collection

$pfx.Import($pfxUnprotectedBytes, $null, [Security.Cryptography.X509Certificates.X509KeyStorageFlags]::Exportable)

$pfxProtectedBytes = $pfx.Export([Security.Cryptography.X509Certificates.X509ContentType]::Pkcs12, $password)

[IO.File]::WriteAllBytes($pfxPath, $pfxProtectedBytes)

Wednesday, November 10, 2021

Add VMSS extensions with settings and protected settings- Powershell

 The below script guides you to add/remove a vmss extension in Azure. The example is for adding Microsoft Monitoring Agent to Azure vmss which will have workspace Id to collect custom logs from VM's

Get VMSS to a variable

$vmssname= Get-AzVmss -ResourceGroupName npqatx -VMScaleSetName <vmssname>

$workspaceId="GUID of log analytics workspace"

 $workspaceKey="workspace key"

$settings=@{"workspaceId" = $workspaceId}

$protSettings=@{"workspaceKey" = $workspaceKey}

#Adding new extension

Add-AzVmssExtension -VirtualMachineScaleSet $vmssname -Name "MMAExtension" -Publisher "Microsoft.EnterpriseCloud.Monitoring" -Type "MicrosoftMonitoringAgent" -TypeHandlerVersion "1.0" -AutoUpgradeMinorVersion $true -Setting $settings -ProtectedSetting $protSettings

Update-AzVmss -ResourceGroupName "RGName" -VMScaleSetName $vmssname.Name -VirtualMachineScaleSet $vmssname

#Remove existing vmss extension

Remove-AzVmssExtension -VirtualMachineScaleSet $webvmss -Name "MMAExtension"

Update-AzVmss -ResourceGroupName "RGName" -VMScaleSetName $vmssname.Name -VirtualMachineScaleSet $vmssname

#make sure to upgrade instances in vmss to the latest model after every change to vmss.

Wednesday, October 27, 2021

Authenticate Azure APIM using Managed Identity to access Storage Account

<!-- check the cache for secret first -->
<cache-lookup-value key="mysecret" variable-name="keyvaultsecretResponse" />
<!-- call Key Vault if not found in cache -->
<when condition="@(!context.Variables.ContainsKey("keyvaultsecretResponse"))">
<send-request mode="new" response-variable-name="keyvaultsecret" timeout="20" ignore-error="false">
<authentication-managed-identity resource="" />
<!-- transform response to string and store in cache -->
<set-variable name="keyvaultsecretResponse" value="@(((IResponse)context.Variables["keyvaultsecret"]).Body.As<string>())" />
<cache-store-value key="mysecret" value="@((string)context.Variables["keyvaultsecretResponse"])" duration="3600" />
<set-status code="200" reason="Done" />
<set-header name="content-type" exists-action="override">
<base />
<base />
<base />
<base />