Login failed for user 'DOMAIN\MACHINENAME$'

Question

I know this is almost a duplicate of : The error "Login failed for user 'NT AUTHORITY\IUSR'" in ASP.NET and SQL Server 2008 and Login failed for user 'username' - System.Data.SqlClient.SqlException with LINQ in external project / class library but some things don't add up compared to other appliations on my server and I am not sure why.

Boxes being used:

Web Box
SQL Box
SQL Test Box

My Application:

I have an ASP.NET Web Application, which references a class library that uses LINQ-to-SQL. Connection string set up properly in the class library. As per Login failed for user 'username' - System.Data.SqlClient.SqlException with LINQ in external project / class library I also added this connection string to the Web Application.

The connection string uses SQL credentials as so (in both web app and class library):

 <add name="Namespace.My.MySettings.ConnectionStringProduction"
        connectionString="Data Source=(SQL Test Box);Initial Catalog=(db name);Persist Security Info=True;User ID=ID;Password=Password"
        providerName="System.Data.SqlClient" />

This connection confirmed as working via adding it to Server Explorer. This is the connection string my .dbml file is using.

The problem:

I get the following error:

System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\MACHINENAME$'.

Now referencing this The error "Login failed for user 'NT AUTHORITY\IUSR'" in ASP.NET and SQL Server 2008 it says that's really the local network service and using any other non-domain name will not work.

But I am confused because I've checked both SQL Box and SQL Test Box SQL Management Studio and both have NT AUTHORITY/NETWORK SERVICE under Security -> Logins, at the database level, that isn't listed under Security -> Users, but at the database level Security -> Users I have the user displayed in the connection string.

At NTFS level on web server, the permissions have NETWORK SERVICE has full control.

The reason why I am confused is because I have many other web applications on my Web Server, that reference databases on both SQL Box and SQL Test Box, and they all work. But I cannot find a difference between them and my current application, other than I am using a class library. Will that matter? Checking NTFS permissions, setup of Security Logins at the server and databases levels, connection string and method of connecting (SQL Server credentials), and IIS application pool and other folder options, are all the same.

Why do these applications work without adding the machinename$ to the permissions of either of my SQL boxes? But that is what the one link is telling me to do to fix this problem.

Solution 1

NETWORK SERVICE and LocalSystem will authenticate themselves always as the correpsonding account locally (builtin\network service and builtin\system) but both will authenticate as the machine account remotely.

If you see a failure like Login failed for user 'DOMAIN\MACHINENAME$' it means that a process running as NETWORK SERVICE or as LocalSystem has accessed a remote resource, has authenticated itself as the machine account and was denied authorization.

Typical example would be an ASP application running in an app pool set to use NETWORK SERVICE credential and connecting to a remote SQL Server: the app pool will authenticate as the machine running the app pool, and is this machine account that needs to be granted access.

When access is denied to a machine account, then access must be granted to the machine account. If the server refuses to login 'DOMAIN\MACHINE$', then you must grant login rights to 'DOMAIN\MACHINE$' not to NETWORK SERVICE. Granting access to NETWORK SERVICE would allow a local process running as NETWORK SERVICE to connect, not a remote one, since the remote one will authenticate as, you guessed, DOMAIN\MACHINE$.

If you expect the asp application to connect to the remote SQL Server as a SQL login and you get exceptions about DOMAIN\MACHINE$ it means you use Integrated Security in the connection string. If this is unexpected, it means you screwed up the connection strings you use.

Solution 2

This error occurs when you have configured your application with IIS, and IIS goes to SQL Server and tries to login with credentials that do not have proper permissions. This error can also occur when replication or mirroring is set up. I will be going over a solution that works always and is very simple. Go to SQL Server >> Security >> Logins and right click on NT AUTHORITY\NETWORK SERVICE and select Properties

In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

Solution 3

Basically to resolve this we need to have some set up like

• Web App Running under ApplicationPoolIdentity
• Web Application connecting to databases through ADO.Net using Windows Authentication in the connection string

The connection string used with Windows authentication include either Trusted_Connection=Yesattribute or the equivalent attribute Integrated Security=SSPI in Web.config file

My database connection is in Windows Authentication mode. So I resolved it by simply changing the Application Pools Identity from ApplicationPoolIdentity to my domain log in credentials DomainName\MyloginId

Step:

1. Click on Application Pools

2. Select Name of your application

3. Go to Advanced Setting

4. Expand Process Model and click Identity. Click three dot on right end.
5. Click Set... button and Provide your domain log in credentials

For me it was resolved.

Note: In Production or IT environment, you might have service account under same domain for app pool identity. If so, use service account instead of your login.

Solution 4

In my case I had Identity="ApplicationPoolIdentity" for my IIS Application Pool.

After I added IIS APPPOOL\ApplicationName user to SQL Server it works.

Solution 5

The trick that worked for me was to remove Integrated Security from my connection string and add a regular User ID=userName; Password=password your connection string in the App.config of your libruary might not be using integrated security but the one created in Web.config is!

View more solutions

Comments

[jcolebrand]

So to recap, you're not using a database user? We create one and can toggle between it and SA depending on what we need to do...

[SventoryMang]

In the connection string I am using a database user, which I created in the Security -> Logins area, added it to the Security -> users of the database, and gave it dbo permissions. Which is how I did all my other apps too.

[Brett Maiwald]

Here's a clear explanation from MSDN using the default machine name, basically you just add the domain/machine$ to sql without hitting search. blogs.msdn.microsoft.com/ericparvin/2015/04/14/…

[SventoryMang]

Right that what was I gathered, thank you for the explanation. However, the question still remains, all of my apps are hosted on my Web Server but access a database on SQL or SQL Test boxes, that would be remote access yes? Yet they are working...but neither of my SQL boxes are granting DOMAIN\MACHINENAME$ access.

[SventoryMang]

Oh Also, I do expect to connect to SQL server as a SQL Login but I've posted my connection strings, I am not using Integrated Security=True option, what else could it be??

[Remus Rusanu]

There are three possible explanations: 1) they use SQL auth instead of integrated auth (which seems to be the most plausible one, since you example has an userid and password in conn string) 2) they use integrated auth and run in an app poll that uses a different credential or 3) they use integrated auth but the ASP app impersonates the caller, thus triggering constrained delegation: technet.microsoft.com/en-us/library/cc739587%28WS.10%29.aspx‌​.

[Remus Rusanu]

Most likely is that your app is not using the connection string you expect it to use. Do you pass in a connection string to your datacontext constructor?

[SventoryMang]

Not sure if I am or not to be honest, I just use SQL Metal to generate the LINQ-to-SQL classes. But when opening my .dbml file, the connection property is set to the one I want to use. Both the application I am getting this error with, and my other applications that are working, are in the same application pool, and both are using SQL auth.

[Remus Rusanu]

You're going to have to track down how the data context generated from the .dbml is instantiated in your code.

[SventoryMang]

I am not passing it anything, I just instantiate it with new MyDataContext()

[SventoryMang]

The app pool its using a predefined identity of Network Service, not sure if that helps.

[SventoryMang]

Well I feel stupid....but it seems like my Web App didn't get the most updated version of my class library dll, So I guess what happened was I built the class library to debug and not release and my web app was referencing release folder dll. This is the only explanation I can come up with because I just deleted the class library reference, re-built class library, re-added reference, and then re-published while choosing to delete all files on there, and it works...

[Remus Rusanu]

Your web app project should reference the class library project, not the dll. Add the class library project to the web app solution, then remove the reference to the dll and add reference to the project. This way, when deploying or testing, the retail web app will reference retail class dll and debug will reference debug, automatically.

[shubniggurath]

A billion thank-yous to you. Huge, huge help. Thank you, thank you, thank you. This is, I'm sure, very obvious but for future folks, it's User Id=something; Password=something;

[cdonner]

I had the same problem. The error from SSAS is the same, but the account is not Network Service. The account is actually: NT Service\MSOLAP$INSTANCENAME

[T3.0]

I was getting the same error in the title of the post. I found that the 'User Id= yourUserid Password= yourPassword' is ignored when "'trusted connection=true'" is in the database connection string. I removed "'trusted connection=true'" from my string and that fixed my problem. This didn't happen until I moved the application from debugging in VS 2012 to iis 8.

[atconway]

Just understand this will change the context for which the ASP.NET application runs under in its entirety. Instead of running under the default 'NETWORK SERVICE' context it will now run under the context of the user using the application (i.e. Domain\someUser). This is OK sometimes, but just understand this change isn't just a quick fix to the OP and does have other downstream implications that may/may not be desired.

[Timothy]

MSDN on <identity />

[JimiSweden]

This was the solution for me since the web application and database are on the same machine. I still got the error "Login failed for user 'DOMAIN\MACHINENAME$" but adding the machine to SQL logins did not help, but adding "NT AUTHORITY\NETWORK SERVICE" did. Though you should not use the role db_owner unless it is needed, normaly db_datareader and db_datawriter is sufficient.

[Rob Davis]

I believe this will only work if IIS and the SQL server are on the same machine.

[dansan]

I'd like to add/clarify to Remus' very good answer that you can also receive this message if you need to add NetworkService to SQL. For example, this message will appear as domain\machine$ in the SqlClient exception, but if you're accessing a local instance of SQL, adding a login for the computer account will not fix the problem; you have to add NetworkService directly.

[Vin Shahrdar]

This worked for me! I have a local IIS-SQL server setup.

[MFry]

Thank you very much. This problem started for me after upgrading my local dev environment from SQL Server 2014 to 2017. Your suggestion was the silver bullet in this situation.

[BrainSlugs83]

While this is all well and good, how do you add the machine login to SQL? -- They're both on the same domain, and I would prefer to use integrated security. But just adding an account named "Domain\MachineName$" completely fails (like, it doesn't exist, and object explorer chokes and fails to find anything like that).

[Remus Rusanu]

CREATE LOGIN [DOMAIN\MACHIN$] FROM WINDOWS;

[mivra]

Thanks, worked for me also. What I would like to highlight is that the error message is still 'Login failed for user 'DOMAIN\MACHINENAME$' even though the application pool is set to run under pool identity and the login fails even if the 'DOMAIN\MACHINENAME$' actually is granted the permissions to connect. Seems like misleading error message to me.

[makil]

For the above question this should be the accepted answer.

[tanuj shrivastava]

THIS IS THE CORRECT ANSWER

[Learner]

@RemusRusanu - I followed this approach and didn't fix my issue. Any suggestions?