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.
Best Answer
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.