11

We're migrating an IIS/SQL Server site from 2008 to 2012, and I'm having a problem with authentication. We use Windows Authentication to authenticate as DOMAIN\COMPUTER$, but this isn't working, giving an error saying:

Login failed for user 'DOMAIN\COMPUTER$'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]

I've verified that the account exists, and even deleted and recreated it

create login "DOMAIN\COMPUTER$" from windows

If I do it again, I get the response:

Msg 15025, Level 16, State 2, Line 1
The server principal 'DOMAIN\COMPUTER$' already exists.

(DOMAIN\COMPUTER is substituted for the real domain and computer name)

So it appears that the site is querying the SQL Server, but the SQL Server isn't looking at its logins very hard.

What have I missed?

Improve this question
7

It is most likely not the DOMAIN\computer$ at all. This message is known to be a misleading. I think all it wants is that "NT AUTHORITY\NETWORK SERVICE" be granted the server access. Open SQL Server login properties and add this account. Also, check what user is used in the IIS to impersonate anonymous user, it should be something like "IUSR_ServerName". Grant access to this user as well

Improve this answer
3
  • 1
    "NT AUTHORITY\NETWORK SERVICE" already has server access. IIS is impersonating as "IUSR" (no _ServerName). As far as I was aware this isn't a real account--and I can't grant access to either "DOMAIN\IUSR" or "DOMAIN\IUSR" as these accounts don't exist. – alt Aug 19 '14 at 21:25
  • This worked for me for a Windows service – user919426 Apr 8 '15 at 8:45
  • After battling a long morning with Copy Database Wizard, and SQL Export-Import wizard, this is the only answer that made it work. Thanks! – Zalakain May 23 '19 at 10:30
6

Both @cha and @alt have right clues - the error message is misleading and Application Pool Identity is involved (well, at least it was in my case).

If your IIS app's application pool uses Application Pool Identity, the user account you need to add to SQL Server is IIS APPPOOL\your_app_pool_name. Note: don't use the Search... button in Login Properties to find this user - it will find it, but will substitute it with MACHINE_NAME\your_app_pool_name which won't work - just type IIS APPPOOL\your_app_pool_name as the login name.

Improve this answer
2
  • 1
    It only works when your web server and sql server are in the same box. – maxisam Mar 20 '18 at 22:39
  • 1
    Sure, the app pool identity accounts are local to the machine AFAIK, so you aren't able to use them on other machines. Just use SQL Server authentication in that case or run the web app on a domain account. – Jakub Januszkiewicz Mar 22 '18 at 22:38
4

This problem should be handled differently depending on the server setup.

  1. SQL and IIS server are on the same machine.

    a. change the Application Pool Identity to Network Service

    b. add NT AUTHORITY\NETWORK SERVICE to your SQL server login

  2. SQL and IIS server are on different machine.

    a. add machine account, DOMAIN\computer-name$, to your SQL server login.

    b. the Application Pool Identity remains Application Pool Identity

Improve this answer
2

I managed to get it to work by changing the site in IIS to use the Application Pool Identity, then changing the Application Pool Identity to Network Service. Feel free to comment on any security issues this may raise.

Improve this answer
1
  • Why not use a domain user for the app pool as you have AD in place or even better a mananged service account? – Spörri Apr 21 '15 at 23:43
1

Although short, Ayan's reply was spot on for me too. The strange thing being, ours is a server with both an instance of 2012(named) and 2017(default) and it was the 2017 CEIP service trying to log onto the 2012 instance, maybe it's by design that it covers ALL instances on the box and the fact that the 2012 was shutdown when 2017 was installed, I don't know. Anyway, disabled the service (forgot to say "No" during installation) and been troubled no more by the logon attempts.

FYI, I ran Profiler with the Errors and Warnings | ErrorLog and the Errors and Warnings | User Error Message events on, which provided the offending Application Name, SQLServerCEIP. The Windows' Application Event log also provides a clue "User: NT SERVICE\SQLTELEMETRY"

Improve this answer
0

I was getting the same error. Logon Error: 18456, Severity: 14, State: 5. Disabling CEIP from SQL Server Error and Usage Reporting resolved the issue for me.

Improve this answer
1
  • 1
    Could you elaborate on your answer a bit? Can you cite any relevant resources? Your answer popped up as low-Quality because of its length and content and could be deleted because of that. – John K. N. Aug 2 '18 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.