SQL 2005: Logon Failure for SQL Services

[fuzzyocelot]

Hey everyone!

We have a big problem and it's driving us nuts!

SQL 2005 SP2
Windows 2003 Server SP2

One of our very important SQL servers “crashed” the other day, and we’re trying to figure out what happened. All I really know is one of our network guys was on the server trying to fix or do something with the Veritas ExecView software and SQL stopped running. This was very bad because the users were in the middle of running payroll. Anyway, no one is sure why SQL stopped running. They rebooted the server and SQL wouldn’t start! That’s when they called me. The SQL services were all set to start automatically. I couldn’t find any errors in the Event Viewer. Apparently the network guy tried to restart the services using Services under Component Management. I told him it was better to use SQL Server Configuration Manager because this is 2005. Anyway, I couldn’t get any of the services to start. They were set to Log On as a local Windows account that is also on the domain. We tried to reset the password but SQL still would’t start. We would get Logon failure messages. So I changed the services to run under the local system account. That got them running so the users could finish payroll. At some point I need to set them back to a Windows domain account. This is new SQL 2005 install that we did a couple of weeks ago. It was running fine until something happened with the Veritas ExecView software. I don’t know much about this Veritas stuff except that it uses SQL Server and backs up the datatbase files to tape.

The only thing I found in the SQL error logs was “2007-05-14 10:00:28.27 spid60 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.” This made me nervous because no one should be running this soft of thing on the server. I checked the configuration options and none of them had been changed.

Another weird thing is that now I can’t access the error logs via Management Studio. When I run “master..xp_readerrorlog”, I get the following error:

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

So I did some digging and found the entry in the registry for the SQL start up. It looks fine. This is also displayed under the Properties for the SQL Server service in SQL 2005 Configuration Manager.

-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

I tried to start SQL via the command prompt using the above start up options and it said it couldn’t find the path. I was in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\. Maybe I typed it in wrong. I typed in this:
sqlservr -dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

I probably should have left the semicolons out.

I was able to get SQL to start this way by using sqlservr –f and I was logged into the server as the right account on the domain.

The error logs are in the right directory too and contain data. I double-checked and this should be the right place. The SQL Agent works fine once I start it as a local system account. So I don’t know what’s wrong.

Also, we are able to log into the server itself onto the domain using the correct login and password.

We tried everything under a KB from MS (

http://support.microsoft.com/kb/282254).

It still doesn’t work.

We tried this KB too:

http://support.microsoft.com/kb/283811

It still doesn’t work.

We’ve removed everything we can find related to the Veritas software within SQL and on the server. We’ve checked the registry up and down and can’t find anything.

The SQL services must run on a domain account otherwise they won’t be able to run replication.

Any ideas? We are stumped!

Thanks!

 

[mrdenny]

First thing is that it doesn't matter what method is used to stop and start the SQL Services. It is a myth that the services should be stoped and started via the SQL Server Configuration Manager. All methods of starting and stoping services do the same thing. They use the Windows API to start and stop the service. The SQL Server Configuration Manager doesn't do anything special that the other methods don't do.

If anyone was playing in the Surface Area Configuration tool that could explain the SQL Agent procedures being disabled.

After you got these error messages:

Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

What was in the event log?

In your post you say that you have a local account which is the same username and password as a domain account. Why is this the case? Are you using the local account or the domain account to start the SQL Service?

What is in the security log?

Removing Veritas shouldn't effect SQL Server much. It certenly shouldn't stop the services from starting.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

Thanks for your post!

I was told by our senior dba that with 2005 we need to use Config Manager and not the Services because of the registry or something along those lines. It's good to know that it shouldn't matter.

Anyway, the xp_readerrorlog works now. We ended up stopping and restarting the SQL services many times yesterday. I'm wondering if that did it or if it worked after we changed the registry per KB 283811. We gave full control to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90 and HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1.

Also, now the replication appears to work. After talking with the client about this, he refreshed my memory that on last Friday the application vendor had us run a couple of T-SQL statements. We're wondering if this caused the problems they had been experiencing. I believe the statements are related to custom stored procedures and tables the vendor implemented. I'll get the statements and post the code later. So at this point I'm not sure if it was the Veritas software that messed things up or if it was the T-SQL code. The replication stopped working Friday afternoon but SQL was fine until Monday morning when the technicain encountered a problem with the Veritas software. Maybe something else occurred. I don't know. I did find some remanents from the Veritas software in the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ folder. I found master$4idr and msdbdata$4idr. I Google'd them and found out they are from the Veritas software. I don't know what it means, if anything. Semantic bought Veritas so our tech guy called Semantic's tech support for help. The person he talked to hadn't even heard of Veritas. So I hope we're not "screwed" here. We're going to get the application vendor involved hoping they know something.

By the way, the event viewer showed no errors whatsoever. Which is very strange. I'll double-check to be sure. The SQL error logs didn't show anything either. The security log showed nothing.

I'm not sure why there's a local account on the server when we need to use the domain account with the same name. I've been told that we need to use the domain account in order for the replication to work. However, the client reports that the replication appears to have worked and the services are running under the "local system" account. So I'm not sure what's going on here.

I apologize if I'm all over the place and I hope I'm not confusing anyone. Normally we have 3 DBAs here but this week I've been the only one here. So I'm feeling a bit overwhelmed. I'll manage, though.

Thanks for the help!

 

[fuzzyocelot]

Here's something else that's interesting. Monday night's database backup jobs failed due to an access denied error. Last night they ran fine. Weird. Now I really wonder if changing the registry fixed something.

 

[mrdenny]

It could have.

Replication will work fine with a SQL Server running as local system. Which ever server controls the replication (distributor for push, subscriber for pull) needs to have the agent running as a domain account, but that's it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

The replication is a merge and the vendor set it up. Of course, it was working great when they left. The distributor and publisher are on the same server. There are tablets that are plugged in nightly and the merge occurs. I'm still new to this whole merge replication stuff, and this agency is the only one of ours that uses replication in this capacity. I have a new problem regarding the replication so I'll start a new thread on it.

Thanks for your help!