SharePoint Azure and o365 Blog

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[secondarydb.database.windows.net]

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

ALTER DATABASE DBName

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.

Connect-AzureAD

$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

try

{

#$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

}

catch{

$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"