Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2005-06-13 : 13:05:11
|
| Something happened on the server and nothing could connect to SQL - even logging on as ADMINISTRATOR on the server itself :-(I did manage to login as SA using the password from the safe!All the Windows NT Authentication users had lost their default database - including the SQL Agent Service (which then proceeded to record a stack of login-failed errors in the log).Reinstating all the users to master, or some other apporpriate default DB, has fixed the problem. I'm curious what the cause might have been, and whether there is any other collateral damage.Some details in case it sparks an idea:At around that time a new domain was being installed, and users were being switched over to the new domain.Immediately prior to the first login-failure there are three messages in the log:spid55 2005-06-10 09:58:39.14 Process ID 57 killed by hostname MY_SERVER, host process ID 3100.spid55 2005-06-10 09:58:39.14 Process ID 62 killed by hostname MY_SERVER, host process ID 3100.spid55 2005-06-10 09:58:39.14 Process ID 58 killed by hostname MY_SERVER, host process ID 3100.Thereafter all NT logins fail. The hourly log backups do not run.2005-06-11 00:14:54.81 the daily Full Backup (Maintenance Wizzard) runs [but the corresponding LOG backups do not]2005-06-11 01:00:05.21 MY_OLD_DOMAIN\Administrator login fails, and the LOG backups do not run2005-06-12 10:15:43.53 the server is shutdown for scheduled reboot (part of the installation of new domain, and new switch)2005-06-12 10:40:15.59 Server restarted2005-06-12 10:41:00.73 Login failed for user 'MY_OLD_DOMAIN\SQL_Server_Service'.No backups run.2005-06-13 15:30:04.73 SQL Server is terminating due to 'stop' request from Service Control Manager.[next log file]2005-06-13 15:30:06.79 initconfig: Error 32(error not found) opening 'C:\Program Files\Microsoft SQL Server\MSSQL\data\master.mdf' for configuration information.2005-06-13 15:30:06.79 Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.2005-06-13 15:30:06.79 Server Process ID is 4240.Three more lines ("All rights ..." "Copyright ..." "MS SQL Server 2000 - 8.00.760 ...", then nothing ...[next log file]2005-06-13 15:30:07.81 Starts up OKLogins fail for all NT Users, including MY_OLD_DOMAIN\SQL_Server_Service[Fix up default databases]2005-06-13 16:11:18.73 SQL Server is terminating due to 'stop' request from Service Control Manager.2005-06-13 16:11:20.54 Starts up OKSeems to be OK; no login error messages; client applications connect OKThere are two other MSDE instances on this box - BackupExec and SophosAntivirus - [but they've been there for ages] ...ThanksKristen |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-06-13 : 13:40:00
|
| Yup, MY_SERVER = The SQL box itself |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-13 : 14:23:58
|
| Did you happend to drop and restore the database that the logins defaulted to? The users default DB is stored in table master.dbo.sysxlogins, so if it got a new ID after the restore, that would have been a problem.CODO ERGO SUM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-13 : 14:37:31
|
| well if the sql login is my_old_Domain\UserNameand the user is now my_New_Domain\UserName...that can't be good.After re-setting the default db, are their db permissions still intact? Which domain does their Loginname, or name column in sysLogins have?Be One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-06-15 : 12:02:01
|
| "Did you happend to drop and restore the database that the logins defaulted to"Not that I know of, but good point.<checks>msdb.dbo.restorehistory has nothing recent (well it does actually, I think not relevant but definitely WEIRD! - see below)However, BUILTIN\Administrators would [most probably] have been set to "master.my_old_Domain\administrators, "my_old_Domain\Domain Users" amd "my_old_Domain\SQL_Server_Service" likewise [I can't know that for sure, but its likely] and thus not open to restoration.The my_old_Domain\UserName still exists, just that that user's profile has been migrated to my_New_Domain\UserName.Any ideas on this?msdb.dbo.restorehistory shows that at midnight each day "master4IDR" and "model4IDR" are being restored. I haven't spotted that before. Google says these are from Veritas - but no database of that name exists, and I thought the Backup was using a difference MSDE instance ... probably unrelated, the logs indicate its been doing this for monthsKristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-15 : 14:01:19
|
| Problems like these are why I always make the default database master.At least you know it will always be there, or if it isn't, you have much bigger problems.CODO ERGO SUM |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-06-15 : 14:42:32
|
| >>The my_old_Domain\UserName still exists, just that that user's profile has been migrated to >>my_New_Domain\UserName. Any ideas on this?If I was doing this I would probably have: (of course no one lets me near production data so take this for what it's worth)And this is assuming there is a problem having the user's actual domain not match the domain part of the loginName.Cursor through all master..syslogins where name like 'my_old_domain\%', fetching into sysname variables: @name, @password, @defaultdb. Then for each login, sp_addlogin --create a new login (my_new_domain, orig_password and orig_defdb)@login='my_new_domain\<name>',@passwd=@password,@defdb=@defaultdb,@encriptOpt = 'skip encryption'then for each database where that login is a user: --assoc dbuser to new login namesp_change_users_login 'update_one', '<name>', 'my_new_domain\<name>'then drop the my_old_domain loginsBe One with the OptimizerTG |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-06-16 : 17:51:56
|
Sorry, I didn't describe this very well. From the workstation standpoiont a user is now on NewDomain, instead of OldDomain.The logins on the SQL box have not changed (the box is going to become redundant, and there are only a couple of old databases on there).So the "profile migration" I referred to related to NT, not SQLMVJ: "I always make the default database master"Well ... in this case the "DBA" and Administration accounts were defaulted to MASTER.Users with one database each, and an Access application, were defaulted to their "home" database - 'coz it makes setting up the ODBC a bit easier, particularly if the DB moves (just adjust the ODBC Connectoid, instead of having to reLink all the tables in Access).BUT, on our Dev machines we default everyone to a "sacrifice" database - lots of times developers just type "CREATE PROCEDURE ...", or DELETE / UPDATE / whatever and forget to change DB first - and they are a pain when they get left behind in master ("Is this Sproc called "JunkStuff" really still needed?")  Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-16 : 18:53:14
|
That's not an issue for me, because I don't give developers any Admin priviliges at the server level even on development servers, so they can't do those things.quote:
Originally posted by KristenBUT, on our Dev machines we default everyone to a "sacrifice" database - lots of times developers just type "CREATE PROCEDURE ...", or DELETE / UPDATE / whatever and forget to change DB first - and they are a pain when they get left behind in master ("Is this Sproc called "JunkStuff" really still needed?")
CODO ERGO SUM |
|
|
|
|
|
|
|
|
Users lost default database