Category Archives: DevOps

Who logged into my #Azure SQL Database?

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.

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.

Advertisements

Talk: When NOT to use PowerShell with Azure

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

Remove-AzureRmResourceGroupDeployment.png

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: Hidden Azure – Boston Azure – Boston location – 2015 Global Azure Bootcamp

Today I was pleased to present the talk “Hidden Azure” as part of the 2015 Global Azure Bootcamp, the 3rd such annual and globally-coordinated event involving many sites (175?) spread all over the world!

There were tens-of-thousands of VM core hours in use (even 22,000 at one point) throughout the day…

I presented live in Boston to an engaged audience, but was not able to see who was listening in the outside world! But I do know there was at least one person – which made it worthwhile. 🙂

Here’s the deck I used:

Find me, Boston Azure, and Global Azure Bootcamp on the twitters:

Talk: Meet Windows Azure, Your Next Data Center

Today I spoke at VirtG Boston’s annual Deep Dive Day. The title of my talk, Meet Windows Azure, Your Next Data Center, is probably descriptive enough to get the gist of it.

My slide deck follows.

2014-03-12 – Meet Windows Azure, Your Next Data Center – VirtG Virtualization Deep Dive Day

Stupid Azure Trick #8 – Take control of Management Certificate names

Examine your Windows Azure MANAGEMENT CERTIFICATES in the Windows Azure Portal (under “SETTINGS” in the left nav, then “MANAGEMENT CERTIFICATES” in the top nav). These are the certificates that control which people or which machines can programmatically manipulate your Windows Azure resources through the Service Management API.

Every time you initiate a Publish Profile file download (whether through the portal, with PowerShell, or through the CLI), a new certificate is generated and added to your list of management certificates. You cannot control these names – they are generated.

Upon examination, you may find that some certificates – like #1 shown below – have generated names. And also look at the several certificates immediately below #1 – they have similar names – also generated. These are hard to distinguish from each other.

SNAGHTML2f43f75e

But this is okay some of the time – it is convenient to let tools create these certificates for you since it saves time. It may be perfectly adequate on low security accounts – perhaps a developer’s individual dev-test account from MSDN, or an account only used to give demos with. But for a team account running production, you probably don’t want it to have 17 untraceable, indistinguishable certificates hanging off it.

Now look at the names for #2 and 3 shown above. They are custom names.

Managing Your Management Certificates Starts with Meaningful Names

While we can debate whether the custom names shown above are truly meaningful (this is a demo account), you can probably appreciate that seeing a certificate name like “BUILD SERVER” or “Person/Machine” (e.g., “Maura/DRAGNIPUR”) or “Foobar Contractor Agency” might be more useful than “Azdem123EIEIO” to a human.

Controlling Certificate Names

The Windows Azure Management Portal has some heuristics for deciding what to display for a certificate’s name, but the first one it considers is the Common Name, and will display its value if present. So the short answer: take control of the Common Name.

Here we show creating a Service Management certificate manually in two steps – first the PEM (for use locally) and second deriving a CER (for uploading to the portal).

openssl req -x509 -nodes -days 365 -newkey rsa:1024 -keyout mycert.pem -out mycert.pem -subj "/CN=This Name Shows in the Portal"
openssl x509 -inform pem -in mycert.pem -outform der -out mycert.cer

Note the use of -subj "/CN=This Name Shows in the Portal" when generating a PEM in the first command. The specified text will appear as the description for this certificate within the Windows Azure Portal. OpenSSL is available on Linux and Mac systems by default. For Windows, you can install it directly, or – if you happen to use GitHub for Windows – it gets installed along with it.

For a pure Windows solution, use makecert to create a Management Certificate for Windows Azure.

Considerations

Once you assume responsibility for naming your own certificates, you are simultaneously also taking on generating them, deploying the certificates containing the private keys to the machines from which your Windows Azure resources will be managed using the Service Management API, and uploading the CER public keys to the portal. To make some parts of this easier – especially if you are distributing to a team – consider building your own publish settings file. Also, realize the same certificate can be used by more than one client, and the can also be applied to more than one subscription on Windows Azure; its a many-to-many relationship that’s allowed.

Resources

Create and Upload a Management Certificate for Windows Azure

X.509 Certificates

Build your own Publish Settings File

[This is part of a series of posts on #StupidAzureTricks, explained here.]

Talk: 7 Things Software Testing Professionals Should Know About the Public Cloud

Today I partnered with XBOSoft to present the webinar: 7 Things Software Testing Professionals Should Know About the Public Cloud.image

This is one of a series of webinars hosted by XBOSoft (@xbosoft) as part of their 2014 Webinar series from the XBOSoft Software Quality Knowledge Center. They are a global company (San Francisco, Amsterdam, Oslo & Beijing) focused on Software Quality Improvement. Check them out here: www.xbosoft.com.

The free Webinar was held on March 6, 2014.

At a high level, these are the 7 things discussed:

  1. Key cloud terminology & concepts
  2. SaaS tools are plentiful
  3. PaaS for environments
  4. IaaS for environments
  5. Understanding costs
  6. Public cloud platforms are global
  7. Considerations for cloud-native applications

The PowerPoint deck I used to walk through the topics is here:

7 Things Software Testing Pros Should Know About Public Cloud

Talk: Make the Cloud Less Cloudy: A Perspective for Software Development Teams: It’s all about Productivity

Today I gave a talk at Better Software Conference East 2013 about how the cloud impacts your development team. The talk was called “Making the Cloud Less Cloudy: A Perspective for Software Development Teams” and was heavy with short demos on making your dev team more productive, then a slightly longer look into how you can evolve your application to fully go cloud-native with some interesting patterns. All the demos showed off the Windows Azure Cloud Platform, though, as I explained, most of the techniques are general and can be used with other platforms such as Amazon Web Services (AWS).

Tweet stream: twitter.com/#bsceadc

http://bsceast.techwell.com/sme-profiles/bill-wilder

http://bsceast.techwell.com/sessions/better-software-conference-east-2013/make-cloud-less-cloudy-perspective-software-developmen

The deck doesn’t mention this explicitly, but all of my demos (and my slide presentation) were done from the cloud! Yes, I was in the room, but my laptop was remotely connected to a Windows Azure Virtual Machine running in Microsoft’s East US Windows Azure data center. It worked flawlessly. 🙂

Here’s the PowerPoint Deck: