April 10, 2019 at 2:33 pm
2017 Enterprise edition running on Server 2016.
This is the default instance (there are 2 others)
Error: 18456, Severity: 14, State: 5.
2019-04-10 09:14:54.40 Logon Login failed for user 'domain\computername$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Originally setup using virtual accounts and changed a couple days ago to an AD account for the SQL service. This error started way before the switch and is still there.
The agent is setup as virtual but its not running.
All i want to know is what on the local machine is trying to log into SQL and how to stop it. Also when switching accounts is there any security on the local machine that needs to be changed?
April 10, 2019 at 6:24 pm
2017 Enterprise edition running on Server 2016. This is the default instance (there are 2 others)
Error: 18456, Severity: 14, State: 5. 2019-04-10 09:14:54.40 Logon Login failed for user 'domain\computername$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
Originally setup using virtual accounts and changed a couple days ago to an AD account for the SQL service. This error started way before the switch and is still there. The agent is setup as virtual but its not running. All i want to know is what on the local machine is trying to log into SQL and how to stop it. Also when switching accounts is there any security on the local machine that needs to be changed?
Is 'Domain\computername$' the computer with this instance, the three instances?
If you change the service accounts using SQL Server configuration manager then you won't need to make any other security, permission changes.
Sue
April 10, 2019 at 7:36 pm
I'm not sure what you're asking but the login error is happening only on the default instance. The server has 2 additional instances and i'm not seeing this error on those.
This is a new server that i setup using virtual accounts for all instances and today changed them all to active directory accounts, but still seeing that domain\computername$ login failure on the default (MSSQLSERVER), just happened a few minutes ago, in fact its happening exactly every 5 minutes.
I shut down the browser service and the full text service just to check and its still happening exactly every 5 minutes.
The only other services are the VSS Writer and SQL Telemetry (CEIP service)
Also i'm using the dbaTools powershell module (Get-DbaService) to switch the service accounts.
April 10, 2019 at 8:06 pm
What I meant is computername$ the name of the server that this instance (and the others) is on?
When you see something like that every 5 minutes, it can often be some type of monitoring tool. Possibly a tool along those lines on one of the other instances.
Sue
April 10, 2019 at 8:14 pm
Yes it is.
Just found the problem
I disabled the Telemetry(CEIP) service for the default instance and its been 15 minutes with no login failure.
Now the question is do i change the registry to disable it? I'm not sure how to fix it or why its even happening.
April 10, 2019 at 8:28 pm
Yes it is. Just found the problem I disabled the Telemetry(CEIP) service for the default instance and its been 15 minutes with no login failure. Now the question is do i change the registry to disable it? I'm not sure how to fix it or why its even happening.
Microsoft enables it by default on SQL Server 2016 and above. Customer Experience Improvement Program. I had a link explaining it but Microsoft moved their sites and the link no longer works. You can disable all of the services. Here is some more information on disabling it and if you wanted to change registry settings, these have information on that as well:
https://stackoverflow.com/questions/43548794/how-to-turn-off-telemetry-for-sql-2016
Sue
April 12, 2019 at 3:01 pm
This was removed by the editor as SPAM
April 12, 2019 at 3:05 pm
If you change the service accounts using SQL Server configuration manager then you won’t need to make any other security, permission changes.
I was using dbatools powershell command update-dbaserviceaccount
Get-DbaService sql1 -Type Engine -Instance MSSQLSERVER | Update-DbaServiceAccount -Username 'MyDomain\sqluser1'
Ran into the SPN (cannot create sspi context) issue but only for some named instances however we are also missing some SPN's they are not associated with either the service AD account or the computer account but i can still login. I know this is a bit off topic and i can post a new topic just wondering if not using Config mgr could be the issue.
April 12, 2019 at 5:13 pm
Ran into the SPN (cannot create sspi context) issue but only for some named instances however we are also missing some SPN's they are not associated with either the service AD account or the computer account but i can still login. I know this is a bit off topic and i can post a new topic just wondering if not using Config mgr could be the issue.
I don't think the spn issue is from dbatools - I'm pretty sure they go through wmi, smo so that it basically is like going through configuration manager. Those cmdlets in dbatools are pretty solid. Check the sql server error logs when those instances started up - there should be an error and return code from when it tried to register the spn at start up. That may give you some ideas on the spn issues.
Sue
April 12, 2019 at 10:32 pm
Thanks Sue i really appreciate the help. The deeper i get with this the more confused i get.
I've created a new post in 2016 administration now that i'm seeing connection types of NTLM and kerberos, it seems like its Kerberos for properly setup SPNs and NTLM for the rest... anyway here's the link if you're interested:
https://www.sqlservercentral.com/forums/topic/question-about-spn-and-kerberos-vs-ntlm
April 18, 2019 at 8:19 am
I've had the same error since applying CU14 update a few days ago, before that this login failure wasn't there.
April 18, 2019 at 2:38 pm
i went into services and disabled the Telemetry CEIP service. It was trying to login every 5 minutes. Try that and see if the login errors stop. Also look above to Sue_H's post with a link to StackOverflow about disabling that.
Here's some more info on that service from Brent:
April 23, 2019 at 9:17 am
Thanks, I looked this last week, stopped after I disabled the service, it's very odd that it started after the CU14 update but only on one SQL 2017 server, the other servers this seems to have been fine after the patching.
April 8, 2020 at 2:18 am
I've got same issue. in my case sql login NT Service\SQLTelemetry is missing, so i added again and grant same permission as in another server.
USE [master]
GO
CREATE LOGIN [NT SERVICE\SQLTELEMETRY] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
use [master]
GO
GRANT ALTER ANY EVENT SESSION TO [NT SERVICE\SQLTELEMETRY]
GO
use [master]
GO
GRANT CONNECT ANY DATABASE TO [NT SERVICE\SQLTELEMETRY]
GO
use [master]
GO
GRANT VIEW ANY DATABASE TO [NT SERVICE\SQLTELEMETRY]
GO
use [master]
GO
GRANT VIEW SERVER STATE TO [NT SERVICE\SQLTELEMETRY]
GO?
April 27, 2021 at 2:54 pm
Thanks @TangoVictor,
This is still a thing in SQL Server 2019. Hadn't noticed that the SQL Server CEIP service was enabled and running. Since we don't use telemetry I disabled and stopped the service and the login failure messages stopped.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply