SqlException: Login failed for user '{DOMAIN}\{MACHINE NAME}' instead of what's indicated in web.config and application.json #3631
Comments
|
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? |
|
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? |
|
I'll defer to @ajcvickers for the connection string questions. |
|
The question is: |
|
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. |
|
So, publishing question? In that case @AngelosP may know. |
|
@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 |
|
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
}
}
} |
|
There you have it. You are using As mentioned later on that page:
So if you want to use |
|
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. |
|
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. |
|
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: 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. |
|
You appear to have a fundamental misunderstanding of the role IIS has with ASP.NET Core applications. The 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. |
|
What my tests have proven is this: 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. |
|
@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. |
|
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. |
|
Microsoft!!!!!!!!! |
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:
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, TaskCompletionSource
1 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, TaskCompletionSource
1 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.QueryingEnumerable
1.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](IEnumerable
1 source, Boolean& found) at lambda_method(Closure ) at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable1.GetEnumerator()at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable
1 results, QueryContext queryContext, IList1 entityTrackingInfos, IList1 entityAccessors)+MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable
1 source, Boolean& found) at System.Linq.Enumerable.First[TSource](IEnumerable1 source)at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1
1.<CompileQueryCore>b__0(QueryContext qc) at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable1 source)at DNOW_Core.ViewComponents.ParametersTab.Invoke(Parameters pModel, Nullable
1 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
The text was updated successfully, but these errors were encountered: