Category Archives: Azure

Related to Microsoft’s Windows Azure platform

Talk: Running #Azure Securely – Are all these security features for me?

Today I had the opportunity to speak at VT Code Camp #11 in Burlington, VT. As part of my series of talks on Running Azure Securely, my talk today was around defense in depth and was called Running Azure Securely – which of these Azure security features are for me?. The session was interactive, engaging a half-dozen folks in the audience in a discussion of how to defend various workloads using the (fictitious) page of photos app as a foil.

Some Resources Mentioned

Azure is in 54 regions around the world
Which services support Managed Identities? (works with Azure Active Directory – AAD)
Azure Sentinel (SIEM) https://docs.microsoft.com/en-us/azure/sentinel/overview
Azure Playbooks (automated actions from Azure Security Center): https://docs.microsoft.com/en-us/azure/security-center/security-center-playbooks
Not mentioned, but might be of interest (more structured than the interactive format used today): https://blog.codingoutloud.com/2019/09/14/talk-running-sql-azure-securely-sql-saturday-877-14-sep-2019/

The deck

VermontCodeCamp-BillWilder-2019-Sep-28.AllTheseSecurityFeatures

Talk description

Azure offers thousands of security features. Some of them are easy to use and others are complicated. Some are free to use and some look really, really expensive. Which ones should I be using for my applications?

In this talk we’ll look at some ways to reason about which security controls you might want to apply and why. We’ll consider groups of Azure security features through a pragmatic lens of security best practices and defense-in-depth/breadth, but tempered by the reality that “more security” is not always the answer, but rather “what is the right security” for a situation. By the end of this talk you should have a better idea of the security feature set offered by Azure, why/when they might or might not be needed, and have discussed some ways to reason about how which are relevant you by helping you think about how to assess appropriately for multiple situations.

Do you have specific questions about the applicability of Azure security features already? Feel free to tweet your questions at Bill in advance to @codingoutloud and he’ll try to work answers to any questions into the talk in advance.

Action Photo

(if I can find one)

Who logged into my #Azure SQL Database?

3 Replies

Ever try to figure out how to track who logged into your Azure SQL database? You checked all the usual ways you might handle that with a SQL Server database, but one-by-one find out they just don’t work. Here’s one way to do it.

To track who is logging into your Azure SQL database, enable auditing (here’s how to do that) with audit entries directed to an Azure storage blob. There are two ways to do this: at the database server level and at the individual database level. Either is fine, but for the example that follows, auditing is assumed to be at the db server level. The example query can be adjusted to work with auditing at the database level, but one of the two auditing options is definitely required to be on!

Run this query to find out all the principals (users) who have logged in so far today into your Azure SQL database.

	— Turn on Audit Logging to Blob for your Azure SQL Database. Then you can query who has logged in.
	— The example below assumes DB Server-level audit logging. Details will vary slightly for Database-level audit logging.
	— The example below shows who logged in so far today.
	— Change "-0" to "-1" to look at yesterday (from a UTC perspective, not your local timezone).
	— Change "-0" to "-100" to look at 100 days ago.
	SELECT FORMATMESSAGE('%s (%s)', CAST(DATEADD(day, –0, CONVERT(date, SYSUTCDATETIME())) as varchar),
	DATENAME(WEEKDAY, DATEADD(day, –0, SYSUTCDATETIME()))),
	server_principal_name,
	COUNT(server_principal_name) as 'Logins'
	FROM sys.fn_get_audit_file(FORMATMESSAGE('https://<MYBLOB>.blob.core.windows.net/sqldbauditlogs/<MYDBSERVER>/<MYDB>/SqlDbAuditing_ServerAudit/%s/',
	CAST(DATEADD(day, –0, CONVERT(date, SYSUTCDATETIME())) as varchar)),default, default)
	WHERE (event_time >= CAST(CONVERT(date, SYSUTCDATETIME()) as datetime2))AND (action_id = 'DBAS')
	GROUP BY server_principal_name
	HAVING COUNT(server_principal_name) > 0

view raw
check-azure-sql-db-logins.sql
hosted with ❤ by GitHub

The output is something like the following, assuming if I’ve logged in 12 times so far today with my AAD account (bill@example.com) and 1 time with a database-specific credential (myadmin):

09-Nov-2019 (Saturday) codingoutloud@example.com 12

09-Nov-2019 (Saturday) myadmin 1

The query might take a while time to run, depending on how much data you are traversing. In one of my test environments, it takes nearly 20 minutes. I am sure it is sensitive the amount of data you are logging, database activity, and maybe settings on your blob (not sure if premium storage is supported, but I’m not using it and didn’t test with it).

Note: There are other ways to accomplish this, but every way I know of requires use of Azure SQL auditing. In this post we pushed them to blobs, but other destinations are available. For example, you could send to Event Hubs for a more on-the-fly tracker.

Talk: Azure Security Toolbox at Boston Azure

Leave a reply

Last night’s Boston Azure meeting featured Marija Strazdas from @AlertLogic who spoke about the Shared Security Model for security in the cloud. I also showed in more detail some of the tools that Azure provides to help customers with their side of the responsibility model including some with Azure SQL DB, Storage, KeyVault, and Azure Security Center. Here are the slides I presented (though I didn’t get through most of them).

EDIT: Here is the deck presented by Marija Strazdas from @AlertLogic who spoke about the Shared Security Model for security in the cloud:

Alert Logic Azure Security Presentation

marija

You can find @bostonazure on twitter, and feel free to join us on slack.

Talk: When NOT to use PowerShell with Azure

1 Reply

Today at PowerShell in Action I spoke twice about not going TOO far in your PowerShell when managing Azure resources.

The point of the talks wasn’t really that using PowerShell is bad/wrong, more that it might not be the best tool for the job in certain scenarios. In particular, an ARM template is a powerful modeling tool in support of a “no pets” policy, which is interesting to consider as your cloud environments grow more complex while also wanting to make environments easier to manage. Another benefit stems from keeping the ARM template itself as an “infrastructure as code” artifact that can be used to document – and, more to the point, as executable documentation – for stamping out environments predictably. And still another feature: the ARM runtime handles a lot of the complex parts that could come by trying to script one resource at a time via imperative PowerShell scripts – for example, error recovery and retries.

The deck is on the event shared github repo. There are lots of otherPowerShelly resources on that repo that you may find worth checking out.

(Added 03-June) For those of you who attended my Advanced session, when I attempted to clean up at the end using Remove-AzureRmResourceGroupDeployment, my PowerShell command had an error in it. Here is the correct version. In the first screen shot I show how to ascertain the correct value for the first the parameter using Get-AzureRmResourceGroupDeployment.

Get-AzureRmResourceGroupDeployment

Remove-AzureRmResourceGroupDeployment `
-Name Microsoft.Template -ResourceGroupName k1

Once that PowerShell command executed, all 8 resources associated with that deployment were removed (deleted, and billing stopped).

Ta da!

Hope to see all you locals at Boston Azure (@bostonazure) in the future for more Azurey action.

Talk: Spy vs. Spy (SQL Server vs. SQL #Azure SQL DB) at @NorthBTownAzure

Last night I spoke at the North Boston Azure cloud group, where the topic was Azure SQL Database – not just a cloud version of SQL Server. So much of the talk was interactive — a really high-energy discussion — big thanks to Jason for hosting me and to the group for a great conversation.

Here’s the deck I used:

comparing-azure-sql-database-and-sql-server-North-Boston-Azure-28-Mar-2017-bill-wilder

And here is some of the CLI 2.0 action, including the last line to clean up a resource group:


az login
az account set --subscription MySubscriptionName
./show-resourcegroup-contents.sh nbazure
z group delete -n nbazure --no-wait

Talk: IoT has a dark side; #Azure DNS can help; Happy 7th Bday @BostonAzure

Leave a reply

Boston Azure is 7 years old!

At today’s October Boston Azure meeting, we celebrated turning 7 years old, and had two talks. I presented one talk related to the Dyn DDoS outage from Friday Oct 21, which illustrated the dark side of IoT since it was used to power a huge DDoS attack on Dyn, taking down Netflix, Github, Spotify, and lots of other sites as collateral damage. In my talk I explained some of the background and how we addressed it with Azure DNS.

Here’s the deck for the “IoT has a dark side” talk:

boston-azure-iot-dyn-azure-dns-thu-27-oct-2016

Jim O’Neil provides some resource relating to his talk. There are listed AS A COMMENT on the meeting page (scroll below the meeting description page to comments – Meetup.com does not allow changing a meeting description after the meeting begins, so amendments tend to be as comments).

And here’s a photo of the evening – taken by @jimoneil (Jim), showing (L->R) Mark (@MHEisenberg), I’m in the middle (@codingoutloud), and @nazik_huq (Nazik Huq) – all who have helped @bostonazure (Boston Azure) thrive through the years. And not to forget about some others who were not present – like @georgebabey (George Babey), @haleyjason (Jason Haley), Michael Stiefel, and @squdgy (Maura Wilder) – who have all deserve credit for the success of the group through the years.

cv0rnylxyaetg_k

And finally, here’s Jim, the event’s featured speaker and the photographer and tweeter of the above photo. I think this photo was taken by @nazik_huq (Nazik Huq).

highres_455490152

Talk: Azure SQL Database – Not just a cloud version of SQL Server

1 Reply

Earlier today I presented at the Get to know SQL Server 2016 / Business Intelligence / Azure event at 1cc. My talk was “Azure SQL Database: Not just a cloud version of SQL Server” to an inquisitive Cambridge audience.

My deck is here: Azure SQL Database – not just a cloud version of SQL Server – NESQL -June-24-2016 – Bill Wilder – posted

The PowerShell script I demo’d is included in the gist referenced below.

	$subName = 'my azure subscription name here'
	$rgName = 'nesql-june24-demo2'
	$region = "East US"
	$serverName = 'billwilder911'
	$myIp = "107.92.120.203" #### CHANGES A LOT!

	Add-AzureRmAccount # then log in interactively, including with 2FA
	Select-AzureRmSubscription –SubscriptionName $subName

	# How many regions am I allowed to deploy SQL to?
	$sqlRegionCount = (Get-AzureRmResourceProvider –ListAvailable \| Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations.Length
	Write-Host "$sqlRegionCount regions available for creating Azure SQL Databases:"
	(Get-AzureRmResourceProvider –ListAvailable \| Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations

	# create a RESOURCE GROUP for this demo
	New-AzureRmResourceGroup –Name $rgName –Location $region
	(Get-AzureRmResourceGroup –Name $rgName).Location

	# create a DATABASE SERVER
	New-AzureRmSqlServer –ResourceGroupName $rgName –ServerName $serverName –Location $region –ServerVersion "12.0"

	#create a DATABASE FIREWALL RULE
	New-AzureRmSqlServerFirewallRule `
	–ResourceGroupName $rgName `
	–ServerName $serverName –FirewallRuleName "one ip to rule them all" `
	–StartIpAddress $myIp –EndIpAddress $myIp
	# -AllowAllAzureIPs


	# create a DATABASE ************************************
	Get-AzureRmSqlCapability –LocationName $region

	$dbName = 'BillDb'
	$dbEdition = "Basic"
	$dbLevel = "S0" # https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/
	$dbLevel = "Basic" # https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/

	$db = New-AzureRmSqlDatabase –ResourceGroupName $rgname `
	–ServerName $serverName `
	–DatabaseName $dbname `
	–Edition $dbEdition `
	–RequestedServiceObjectiveName $dbLevel

	$db

	Write-Host "Firewall?"
	Write-Host "${dbName}.database.secure.windows.net"

	# comment out to NOT delete right away
	Write-Host "Remove-AzureRmResourceGroup -Name $rgname -Force"
	$subName = 'Bill Wilder – Finomial MSDN(Converted to EA)'
	$rgName = 'nesql-june24-demo2'
	$region = "East US"
	$serverName = 'billserver981'
	$myIp = "107.92.120.203" #### CHANGES A LOT!

	Add-AzureRmAccount # then log in interactively, including with 2FA
	Select-AzureRmSubscription –SubscriptionName $subName

	# How many regions am I allowed to deploy SQL to?
	$sqlRegionCount = (Get-AzureRmResourceProvider –ListAvailable \| Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations.Length
	Write-Host "$sqlRegionCount regions available for creating Azure SQL Databases:"
	(Get-AzureRmResourceProvider –ListAvailable \| Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations

	# create a RESOURCE GROUP for this demo
	New-AzureRmResourceGroup –Name $rgName –Location $region
	(Get-AzureRmResourceGroup –Name $rgName).Location

	# create a DATABASE SERVER
	New-AzureRmSqlServer –ResourceGroupName $rgName –ServerName $serverName –Location $region –ServerVersion "12.0"

	#create a DATABASE FIREWALL RULE
	New-AzureRmSqlServerFirewallRule `
	–ResourceGroupName $rgName `
	–ServerName $serverName –FirewallRuleName "one ip to rule them all" `
	–StartIpAddress $myIp –EndIpAddress $myIp
	# -AllowAllAzureIPs


	# create a DATABASE ************************************
	Get-AzureRmSqlCapability –LocationName $region

	$dbName = 'BillDb'
	$dbEdition = "Basic"
	$dbLevel = "S0" # https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/
	$dbLevel = "Basic" # https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/

	$db = New-AzureRmSqlDatabase –ResourceGroupName $rgname `
	–ServerName $serverName `
	–DatabaseName $dbname `
	–Edition $dbEdition `
	–RequestedServiceObjectiveName $dbLevel

	$db

	Write-Host "${dbName}.database.secure.windows.net"


	Write-Host "Run this later to delete this resource group containing database server and database"
	Write-Host "Remove-AzureRmResourceGroup -Name $rgname -Force"
	#Remove-AzureRmResourceGroup -Name $rgname -Force

view raw
make-azure-sql-database.ps1
hosted with ❤ by GitHub

Talk: Comparing Azure SQL Database and SQL Server

Leave a reply

I spoke tonight at the New England SQL Server Group to a large audience of feisty SQL experts. Thanks for all the tough questions. And thanks to George for backing me up on questions I couldn’t answer.

Abstract:

The Microsoft Azure cloud platform offers a service called Azure SQL Database. How does this compare to SQL Server, the venerable boxed product so many DBAs have come to know and love? In this talk from long-time Azure MVP Bill Wilder, we will look at similarities and differences. We will also briefly talk about complementary data storage sources that may also interest DBAs. At the end of the talk, you will understand the big picture of database options in the cloud, how Azure SQL Database is positioned and stacks up relative to SQL, and leave with an appreciation of key scenarios where it might make sense to use one over the other.

One thing I didn’t get to work was the PowerShell cleanup at the end. I ran it later and here is the code (the last line is the one I botched during the demo):

Add-AzureRmAccount # then log in interactively, including with 2FA 🙂
Select-AzureRmSubscription -SubscriptionName MyAzureSubsriptionNameHere
Get-AzureRmSubscription # optional
Find-AzureRmResource -ResourceGroupNameContains sqltest # optional
Remove-AzureRmResourceGroup -Name sqltest -Force