Solved

Login failed in SQL Server implying all IIS sites down

Posted on 2009-06-30
18
1,619 Views
Last Modified: 2012-08-14
Hello everybody.

Yesterday morning everything was working perfectly. I have a MS Server 2008 with SQL Server 2008, MS Exchange Server 2007, Sharepoint 3.0 and Dynamics CRM 4.0.

Now I get an error 503 when trying to access any website located in the server (our own website, OWA, Sharepoint, CRM....), the APPPools in IIS are "stopped due to errors".

In the Event Viewer I can see constantly the following SQL Server error:

Login failed for user 'DOMAIN\Administrador'. Specified databe cannot be opened. [CLIENT: <local machine>]

Source:  MSSQLSERVER        Event Id: 18456.

Extended info:

- System

  - Provider
   [ Name]  MSSQLSERVER
  - EventID 18456
   [ Qualifiers]  49152
   Level 0
   Task 4
   Keywords 0x90000000000000
  - TimeCreated
   [ SystemTime]  2009-06-30T07:30:00.000Z
    EventRecordID 479101
    Channel Application
    Computer servername.domain.es
   - Security
   [ UserID]  S-1-5-21-839165756-4290762602-175011524-500
- EventData
   DOMAIN\Administrador
   Motivo: no se puede abrir la base de datos explícitamente especificada.
   [CLIENTE: <local machine>]
 184800000E0000000D00000053004500520056004500520049004E0053004500520031000000070000006D00610073007400650072000000


--------------------------------------------------------------------------------

Binary data:
In words
0000: 00004818 0000000E 0000000D 00450053
0008: 00560052 00520045 004E0049 00450053
0010: 00310052 00070000 006D0000 00730061
0018: 00650074 00000072  

In bytes
0000: 18 48 00 00 0E 00 00 00   .H......
0008: 0D 00 00 00 53 00 45 00   ....S.E.
0010: 52 00 56 00 45 00 52 00   R.V.E.R.
0018: 49 00 4E 00 53 00 45 00   I.N.S.E.
0020: 52 00 31 00 00 00 07 00   R.1.....
0028: 00 00 6D 00 61 00 73 00   ..m.a.s.
0030: 74 00 65 00 72 00 00 00   t.e.r...

------------------------------------------------------------------------------------

"Administrador"  is an Active Directory user with Admin rights everywhere.

I assume it is an error connecting to the 'master' database ??    The user has admin rights in that database, and I can query on that database with MS SQL Management Studio.  

This error always comes before two other errors  of Sharepoint, which are the following error twice:

Error in LoadBalancer.RegisterLauncher: remote name: 'servername' could not be resolved.  (Source: Office Sharepoint Server, EventID: 6102, Level 2, Task: 1561)

I tried checking the admin rights of user 'Administrador' and re-starting manually all the Application Pools in IIS, but the rights seem to be OK and I still get the 503 error when browsing any website...


Any hint what could be the problem?    Thanks in advance...
0
Comment
Question by:McGregor09
  • 7
  • 5
  • 4
  • +1
18 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 100 total points
ID: 24743291
are you sure databases you are trying to access is mapped with the login "Administrator"?
0
 

Author Comment

by:McGregor09
ID: 24743315
The thing is that it is mapped to all necessary databases, including master, model, and all CRM and  Sharepoint-realted...

The  additional information in the SQL Server error means that the error takes place trying to connect to the 'master' database?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24743337
may be, one of the possible reason.

can't you check what server role your "Administrator" account has? are you manually able to connect to all databases with "Adminstrator" login and execute queries?
0
 

Author Comment

by:McGregor09
ID: 24743387
It has all the server roles assigned.  I can connect through the MS SQL Server Management Studio to any database and make queries, no problem with that.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24743439
Have you checked the SQL Server Data Directory isnt full?


>>"Administrador"  is an Active Directory user with Admin rights everywhere.
Also - slap on the wrists for running everything as a DA. Change everything to service accounts with limited permissions.
0
 

Author Comment

by:McGregor09
ID: 24743461
The drive where SQL data is stored still has 261 GB free.

I know assigning all admin rights to one user is not right, it is just testing if it is a permission problem on the applications.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24743468
In SQL, run 'xp_readerrorlog' and what does that come back with ?
0
 

Author Comment

by:McGregor09
ID: 24743488
It contains with these two errors continuously :

LogDate      ProcessInfo      Text
2009-06-30 10:57:01.040      NULL      Error: 18456, gravedad: 14, estado: 38.
2009-06-30 10:57:01.040      NULL      Login failed for user 'INSERMEDICA\Administrador'. Motivo: no se puede abrir la base de datos explícitamente especificada. [CLIENTE: <local machine>]  

English is "Due to: the Database explicitly specified cannot be opened"
0
 
LVL 13

Accepted Solution

by:
St3veMax earned 250 total points
ID: 24743496
In Management Studio, Expand Databases - They all look normal - No (Offline, etc) next to the DB Name ?

Under Security, Find your account, what role is assigned to that DB ?

I'm assuming the account isnt locked out?

0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:McGregor09
ID: 24743511
All databases are Online, nothing special.

It actually has all the roles (all bulkadmin,      dbcreator,       diskadmin,      processadmin,     public,     securityadmin,     serveradmin,     setupadmin,     sysadmin)

And nope, it is not blocked.
0
 
LVL 13

Expert Comment

by:St3veMax
ID: 24743529
Only needs to be one role, sysadmin - this covers all of the other roles.

What happens if you try and connect to Log into the server under that account?

Also, Log into SQL Server Management Studio, Right click on the Server Name, go to Properties, under security, what authentication mode are you in ?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24743537
Double check these

> In logins check/create a login for 'INSERMEDICA\Administrador'
> For that login, grant dbo on whatever database needs to be accessed

If the above does not work, that means you problem is elsewhere than in SQL Server security.  Let us know
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24743546
<<It actually has all the roles >>
As mentionned,sysadmin is he maximum.  Your problem may be related to Kerberos.  See if you can switch to SQL based security to give you time to troubleshoot...hth
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24743554
To make sure, I'd drop and recreate the login user but only with dbo writes on the database.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24743559
You may want to double check you system event log to see if you don't have Kerberos related issues.  In that case, you'd to solve these first...
0
 

Author Comment

by:McGregor09
ID: 24743661
When connecting to the SQL Server Management Studio it is through Windows Authentication.

There are no Kerberos issues  at all. And all the security events in the event viewer are correct.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 150 total points
ID: 24743674
<<There are no Kerberos issues  at all. >>
Just double checking.  Have you tried to drop recreate the login.  Also you should consider refreshing your active directory credentials.
0
 

Author Comment

by:McGregor09
ID: 24743742
At the end, while chekcing all that, I searched for the parameter of an error I saw in the WAS service, and I found the following page:

http://blogs.msdn.com/jmacleod/archive/2008/06/25/iis7-sharepoint-2007-fails-with-503-service-unavailable-errors.aspx

At the end, it seems the failure is related to the Sharepoint configuration. Regarding the Office Sharepoint Server Search, when selecting the option "Use a dedicated web front end computer for crawling" instead of  "Use all web front end for crawling", some IPv6 entries are added in the HOSTS file. These entries caused all the side effects.

So, setting that Sharepoint option back to its previous value made everything work again.

Thanks everybody for your effort!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now