Sunday, 13 July 2014

Configuring ELMAH to use SQL Server

ELMAH (Error Logging Modules and Handlers for ASP.NET) is a great little project but the documentation could be improved. One thing I like to do is to get ELMAH logging to a SQL Server database pretty much as soon as it’s integrated into a project but the documentation is a bit scant. Here’s how you do it, in this case for an MVC application.

At the time of writing the core ELMAH library is in version 1.2.1 and the Elmah.MVC package is in version 2.1.1.

Install ELMAH

In your MVC application Manage NuGet Packages… and search for ‘elmah’. Install the Elmah.MVC package which will also install the ELMAH core library as a dependency.

image

Create the ELMAH database

Hop over to the ELMAH site and locate the Microsoft SQL Server DDL script on the downloads page. Download the DDL script to your machine.

SNAGHTMLdc8946

Open SQL Server Management Studio and create a database. I called mine Elmah. Open the DDL script and run it against the new database. This will create the tables and stored procedures used by ELMAH.

SNAGHTMLe49de1

Create a SQL login which will be used by ELMAH to connect to the Elmah database from your MVC application. I use SQL Server authentication. You’ll probably want to cancel password policy enforcement etc. if you do the same.

SNAGHTMLe842dc

Create a new User in the Elmah database using the Elmah login. Give the user data reader and data writer roles.

SNAGHTMLea5188 

You will also need to grant execute permissions to the ELMAH stored procedures:

USE Elmah; 
GRANT EXECUTE ON OBJECT::ELMAH_GetErrorsXml
    TO Elmah;
GO 

GRANT EXECUTE ON OBJECT::ELMAH_GetErrorXml
    TO Elmah;
GO 

GRANT EXECUTE ON OBJECT::ELMAH_LogError
    TO Elmah;
GO

Modify the ELMAH configuration in the MVC application

When you added ELMAH to your MVC application it will have created an elmah section in your Web.config. You will need to update Web.config to include a connection string for the Elmah database and then update the elmah section to use that connection string.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <!-- other configuration removed for clarity -->
  <connectionStrings>
    <add name="elmah" connectionString="server=localhost;database=;uid=Elmah;password=password;" />
  </connectionStrings>
  <!-- other configuration removed for clarity -->
  <elmah>
    <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="elmah" applicationName="YourApplicationName"/>
  </elmah>
</configuration>

That should be all there is to get started. Now you’ll probably want to secure ELMAH.