SQL Server 2012 Can't log in with machine account

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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"

Answer 6

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.