| Author |
Topic |
|
OSUJeff
Starting Member
7 Posts |
 Posted - 2002-04-08 : 16:21:21
|
Preface: Running SQL 7, with the latest service pack, running on Windows 2000, all service packs and security updates (from Windows Update) have been applied.At 2:00pm today, I received a call from one of our clients saying they were getting a database error when trying to use their website. I went to Query Analyzer to run a SELECT on one of the tables, and the table wasn't there.I immediately went to Enterprise Manager to look at the status of our other databases. Out of approximately 30 databases, only 11 remained. No trace of them exists in Enterprise Manager. I checked the Windows 2000 event log, and nothing seems out of the ordinary for the time period in question.The 2nd thing I did was check the SQL/Data directory to see if the physical MDFs and LDFs were there for the missing DBs. They were all there, with a timestamp of 2:08pm today. The next action taken was to stop-start the MSSQL service, in the hopes that these databases might be rebuilt. That failed.The next action taken was to reboot the SQL server - again, that failed. The missing databases are still missing.Here is my question for the forum: How can I go about finding out what has happened to these databases?I am not willing to rule out any possibility at this point. A team of approximately 12 developers all have the SA password. It's possible that a mistake or malicious attempt was made to delete these DBs. A hack or a system failure is also possible. But I have no idea how to determine what happened.I looked at the SQL server log files, and here is what I found: 2002-04-02 12:58:51.44 spid13 Opening file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-02 12:58:51.44 spid13 Opening file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-02 12:58:51.44 spid13 Starting up database 'CollegeBoard_Test'.2002-04-08 14:00:24.12 spid51 Closing file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-08 14:00:24.14 spid51 Closing file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-08 14:00:24.17 spid51 Opening file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-08 14:00:24.17 spid51 Starting up database 'CollegeBoard_Test'.2002-04-08 14:00:24.21 spid51 Opening file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-08 14:00:31.82 spid51 Closing file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-08 14:00:31.84 spid51 Closing file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-08 14:00:35.68 spid51 Closing file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-08 14:00:35.68 spid51 Opening file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-08 14:00:35.70 spid51 Closing file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-08 14:00:35.70 spid51 Opening file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-08 14:00:35.92 spid51 Opening file C:\MSSQL7\data\CollegeBoard_Test_Log.LDF. 2002-04-08 14:00:35.92 spid51 Opening file C:\MSSQL7\data\CollegeBoard_Test_Data.MDF. 2002-04-08 14:00:35.92 spid51 Starting up database 'CollegeBoard_Test'.2002-04-08 14:00:48.74 spid51 Closing file C:\MSSQL7\data\Datawarehouse Demon_Data.MDF. 2002-04-08 14:00:48.76 spid51 Closing file C:\MSSQL7\data\Datawarehouse Demon_Log.LDF. 2002-04-08 14:00:48.79 spid51 Opening file C:\MSSQL7\data\Datawarehouse Demon_Data.MDF. 2002-04-08 14:00:48.79 spid51 Starting up database 'Datawarehouse Demon'.2002-04-08 14:00:48.81 spid51 Opening file C:\MSSQL7\data\Datawarehouse Demon_Log.LDF. 2002-04-08 14:00:49.39 spid51 Closing file C:\MSSQL7\data\Datawarehouse Demon_Data.MDF. 2002-04-08 14:00:49.40 spid51 Closing file C:\MSSQL7\data\Datawarehouse Demon_Log.LDF. 2002-04-08 14:00:51.29 spid51 Closing file C:\MSSQL7\data\Datawarehouse Demon_Data.MDF. 2002-04-08 14:00:51.29 spid51 Opening file C:\MSSQL7\data\Datawarehouse Demon_Data.MDF. 2002-04-08 14:00:51.31 spid51 Closing file C:\MSSQL7\data\Datawarehouse Demon_Log.LDF. 2002-04-08 14:00:51.31 spid51 Opening file C:\MSSQL7\data\Datawarehouse Demon_Log.LDF. 2002-04-08 14:00:51.49 spid51 Opening file C:\MSSQL7\data\Datawarehouse Demon_Data.MDF. 2002-04-08 14:00:51.49 spid51 Starting up database 'Datawarehouse Demon'. This continues for approximately 14 minutes, going through almost all of our databases on this server. It opens and closes almost all of them, and there is no pattern - databases that are currently missing, and databases that are currently still there. The next thing in the log is the shutdown of the SQL service.If you notice, prior to this strange opening & closing of DBs, there is nothing. The last data logged was from 4/02.To recap my question: Is there any sort of auditing I can look at to find out what happened to these databases? I find it hard to believe that SQL would "misplace" these databases.Please, any suggestions are welcome. We have no real SQL DBA on staff, just us programmers. Thank you in advance. - Jeff C Dallas, TXEdited by - OSUJeff on 04/08/2002 16:23:41 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-04-08 : 16:26:47
|
Hello Jeff, I'm also in Dallas  Is it the Databases that are missing or just tables?If it is just the Databases but the files still exist did you try to use the sp_Attach_DB stored proc?DanielSQL Server DBA |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-08 : 16:27:16
|
| If the physical files are still there, no need to panic, just re-attach them ( sp_attach_db ).Your log output suggests that the databases were set to auto_close ( you should check the other databases and uncheck this option, you hardly ever want it ).As for why the databases aren't registered w/ your SQL Server installation ... without a auditing product, or an actively running profiler trace, i don't know of a way.setBasedIsTheTruepath<O> |
|
|
|
OSUJeff
Starting Member
7 Posts |
Posted - 2002-04-08 : 17:11:39
|
quote:
Hello Jeff, I'm also in Dallas Is it the Databases that are missing or just tables?If it is just the Databases but the files still exist did you try to use the sp_Attach_DB stored proc?DanielSQL Server DBA
Daniel, thank you! The databases were missing but the physical files were still there. sp_attach_db worked like a champ. Since you're in Dallas, there's a free lunch offer on the table if you want it!That still doesn't answer my question as to what might have happened. But I'll save that one for tomorrow. whewThanks again! |
|
|
|
sqlxpert
Starting Member
11 Posts |
Posted - 2002-04-08 : 18:09:01
|
| hello JeffIt seems that somebody had detached your databases from the server, you need to check who all got this privalege, either your sa password is leaked or somebody from the doman admins NT group had entred. For prevention you need to close any unauthorized access to your server. ALso you were lucky that dbs were detached not deleted. SQL Server can never miss a database on its own.AB |
|
|
|
OSUJeff
Starting Member
7 Posts |
Posted - 2002-04-08 : 18:14:47
|
| Status as of 5:15pm:All databases are up thanks to sp_attach_db. None of the physical files were deleted.None of the databases are currently set to Auto-Close. I don't believe any of them have ever been set to Auto-Close.As for the SA password, only the developers and a couple of the IT managers have it. We don't seem to have any disgruntled employees, but you never know. I'll recommend changing the SA password first thing tomorrow morning.Is there anything else any of you can think of that would cause these databases to become detached? (besides user intervention - whether an employee or a hacker?) |
|
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-04-08 : 18:33:59
|
| Jeff,As sqlxpert pointed out, anyone who has admin privileges on the server that the sql server resides on have permissions to the databases as well. The purpose of this is that Administrators of the machine are supposed to know what they are doing and since the server resides on their machine, they should have full control of the server.Next stop, the NT Administrators.Jeremy |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-04-08 : 19:28:54
|
| You should give your developers only the access they need to the database - not sysadmin. SQL provides a number of security roles that will allow them to do their work but not perform many of the system functions that cause problems. You should have one or two people handle sysadmin roles to minimize risk. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-04-08 : 20:12:54
|
| Do you have a third party backup product running?Maybe something is connecting, closing the files, backing them up ....It's not a good thing to do normally but I would leave the profiler running to log commands to a text file. As there is a sql server connection causing tis it should give some info.Also make sure you are taking daily full backups to disk - and I would also consider incrementals / log backups until this is resolved.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-04-09 : 01:36:14
|
| There is nothing that would just randomly detach or delete your databases. If that happened we wouldn't sell many SQL Server licenses, that is for sure.Outside of having a profiler trace running, there is unfortunately no way to know what happened.Maybe one of the developers discovered the sp_detach_db proc, and wanted to see what it did?You should heed the advice of smccreadie and give developers only the necessary permissions. In SQL 2000 we have more auditing features, but they would have to be enabled.-Chad |
|
|
|
OSUJeff
Starting Member
7 Posts |
Posted - 2002-04-09 : 10:07:52
|
| nr - Good suggestion. I'll check with our admins and find out if there were any backup jobs running at that time.smc - Unfortunately with no real true SQL DBA on hand, our developers know more about SQL than our admins. I will definitely start some reading on SQL roles and permissions. It's never a bad idea to give anyone only the access they need.chadmat - Based on what I've heard here and what I already know, my opinion is that it was either user error or malicious intent. I doubt seriously that it was malicious intent. If it was, databases probably would have been deleted rather than detached. Unfortunately, no one is fessing up to user error.Again, thanks to everyone for their help in this matter. I'll begin reading more on SQL server security and on the SQL 2000 auditing capabilities.Thanks. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-04-09 : 10:19:00
|
| It could be that one of the developers or admins was trying out some new stuff that they read about and then got themselves into trouble... "Hmm, detach a database... that sounds cool."I've had in the past where people that were not in the IT dept were trying to Hack into my Oracle server with the default passwords (SYSTEM/MANAGER and SYS/CHANGE_ON_INSTALL) because they were taking some Oracle classes in College and thought it might be cool to see what we had setup...So with that in mind your SA login should be exclusive give your nt trusted login SA rights, disable the BUILTIN\ADMINS login and change the SA Password...DanielSQL Server DBA |
|
|
|
Christine Glew
Starting Member
7 Posts |
Posted - 2002-07-08 : 06:27:50
|
| Just saw your problem when I was looking for something else. I have found that if you run the database copy wizard and the copy fails for some reason (usually because someone is using the source database) then this can leave databases detached. This is because the wizard works by detaching the databases, copying them and then attaching them again. |
|
|
|
davy_boy2000
Starting Member
28 Posts |
Posted - 2002-07-08 : 11:22:54
|
As per Christine's comments, I have noticed the missing databases after using the Copy Database wizard. It usually happens when I copy from SQL 7.0 to 2000 using the Enterprise Manager on the 2000 box. For some reason, (not due to any errors, as none were displayed), the databases on the SQL 7.0 server were not re-attached after the copy wizard finished. I have had two occurrences of this, (and freaked  the first time I saw it, as I was copying from a prod server to a new prod server...!!!)This might have been what had happened, the developers found the copy wizrd and thought, "that looks handy!!!" |
|
|
|
|
Please help! Possible SQL hack