Single User Mode

[Galaxiom]

I was having problems obtaining a lock on a table in a multiuser database.

I connected as dbo via Management Studio on the host server machine and changed the Restrict Access Option to SINGLE_USER. The warning was displayed that all other connections would be dropped and the database then showed Single User.

I was still unable to obtain the lock so I went to set it back to MULTI_USER and was told I didn't have access. Somehow another connection overrode mine, the dbo on the server who put it into single user mode and became the single user.

How is this possible?

So I rebooted the server and went back into properties. Then decided I would have another go at getting the lock which failed again. Returning to the Properties I was told I no longer had access again.

This really shouldn't happen. Rebooted again and very quickly put it back to MULTI_USER before the mystery competitor got back in.

The only other connection that should have possibly been active at the time was a web server that intermittently queries this database. Would it be able to connect if it was the same user as the dbo? I have not checked this yet. Would seem like a bad setup if it were.

 

[Minty]

It's possible that the web server connection was (probably incorrectly) resetting things.

You'll maybe find that the webserver is connecting using the SA account, and that was performing some dubious operations that it shouldn't be.

Also check that other ports aren't exposed to the outside world. We tried setting up external secured access on one of our SQL servers, and someone managed to get in within about 2 hours. So we stopped that little experiment.

 

[Galaxiom]

Thanks Minty.

Everything is very locked down on our system (financial institution) so I don't think there is anything unexpected getting in.

I've discovered the website connects through a Web Service but it doesn't seem to have an Entry Point as such on the SQL server so I guess it is a bespoke web service that predates the native web services in late model MSSQL Server.

 

[Minty]

Probably using IIS if it's more than a couple of years old.