ELMAH fails to login to DB on production but not test server

Question

First off, here's a few of the potential duplicates that didn't help:
Can't access /elmah on production server with Elmah MVC?
ELMAH doesn't insert error logs to SQL DB on production
ELMAH works on local machine, but not on production
Elmah, convert to .Net4 vs2010, run on server 2008, does not work
Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'
The error "Login failed for user 'NT AUTHORITY\IUSR'" in ASP.NET and SQL Server 2008

My ASP.NET MVC application uses one SQL Server database and ELMAH uses another. On the test server, the IIS app pool account is used to connect to SQL Server, as intended, and everything works fine. However, moving to production with the same settings has Entity Framework continuing to use the IIS APPPOOL\Product Windows account to connect to SQL Server, but ELMAH starts attempting to use the DOMAIN\MACHINENAME$ account (which I didn't create/set up -- I don't know enough about Windows domains to know where that comes from).

Attempting to go to the ELMAH page that lists errors (i.e. http://localhost/elmah) returns HTTP 500 and the event log shows this classic error message:

Cannot open database "Errors" requested by the login. The login failed. Login failed for user 'DOMAIN\MACHINENAME$'

Out of desperation, I've tried just giving DOMAIN\MACHINENAME$ database permissions (used SSMS to map the login to the Errors database and selected db_datawriter and db_datareader), including the stored procedures suggested here, but even that doesn't work. That process is what I did to set it up on the test server.

Production is Windows Server 2012 R2 running IIS 8.5. Test is Windows 10 running IIS 10. Both are running SQL Server 2016 Express v13, for now.

Web.config with everything included that's even slightly related to this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <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>
    <clear />
    <!-- Entity Framework connection, works fine on both servers -->
    <add name="DbContext" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Data.mdf;Initial Catalog=Data;Integrated Security=True" providerName="System.Data.SqlClient" />
    <!-- ELMAH connection, only works on test server -->
    <add name="ElmahLog" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Errors.mdf;Initial Catalog=Errors;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <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="true" />
    <add key="elmah.mvc.allowedRoles" value="Developer" />
    <add key="elmah.mvc.route" value="dev/elmah" />
    <add key="elmah.mvc.UserAuthCaseSensitive" value="true" />
  </appSettings>
  <system.web>
    <globalization culture="en-US" />
    <authentication mode="Windows" />
    <compilation debug="false" targetFramework="4.5.2">
      <assemblies>
        <add assembly="System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
      </assemblies>
    </compilation>
    <httpRuntime targetFramework="4.5.2" />
    <httpModules>
      <add name="ErrorLog" type="Elmah.ErrorLogModule, Elmah" />
      <add name="ErrorMail" type="Elmah.ErrorMailModule, Elmah" />
      <add name="ErrorFilter" type="Elmah.ErrorFilterModule, Elmah" />
    </httpModules>
    <customErrors mode="Off" />
  </system.web>
  <system.webServer>
    <modules>
      <add name="ErrorLog" type="Elmah.ErrorLogModule, Elmah" preCondition="managedHandler" />
      <add name="ErrorMail" type="Elmah.ErrorMailModule, Elmah" preCondition="managedHandler" />
      <add name="ErrorFilter" type="Elmah.ErrorFilterModule, Elmah" preCondition="managedHandler" />
    </modules>
    <validation validateIntegratedModeConfiguration="false" />
  </system.webServer>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
  <elmah>
    <security allowRemoteAccess="true" />
    <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="ElmahLog" />
  </elmah>
  <system.codedom>
    <compilers>
      <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.8.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:6 /nowarn:1659;1699;1701" />
      <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.8.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:14 /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+" />
    </compilers>
  </system.codedom>
</configuration>

Why is ELMAH using a different account and why can't I give that account permissions?

I'm curious to hear what happens if you right-click the server (in management studio), click properties, go to Security and select "SQL Server and Windows Authentication mode". Then restart SQL Server express and try again. I know it sounds weird, but in my experience, this could help in producing a better error message. Don't know why. — ThomasArdal, Dec 07 '18 at 08:28
@ThomasArdal Same error when trying to access ELMAH. Though I do get a *slightly* different error during SQL Server start (which was presumably there before changing authentication modes, I just haven't restarted in a long time): `Login failed for user 'DOMAIN\MACHINENAME$'. Reason: Failed to open the explicitly specified database 'Errors'. [CLIENT: ]` — Sinjai, Dec 07 '18 at 20:23
I know that you don't want the domain user accessing the db at all. But if you go into management studio, expand the Security -> Logins nodes. Do you have your domain user as part of the logins? If not, maybe try to add it and asign a User Mapping to your ELMAH database. Select dbo in User and Default Schema. Would be interesting to hear if your domain user can access the database or not. — ThomasArdal, Dec 08 '18 at 08:15
@ThomasArdal As in `DOMAIN\MACHINENAME$`? Like I attempted to say in the OP, there is a login and it *should* have access to the database. But it apparently can't login. — Sinjai, Dec 08 '18 at 08:17
Yeah, I have a user named `MYMACHINE\MYUSERNAME`. Do you have that beneath Logins (not on the database on globally on the server)? — ThomasArdal, Dec 08 '18 at 08:23
@ThomasArdal I have both `DOMAIN\MACHINE$` and `MACHINE\USER` under logins, where `MACHINE\USER` is the account I use to log in to the server. — Sinjai, Dec 09 '18 at 21:59
Does `DOMAIN\MACHINENAME$` have a user mapping to `dbo` on your ELMAH database? — ThomasArdal, Dec 11 '18 at 09:36
@ThomasArdal Under user mapping for `DOMAIN\MACHINE$`, user is `DOMAIN\MACHINE$` and default schema is `dbo`. Changing user to dbo gives me an error: `User, group, or role 'dbo' already exists in the current database.` — Sinjai, Dec 11 '18 at 17:44
If you can create a test project that re-produce the issue, I can test it on my machine to see if I get the same error. — ThomasArdal, Dec 11 '18 at 17:53
@ThomasArdal After failing to reproduce the error with a test project, I decided to delete the database and create a new one. And sure enough, it connects with no problem, using the application pool identity, as intended. Unfortunately, I still don't have the slightest idea what was wrong -- but at least it works now. — Sinjai, Dec 11 '18 at 22:41
Ooh. Happy to hear that it is working, but would have been nice to find the cause yes :) — ThomasArdal, Dec 12 '18 at 06:10

score 0 · Answer 1 · answered Dec 17 '18 at 22:13

0

It sounds like your machines SID changed between development and deployment. Did you join the machine to a domain?

Or, you moved the Client from a non-domain computer to a domain joined computer?

A reinstall to correct the problem implies the SIDs either local or domain were not correct.

answered Dec 17 '18 at 22:13

Strom

4,146
8
32

I'm not sure I understand the question, could you try to rephrase? Development and deployment are two different machines. – Sinjai Dec 18 '18 at 18:14

ELMAH fails to login to DB on production but not test server

1 Answers1

Linked