Monitoring SQL Server: fiddling with sp_whoisactive output

Building up on from basic telegraf inputs we now want some actual SQL data. And this time around it’s going to be not just numeric telemetry, we’d gain some insights into actual queries that get run.

Our choice of Elastic

Up to this point our choice of data back-end has been unjustified. Telegraf is intended to work with time series DBs like Influx. These are heavily optimised based on assumption what kind of data they store and query (i.e. timed series of events). Elastic is however a full text search. So it’s about the time we started to use it for what it was designed for, right?

Finding active sessions with queries and wait reasons

Taking a step back, we must acknowledge work of Adam Machanic and call out sp_whoisactive as probably the most useful tool every DBA should be aware of. This script provides great overview of what the system is doing right now, how long the session have been running for and what they are waiting on. It even allows for dumping data into predefined tables out of the box! Again, if we were looking to utilise SEL server for monitoring we could have easily gone this way and with a bit of SQL Agent scheduling we’d be done in no time.

There’s one slight issue with sp_whoisactive – it’s not meant to work with anything else but SQL table as data store. For our case it is not ideal . It also does not really know anything about SQL Server Query Store which we think is a nice to have.

It’s a known fact we can’t join results of a stored proc with other tables unless we store results in an interim temp table first. So we’d use sp_whoisactive‘s own temp table scripting facility to store results while we loop through query stores on all our databases to grab some extra bits of data we think might be useful:

DECLARE @table_schema NVARCHAR(MAX);
DECLARE @temp_table NVARCHAR(MAX);
DECLARE @result_table NVARCHAR(MAX);
SET NOCOUNT ON;
/*EXEC sp_WhoIsActive @get_outer_command = 1,
					@find_block_leaders = 1,
					@return_schema = 1,
					@format_output = 0,
					@schema = @table_schema OUTPUT;*/
SET @table_schema = N'CREATE TABLE <table_name> ( [session_id] smallint NOT NULL,[sql_text] nvarchar(max) NULL,[sql_command] nvarchar(max) NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] int NULL,[tempdb_allocations] bigint NULL,[tempdb_current] bigint NULL,[blocking_session_id] smallint NULL,[blocked_session_count] smallint NULL,[reads] bigint NULL,[writes] bigint NULL,[physical_reads] bigint NULL,[used_memory] bigint NOT NULL,[status] varchar(30) NOT NULL,[open_tran_count] smallint NULL,[percent_complete] real NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)';
SET @temp_table = REPLACE(@table_schema, '<table_name>', '#whoisactive_monitoring');
SET @result_table = REPLACE(@table_schema, '<table_name>', '#result');
SET @result_table = REPLACE(@result_table, 'NOT NULL)', 'NOT NULL,[duration] bigint NOT NULL DEFAULT 0)'); -- add placeholder for duration column 
SET @result_table = REPLACE(@result_table, 'DEFAULT 0)', 'DEFAULT 0,[query_text_id] bigint NULL)'); -- add placeholder for query_text_id from Query Store
---
DECLARE @db_name NVARCHAR(120);
DECLARE @enhance_sql NVARCHAR(MAX);

SET NOCOUNT ON
-- here we just build a list of databases on the server using known table Users as an anchor. Your databases will have other well known tables
DECLARE @dbs table ([db_name] sysname)
DECLARE @db_query_sql nvarchar(4000)
SET @db_query_sql='select ''?'' as [db_name] from [?].sys.tables t WHERE t.name = ''Users'''; 
INSERT INTO @dbs ([db_name]) EXEC sp_msforeachdb @db_query_sql
SET NOCOUNT OFF

DECLARE db_name_cursor CURSOR FOR
SELECT [db_name]
FROM @dbs
FOR READ ONLY;
OPEN db_name_cursor;
FETCH NEXT FROM db_name_cursor
INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @enhance_sql
		= CONCAT(
					@enhance_sql,
					CHAR(13),
					CHAR(10),
					CAST(N'USE [' + @db_name + ']
						IF EXISTS(SELECT 1 FROM sys.database_query_store_options WHERE actual_state_desc <> ''OFF'')
						UPDATE #result 
						SET query_text_id = qsqt.query_text_id 
						FROM #result wm 
						LEFT JOIN sys.query_store_query_text qsqt ON wm.sql_text COLLATE DATABASE_DEFAULT = qsqt.query_sql_text COLLATE DATABASE_DEFAULT 
						WHERE wm.database_name = ''' + @db_name + ''';' AS NVARCHAR(MAX))
				);
	FETCH NEXT FROM db_name_cursor
	INTO @db_name;
END;
CLOSE db_name_cursor;
DEALLOCATE db_name_cursor;
---
DECLARE @main_script NVARCHAR(MAX);
SET @main_script
	= CAST(CONCAT(
						@temp_table, N';',
						CHAR(13),
						CHAR(10),
						'USE [master]; EXEC [master].[dbo].sp_WhoIsActive @get_outer_command=1, @find_block_leaders=1,@format_output=0, @destination_table=''#whoisactive_monitoring'';',
						CHAR(13),
						CHAR(10),
						@result_table, ';',
						CHAR(13),
						CHAR(10),
						N'INSERT INTO #result SELECT	wm.*, 
													CASE
														WHEN DATEDIFF(hour, wm.start_time, wm.collection_time) > 576 THEN
															DATEDIFF(second, wm.collection_time, wm.start_time)
														ELSE DATEDIFF(ms, wm.start_time, wm.collection_time)
													END AS duration, 
													NULL 
											FROM #whoisactive_monitoring wm;',
						CHAR(13),
						CHAR(10),
						@enhance_sql,
						CHAR(13),
						CHAR(10),
						N'DROP TABLE #whoisactive_monitoring;',
						CHAR(13),
						CHAR(10),
						N'SELECT * FROM #result;DROP TABLE #result;'
					) AS NVARCHAR(MAX));
--PRINT @main_script
EXEC (@main_script);

Now that we’ve got the data

We want Logstash to push the output of this query into Elastic. This way we will always have some idea what’s been going on the DB server. One way to run queries against SQL is JDBC driver – download .jre8.jar, unpack and remember the location.

Defining pipeline

After we’ve got the driver, everything else is easy: the pipeline is literally one input and one output:

input {
	############################################################# Master sp_WhoIsActive ############################################################################
	jdbc {
		id => "master_spWhoIsActive"
		jdbc_driver_library => "path\to\jdbc\lib\mssql-jdbc-7.2.1.jre8.jar"
		jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
		jdbc_connection_string => "jdbc:sqlserver://<your connection string>"
		jdbc_user => nil
		schedule => "* * * * *" ## runs each minute
		statement => "-- literally paste sql code from above inside quotes here"
		add_field => {
						"database" => "master" ## optionally add more fields to distinguish where data comes from
						"instance" => "MY-SQL-Server"
					}
	}
	################################################################################################################################################################	
}
output {
	elasticsearch {
		hosts => "elasticsearch:9200"
		index => "whoisactive-%{+YYYY.MM}"            
	}
}

Restart Logstash and watch Kibana updating with new events!

Monitoring SQL Server: setting up Logstash with telegraf

Having set up basic ELK stack we probably should get on to ingesting some data.

Wait, what about schema?

Theoretically, we could play it very strict and define our expected documetns similar to the way we define SQL tables. But it’s not fun and besides, Elastic comes with sensible defaults. It has done a decent enough job for us to have never needed to correct it. Not bad at all.

Setting up the pipeline

Logstash operates in terms of pipelines. If I were to reduce it to the bare basics, each pipeline allows us to define where data comes from (input), how Logstash should pick events to process it (filter) and finally, where to send it to (output). The last bit is kinda obvious and easy – we want to send processed events to Elastic. And by virtue of running doker we happen to know that it’s hosted at http://elasticsearch:9200. Too easy.

Input

Being quite modular, Logstash operates in terms of plugins. And there are HEAPS to chose from! Apart from obvious bits you get to see in the documentation, one thing that might not be quite obvious – you can have multiple plugins running for one input stream!

Our telemetry agent of choice, telegraf supports sending metrics via Influx Line Protocol, but Logstash does not have a plugin for that – so we will need to craft something bit more complex. Suppose we want basic metrics and sql-stats

input {
	tcp {
		port => 8098
		type => "telegraf"
	} # opens up a TCP listener for events sent by telegraf (which happens to support Influx Line Protocol)
	tcp {
		port => 8099
		type => "sql-stats"
	} # opens up a TCP listener for events coded in Influx Line protocol
}

Filter

This bit is optional, but sometimes is very helpful. And again, we’ve got quite a variety of plugins to suit. Here we’ve got a chance to inspect incoming events and either transform or outright reject some. For example here we parse Influx Line protocol sent by telegraf and do some basic enhancements on data coming from SQL-stats poller:

filter {
	if [type] == "telegraf" {		
		dissect {
			mapping => {
				"message" => "%{measurement}.%{metric};%{tags} %{value} %{ts}"
			}
		}
		kv {
			source => "tags"
			field_split => ";"
		}	
		date {
			match => ["ts", "UNIX"]
		}
		mutate {
			convert => {
				"value" => "float"
			}
			remove_field => ["tags", "message", "ts", "port"]
		}
	}
	if [type] == "sql-stats" {		
		grok {
			match => {
				"message" => "%{WORD:measurement}(\.%{WORD:metric})?;%{GREEDYDATA:tags} (%{BASE10NUM:value}) %{NUMBER:ts}"
			}
		}
		kv {
			source => "tags"
			field_split => ";"
		}	
		date {
			match => ["ts", "UNIX"]
		}
		mutate {
			convert => {
				"value" => "float"
			}
			rename => {
				"counter" => "metric"
				"clerk_type" => "metric"
			}
			remove_field => ["tags", "message", "ts", "port", "host"]
		}
	}
}

All that’s left

To store the events, we invoke output like so:

output {
	if [type] == "telegraf" {		
		elasticsearch {
			hosts => "elasticsearch:9200"
			index => "telegraf-%{+YYYY.MM}"
		}
	}
	if [type] == "sql-stats" {		
		elasticsearch {
			hosts => "elasticsearch:9200"
			index => "sql-stats-%{+YYYY.MM}"
		}
	}
}

Monitoring SQL Server: setting up ELK+G

In 9 cases out of 10 our clients have some sort of database that they want to interface with. And 8 out of those 9 cases the database is going to be SQL Server. Yes, this is us being biased, but you know what?

It does not matter

The important bit is – out clients like to know how the database is doing. Some are happy to pay for commercial APMs, others either have very specific needs or love the challenge to DIY.

We are here to help

One way to get better picture of what’s happening with the DB would be to keep grabbing vitals over time and plotting them on a graph of some sort. Grafana is a fantastic way to achieve that. It supports a whole bunch of backends (including SQL server) and allows insane amount of customisations.

Diversify

It is possible to store SQL telemetry in another SQL database on the same server (you could even set up SQL Agent jobs to do the polling – all nicely packaged). We however thought it might be a good idea to not store all data on the same machine. We’d like to not overstrain the main database in time of pinch and completely decouple analytics from critical business processes.

ELK G stack

One of many ways to approach this is to introduce a (somewhat) free and open source ElasticSearch into the mix. And mightly Logstash for data ingestion. This is where we’d normally go on to Kibana for dashboards and nice UI (and we did end up running it), but the main focus of this exercise will still fall onto Grafana.

Setting it up

Theres no point repeating official documentation for respective products, let’s instead write up a docker-compose file:

version: '3'
services:
    elasticsearch:
        image: docker.elastic.co/elasticsearch/elasticsearch:7.6.1
        environment:
            - node.name=elastic01
            - discovery.type=single-node  
            - bootstrap.memory_lock=true
            - "ES_JAVA_OPTS=-Xms512m -Xmx512m"        
        volumes:
            - ./elastic:/usr/share/elasticsearch/data
    logstash:
        image: docker.elastic.co/logstash/logstash:7.6.1
        volumes: 
            - ./logstash-pipeline:/usr/share/logstash/pipeline/
            - ./logstash-config/usr/share/logstash/config/
        depends_on:
          - elasticsearch
    kibana:
        image: docker.elastic.co/kibana/kibana:7.6.1
        environment:
          - ELASTICSEARCH_HOSTS=http://elasticsearch:9200
        ports:
          - 5601:5601
        depends_on:
          - elasticsearch
    grafana:
        image: grafana/grafana
        ports:
          - 3000:3000
        depends_on:
          - elasticsearch

All that’s left to do is docker-compose up -d and run. Stay tuned for next posts in the series.

Custom Routing in .NET WebAPI

We all need to do weird things sometimes. One assignment we’ve got was to implement an API that would totally obfuscate all parameters in a Base64 encoded string. This will clearly go against stock standard routing and action mapping that ASP.NET WebAPI comes with out of the box. But that got us thinking about ways we can achieve it nonetheless.

By default

Normally, the router will:

  1. get the request URI,
  2. match it against given templates (those "{controller}/{action}" things), and
  3. invoke an {action} on {controller} with whatever parameters happen to be passed along

Then we realise

We’re constrained to full .net framework on the project and fancy .net core middleware are not a thing yet. Luckily for us custom Message Handler is a thing so theoretically we could bootstrap ourselves through that and override IHttpControllerSelector (and potentially IHttpActionSelector).

Setup

Writing code directly in global.asax is an option, but as it calls through to WebApiConfig.Register() by default:

 GlobalConfiguration.Configure(WebApiConfig.Register);

it’s probably a better place for things to do with WebAPI.

App_Start/WebApiConfig.cs

    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services
            // Web API routes
            config.MessageHandlers.Add(new TestHandler()); // if you define a handler here it will kick in for ALL requests coming into your WebAPI (this does not affect MVC pages though)
            config.MapHttpAttributeRoutes();
            config.Services.Replace(typeof(IHttpControllerSelector), new MyControllerSelector(config)); // you likely will want to override some more services to ensure your logic is supported, this is one example

            // your default routes
            config.Routes.MapHttpRoute(name: "DefaultApi", routeTemplate: "api/{controller}/{id}", defaults: new {id = RouteParameter.Optional});

            //a non-overlapping endpoint to distinguish between requests. you can limit your handler to only kick in to this pipeline
            config.Routes.MapHttpRoute(name: "Base64Api", routeTemplate: "apibase64/{query}", defaults: null, constraints: null
                //, handler: new TestHandler() { InnerHandler = new HttpControllerDispatcher(config) } // here's another option to define a handler
            );
        }
    }

and then define our handler:

TestHandler.cs

    public class TestHandler : DelegatingHandler
    {
        protected override async Task<HttpResponseMessage> SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
        {
            //suppose we've got a URL like so: http://localhost:60290/api/VmFsdWVzCg==
            var b64Encoded = request.RequestUri.AbsolutePath.Remove(0, "/apibase64/".Length);
            byte[] data = Convert.FromBase64String(b64Encoded);
            string decodedString = Encoding.UTF8.GetString(data); // this will decode to values
            request.Headers.Add("controllerToCall", decodedString); // let us say this is the controller we want to invoke
            HttpResponseMessage resp = await base.SendAsync(request, cancellationToken);
            return resp;
        }
    }

Depending on what exactly we want handler to do, we might also have to supply a custom ControllerSelector implementation:

WebApiConfig.cs

// add this line in your Register method
config.Services.Replace(typeof(IHttpControllerSelector), new MyControllerSelector(config));

MyControllerSelector.cs

    public class MyControllerSelector : DefaultHttpControllerSelector
    {
        public MyControllerSelector(HttpConfiguration configuration) : base(configuration)
        {
        }

        public override string GetControllerName(HttpRequestMessage request)
        {
            //this is pretty minimal implementation that examines a header set from TestHandler and returns correct value
            if (request.Headers.TryGetValues("controllerToCall", out var candidates))
                return candidates.First();
            else
            {
                return base.GetControllerName(request);
            }
        }
    }

Applying this in real life?

Pretty neat theory. We however couldn’t quite figure out a way to take it to our customers that wouldn’t raise a few questions on whether we’re doing something shady there.

Programmatically submitting Google Forms with AngularJs

Google Forms is a viable way to do business. We’ve seen a few successful compaines that rely on it for day-to-day operations. The flow would normally involve users entering data on the go and someone at the back office analysing the responses with Google Spreadsheet.

Forms are flexible

One huge selling point if that we can design our own forms for all kinds of situations: racing bets, work time/attendance, baby feeding – we’ve seen a few exotic cases. Static form data is not enough, we can opt for Google Apps script.

One thing remains the same though

Look and feel of Google forms and default validations do leave much to be desired. What if there was a way to Swap the form UI out for a custom branded SPA with fancy lookaheads and what not?

There is a way

Surely, it all starts with making a form. We’ll go to google forms are design a new one. Expect to spend some time getting it right for your needs. For purposes of this demo we’ll be submitting a table (we’d cheat a bit and post JSON only though).

We’d also ensure that answers get submitted into a new spreadsheet:

Now we need to grab field names Google generated for a form (it is a simple HTML form after all!). Open up form preview, and go to dev tools console in the new tab

And run the following snippet in the console and note the outputs:

document.querySelectorAll('form').forEach((x) => {console.log(x.action)});
document.querySelectorAll('[name^="entry."]').forEach((x) => {console.log(x.name + '=' + x.closest('[role="listitem"]').querySelector('[role="heading"]').innerText)})

Oh, and one more thing…

Well, we’ve got the fields, but to succesfully submit the it we need to know where to submit to. Apparently, it’s a simple matter of picking up the form Id and crafting a URL: https://docs.google.com/forms/d/<your id here>/formResponse

And we are done…almost

Dissecting Google forms was fun. Now we need to somehow build our own frontend to the form. For our specific use case we wanted to show off how we would go about submitting a table dynamically populated with content. As I’ve got a soft spot for AngularJs, I figured I might as well got for it.

Building a custom form

There’s plenty of resources online on how to build SPAs, so I’d not elaborate much on that. There’s however a couple of considerations that in my opinion will make the form submission process seamless for an SPA experience. First and foremost – we’d like to stay on the same page when forms gets sent away – we’d also like to get notified when form gets submitted so our SPA can take own action. One way to do it is to submit a from into a hidden iframe and use its onLoad event to report back (that’s the method I ended up implementing in the example snippet).

Talk is cheap, show me the code

Working example of this technique can be found here: https://codepen.io/timur_kh/pen/oNXYNdL

Making Swagger to get the authorization token from URL query string

Swagger is extremely useful when developing and debugging Web APIs. Some dev environments however got a bit of security added on top which can get a bit too painful to work around.

Enter API key

It doesn’t need to be tedious! We’ll be looking at overriding Swagger-UI’s index page so we can plug a custom handler into¬†onComplete¬†callback. Solution is extremely simple:

  1. Grab latest index.html from Swashbuckle’s source repo (ideally, get the matching version)
  2. Tweak configObject to add an OnComplete callback handler so it will call preauthorizeApiKey when the UI is ready
  3. Override IndexStream in UserSwaggerUI extension method to serve the custom html

I ended up having the following setup (some bits are omitted for brevity):

wwwroot/swashbuckle.html

<!-- your standard HTML here, nothing special -->
<script>
    // some boilerplate initialisation
    // Begin Swagger UI call region
    configObject.onComplete = () => {

        // get the authorization portion of the query string
        var urlParams = new URLSearchParams(window.location.search);
        if (urlParams.has('authorization')) {
            var apikey = urlParams.get('authorization');

            // this is the important bit, see documentation
            ui.preauthorizeApiKey('api key', apikey );// key name must match the one you defined in AddSecurityDefinition method in Startup.cs
       }
    }
    const ui = SwaggerUIBundle(configObject);
    window.ui = ui        
}
</script>

Startup.cs

    public void ConfigureServices(IServiceCollection services)
    {
        .........
        services.AddSwaggerGen(c => {
            c.SwaggerDoc("v1", new Info { Title = "You api title", Version = "v1" });
            c.AddSecurityDefinition("api key", new ApiKeyScheme() // key name must match the one you supply to preauthorizeApiKey call in JS
            {
                Description = "Authorization query string expects API key",
                In = "query",
                Name = "authorization",
                Type = "apiKey"
            });

            var requirements = new Dictionary<string, IEnumerable<string>> {
                { "api key", new List<string>().AsEnumerable() }
            };
            c.AddSecurityRequirement(requirements);
        });
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    {
        app.UseSwagger();
        app.UseSwaggerUI(c =>
        {
            c.IndexStream = () => File.OpenRead("wwwroot/swashbuckle.html"); // this is the important bit. see documentation https://github.com/domaindrivendev/Swashbuckle.AspNetCore/blob/master/README.md
            c.SwaggerEndpoint("/swagger/v1/swagger.json", "My API V1"); // very standard Swashbuckle init
        });
        app.UseMvc();
    }

After having finished all that, calling the standard swagger URL with ?authorization=1234567890 should automatically authorize the page.

Integration testing aide for MVC core routing

Sometimes unit tests just don’t cut it. This is where integration tests come in. This however brings a whole new set of issues with finding the beast way to isolate the aspects under test and mock everything else away.

Problem statement

Suppose, we’ve got an api and a test that needs to make an http call to our api endpoint, like so:

 [ApiController]
  public class TestController : ControllerBase {

    public IActionResult OkTest() {
      return Ok(true);
    }
  }
.....
public class TestControllerTests {

    private readonly HttpClient _client;

    public TestControllerTests() {
      _client = TestSetup.GetTestClient();
    }

    [Test]
    public async Task OkTest() {
      var path = GetPathHere(nameof(OkTest)); // should return "/api/test/oktest".
      var response = await _client.GetAsync(path);
      response.EnsureSuccessStatusCode();
    }
}

Solution

Knowing that ASP.NET Core comes with such a lightweight package now, and exposes so many extensibility points, one approach we found efficient was to build up the whole Host and query its properties:

private string GetPathHere(string actionName)
    {
        var host = Program.CreateWebHostBuilder(new string[] { }).Build();
        host.Start();
        IActionDescriptorCollectionProvider provider = (host.Services as ServiceProvider).GetService<IActionDescriptorCollectionProvider>();
        return provider.ActionDescriptors.Items.First(i => (i as ControllerActionDescriptor)?.ActionName == actionName).AttributeRouteInfo.Template;
    }

    [TestMethod]
    public void OkTestShouldBeFine()
    {
        var path = GetPathHere(nameof(ValuesController.OkTest)); // "api/test/oktest"
    }

Applicability

This is pretty basic case we’ve been dealing with, and the code makes quite a few assumptions. This approach however seems to hold up pretty well and surely will be our starting point next time round we test MVC actions!

Moq-ing around existing instance

We love unit testing! Seriously, it makes sense if you consider how many times a simple test had saved us from having to revisit that long forgotten project we’ve already moved on from. Not fun and not good for business.

Moq: our tool of choice

To be able to only test the code we want, we need to isolate it. Of course, there’s heaps libraries for that already. Moq is just one of them. It allows of to create objects based on given interfaces and set up the expected behaviour in a way that we can abstract away all code we don’t currently test. Extremely powerful tool

Sometimes you just need a bit more of that

Suppose we’re testing a object that depends on internal state that’s tricky to abstract away. We’d however like to use Mock to replace one operation without changing the others:

public class A
{
    public string Test {get;set;}
    public virtual string ReturnTest() => Test;
}
//and some code below:
void Main()
{
    var config = new A() {
        Test = "TEST"
    } ;

    var mockedConfig = new Mock<A>(); // first we run a stock standard mock
    mockedConfig.CallBase = true; // we will enable CallBase just to point out that it makes no difference  
    var o = mockedConfig.Object;
    Console.WriteLine(o.ReturnTest()); // this will be null because Test has not been initialised from constructor
    mockedConfig.Setup(c => c.ReturnTest()).Returns("mocked"); // of course if you set up your mocks - you will get the value
    Console.WriteLine(o.ReturnTest()); // this will be "mocked" now, no surprises
}

The code above illustrates the problem quite nicely. You’ll know if this is your case when you see it.

General sentiment towards these problems

“It can’t be done, use something else”, they say. Some people on StackOverflow suggest to ditch Moq completely and go for it’s underlying technology Castle DynamicProxy. And it is a valid idea – create a proxy class around yours and intercept calls to the method under test. Easy!

Kinda easy

One advantage or Moq (which is by the way built on top of Castle DynamicProxy) is that it’s not just creating mock objects, but also tracks invocations and allows us to verify those later. Of course, we could opt to write the reuiqred bits ourselves, but why reinvent the wheel and introduce so much code that no one will maintain?

How about we mix and match?

We know that Moq internally leverages Castle DynamicProxy and it actually allows us to generate proxies for instances (they call it Class proxy with target). Therefore the question is – how do we get Moq to make one for us. It seems there’s no such option out of the box, and simply injecting the override didn’t quite go well as there’s not much inversion of control inside the library and most of the types and properties are marked as internal, making inheritance virtually impossible.

Castle Proxy is however much more user firendly and has quite a few methods exposed and available for overriding. So let us define a ProxyGenerator class that would take the method Moq calls and add required functionality to it (just compare CreateClassProxyWithTarget and CreateClassProxy implementations – they are almost identical!)

MyProxyGenerator.cs

class MyProxyGenerator : ProxyGenerator
{
    object _target;

    public MyProxyGenerator(object target) {
        _target = target; // this is the missing piece, we'll have to pass it on to Castle proxy
    }
    // this method is 90% taken from the library source. I only had to tweak two lines (see below)
    public override object CreateClassProxy(Type classToProxy, Type[] additionalInterfacesToProxy, ProxyGenerationOptions options, object[] constructorArguments, params IInterceptor[] interceptors)
    {
        if (classToProxy == null)
        {
            throw new ArgumentNullException("classToProxy");
        }
        if (options == null)
        {
            throw new ArgumentNullException("options");
        }
        if (!classToProxy.GetTypeInfo().IsClass)
        {
            throw new ArgumentException("'classToProxy' must be a class", "classToProxy");
        }
        CheckNotGenericTypeDefinition(classToProxy, "classToProxy");
        CheckNotGenericTypeDefinitions(additionalInterfacesToProxy, "additionalInterfacesToProxy");
        Type proxyType = CreateClassProxyTypeWithTarget(classToProxy, additionalInterfacesToProxy, options); // these really are the two lines that matter
        List<object> list =  BuildArgumentListForClassProxyWithTarget(_target, options, interceptors);       // these really are the two lines that matter
        if (constructorArguments != null && constructorArguments.Length != 0)
        {
            list.AddRange(constructorArguments);
        }
        return CreateClassProxyInstance(proxyType, list, classToProxy, constructorArguments);
    }
}

if all of the above was relativaly straightforward, actually feeding it into Moq is going to be somewhat of a hack. As I mentioned, most of the structures are marked internal so we’ll have to use reflection to get through:

MyMock.cs

public class MyMock<T> : Mock<T>, IDisposable where T : class
{
    void PopulateFactoryReferences()
    {
        // Moq tries ridiculously hard to protect their internal structures - pretty much every class that could be of interest to us is marked internal
        // All below code is basically serving one simple purpose = to swap a `ProxyGenerator` field on the `ProxyFactory.Instance` singleton
        // all types are internal so reflection it is
        // I will invite you to make this a bit cleaner by obtaining the `_generatorFieldInfo` value once and caching it for later
        var moqAssembly = Assembly.Load(nameof(Moq));
        var proxyFactoryType = moqAssembly.GetType("Moq.ProxyFactory");
        var castleProxyFactoryType = moqAssembly.GetType("Moq.CastleProxyFactory");     
        var proxyFactoryInstanceProperty = proxyFactoryType.GetProperty("Instance");
        _generatorFieldInfo = castleProxyFactoryType.GetField("generator", BindingFlags.NonPublic | BindingFlags.Instance);     
        _castleProxyFactoryInstance = proxyFactoryInstanceProperty.GetValue(null);
        _originalProxyFactory = _generatorFieldInfo.GetValue(_castleProxyFactoryInstance);//save default value to restore it later
    }

    public MyMock(T targetInstance) {       
        PopulateFactoryReferences();
        // this is where we do the trick!
        _generatorFieldInfo.SetValue(_castleProxyFactoryInstance, new MyProxyGenerator(targetInstance));
    }

    private FieldInfo _generatorFieldInfo;
    private object _castleProxyFactoryInstance;
    private object _originalProxyFactory;

    public void Dispose()
    {
         // you will notice I opted to implement IDisposable here. 
         // My goal is to ensure I restore the original value on Moq's internal static class property in case you will want to mix up this class with stock standard implementation
         // there are probably other ways to ensure reference is restored reliably, but I'll leave that as another challenge for you to tackle
        _generatorFieldInfo.SetValue(_castleProxyFactoryInstance, _originalProxyFactory);
    }
}

Then, given we’ve got the above working, the actual solution would look like so:

    var config = new A()
    {
        Test = "TEST"
    };
    using (var superMock = new MyMock<A>(config)) // now we can pass instances!
    {
        superMock.CallBase = true; // you still need this, because as far as Moq is oncerned it passes control over to CastleDynamicProxy   
        var o1 = superMock.Object;
        Console.WriteLine(o1.ReturnTest()); // but this should return TEST
    }

.NET Membership audit preparation steps

Not so long ago one of our clients asked us to audit their custom .net CMS users password security. It’s not a secret that 99% of cases when we review .net user accounts and security we would see default SqlMembershipProvider implementation. It has the potential to be extremely secure. Opposite however is also true – you can make it a disaster allowing symmetric encryption, for example. Our case was pretty much default settings across the board, so our passwords were SHA1 hashed with random salts. Good enough, really.
Following up on Troy Hunt’s post we have got ourselves a fresh copy of Hashcat and a few dictionaries to play with.

This is where it all went wrong

To begin with, although EpiServer is running on .net platform, it seems to use slightly relaxed version of membership provider: their salts seem to be shorter than the default. Hashcat however seems to make a great fuss out of this difference:
[cc lang=”bash”]Z:\hashcat>hashcat64.exe -m 141 z:\hashes.txt dictionary.txt
hashcat (v4.2.1) starting…

Hashfile ‘z:\hashes.txt’ on line 1 ($epise…/JapfY=*j+gvb/c0mt28CHJssmwFvQ==): Token length exception
No hashes loaded.

Started: Thu Sep 01 07:48:39 2018
Stopped: Thu Sep 01 07:48:39 2018[/cc]
So apparently Troy’s Episerver-specific routine does not work for any other ASP membership hashes. This seems to be a show stopper.

This is still .net membership though

We know where exactly Sql Memebership Provider is located, we can decompile it and have a peek at how exactly it works. I quoted the relevant function with some comments below:
[cc lang=”c#”]namespace System.Web.Security
{
public class SqlMembershipProvider : MembershipProvider
{
private string EncodePassword(string pass, int passwordFormat, string salt)
{
if (passwordFormat == 0) return pass; // this is plain text passwords. keep away from it
byte[] bytes = Encoding.Unicode.GetBytes(pass); // this is very important, we’ll get back to it later
byte[] numArray1 = Convert.FromBase64String(salt);
byte[] inArray;
if (passwordFormat == 1) // this is where the interesting stuff happens
{
HashAlgorithm hashAlgorithm = this.GetHashAlgorithm();//by default we’d get SHA1 here
if (hashAlgorithm is KeyedHashAlgorithm)
{
// removed this block for brevity as it makes no sense in our case as SHA1 does not require a key.
}
else
{
/* and so we end up with this little block of code that does all the magic.
it’s pretty easy to follow: concatenates salt+password (order matters!) and runs it through SHA1 */

byte[] buffer = new byte[numArray1.Length + bytes.Length];
Buffer.BlockCopy((Array)numArray1, 0, (Array)buffer, 0, numArray1.Length);
Buffer.BlockCopy((Array)bytes, 0, (Array)buffer, numArray1.Length, bytes.Length);
inArray = hashAlgorithm.ComputeHash(buffer);
}
}
else
{
// symmetric encryption. again, keep away
}
return Convert.ToBase64String(inArray);
}
}
}[/cc]
Hashing implementation is not secret either, we can look through Hashcat’s extensive list of supported hashing modes and see if anything pops out.
We’re looking for something that does SHA1 over concatenated salt and password. At first glance we’ve got mode 120 | sha1($salt.$pass), but there’s a catch.
As I noted in the code comment, Encoding.Unicode actually represents UTF16, not UTF8 most of us are used to.
But that’s fine, because Hashcat has just the method for us: 140 | sha1($salt.utf16le($pass)). Fantastic!

A bit of housekeeping

The easiest way to prepare a hashlist in our case was to run a simple LINQPad script against membership database and save results in a file. We just need to keep in mind that salts are represented as hex-encoded strings, so we’ll need to let Hashcat know.
[cc lang=”csharp”]static class StringExtensions {
public static string ToHex(this string input) {
byte[] bytes = Convert.FromBase64String(input);
var result = BitConverter.ToString(bytes).Replace(“-“, “”).ToLower();
return result;
}
}
///

/// to make this work you’d need to set up a database connection in your LINQ Pad first
/// we assume Memberships and Users tables exist in the database we’re connected to. Tweak to your environment
///

void Main()
{
var f = File.CreateText(@”z:\hashes.txt”);
/// Memberships – main ASP.NET Membership table. can be called differently, so watch out for your use case
/// Users – table mapping memberships to user names. Again, can be called differently
foreach (var user in Memberships.Join(Users, m => m.UserId, u => u.UserId, (mm, uu) => new { uu.UserName, mm.Email, mm.Password, mm.PasswordSalt, uu.UserId }).OrderBy(x => x.UserName))
{
//choose either of these lines, to add users or not. We found *hash:salt* format to be less painful to work with in Hashcat
//f.WriteLine($”{user.UserName}:{user.Password.ToHex()}:{user.PasswordSalt.ToHex()}”);
f.WriteLine($”{user.Password.ToHex()}:{user.PasswordSalt.ToHex()}”);
}
f.Close();
}[/cc]