asked
Facing Login failed for user ''/Login failed for user 'INTRANET\$' errors randomly
Hi, We have a MS SQL Server 2005 [Windows NT 5.2 (Build 3790: Service Pack 2)] database to which multiple
ASP.NET web servers [.Net SqlClient Data Provider] connect but randomly we face problems connecting to the database. From the SQL Server logs we can see that some servers it throws the 1st error whereas for the others it throws the 2nd consistently. 1) 2010-10-04 09:23:03.38 Logon Error: 17806, Severity: 20, State: 2. 2010-10-04 09:23:03.38 Logon SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: ] 2010-10-04 09:23:03.38 Logon Error: 18452, Severity: 14, State: 1. 2010-10-04 09:23:03.38 Logon Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: ] 2) 2010-10-04 09:25:03.30 Logon Error: 18456, Severity: 14, State: 11. 2010-10-04 09:25:03.30 Logon Login failed for user 'INTRANET\ $'. [CLIENT: ] I browsed through a number of blogs but haven't found any which talk about facing this issue randomly. Any links/blogs on this much appreciated. Thank you.
Check your firewall settings and authentication mode settings
-- Fire wall settings -> Open Windows Firewall settings from control panal -> Advanced Tab -> Choose current Network Connection -> go to Setting -> On the Advanced Settings -> Services Tab -> Click Add Button -> On the Services Settings dialog provide the Host name or IP address of the SQL Server -> Then Enter the default port for SQL Server (1433) as Both External and Internal Port -> choose the protocol required as TCP -- Authentication mode Verify your authentication settings -> right click Server on SSMS Object Explorer goto properties, under security note the server authentication settings. If your are on windows authentication settings make sure that you are trying to connect using a windows login. if you tried to connect using SQL login then you may need to change the authentication settings to Mixed mode.
Do the web servers' system accounts have database access? EG. if you have a server called MyIntranetServer that needs access to the EmployeeDirectory database on a SQLServer instance called OHIO on a server called Einstein where all servers are in a Domain called Contico then in the security for Einstein\\OHIO you would need to add the Contico\\MyIntranetServer$ account and give it access to the EmployeeDirectory database as, at least, db_datareader.
If you are trying to use SQL login, and get the first error - I have seen this happen when the connection string is incorrectly formed, so I would check those. The second error reports state 11 - this means that the user is a valid account, but is denied access at the server level. This suggests you are using Windows authentication, and simply that windows account is denied access to the windows server. The randomness could be just down to one particular connection string being incorrect, whereas the others are fine....
