Login failure 2

[micang]

SQL 2000

Hi All,

Sorry for vagueness. We have a desktop pc running SQL 2000 to hold data. Yesterday all was fine. This mornign I tried to login to the server and get "Cannot open user defined database. Login failure."

It won't connect to the server at all. I am new to SQL and would appreciate any guidance on what the next step is to try and solve this.

Many thanks in advance.

Michael

 

[ptheriault]

Have you checked to make sure the services are running?


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

Yes, the services are running. When I try to connect with QA or EM, it gives the above error.

Thanks

Michael

 

[ptheriault]

Is there another user that you can try?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

Yep, 2 of us tried and we get the same error. I kinda don;t know enough to know what to do next, other rhan reinstalling SQL 2000 from scratch!

Thanks

Michael

 

[ptheriault]

Have you tried to login as 'sa'?
Have you check you log?
I don't know your install path but it may be something like this.

c:\program files\Microsoft SQL Server\msql\LOG

In the log folder read the ERRORLOG. Are all databases available?

Also,
Before you re-install you should at least try to restart the SQL Services.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

I tried sa, no luck, this is the last last dated log:

2007-04-26 13:26:56.71 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-04-26 13:26:56.73 server Copyright (C) 1988-2000 Microsoft Corporation.
2007-04-26 13:26:56.73 server All rights reserved.
2007-04-26 13:26:56.73 server Server Process ID is 1844.
2007-04-26 13:26:56.73 server Logging SQL Server messages in file 'd:\microsoft sql server\MSSQL$ADS\log\ERRORLOG'.
2007-04-26 13:26:56.76 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2007-04-26 13:26:58.34 server SQL Server configured for thread mode processing.
2007-04-26 13:26:58.56 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2007-04-26 13:26:59.96 spid3 Starting up database 'master'.
2007-04-26 13:27:00.93 server Using 'SSNETLIB.DLL' version '8.0.311'.
2007-04-26 13:27:00.93 spid5 Starting up database 'model'.
2007-04-26 13:27:00.98 spid3 Server name is 'ADSXP03\ADS'.
2007-04-26 13:27:00.98 spid3 Skipping startup of clean database id 4
2007-04-26 13:27:00.98 spid3 Skipping startup of clean database id 5
2007-04-26 13:27:00.98 spid3 Skipping startup of clean database id 6
2007-04-26 13:27:00.98 spid3 Skipping startup of clean database id 8
2007-04-26 13:27:00.98 spid3 Skipping startup of clean database id 9
2007-04-26 13:27:00.98 spid3 Skipping startup of clean database id 11
2007-04-26 13:27:00.98 spid3 Starting up database 'ADS'.
2007-04-26 13:27:01.56 spid3 Bypassing recovery for database 'ADS' because it is marked SUSPECT.
2007-04-26 13:27:01.59 spid5 Clearing tempdb database.
2007-04-26 13:27:02.26 server SQL server listening on 10.10.6.52: 3898.
2007-04-26 13:27:02.26 server SQL server listening on 127.0.0.1: 3898.
2007-04-26 13:27:02.64 spid5 Starting up database 'tempdb'.
2007-04-26 13:27:02.79 server SQL server listening on TCP, Shared Memory, Named Pipes.
2007-04-26 13:27:02.81 server SQL Server is ready for client connections
2007-04-26 13:27:02.92 spid3 Recovery complete.
2007-04-26 13:27:07.00 spid51 Starting up database 'msdb'.
2007-04-26 13:27:09.90 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.

I am not sure what all this means...but I see the word SUSPECT - which can't be good!

Thanks

Michael

 

[ptheriault]

You right. That is all bad! You need an account to login to instance with. In this case it should be sa. Try another reboot then check out your log again. By the way what time is it where you are?

You server was started at 2007-04-26 13:27:09.90. Is it 1:30 PM there?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

I am in London.

When I try to register the server, it gives me the error that it can't register because it can't log into the default database, login failure etc.

It's been only me accessing it for the past month and I have been using Windows Authentication.

I restarted the server and this is the error log file:

2007-04-26 14:01:10.89 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2007-04-26 14:01:10.89 server Copyright (C) 1988-2000 Microsoft Corporation.
2007-04-26 14:01:10.89 server All rights reserved.
2007-04-26 14:01:10.89 server Server Process ID is 468.
2007-04-26 14:01:10.89 server Logging SQL Server messages in file 'd:\microsoft sql server\MSSQL$ADS\log\ERRORLOG'.
2007-04-26 14:01:10.92 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2007-04-26 14:01:11.03 server SQL Server configured for thread mode processing.
2007-04-26 14:01:11.03 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2007-04-26 14:01:11.06 spid3 Starting up database 'master'.
2007-04-26 14:01:11.21 server Using 'SSNETLIB.DLL' version '8.0.311'.
2007-04-26 14:01:11.21 spid5 Starting up database 'model'.
2007-04-26 14:01:11.22 server SQL server listening on 10.10.6.52: 3898.
2007-04-26 14:01:11.22 server SQL server listening on 127.0.0.1: 3898.
2007-04-26 14:01:11.25 spid3 Server name is 'ADSXP03\ADS'.
2007-04-26 14:01:11.25 spid3 Skipping startup of clean database id 4
2007-04-26 14:01:11.25 spid3 Skipping startup of clean database id 5
2007-04-26 14:01:11.25 spid3 Skipping startup of clean database id 6
2007-04-26 14:01:11.25 spid3 Skipping startup of clean database id 8
2007-04-26 14:01:11.25 spid3 Skipping startup of clean database id 9
2007-04-26 14:01:11.25 spid3 Skipping startup of clean database id 11
2007-04-26 14:01:11.25 spid3 Starting up database 'ADS'.
2007-04-26 14:01:11.35 server SQL server listening on TCP, Shared Memory, Named Pipes.
2007-04-26 14:01:11.35 server SQL Server is ready for client connections
2007-04-26 14:01:11.56 spid3 Bypassing recovery for database 'ADS' because it is marked SUSPECT.
2007-04-26 14:01:11.56 spid5 Clearing tempdb database.
2007-04-26 14:01:12.19 spid5 Starting up database 'tempdb'.
2007-04-26 14:01:12.27 spid3 Recovery complete.

I wish I was on a beach somewhere!!!

Michael

 

[ptheriault]

It would be my guess that your login's default databaes is ADS. Since the database is marked suspect you can't login.

Let's try to start SQL Server in single user.

To start SQL Server in single user mode:
1. Start a command session (start > Run > "cmd" [Return]).
2. To ensure that SQL Server is not running type:
net stop MSSQLServer
This may say that this will also stop another service (typically SQLSERVERAGENT), in which case allow it to continue. It will also indicate if the service is already stopped.
3. To start SQL Server in single user mode type:
sqlservr -m
If this is not recognised then cd to 'C:\Program Files\Microsoft SQL Server\MSSQL\BINN' and try again.

Then try to login.


Do you have a backup of that database? You may have no choice but to re-install and restore the database. If you do that, I would suggest that you install sql with mixed mode. Write down the 'sa' password and keep it safe somewhere.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Here's what I think...

I think your login has the default database set to ADS, but since ADS is marked as suspect, you can't login.

I also think that you could probably use OSQL to attach to the master database and then change your default database to master. Once this is done, you could probably log in just fine.

I'm no expert (with osql), but here's what I think you need to do...

Click Start -> Run
Type CMD
click ok

At command prompt...

Type:
osql -S ADSXP03\ADS -d master -E

This should connect you to the master database. You should see a prompt like this...
1>

Now, you will need to figure out what your login user is...

At the prompt, type...
[tt][blue]Select suser_sname()[/blue][/tt]
Hit Enter, type GO, hit enter again.


Now, you will need to change your default database using the following command.

sp_defaultdb '[!]YourLoginHere[/!]', 'master'

Hit Enter, type go, and hit enter again.




-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Good call George. I thought OSQL would also try to authenticate against the default db.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Hi Paul,

Thanks so much for your efforts.

I unonstalled and reinstalled the server instance. I had 3 databases on the server - 2 I managed to attach no problem. the main one (as you say ADS) did not attach. I have a back up of it and am currently restoring it...fingers crossed.

Paul, thanks so much, you've been a star.

Michael

 

[gmmastros]

It might. I'm a little fuzzy here, but I thought the authentication was seperate from user database.

Here's what I think happens at log in. (Please correct me if I am wrong).

1. Your login is validated (either with Windows Authentication or SQL Authentication).
2. You 'switch' to the default database if no database is specified.

I suspect that the login is authenticating properly, but step 2 is throwing the error because the database is suspect. By using OSQL and specifying the database, step 2 (connecting to the database) won't fail because master is OK.

Make sense?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

No problem Michael. Although I think Georges solution would may have worked for you too. I did't even think of that. I would leave you default db as master so this doesn't happen in the future.

Good Luck!

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

And thansk to George too, I did not know that and will ook into that. I think having it going to master by default is propbably a good thing in my situation.

Man, this is a huge learnign curve.

Michael

 

[micang]

George,Paul,

I am back up and running, thanks for all the help.

I think you are both correct, I probably could have avoided the re-install by following George's suggestion (and then just restored the ADS database) as the ADS database was the one with errors.

I now have let the login to master, I think it's much safer.

Thanks so much, have learnt a lot.

Michael

 

[micang]

Hi All,

I have reinstalled SQL 2000 SP2. Logging in on the server itself is fine, but logging in via the netwirk is giving the same error. I remember I think there is a patch to install to solve this, again, apologies for the vagnuess, but does anyone know which patch this is?

Many thanks.

Michaael

 

[ptheriault]

You should patch your SQL Server 2000 up to sp4.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[micang]

Thanks Paul, will do.

Michael