Analyzing Table Storage bindings locally in Azure Functions

In one of my current projects I heavily rely on Table Storage bindings used in many of my functions. In fact I have several dozen API functions, which are the base of the whole system. Because codebase grows each day, I needed a tool, which will allow me easily validate whether I query Table Storage correctly - that means I follow some basic principles like:

  • using both PartitionKey and RowKey in a query
  • if RowKey is unavailable - using PartitionKey so I won't have to read the whole table
  • using $top whenever possible so I won't load the whole partition
  • using query projection - leveraging $select for selecting only a subset of columns in a row

In fact I knew two ways of doing that:

  1. Checking logs of Storage Emulator, what I described in this blog post. The disadvantage of that solution is that is logs nearly each and every request so it is hard to find a particular one you're interested in
  2. Using SQL Server Profiler to check what kind of queries are materialized 

As you can see above logs from Storage Emulator are quite detailed, yet painful to work with

I needed a tool, which would combine features of both solutions.

Reading SQL Server Profiler

The idea was to somehow read what SQL Server Profiler outputs when queries are sent to Storage Emulator. Fortunately it is really simple using following classes:

  • SqlConnectionInfo
  • TraceServer

Both are easily accessible in SQL Server directory:

  • C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
  • C:\Program Files (x86)\Microsoft SQL Server\140\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfoExtended.dll

There is however a small gotcha. Since SQL Server Profiler is a 32-bit application, you cannot use above classes in 64-bit one. Additionally those assemblies are SQL Server version sensitive - locally I have an instance of SQL Server 2017, if you have other version, you'd have to change the path to point to the correct one.

Does it work?

After some initial testing it seems it works. Let's assume you have following code:

public static Task<HttpResponseMessage> DeviceList(
	[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "device")] HttpRequestMessage req,
	[Table(TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<DeviceEntity> devices,
	[Table(Firmware.Firmware.TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<Firmware.Firmware.FirmwareEntity> firmware,
	[Table(CounterType.CounterType.TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<CounterType.CounterType.CounterTypeEntity> counterTypes,
	[Table(Location.Location.TableName, Connection = Constants.TableStorageConnectionName)] IQueryable<Location.Location.LocationEntity> locations,
	[Identity] UserIdentity identity,
	TraceWriter log)
	if (identity.IsAuthenticated() == false) return identity.CreateUnauthorizedResponse();

	var firmwareVersionsCached = firmware.Take(50).ToList();
	var counterTypesCached = counterTypes.Take(50).ToList();
	var locationsCached = locations.Take(50).ToList();

	var query = devices.Where(_ => _.PartitionKey != "device").Take(100).ToList().Select(_ => new
		Id = _.RowKey,
		Name = _.Name,
		SerialNumber = _.SerialNumber,
		Firmware = firmwareVersionsCached.First(f => f.RowKey == _.FirmwareId.ToString()).Version,
		CounterType = counterTypesCached.First(ct => ct.RowKey == _.CounterTypeId.ToString()).Name,
		Location = locationsCached.First(l => l.RowKey == _.LocationId.ToString()).Name

	var response = req.CreateResponse(HttpStatusCode.OK,

	return Task.FromResult(response);


Here you can find a part of diagnostic logs from executing above function:

You can find the whole project on GitHub: After some time spent with this tools I found planty of issues in my code like:

  • not using PartitionKey
  • reading the same table twice
  • materializing all rows from a table when I needed only a subset

I guess I will even more flaws in the next days. 

Secure Azure Functions locally using a custom provider

Developing Azure Functions could be cumbersome if you want to use App Service Authentication feature. While it works flawlessly when a function is deployed, it brings many unfair challenges when working locally(mostly because of a need to create an artificial mock of identity provider and injecting it somehow). I decided to give it a try and modify Azure Functions CLI a little bit, to it has that feature implemented already. Surprisingly it was easier that I thought.

How does Azure Functions CLI work?

When you're working with Functions locally, when you hit F5, you'll see a local runtime starting and ready to handle a request(or trigger a function). In fact VS starts it using Azure Functions CLI and invoking following command:

> func start

Local instance of runtime with a boilerplate function enabled

When this local host is started, it handles multiple features like:

  • loading settings from local.settings.json
  • starting a runtime
  • providing an endpoint to handle HTTP requests
  • ...and many more

In general you're able to pass many different parameters so you can start a host listening on a specific port, with HTTPS enabled and CORS configured. You can do it like so:

func start --useHttps=true --cors=*

I got an idea to extend parameters so you can do following:

func start --security=true

So each function invocation has to be challenged against a custom security provider. How did I achieve this?

Handling a parameter

The very first thing I had to do was to handle a security parameter in CLI. To do so I modified StartHostAction class which parses inline arguments when CLI is started. Added line looks like this:

	.WithDescription("Enable securing HTTP functions using available providers")
	.Callback(s => Security = s);

This was super easy. Let's do something more difficult - use this parameter so some logic is performed.

Securing each request

Because CLI is built against new ASP.NET Core pipeline, you have to provide a custom middleware so each request has to pass through it. There's a Startup class, which is the foundation of the whole host. There you can inject your functionality as I did:

app.Use(async (context, next) =>
	if (_security)
		var provider = new SecurityProvider();
		var authenticationResult = provider.IsAuthenticated(context.Request);

		if (authenticationResult == false)
			context.Response.StatusCode = 401;

	await next.Invoke();

Now if security is enabled, each request will be validated using some SecurityProvider,which is a custom class implementing following interface:

internal interface ISecurityProvider
	bool IsAuthenticated(HttpRequest req);

How does it work?

Now when I start CLI using following command:

func start --security=true

I'm getting the following result:

When I disable security:

Of course the error in the second response comes from the boilerplate function because I didn't pass name parameter.

Now since IsAuthenticated() has a full request passed, you can implement whichever flow you want, starting from a very basic one like me:

public class SecurityProvider : ISecurityProvider
	public bool IsAuthenticated(HttpRequest req)
		if (req.Headers.ContainsKey("Authorization"))
			return true;

		return false;

What's next?

In the next episode I'll try to enhance this solution a little bit, so ISecurityProvider will be loaded from a Function App you'll be developing locally(so it gains much flexibility). For now you can following this issue on GitHub, where I proposed solution I described above.