Elmah.Mvc where are the error logs stored? How can I store them in an azure SQL database?

Question

I am using a simple aspnet MVC project that utilises elmah MVC for error logging. Locally (eg running on localhost) elmah works great however I'm not sure I understand where the data is stored? I noticed an "AzureStorageEmulatorDb45" database that has been created, is this for elmah?

How does elmah know what database to connect to?

How can I tell elmah to use my azure SQL database that is hooked up to my MVC project via Entity framework?

Currently when I try to access elmah on the deploy server it simply fails, no doubt as it can't find the database.

I see lots of old posts about the older elmah.axd etc, some about using azure table storage however I want to use an azure sql database.

So two questions. When I run my MVC project on localhost, where are the logs stored and how does elmah know where to find them?

Second question, how do I get my published to azure website to use an azure SQL database as the datastore for elmah mvc?

edit: I followed the instructions below and created the database tables with the following script

SET ansi_nulls ON 
SET quoted_identifier ON 

IF NOT EXISTS (SELECT * 
               FROM   sys.objects 
               WHERE  object_id = Object_id(N'[dbo].    [ELMAH_Error]') 
                      AND type IN ( N'U' )) 
  BEGIN 
      CREATE TABLE [dbo].[elmah_error] 
        ( 
           [errorid]     [UNIQUEIDENTIFIER] NOT NULL, 
           [application] [NVARCHAR](60) NOT NULL, 
           [host]        [NVARCHAR](50) NOT NULL, 
           [type]        [NVARCHAR](100) NOT NULL, 
           [source]      [NVARCHAR](60) NOT NULL, 
           [message]     [NVARCHAR](500) NOT NULL, 
           [user]        [NVARCHAR](50) NOT NULL, 
           [statuscode]  [INT] NOT NULL, 
           [timeutc]     [DATETIME] NOT NULL, 
           [sequence]    [INT] IDENTITY(1, 1) NOT NULL, 
           [allxml]      [NVARCHAR](max) NOT NULL, 
           CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY CLUSTERED ( [errorid] ASC ) 
           WITH 
           ( 
           statistics_norecompute = OFF, ignore_dup_key = OFF) 
        ) 
  END 

IF NOT EXISTS (SELECT * 
               FROM   sys.indexes 
               WHERE  object_id = Object_id(N'[dbo].[ELMAH_Error]') 
                      AND NAME = N'IX_ELMAH_Error_App_Time_Seq') 
  CREATE NONCLUSTERED INDEX [IX_ELMAH_Error_App_Time_Seq] 
    ON [dbo].[ELMAH_Error] ( [application] ASC, [timeutc] DESC, [sequence] DESC 
  ) 
    WITH (statistics_norecompute = OFF, ignore_dup_key = OFF, drop_existing = 
  OFF, 
  online = OFF) 

go 

IF NOT EXISTS (SELECT * 
               FROM   dbo.sysobjects 
               WHERE  id = Object_id(N'[DF_ELMAH_Error_ErrorId]') 
                      AND type = 'D') 
  BEGIN 
      ALTER TABLE [dbo].[elmah_error] 
        ADD CONSTRAINT [DF_ELMAH_Error_ErrorId] DEFAULT (Newid()) FOR [ErrorId] 
  END 

go 

SET ansi_nulls ON 
SET quoted_identifier ON 

IF NOT EXISTS (SELECT * 
               FROM   sys.objects 
               WHERE  object_id = Object_id(N'[dbo].[ELMAH_GetErrorsXml]') 
                      AND type IN ( N'P', N'PC' )) 
  BEGIN 
      EXEC dbo.Sp_executesql 
        @statement = N' CREATE PROCEDURE [dbo].[ELMAH_GetErrorsXml] ( @Application NVARCHAR(60), @PageIndex INT = 0, @PageSize INT = 15, @TotalCount INT OUTPUT ) AS  SET NOCOUNT ON DECLARE @FirstTimeUTC DATETIME DECLARE @FirstSequence INT DECLARE @StartRow INT DECLARE @StartRowIndex INT SELECT      @TotalCount = COUNT(1)  FROM      [ELMAH_Error] WHERE      [Application] = @Application -- Get the ID of the first error for the requested page SET @StartRowIndex = @PageIndex * @PageSize + 1 IF @StartRowIndex <= @TotalCount BEGIN     SET ROWCOUNT @StartRowIndex     SELECT           @FirstTimeUTC = [TimeUtc],         @FirstSequence = [Sequence]     FROM          [ELMAH_Error]     WHERE            [Application] = @Application     ORDER BY          [TimeUtc] DESC,          [Sequence] DESC END ELSE BEGIN     SET @PageSize = 0 END -- Now set the row count to the requested page size and get -- all records below it for the pertaining application. SET ROWCOUNT @PageSize SELECT      errorId     = [ErrorId],      application = [Application],     host        = [Host],      type        = [Type],     source      = [Source],     message     = [Message],     [user]      = [User],     statusCode  = [StatusCode],      time        = CONVERT(VARCHAR(50), [TimeUtc], 126) + ''Z'' FROM      [ELMAH_Error] error WHERE     [Application] = @Application AND     [TimeUtc] <= @FirstTimeUTC AND      [Sequence] <= @FirstSequence ORDER BY     [TimeUtc] DESC,      [Sequence] DESC FOR     XML AUTO ' 
  END 

go 

SET ansi_nulls ON 
SET quoted_identifier ON 

IF NOT EXISTS (SELECT * 
               FROM   sys.objects 
               WHERE  object_id = Object_id(N'[dbo].[ELMAH_GetErrorXml]') 
                      AND type IN ( N'P', N'PC' )) 
  BEGIN 
      EXEC dbo.Sp_executesql 
        @statement = N' CREATE PROCEDURE [dbo].[ELMAH_GetErrorXml] ( @Application NVARCHAR(60), @ErrorId UNIQUEIDENTIFIER ) AS SET NOCOUNT ON SELECT      [AllXml] FROM      [ELMAH_Error] WHERE     [ErrorId] = @ErrorId AND     [Application] = @Application ' 
  END 

go 

SET ansi_nulls ON 
SET quoted_identifier ON 

IF NOT EXISTS (SELECT * 
               FROM   sys.objects 
               WHERE  object_id = Object_id(N'[dbo].    [ELMAH_LogError]') 
                      AND type IN ( N'P', N'PC' )) 
  BEGIN 
      EXEC dbo.Sp_executesql 
        @statement = N' CREATE PROCEDURE [dbo].[ELMAH_LogError] ( @ErrorId UNIQUEIDENTIFIER, @Application NVARCHAR(60), @Host NVARCHAR(30), @Type NVARCHAR(100), @Source NVARCHAR(60), @Message NVARCHAR(500), @User NVARCHAR(50), @AllXml NVARCHAR(MAX), @StatusCode INT, @TimeUtc DATETIME ) AS SET NOCOUNT ON INSERT INTO     [ELMAH_Error]     (         [ErrorId],         [Application],         [Host],         [Type],         [Source],         [Message],         [User],         [AllXml],         [StatusCode],         [TimeUtc]     ) VALUES     (         @ErrorId,         @Application,         @Host,         @Type,         @Source,         @Message,         @User,         @AllXml,         @StatusCode,         @TimeUtc     ) ' 
  END 

go

I then added the following to web.config:

<configuration>
  <configSections>
    </sectionGroup>
      <sectionGroup name="elmah">
      <section name="security" requirePermission="false" type="Elmah.SecuritySectionHandler, Elmah" />
      <section name="errorLog" requirePermission="false" type="Elmah.ErrorLogSectionHandler, Elmah" />
      <section name="errorMail" requirePermission="false" type="Elmah.ErrorMailSectionHandler, Elmah" />
      <section name="errorFilter" requirePermission="false" type="Elmah.ErrorFilterSectionHandler, Elmah" />
    </sectionGroup>
  </configSections>

  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=tcp:MYWEBSITELOL.database.windows.net,1433;Initial Catalog=LOL;Persist Security Info=True;User ID=trololol;Password=trolololol;Connect Timeout=30;Encrypt=True;TrustServerCertificate=False" providerName="System.Data.SqlClient"/>
  </connectionStrings>

  <elmah>
    <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="DefaultConnection" applicationName="Lol" />
  </elmah>

  <appSettings>
    <add key="elmah.mvc.disableHandler" value="false" />
    <add key="elmah.mvc.disableHandleErrorFilter" value="false" />
    <add key="elmah.mvc.requiresAuthentication" value="true" />
    <add key="elmah.mvc.IgnoreDefaultRoute" value="false" />
    <add key="elmah.mvc.allowedRoles" value="admin" />
    <add key="elmah.mvc.allowedUsers" value="waewaewa@lolol.com />
    <add key="elmah.mvc.route" value="ServerLog" />
    <add key="elmah.mvc.UserAuthCaseSensitive" value="true" />
  </appSettings>

  <system.web>
  <httpModules>
      <add name="ErrorLog" type="Elmah.ErrorLogModule, Elmah" />
      <add name="ErrorMail" type="Elmah.ErrorMailModule, Elmah" />
      <add name="ErrorFilter" type="Elmah.ErrorFilterModule, Elmah" />
    </httpModules><httpHandlers>
      <add verb="POST,GET,HEAD" path="elmah.axd" type="Elmah.ErrorLogPageFactory, Elmah" />
    </httpHandlers>
  </system.web>

Now when I run the web server on localhost (using the azure DB) I can log in and view elmah with the following text indicating success.

This log is provided by the Microsoft SQL Server Error Log.

I then publish the website and when I attempt to access the elmah log I get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

SQLExpress database file auto-creation error:

The connection string specifies a local Sql Server Express instance using a database location within the application's App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

As the localhost version is using the same DB I can actually use elmah-localhost to view the elmah-azure related error which is handy. Elmah reports the following error of which its timestamp matches when I attempted to view Elmah from the azure website:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

ERRORSHELPABOUT System.Data.SqlClient.SqlException A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Is there something I need to add into the web.RELEASE.config etc to allow elmah to work correctly when running in a release/azure environment?

The errors seem conflicting, one says timeout, the other says autocreation error (what is it trying to autocreate and why?)

By the way, AzureStorageEmulatorDb45 is the database that Azure Storage Emulator creates for itself. It comes with the Azure SDK and allows you to test things like Blob and Table Storage locally without a Storage account. It's not related to ELMAH (unless you configure it to store logs in Table Storage in the emulator). — juunas, Jan 10 '17 at 08:34
The first error means you haven't updated the connection string to point to Azure SQL, it's still pointing at an express DB. The second one probably means you haven't added a firewall exception for your IP address in Azure SQL. — juunas, Jan 16 '17 at 08:49
I've done both of those things though. I know the firewall works as. I can connect to the database to perform user log in etc. Express dB? Why does my localhost copy connect then? — rollsch, Jan 16 '17 at 20:41

score 1 · Answer 1 · answered Jan 10 '17 at 07:15

The logs are stored where you configure them. If you look in the web.config, you should find something like this:

<elmah>
  <errorLog type="Elmah.XmlFileErrorLog, Elmah" logPath="~/App_Data" />
</elmah>

In this case logs are stored on the file system as XML files. In the App_Data folder.

If you want to use SQL Database for storage then you can follow the instructions here: http://www.andyfrench.info/2014/07/configuring-elmah-to-use-sql-server.html

Basically you will have to add the connection string, create the ELMAH tables manually with a pre-made script, and then modify the configuration like this:

<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>

The ELMAH page also tells you which one you are using currently on the bottom of the page, e.g.:

This log is provided by the Microsoft Azure Storage Error Log.

Thank you, I'll have a go at that and report back. – rollsch Jan 10 '17 at 07:17 — rollsch, Jan 10 '17 at 07:17
Updated main post with how far I got. – rollsch Jan 11 '17 at 03:22 — rollsch, Jan 11 '17 at 03:22

Elmah.Mvc where are the error logs stored? How can I store them in an azure SQL database?

1 Answers1