I know this is almost duplicate of : http://stackoverflow.com/questions/1269706/the-error-login-failed-for-user-nt-authority-iusr-in-asp-net-and-sql-server-2 and http://stackoverflow.com/questions/97594/login-failed-for-user-username-system-data-sqlclient-sqlexception-with-linq-i 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've got aASP.NET Web Application, which references a class library that uses LINQ-to-SQL. Connection string set up properly in the class library. As per http://stackoverflow.com/questions/97594/login-failed-for-user-username-system-data-sqlclient-sqlexception-with-linq-i 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 http://stackoverflow.com/questions/1269706/the-error-login-failed-for-user-nt-authority-iusr-in-asp-net-and-sql-server-2 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.