Category Archives: Identity

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.


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&gt;.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

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.

Advertisement

Stupid Azure Trick #9 – Embrace SSL During Development when authenticating with Azure Active Directory

If you are developing applications that authenticate users or handle sensitive personal or business data, you should be using SSL for your whole site. That’s the most secure approach. Plain old HTTP is not gonna cut it, and flipping between HTTP and HTTPS exposes undesirable vulnerabilities.

So let’s suppose you are building a Windows Azure Web Site using ASP.NET MVC and you want to take advantage of Azure Active Directory for authentication. Maybe you create an Azure Active Directory account, add some users, now you are ready to use it for authentication within your application.

Using SSL during development will help you smoke out issues – one might be cross-protocol warnings – while also keeping your credentials secure on the wire (if you develop locally using AAD, logins still travel over public internet). It’s just good hygiene. But there is a nuisance factor because, by default, using SSL locally (in the latest tool stack for ASP.NET development) uses the SSL certificate that ships with IIS Express, and that’s not trusted by your web browser, so you get a warning every time. This tip today will show you how to easily fix that. (To skip all the context and get right to the main point, search for the word ‘core’ below.)

Certificate Store on Windows

The Certificate Storage on Windows (desktop and server) is a trusted location for storing digital certificates for all kinds of reasons, including those used by Web Browsers to trust whether or not to trust an SSL connection to a web site, or whether to give a warning.

Only certificates that live in a special location in your local Windows Certificate Store – or digital certificates signed by those certificates (or in a signing chain) – are allowed to be used without a warning. This special location is called Trusted Root Certification Authorities. If your certificate is not in there, or itself was not signed by a certificate in there, and so on, then the browsers will show the users a stern warning.

You can view the certificates in your Trusted Root Certification Authorities store by running certmgr.msc from a command program. Here’s what it looks like on my machine.

image

We’ll come back to this tool later.

Create a Simple ASP.NET MVC app that authenticates with Azure Active Directory

You can skip this section if you already know how to do this. This is a quick walkthrough showing how to use Visual Studio 2013 to create simple ASP.NET MVC application and connect it to an existing Azure Active Directory. (You can easily create an AAD either from the Windows Azure portal, or outside it. You can also substitute an Office 365 directory since that automatically uses AAD.)

File | New Project, choose as below:

image

Click OK.

image

Click Change Authentication.

image

Slect Organizational Accounts in the radio button on the left, and type in your AAD domain (could also be Office 365). Choose Single Sign On for Access Level for simple authentication, or choose Single Sign On, Read directory data if you also plan to use AAD for authorization (such as RBAC). Click OK.

After authenticating as a Global Administrator user on the specified domain, you will be back to your New ASP.NET Project dialog, though with a new value for Authentication setting.

image

Click OK. Now your project will be generated. If you display the Project Properties window for your project, as shown below, notice the configuration options for SSL. You also have both an SSL endpoint and a regular HTTP endpoint.

image

Simply hit F5 now to debug. The default configuration here will bring up the SSL endpoint. Let’s explore what happens below.

Web Browser, Please Protect Me!

Once you’ve started to debug, you won’t see your app directly, but rather you’ll see something like the following:

image

This is because of this entry in Web.config:

<system.web>

<authorization>
<deny users="?" />
</authorization>


</system.web>

This says, in a nutshell, only allow authenticated users access to my site, and if they are not authenticated already, send them to the configured AAD login screen.

(It is possible to selectively disable this for certain pages or areas, but we won’t cover that here. But you can see an example in you web.config that uses the location element.)

Also note that the login screen is using SSL. After logging in, we stay on SSL, and get the following warning:

image

Click the Continue to this websites (not recommended). link and you get your application page, but without the trusty padlock:

image

What does this mean – SSL without the padlock? It means your data is cryptographically secure on the wire (safe from snooping, because the channel is encrypted), but you are sending your data to a web site whose identity has not been independently verified.

The experience with Chrome and Firefox is similar:

Warning from Chrome – “The site’s security certificate is not trusted!”

Hit F5 from Visual Studio if Chrome is your default browser (or type the appropriate URL into Chrome while debugging from Visual Studio).

image

Warning from Firefox – “This Connection is Untrusted”

Hit F5 from Visual Studio if Firefox is your default browser (or type the appropriate URL into Firefox while debugging from Visual Studio).

image

Why Getting Rid of SSL Warnings is OKAYish Here

Before we get rid of the warning, let’s cover a couple of basics.

We get rid of the SSL warnings by telling Windows to trust the IIS Express certificate. In general, this is a Bad Idea, but in this narrow case it ought to be fine. Here’s the logic:

  1. Your IIS Express certificate is unique to your machine
  2. It only honors ports starting at 44300 (up to, I think, 44399)
  3. You can undo this
  4. You are a developer and Know What You Are Doing
  5. You would NEVER do this on an internet-facing production machine

We’ll use Internet Explorer to make the fix, but realize that since all browsers are using the same underlying Certificate Store on Windows, you only need to do this ONCE (in IE in our case) and the others will also automatically trust the certificate for SSL.

Getting Rid of SSL Warnings for *all* Browsers, Courtesy of IE

Here’s the core of the tip in this article, and it starts at the point after you’ve hit F5 in Visual Studio, and assumes IE is configured as the default browser (and, if not, simple load the page into IE before proceeding).

image

Simple click on Certificate error and you’ll see this popop:

image

Click on View certificates.

image

Click on Install Certificate.

image

Click Next (Current User is desired location).

image

Click Place all certificates in the following store and click browse:

image

Choose Trusted Root Certification Authorities. Click OK.

image

Click Next.

image

Click Finish.

There will be a Security Warning:

image

Now read it. If you are cool with it, click Yes.

Now if you run certmgr.msc again, you can see the new entry:

image

Undoing the Fix

To remove it again, simply select it, as show above, and hit the DELETE key. You’ll get a couple of warnings:

image

Click Yes.

Back to normal.

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

Examine User Identity and Claims from Visual Studio Debugger

When debugging a claims-aware application (you ARE using claims, aren’t you?), sometimes it is useful to answer the question “which user is logged in (if any) and (if so) which claims are associated with said user.”

Assuming you are using Visual Studio and .NET 4.5, the simple solution is to add the following to one of your Visual Studio Watch windows:

System.Threading.Thread.CurrentPrincipal

[If you happen to be debugging ASP.NET code, you could save a little typing and instead add User to your Watch window. User should have the same value as the CurrentPrincipal in the context of ASP.NET. For ASP.NET WebForms User is a property of the Page class (Page.User), while for ASP.NET MVC User is a property of both the Controller class (Controller.User) and the HttpContext class (HttpContext.User).]

Drill in, and you will see something like the following:

image

If you then right-click on the Results View entry under Claims (the one that says “Expanding the Results View will enumerate the IEnumerable”) and, uhh, click on that entry to expand the results view, you will see all the claims.

In my case, some claims were flowing through Windows Azure Access Control Service (ACS), and these list the ACS namespace as the Issuer. Other claims were added at runtime by my code using a ClaimsAuthenticationManager module, and these list LOCAL AUTHORITY as the Issuer.

image

Alternatively, you can add the more complex direct expression to your Watch window – using the cast to coerce the right values:

((System.Security.Claims.ClaimsPrincipal)(System.Threading.Thread.CurrentPrincipal))

This will also do the job – with a little less drilling.