SqlException: Login failed for user '{DOMAIN}\{MACHINE NAME}' instead of what's indicated in web.config and application.json #3631
Description
Using .Net Core 2.1
My project currently has no authentication on it. When I debug it, it runs fine - connects to the database with the intended login. However when I web deploy to IIS, I get this error (printed in full below).
I've tried various things to fix this, based on articles I've read:
- turned on windows auth and turned off anonymous auth in both my project, and on IIS web site.
- Included windows auth sections in web config and application.json.
- Made sure that the correct web.config application.json was being pushed
- included IISOptions Services calls in my Startup.cs
None of this is working. I wish deploying a simple application to IIS were as easy as simply launching it in IIS Express via DEBUG. Why is this so difficult?
Error-----------------------------------------------------------------------
An unhandled exception occurred while processing the request.
SqlException: Login failed for user '{DOMAIN}{MACHINE NAME}'.
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, object providerInfo, string newPassword, SecureString newSecurePassword, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling)
• Stack
• Query
• Cookies
• Headers
• SqlException: Login failed for user '{DOMAIN}{MACHINE NAME}'.
o System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, object providerInfo, string newPassword, SecureString newSecurePassword, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling)
o System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
o System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
o System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
o System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
o System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, uint waitForMultipleObjectsTimeout, bool allowCreate, bool onlyOneCheckConnection, DbConnectionOptions userOptions, out DbConnectionInternal connection)
o System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource retry, DbConnectionOptions userOptions, out DbConnectionInternal connection)
o System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, out DbConnectionInternal connection)
o System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource retry, DbConnectionOptions userOptions)
o System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource retry)
o System.Data.SqlClient.SqlConnection.Open()
o Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(bool errorsExpected)
o Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(bool errorsExpected)
o Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.BufferlessMoveNext(DbContext _, bool buffer)
o Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute<TState, TResult>(TState state, Func<DbContext, TState, TResult> operation, Func<DbContext, TState, ExecutionResult> verifySucceeded)
o Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable+Enumerator.MoveNext()
o System.Linq.Enumerable.TryGetFirst(IEnumerable source, out bool found)
o lambda_method(Closure )
o Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ResultEnumerable.GetEnumerator()
o Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities<TOut, TIn>(IEnumerable results, QueryContext queryContext, IList entityTrackingInfos, IList<Func<TIn, object>> entityAccessors)+MoveNext()
o Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider+ExceptionInterceptor+EnumeratorExceptionInterceptor.MoveNext()
o System.Linq.Enumerable.TryGetFirst(IEnumerable source, out bool found)
o System.Linq.Enumerable.First(IEnumerable source)
o Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler+<>c__DisplayClass15_1.b__0(QueryContext qc)
o System.Linq.Queryable.FirstOrDefault(IQueryable source)
o DNOW_Core.ViewComponents.ParametersTab.Invoke(Parameters pModel, Nullable orderid) in ParametersTab.cs
o lambda_method(Closure , object , object[] )
o Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeSyncCore(ObjectMethodExecutor executor, ViewComponentContext context)
o Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeAsync(ViewComponentContext context)
o Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentHelper.InvokeCoreAsync(ViewComponentDescriptor descriptor, object arguments)
o AspNetCore.Pages_Index.b__39_1() in Index.cshtml
o Microsoft.AspNetCore.Razor.Runtime.TagHelpers.TagHelperExecutionContext.SetOutputContentAsync()
o AspNetCore.Pages_Index.ExecuteAsync()
o Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context)
o Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, bool invokeViewStarts)
o Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context)
o Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, string contentType, Nullable statusCode)
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultAsync(IActionResult result)
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResultFilterAsync<TFilter, TFilterAsync>()
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResultExecutedContext context)
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.ResultNext<TFilter, TFilterAsync>(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultFilters()
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
o Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
o Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
o Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
o Microsoft.AspNetCore.Builder.Extensions.UsePathBaseMiddleware.Invoke(HttpContext context)
o Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
•
Show raw exception details
System.Data.SqlClient.SqlException (0x80131904): Login failed for user 'DNOW\USA-2UA8261SMX$'.
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable1.Enumerator.MoveNext()
at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable1 source, Boolean& found) at lambda_method(Closure ) at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable1.GetEnumerator()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable1 results, QueryContext queryContext, IList1 entityTrackingInfos, IList1 entityAccessors)+MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()
at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable1 source, Boolean& found) at System.Linq.Enumerable.First[TSource](IEnumerable1 source)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_11.<CompileQueryCore>b__0(QueryContext qc) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source)
at DNOW_Core.ViewComponents.ParametersTab.Invoke(Parameters pModel, Nullable1 orderid) in C:\Users\Dunnj\Documents\DNOW\App\DNOW_Core\DNOW_Core\ViewComponents\ParametersTab.cs:line 27 at lambda_method(Closure , Object , Object[] ) at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeSyncCore(ObjectMethodExecutor executor, ViewComponentContext context) at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentInvoker.InvokeAsync(ViewComponentContext context) at Microsoft.AspNetCore.Mvc.ViewComponents.DefaultViewComponentHelper.InvokeCoreAsync(ViewComponentDescriptor descriptor, Object arguments) at AspNetCore.Pages_Index.<ExecuteAsync>b__39_1() in C:\Users\Dunnj\Documents\DNOW\App\DNOW_Core\DNOW_Core\Pages\Index.cshtml:line 208 at Microsoft.AspNetCore.Razor.Runtime.TagHelpers.TagHelperExecutionContext.SetOutputContentAsync() at AspNetCore.Pages_Index.ExecuteAsync() at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageCoreAsync(IRazorPage page, ViewContext context) at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderPageAsync(IRazorPage page, ViewContext context, Boolean invokeViewStarts) at Microsoft.AspNetCore.Mvc.Razor.RazorView.RenderAsync(ViewContext context) at Microsoft.AspNetCore.Mvc.ViewFeatures.ViewExecutor.ExecuteAsync(ViewContext viewContext, String contentType, Nullable1 statusCode)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultAsync(IActionResult result)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResultFilterAsyncTFilter,TFilterAsync
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResultExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeResultFilters()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeNextResourceFilter()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Rethrow(ResourceExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeFilterPipelineAsync()
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.InvokeAsync()
at Microsoft.AspNetCore.Builder.RouterMiddleware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.StaticFiles.StaticFileMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Builder.Extensions.UsePathBaseMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
ClientConnectionId:20931594-081c-4f09-8e54-013fa6c0b414
Error Number:18456,State:1,Class:14
Metadata
Metadata
Assignees
Labels
Type
Projects
Milestone
Relationships
Development
Participants
Activity
Tratcher commentedon Oct 15, 2018
ASP.NET Core does not impersonate the request's Windows Auth user. Your project works locally because everything is running as you, but in IIS the processes are all run as the app pool identity.
Do you really need to access the database with individual user accounts? Or could you assign an identity to the app pool that has permissions?
JDGHHou commentedon Oct 15, 2018
That ended up being the answer. Thanks!
I remain confused about why .Net goes to such great lengths to have you specify connection strings in various config files if it's going to completely ignore it all in favor of the App Pool Identity.
Are files like application.json and web.config only for local debugging?
Why are there context menu commands in Visual Studio 2017 about publishing these files to a web server?
Tratcher commentedon Oct 15, 2018
I'll defer to @ajcvickers for the connection string questions.
ajcvickers commentedon Oct 15, 2018
@Tratcher @JDGHHou I doubt I will be able to help given the context, but what is the question, specifically?
JDGHHou commentedon Oct 15, 2018
The question is:
ASP .Net applications have us do all this extensive configuration using application.json and web.config, yet IIS completely ignores what I've entered in these configs for Connection Strings. I just wasted so much time messing with those files when the answer was that I just needed to set the App Pool Identity in IIS. Is it the case that these configuration files are intended only for local debugging. Since logins in connection string entries in these files are completely ignored, why do you think so many forums and articles keep telling people to enter that stuff in with the hopes of having it communicated to IIS?
ajcvickers commentedon Oct 15, 2018
@JDGHHou Sorry, I don't know anything about how IIS and connection strings interact. Maybe someone on the IIS team knows? @Tratcher do you have a contact?
Tratcher commentedon Oct 15, 2018
I doubt the issue is IIS specific. The main issue I see above is that they're supplying credentials in the connection string but those aren't getting used in the published app.
ajcvickers commentedon Oct 15, 2018
So, publishing question? In that case @AngelosP may know.
poke commentedon Oct 16, 2018
@JDGHHou Can you actually show the configured connection string (blank out the password of course)? I would assume that the connection string is just not correct and it happens to work in development because your user account happens to have user access to the database.
In general though, there is nothing with IIS that would overwrite the configuration done through the
appsettings.json(unless IIS sets some particular environment variables which I wouldn’t expect it to do at the moment). Aweb.configisn’t used for configuration with ASP.NET Core though.JDGHHou commentedon Oct 17, 2018
Below is my appsettings.json file (with sensitive data replaced). IIS is ignoring the Connection String login creds, but Visual Studio was using it properly when I debugged in IIS Express.
{ "Logging": { "IncludeScopes": false, "LogLevel": { "Default": "Warning" } }, "ConnectionStrings": { "DB_Context": "Server={SERVERNAME};Database={DATABASE};Integrated Security=SSPI;User Id={DOMAIN}\{USERNAME};Password={USERNAME};MultipleActiveResultSets=true" }, "iisSettings": { "windowsAuthentication": true, "anonymousAuthentication": false, "iisExpress": { "applicationUrl": "http://localhost/appname", "sslPort": 0 } } }poke commentedon Oct 17, 2018
There you have it. You are using
Integrated Security=SSPIwhich essentially means use Windows Authentication with the current user. So when you are debugging from Visual Studio, your own user account is being used and that probably has access to the database.As mentioned later on that page:
So if you want to use
User IDandPassword, you will have to remove theIntegrated Security=SSPIfrom your connection string.JDGHHou commentedon Oct 17, 2018
I get that, but here's why this is confusing:
My access to the database is through Windows Authentication. I do not have an identical SQL Authentication login for that DOMAIN\Username on that database server. So, I want IIS to read appsettings.json and login via Windows (not SQL Auth and not the App Pool Identity) using that login information to access the database. So, when I take out Integrated Security=SSPI, it tries to use the credentials as though they were SQL Auth credentials. If I put Integrated Security=SSPI back in, it seems to use the App Pool Identity instead of what's written in the appsettings file.
poke commentedon Oct 17, 2018
IIS does not care about the appsettings.json. So what you have in there just is of no interest. IIS will use the app pool user to run the ASP.NET Core application, and that’s the user that is being used when you are doing Windows authentication inside of your ASP.NET Core application.
JDGHHou commentedon Oct 17, 2018
Right, that's kinda what I was guessing at higher up in the thread: IIS doesn't care what is written in appsettings.json with regard to logging into a database using a network account.
My statement:
"Is it the case that these configuration files are intended only for local debugging. Since logins in connection string entries in these files are completely ignored, why do you think so many forums and articles keep telling people to enter that stuff in with the hopes of having it communicated to IIS?"
As I now understand it, the answer is - there is no reason to write anything in Connection Strings section of appsettings.json with the hope of communicating a Windows credential to IIS to access a database with. It is purely meant for passing strictly a SQL Auth login. If the intent is to use a Windows login, the only way is to set the Application Pool Identity to that Windows Login and forget about the credentials string in appsettings.json.
poke commentedon Oct 17, 2018
You appear to have a fundamental misunderstanding of the role IIS has with ASP.NET Core applications. The
appsettings.jsonis not ignored or only used for debugging purposes. It is the primary source of configuration for ASP.NET Core applications, and the application will pick up the configuration from that file (and a few other sources) by default.IIS however has little to no impact on how the ASP.NET Core application runs. Unlike with the previous ASP.NET (MVC), IIS does not run your application. It merely forwards the requests to the ASP.NET Core application which is running as its own process and with its own web server (Kestrel). So IIS is not involved with the application configuration and simply does not care what is going on there. It’s just a reverse proxy for ASP.NET Core, and also happens to manage the web application’s process using its app pool. That’s why the configured app pool identity in IIS is the user that will own the ASP.NET Core process.
But IIS is not involved at all with communicating with a database, or any of the other configuration your application may or may not have. That’s the responsibility of the web application.
JDGHHou commentedon Oct 17, 2018
What my tests have proven is this:
IIS, running/hosting my web application, communicates to ASP .Net Core the Application Pool identity registered not in the web application's files, but in IIS's configuration as the Windows Authenticated Identity which my web application will use to log into the database via the Connection String specified in my web application's appsettings.json file, a file which is copied to the file directory where IIS looks to find my web application's files (and which is different from the location where I develop and debug locally).
IIS, in communicating with ASP.Net Core on behalf of my web application, applies part of what is specified in appsettings.json (the database server name and database name) and part of what is registered in IIS's App Pool Identity (the Windows credentials registered to that App Pool managed by IIS).
If, however, the credentials in the web application's appsettings.json file relate to a SQL Authentication credential, IIS allows the web application to supply that credential instead of using the one configured with IIS's Application Pool.
dcarr42 commentedon Oct 18, 2018
@poke is absolutely correct, of course configuration does not play a part here..please understand this...It is not who you are logged in as but under who the website is running. This can vary depending on whether you are running locally under IISExpress or using Kestrel by itself or with a reverse proxy server.
Eilon commentedon Oct 18, 2018
Right, what the folks here are explaining is exactly right. IIS doesn't know anything about appsettings.json - it doesn't look at it, read it, process it, or anything else. IIS only uses the IIS configuration to run ASP.NET COre as whatever identity it was told to, and then has ASP.NET Core process the request. Then within ASP.NET Core, the appsettings.json file is used (typically, but not necessarily), and whatever configuration is in that file gets used (e.g. for EF DbContext connection strings). If the settings in appsettings.json conflict in some way with the IIS configuration, that needs to be resolved within the application.
George-01 commentedon Sep 10, 2019
Microsoft!!!!!!!!!
All i had to do after several hours was:
go to iis -> application pools -> find your application pool used in application -> click it and then click 'Advance Settings' in Actions panel. Find 'Identity' property and change it to localsystem.
thanks to this thread -> https://www.codeproject.com/Questions/558566/LoginplusfailedplusforplususerplusIISplusAPPPOOL-5
Peuw!!!!!