Login failed for domain\computer$

  • AlphaTangoWhiskey

    SSChampion

    Points: 10780

    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?

  • Sue_H

    SSC Guru

    Points: 90011

    AlphaTangoWhiskey wrote:

    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

     

  • AlphaTangoWhiskey

    SSChampion

    Points: 10780

    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.

  • Sue_H

    SSC Guru

    Points: 90011

    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

  • AlphaTangoWhiskey

    SSChampion

    Points: 10780

    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.

  • Sue_H

    SSC Guru

    Points: 90011

    AlphaTangoWhiskey wrote:

    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://blog.dbi-services.com/sql-server-tips-deactivate-the-customer-experience-improvement-program-ceip/

    https://stackoverflow.com/questions/43548794/how-to-turn-off-telemetry-for-sql-2016

     

    Sue

  • This was removed by the editor as SPAM

  • AlphaTangoWhiskey

    SSChampion

    Points: 10780

    wrote:

    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.

  • Sue_H

    SSC Guru

    Points: 90011

    AlphaTangoWhiskey wrote:

    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

  • AlphaTangoWhiskey

    SSChampion

    Points: 10780

    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

  • BU69

    SSCrazy

    Points: 2783

    I've had the same error since applying CU14 update a few days ago, before that this login failure wasn't there.

  • AlphaTangoWhiskey

    SSChampion

    Points: 10780

    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:

    https://www.brentozar.com/archive/2019/02/what-queries-does-microsofts-telemetry-service-run-on-your-sql-server/

     

  • BU69

    SSCrazy

    Points: 2783

    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.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply