Which user is this?

Question

Which user is this?

shahgols

SSCrazy Eights

Points: 9874
More actions
April 16, 2012 at 4:34 pm

#256558

Hi everyone,
I have a SQL 2005 Enterprise 64-bit (Sp4) on Win 2008 R2 64-bit. In the logs I see a message about a login failure for an interesting user: <domain>\<another server>$
For example, the name is: MyDomain\Server2$
What is this user? I can't create a login for it using the GUI because it doesn't come up as a user on the domain. I was able to add it as a login using code, but that didn't fix the errors that appear in the logs. Any idea what this is and what I should do about it?
The error code is:
Message
Error: 18456, Severity: 14, State: 16.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

The $ after the name signifies it's a machine account, i.e. the name before the $ identifies a server. It means that something running on that machine tried to log into your SQL Server instance for something. I am not 100% sure, but I think whatever is trying is running under the built-in "network service" account. At any rate, this is common with websites where the application running on the site is using AD authentication and the admin setting it up neglected to modify the configuration to run the site under a domain account with DB access.

Sometimes things need to run as machine accounts, so if this is one of those legitimate cases and you are asked to create a login, while you may not be able to browse for the name through SSMS you can still create a login using T-SQL, like this:

CREATE LOGIN [DOMAIN\MACHINE$] FROM WINDOWS

I would recommend finding out what's making the attempt though before proceeding in making any changes to your database instance security.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

PS if the "machine" is the local machine it could be a service running under the "local system" account. Even in that case my recommendation about finding the source before making changes would still stand.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

shahgols SSCrazy Eights Points: 9874 More actions · Answer 3

Thank you very much for that information!

I have already created that user using code, and it still cannot log in...that's what I see in SQL Server logs. It's very strange.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Found this. Says State = 16 means the users failed to log into the target database.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

shahgols SSCrazy Eights Points: 9874 More actions · Answer 5

Thanks again, this is strange, since I have added the user as you had mentioned, but it's still not able to log in. I dropped the user and recreated just in case and it still cannot log in.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Make sure that the login has a mapped user in its default database and that the database it has listed as default exists. That's what login failure state 16 means

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

I am picking up on some terminology issues. For the record, and to clear up the terminology, we need a Server Login and a Database User. The Server Login is for authentication and grants access to the Database Instance. A User is at the Database Level and authorizes access to a database.

Was your use of "user" in your last post correct? Or did you actually drop and recreate the login? Have you created a Database User?

The code I showed above creates a Server Login. Here is code to create a Database User:

USE [logins_default_database]

GO

CREATE USER [MACHINE$] FROM LOGIN [DOMAIN\MACHINE$];

GO

What did you set the Login's default database to? If master and you are still seeing the error it means the login attempt is for a different database...and I am not sure how to find out what that might be.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Eric M Russell SSC Guru Points: 125561 More actions · Answer 8

shahgols (4/16/2012)
Thank you very much for that information!
I have already created that user using code, and it still cannot log in...that's what I see in SQL Server logs. It's very strange.

You need to investigate and find out exactly what this process is, but unless someone is complaining and they have a legitimate need to login, I wouldn't add them a login at this point.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

shahgols SSCrazy Eights Points: 9874 More actions · Answer 9

Hi everyone,

Just as Gail had said, the user's default database was set wrong, once I corrected that, the entries in the SQL Server logs stopped happening. Thanks for all your help!