February 20, 2019 at 2:03 pm
I have a .net web app on development machine. It is WIndows 10 with .Net Framework 4.7, SQL Server 2012 and IIS as a web server.
This was working fine on Windows7 with .Net Framework 4.0. But had to be updated to support TLS 1.2.
There is no code change at all.
But when a web client tries to connect it gives an error
System.Data.sqlclient.sqlexception: Login Failed for user Domain\User
I do not see that message on any of my three machines, all other users do. I am dbowner.
I checked NT SYSTEM\NETWORKSERVICE account and it is properly set up with access ot the DB in question.
Not sure what else I have to fix . Any help would be greatly appreciated. Thanks.
February 21, 2019 at 6:30 pm
each client that used to connect to the server, if it has a problem, needs to install the updated SQL Native Client.
it sounds like your machine , specifically needs to get the latest client.
if the developer to your left has the same problem, it is his local tools, not the server itself.
https://www.microsoft.com/en-us/download/details.aspx?id=50402
this list has what SQL version started supporting TLS, as well as local drivers:
https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server
Lowell
February 24, 2019 at 8:22 am
That client is a web client. They do not need to install anything.
But I will update my SSMS to the right version in your link. And see what happens. Though I am not sure why would that matter to a remote web client.
February 24, 2019 at 10:38 pm
You need to extract more information on the login failures. SQL Server records a severity and state for every error in SQL, and with login failures this is key to understanding why a login failed.
Check the SQL Server error log to find the login failures and report back with the full error text, then we can provide more insight. To give you some examples, login state 5 means the username provided doesn't exist. State 6 means you're trying to perform SQL authentication with a domain account. There are dozens of possible causes, but without the accompanying state and error message information, it is difficult to diagnose.
At a guess, your issue might be caused by impersonation issues. Specifically, if you see the login failure message referencing the account NT AUTHORITY\ANONYMOUS LOGON then it indicates your login attempt required Kerberos authentication, but your SPNs are not configured correctly.
February 25, 2019 at 10:49 am
I have basicauth turned on with impersonation enabled. No anonymous login.
The err
System.Data SqlClient.SqlException: Log in failed for user 'domain\user name'.
Error: 18456, Severity: 14, State: 5.
2019-02-25 12:41:21.38 Logon Login failed for user 'domain\user name'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
February 25, 2019 at 5:25 pm
That error means the user doesn't have a corresponding login in SQL Server. If you log in to the SQL Server as an administrator and run the below command, does it return any results?
EXEC xp_logininfo 'domain\user name', 'all'
This should return all of the available paths for that user to login to SQL Server, either through a login for that user, or membership in an AD group that is configured for login to SQL Server. If the result set is empty, then the user doesn't exist and needs to be added to SQL Server.
February 26, 2019 at 12:30 pm
Right but they are coming thru a web app - .net app. So they use Network Service account to log in.
So not sure why it is looking for domain\user name for permissions.
The server is set up as mixed mode auth - Windows and SQL Server. Both NETWORK Service account is defined with the right permissions. Or hte same ones as I used to have when it worked. The only change was upgrade to Windows 10, .NET Framework 4.7 to support TLS 1.2 and hence had to reinstall the SQL Server.
February 26, 2019 at 1:19 pm
tinausa - Tuesday, February 26, 2019 12:30 PMRight but they are coming thru a web app - .net app. So they use Network Service account to log in.
So not sure why it is looking for domain\user name for permissions.The server is set up as mixed mode auth - Windows and SQL Server. Both NETWORK Service account is defined with the right permissions. Or hte same ones as I used to have when it worked. The only change was upgrade to Windows 10, .NET Framework 4.7 to support TLS 1.2 and hence had to reinstall the SQL Server.
Upgrading from Windows 7 to Windows 10 also changes the version of IIS. It seems that is where the issue is so you may want to check the IIS configurations.
Sue
February 26, 2019 at 1:53 pm
Yes, that's exactly what I am looking at right now. Will post what I find.
February 26, 2019 at 5:21 pm
tinausa - Monday, February 25, 2019 10:49 AMI have basicauth turned on with impersonation enabled. No anonymous login.
Impersonation in IIS and Integrated Security in your connection string would mean users credentials are being passed through to SQL Server for authentication, rather than Network Service, would it not?
tinausa - Tuesday, February 26, 2019 12:30 PMThe only change was upgrade to Windows 10, .NET Framework 4.7 to support TLS 1.2 and hence had to reinstall the SQL Server.
Did you back up the users etc from the old instance before reinstalling? Is there an AD group missing that was on the previous server?
The error is quite clear about the cause of the problem, those users don't exist on the SQL Server, and if you're using impersonation and integrated security, it should be trying to use the user credentials to access SQL Server.
February 26, 2019 at 7:27 pm
HandyD - Tuesday, February 26, 2019 5:21 PMtinausa - Monday, February 25, 2019 10:49 AMI have basicauth turned on with impersonation enabled. No anonymous login.Impersonation in IIS and Integrated Security in your connection string would mean users credentials are being passed through to SQL Server for authentication, rather than Network Service, would it not?
tinausa - Tuesday, February 26, 2019 12:30 PMThe only change was upgrade to Windows 10, .NET Framework 4.7 to support TLS 1.2 and hence had to reinstall the SQL Server.Did you back up the users etc from the old instance before reinstalling? Is there an AD group missing that was on the previous server?
The error is quite clear about the cause of the problem, those users don't exist on the SQL Server, and if you're using impersonation and integrated security, it should be trying to use the user credentials to access SQL Server.
In the original post, they checked the permissions to ensure network service still had access. If that's how it was set up originally and how access worked then they weren't using impersonation, delegation. They were using windows authentication but not impersonation. They would have been using the trusted subsystem model with the process set to use network service.
Sue
February 27, 2019 at 8:26 am
In the old machine, the users came in as network service. No individual logins were set up.
February 27, 2019 at 11:03 am
tinausa - Wednesday, February 27, 2019 8:26 AMIn the old machine, the users came in as network service. No individual logins were set up.
Pretty much what I thought. You would want to disable impersonation and still use Windows Authentication.
Sue
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply