Run a SQL Query with Azure Arc
Hi All, In this article, you can find a way to retrieve database permission from all your onboarded databases through Azure Arc. This idea is born from a customer request around maintaining a standard permission set, in a very wide environment (about 1000 SQL Server). This solution is based on Azure Arc, so first you need to onboard your SQL Server to Azure Arc and enable the SQL Server extension. If you want to test Azure Arc in a test environment, you can use the Azure Jumpstart, in this repo you will find ready-to-deploy arm templates the deploy demos environments. The other solution components are an automation account, log analytics and a Data collection rule \ endpoint. Here you can find a little recap of the purpose of each component: Automation account: with this resource you can run and schedule a PowerShell script, and you can also store the credentials securely Log Analytics workspace: here you will create a custom table and store all the data that comes from the script Data collection Endpoint / Data Collection Rule: enable you to open a public endpoint to allow you to ingest collected data on Log analytics workspace In this section you will discover how I composed the six phases of the script: Obtain the bearer token and authenticate on the portal: First of all you need to authenticate on the azure portal to get all the SQL instance and to have to token to send your assessment data to log analytics $tenantId = "XXXXXXXXXXXXXXXXXXXXXXXXXXX" $cred = Get-AutomationPSCredential -Name 'appreg' Connect-AzAccount -ServicePrincipal -Tenant $tenantId -Credential $cred $appId = $cred.UserName $appSecret = $cred.GetNetworkCredential().Password $endpoint_uri = "https://sampleazuremonitorworkspace-weu-a5x6.westeurope-1.ingest.monitor.azure.com" #Logs ingestion URI for the DCR $dcrImmutableId = "dcr-sample2b9f0b27caf54b73bdbd8fa15908238799" #the immutableId property of the DCR object $streamName = "Custom-MyTable" $scope= [System.Web.HttpUtility]::UrlEncode("https://monitor.azure.com//.default") $body = "client_id=$appId&scope=$scope&client_secret=$appSecret&grant_type=client_credentials"; $headers = @{"Content-Type"="application/x-www-form-urlencoded"}; $uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" $bearerToken = (Invoke-RestMethod -Uri $uri -Method "Post" -Body $body -Headers $headers).access_token Get all the SQL instances: in my example I took all the instances, you can also use a tag to filter some resources, for example if a want to assess only the production environment you can use the tag as a filter $servers = Get-AzResource -ResourceType "Microsoft.AzureArcData/SQLServerInstances" When you have all the SQL instance you can run your t-query to obtain all the permission , remember now we are looking for the permission, but you can use for any query you want or in other situation where you need to run a command on a generic server $SQLCmd = @' Invoke-SQLcmd -ServerInstance . -Query "USE master; BEGIN IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8' begin IF EXISTS (SELECT TOP 1 * FROM tempdb.dbo.sysobjects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser end end ELSE begin IF EXISTS (SELECT TOP 1 * FROM tempdb.sys.objects (nolock) WHERE name LIKE '#TUser%') begin DROP TABLE #TUser end end CREATE TABLE #TUser (DBName SYSNAME,[Name] SYSNAME,GroupName SYSNAME NULL,LoginName SYSNAME NULL,default_database_name SYSNAME NULL,default_schema_name VARCHAR(256) NULL,Principal_id INT); IF LEFT(CAST(Serverproperty('ProductVersion') AS VARCHAR(1)),1) = '8' INSERT INTO #TUser EXEC sp_MSForEachdb ' SELECT ''?'' as DBName, u.name As UserName, CASE WHEN (r.uid IS NULL) THEN ''public'' ELSE r.name END AS GroupName, l.name AS LoginName, NULL AS Default_db_Name, NULL as default_Schema_name, u.uid FROM [?].dbo.sysUsers u LEFT JOIN ([?].dbo.sysMembers m JOIN [?].dbo.sysUsers r ON m.groupuid = r.uid) ON m.memberuid = u.uid LEFT JOIN dbo.sysLogins l ON u.sid = l.sid WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in (''public'',''dbo'',''guest'') ORDER BY u.name ' ELSE INSERT INTO #TUser EXEC sp_MSforeachdb ' SELECT ''?'', u.name, CASE WHEN (r.principal_id IS NULL) THEN ''public'' ELSE r.name END GroupName, l.name LoginName, l.default_database_name, u.default_schema_name, u.principal_id FROM [?].sys.database_principals u LEFT JOIN ([?].sys.database_role_members m JOIN [?].sys.database_principals r ON m.role_principal_id = r.principal_id) ON m.member_principal_id = u.principal_id LEFT JOIN [?].sys.server_principals l ON u.sid = l.sid WHERE u.TYPE <> ''R'' and u.TYPE <> ''S'' and u.name not in (''public'',''dbo'',''guest'') order by u.name '; SELECT DBName, Name, GroupName,LoginName FROM #TUser where Name not in ('information_schema') and GroupName not in ('public') ORDER BY DBName,[Name],GroupName; DROP TABLE #TUser; END" '@ $command = New-AzConnectedMachineRunCommand -ResourceGroupName "test_query" -MachineName $server1 -Location "westeurope" -RunCommandName "RunCommandName" -SourceScript $SQLCmd In a second, you will receive the output of the command, and you must send it to the log analytics workspace (aka LAW). In this phase, you can also review the output before sending it to LAW, for example, removing some text or filtering some results. In my case, I’m adding the information about the server where the script runs to each record. $array = ($command.InstanceViewOutput -split "r?n" | Where-Object { $.Trim() }) | ForEach-Object { $line = $ -replace '\', '\\' ù$array = $array | Where-Object { $_ -notmatch "DBName,Name,GroupName,LoginName" } | Where-Object {$_ -notmatch "------"} The last phase is designed to send the output to the log analytics workspace using the dce \ dcr. $staticData = @" [{ "TimeGenerated": "$currentTime", "RawData": "$raw", }]"@; $body = $staticData; $headers = @{"Authorization"="Bearer $bearerToken";"Content-Type"="application/json"}; $uri = "$endpoint_uri/dataCollectionRules/$dcrImmutableId/streams/$($streamName)?api-version=2023-01-01" $rest = Invoke-RestMethod -Uri $uri -Method "Post" -Body $body -Headers $headers When the data arrives in log analytics workspace, you can query this data, and you can create a dashboard or why not an alert. Now you will see how you can implement this solution. For the log analytics, dce and dcr, you can follow the official docs: Tutorial: Send data to Azure Monitor Logs with Logs ingestion API (Resource Manager templates) - Azure Monitor | Microsoft Learn After you create the dcr and the log analytics workspace with its custom table. You can proceed with the Automation account. Create an automation account using the creating wizard You can proceed with the default parameter. When the Automation Account creation is completed, you can create a credential in the Automation Account. This allows you to avoid the exposition of the credential used to connect to Azure You can insert here the enterprise application and the key. Now you are ready to create the runbook (basically the script that we will schedule) You can give the name you want and click create. Now go in the automation account than Runbooks and Edit in Portal, you can copy your script or the script in this link. Remember to replace your tenant ID, you will find in Entra ID section and the Enterprise application You can test it using the Test Pane function and when you are ready you can Publish and link a schedule, for example daily at 5am. Remember, today we talked about database permissions, but the scenarios are endless: checking a requirement, deploying a small fix, or removing/adding a configuration — at scale. At the end, as you see, Azure Arc is not only another agent, is a chance to empower every environment (and every other cloud provider 😉) with Azure technology. See you in the next techie adventure. **Disclaimer** The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.OPNsense Firewall as Network Virtual Appliance (NVA) in Azure
This blog is available as a video on YouTube: youtube.com/watch?v=JtnIFiB7jkE Introduction to OPNsense In today’s cloud-driven world, securing your infrastructure is more critical than ever. One powerful solution is OPNsense. OPNsense is a powerful open-source firewall that can be used to secure your virtual networks. Originally forked from pfSense, which itself evolved from m0n0wall. OPNsense could run on Windows, MacOS, Linux including OpenBSD and FreeBSD. It provides a user-friendly web interface for configuration and management. What makes OPNsense Firewall stand out is its rich feature set: VPN Support for point-to-site and site-to-site connections using technologies like WireGuard and OpenVPN. DNS Management with options such as OpenDNS and Unbound DNS. Multi-network handling enabling you to manage different LANs seamlessly. Advanced security features including intrusion detection and forward proxy integration. Plugin ecosystem supporting official and community extensions for third-party integrations. In this guide, you’ll learn how to install and configure OPNsense Firewall on an Azure Virtual Machine, leveraging its capabilities to secure your cloud resources effectively. We'll have three demonstrations: Installing OPNsense on an Azure virtual machine Setting up point-to-site VPN using WireGuard Here is the architecture we want to achieve in this blog, except the Hb and Spoke configuration which is planned for the second part coming soon. 1. Installing OPNsense on an Azure Virtual Machine There are three ways to have OPNsense in a virtual machine. Create a VM from scratch and install OPNsense. Install using the pre-packaged ISO image created by Deciso the company that maintains OPNsense. Use a pre-built VM image from the Azure Marketplace. In this demo, we will use the first approach to have more control over the installation and configuration. We will create an Azure VM with FreeBSD OS and then install OPNsense using a shell script through the Custom Script Extension. All the required files are in this repository: github.com/HoussemDellai/azure-network-course/205_nva_opnsense. The shell script configureopnsense.sh will install OPNsense and apply a predefined configuration file config.xml to set up the firewall rules, VPN, and DNS settings. It will take 4 parameters: GitHub path where the script and config file are hosted, in our case it is /scripts/. OPNsense version to install, currently set to 25.7. Gateway IP address for the trusted subnet. Public IP address of the untrusted subnet. This shell script is executed after the VM creation using the Custom Script Extension in Terraform represented in the file vm_extension_install_opnsense.tf. OPNsense is intended to be used an NVA so it would be good to apply some of the good practices. One of these practices is to have two network interfaces: Trusted Interface: Connected to the internal network (spokes). Untrusted Interface: Connected to the internet (WAN). This setup allows OPNsense to effectively manage and secure traffic between the internal network and the internet. Second good practice is to start with a predefined configuration file config.xml that includes the basic settings for the firewall, VPN, and DNS. This approach saves time and ensures consistency across deployments. It is recommended to start with closed firewall rules and then open them as needed based on your security requirements. But for demo purposes, we will allow all traffic. Third good practice is to use multiple instances of OPNsense in a high-availability setup to ensure redundancy and failover capabilities. However, for simplicity, we will use a single instance in this demo. Let's take a look at the resources that will be created by Terraform using the AzureRM provider: Resource Group Virtual Network (VNET) named vnet-hub with two subnets: Trusted Subnet: Internal traffic between spokes. Untrusted Subnet: Exposes the firewall to the internet. Network Security Group (NSG): attached to the untrusted subnet, with rules allowing traffic to the VPN, OPNsense website and to the internet. Virtual Machine: with the following configuration: FreeBSD OS image using version 14.1. VM size: Standard_D4ads_v6 with NVMe disk for better performance. Admin credentials: feel free to change the username and password with more security. Two NICs (trusted and untrusted) with IP forwarding enabled to allow traffic to pass through the firewall. NAT Gateway: attached to the untrusted subnet for outbound internet connectivity. Apply Terraform configuration To deploy the resources, run the following commands in your terminal from within the 205_nva_opnsense directory: terraform init terraform apply -auto-approve Terraform provisions the infrastructure and outputs resource details. In the Azure portal you should see the newly created resources. Accessing the OPNsense dashboard To access the OPNsense dashboard: Get the VM’s public IP from the Azure portal or from Terraform output. Paste it into your browser. Accept the TLS warning (TLS is not configured yet). Log in with Username: root and Password: opnsense you can change it later in the dashboard. You now have access to the OPNsense dashboard where you can: Monitor traffic and reports. Configure firewall rules for LAN, WAN, and VPN. Set up VPNs (WireGuard, OpenVPN, IPsec). Configure DNS services (OpenDNS, UnboundDNS). Now that the OPNsense firewall is up and running, let's move to the next steps to explore some of its features like VPN. 2. Setting up Point-to-Site VPN using WireGuard We’ll demonstrate how to establish a WireGuard VPN connection to OPNsense firewall. The configuration file config.xml used during installation already includes the necessary settings for WireGuard VPN. For more details on how to set up WireGuard on OPNsense, refer to the official documentation. We will generate a Wireguard peer configuration using the OPNsense dashboard. Navigate to VPN > WireGuard > Peer generator then add a name for the peer, fill in the IP address for the OPNsense which is the public IP of the VM in Azure, use the same IP if you want to use the pre-configured UnboundDNS. Then copy the generated configuration and click on Store and generate next and Apply. Next we'll use that configuration to set up WireGuard on a Windows client. Here you can either use your current machine as a client or create a new Windows VM in Azure. We'll go with this second option for better isolation. We'll deploy the client VM using Terraform file vpn_client_vm_win11.tf. Make sur it is deployed using command terraform apply -auto-approve. Once the VM is ready, connect to it using RDP, download and install WireGuard. Alternatively, you can install WireGuard using the following Winget command: winget install -e --id WireGuard.WireGuard --accept-package-agreements --accept-source-agreements Launch WireGuard application, click on Add Tunnel > Add empty tunnel..., then paste the peer configuration generated from OPNsense and save it. Then click on Activate to start the VPN connection. We should see the data transfer starting. We'll verify the VPN connection by pinging the VM, checking the outbound traffic passes through the Nat Gateway's IPs and also checking the DNS resolution using UnboundDNS configured in OPNsense. ping 10.0.1.4 # this is the trusted IP of OPNsense in Azure # Pinging 10.0.1.4 with 32 bytes of data: # Reply from 10.0.1.4: bytes=32 time=48ms TTL=64 # ... curl ifconfig.me/ip # should display the public IP of the Nat Gateway in Azure # 74.241.132.239 nslookup microsoft.com # should resolve using UnboundDNS configured in OPNsense # Server: UnKnown # Address: 135.225.126.162 # Non-authoritative answer: # Name: microsoft.com # Addresses: 2603:1030:b:3::152 # 13.107.246.53 # 13.107.213.53 # ... The service endpoint ifconfig.me is used to get the public IP address of the client. You can use any other similar service. What's next ? Now that you have OPNsense firewall set up as an NVA in Azure and have successfully established a WireGuard VPN connection, we can explore additional features and configurations such as integrating OPNsense into a Hub and Spoke network topology. That will be covered in the next part of this blog. Special thanks to 'behind the scene' contributors I would like to thank my colleagues Stephan Dechoux thanks to whom I discovered OPNsense and Daniel Mauser who provided a good lab for setting up OPNsense in Azure available here https://github.com/dmauser/opnazure. Disclaimer The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
