• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

IIS and SQL Login Failure

I am having a problem with either IIS or SQL Server that is starting to cause problems.  We have a production website that runs on .Net and ASP.  This site of course has a database back end to it.  What we are noticing is that randomly the site will give a login failure when trying to access the databases.  In looking at the logs we get a login success and then right after that a login failure.  If I restart the SQL Service the site will work fine until the login failure reappears.  

Now this machine is running Windows 2003 Web Edition and is using SQL Express 2005.  The machine is NOT a part of the domain.  I have changed the SQL server to run as a system account and I have also configured the IIS application pool to run as the same user account.  The passwords do not change.
0
TSRich02
Asked:
TSRich02
  • 6
  • 4
  • 3
  • +1
1 Solution
 
TSRich02Author Commented:
To clarify: We have changed the SQL and IIS Application Pool process to run as a User account on the local server and not a NT AUTHORITY\* account.
0
 
SharathData EngineerCommented:
could you post your exact error? did you check whether SQL Server (MSSQLServer)  service is running?
0
 
TSRich02Author Commented:
SQL Service is in fact running.  

[Errors from SQL Event Log]

2009-04-22 06:53:19.50 Logon       Login succeeded for user 'WWW2\sqldba'. Connection: trusted. [CLIENT: <local machine>]
2009-04-22 06:53:19.50 Logon       Error: 18456, Severity: 14, State: 16.
2009-04-22 06:53:19.50 Logon       Login failed for user 'WWW2\sqldba'. [CLIENT: <local machine>]
2009-04-22 06:55:53.34 Logon       Login succeeded for user 'WWW2\sqldba'. Connection: trusted. [CLIENT: <local machine>]
2009-04-22 06:55:53.34 Logon       Error: 18456, Severity: 14, State: 16.
2009-04-22 06:55:53.34 Logon       Login failed for user 'WWW2\sqldba'. [CLIENT: <local machine>]
2009-04-22 07:16:20.64 Server      Server resumed execution after being idle 314 seconds: user activity awakened the server. This is an informational message only. No user action is required.
2009-04-22 07:16:20.64 Logon       Login succeeded for user 'WWW2\sqldba'. Connection: trusted. [CLIENT: <local machine>]
2009-04-22 07:16:20.64 Logon       Error: 18456, Severity: 14, State: 16.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
TSRich02Author Commented:
Any thoughts?  Didn't think this question was to hard.  :-)
0
 
Jim P.Commented:
Go into the SQL Server Management Studio and make sure the database(s) are not set to auto Close and Auto Shrink. Especially the Master DB.


SQL-DB-Auto-Close.jpg
0
 
TSRich02Author Commented:
Verified that all but one database was set to not auto close or auto shrink.  There was one database that did have those options set.  I changed them to false.  Do I need to restart SQL?  

(Thank you for the screenshot that was a big help to identify what I needed to do).
0
 
Ted BouskillSenior Software DeveloperCommented:
That's a fairly generic error.  For example, in Sharepoint we have that problem occasionally because of a rarely used configuration change.  It orphans a timed job that is deleting expired sessions for a database that no longer exists.

Check your SQL Server Logs.  That is where a precise clue will be for what the true error is.
0
 
Jim P.Commented:
>> Do I need to restart SQL?  

No. It should take effect immediately.  See if you continue to get the error. If you do please do as Ted suggested and post events from the log.

0
 
TSRich02Author Commented:
I am going to have to leave the question open as the issue does not always happen within a few days are so.  It is usually about 5 days that we notice it.  I will keep everyone posted.  Thank you both for assisting on this question.  
0
 
Ted BouskillSenior Software DeveloperCommented:
That sounds like a timed job that is causing the problem.  You should look closely at the SQL logs (not the Windows Event Logs)
0
 
Jim P.Commented:
Look in the web.config file and make sure the userid and password is correct, and that you only have one web.config file.
0
 
TSRich02Author Commented:
My response to Sharath_123 showed a copy of the actual SQL error log.  About what might I be looking for in this?
0
 
Ted BouskillSenior Software DeveloperCommented:
Ah, I misread, I thought those were from the Windows Application log.  Are there any timed SQL agent jobs running at the same time?
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 6
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now