MS SQL server loosing conectivity
Posted on 2013-07-01
I have a strange problem at one of my clients.
This client has one Hell of the SBS 2008, pimped-up to the nonsense. At first, they bought SBS 2008 from me, explicitelly said they do not need anything else, just a plain File server + domain controller. They requested me to set it up, then refused to pay for monthly SLA.
Ok, it has been 3 years since, and they called me to take a look at their SBS 2008, saying they have problems with SQL.
Woooohooo...I have never seen such a pimped up SBS :)
It has, beside SBS functionality:
- 1 MySQL database
- 1 Firebird SQL
- 1 MS SQL 2000 instance
- 2 MS SQL 2005 instances
- 5 MS SQL 2008 instances
- 1 Oracle VirtualBox virtual PC inside
- all volumes and shares have "Everyone" with Full Permissions granted
Now, let's skip debate whether we should laugh or cry, and also must avoid any EXPENSIVE tasks. They do not have money, so 500 EUR is top they can pay for repair. Othervise they'll just switch back to work on each workstation separately.
Now, THE PROBLEM
All SQL databases were working fine for 3 years, until 2 weeks ago. Then SBSMonitoring DB got filled up to 4GB, which is top, and it begun drawing red circles in Event Logs.
At that moment most probably SOMEBODY did something nasty to SQL server, but there are 5 admins from 5 different applications, and it is impossible to know who did what.
The fact is, that from there on SQL instances begun refusing connections. Some applications worked for up to 5 minutes, then threw SQL Connection Error and froze.
Now, they called me.
First I sanitized blown up SBSMonitoring database, which I simply copied over from healthy source, put it into proper folder and started up SQL instance. All went fine (ok, SBS Console is unaware of server status, but doesn't matter).
But this did not solve SQL Connectivity Error.
I did some more insight excursion into SQL Management and found out, that most of SQL Admin roles simply vanished from SQL --> Security --> Logins!!!??? Simpy they were not there.
I called tech guy from one of application, and we re-created Admin role for their app in SQL...and Voila...this application raised up from dead. Working fine.
I thought kewl, we've got it...and recreated all other missing Admin roles...
...but this only repaired 3 out of 5 applications, which are using some SQL instance.
Now, the situation is:
- if run FROM SERVER directly, the application works fine
- but if run FROM CLIENT computer, some SQL error pops-up. Meaningless SQL error, different for each application.
Any idea what was going on with that server?