0

Whilst trying to restore a "master" backup file (on my Windows Server 2003 / SQL Server 2005) machine I've dropped a clanger and re set the number of users from "0" (unlimited) to "1" in the SQL enterprise manager console (ignoring the warning that Enterprise Manager shows about no longer being able to use the management console if I did this !).

Can anyone explain how to reverse this? I'm sort of expecting that it will have to be done from a command prompt somehow?

I have an identical twin "standby" Proliant ML350 as a backup for a Sage enterprise system. I periodically backup the SQL Server databases from Sage and restore them to my standby machine. This has worked well for years - but you do have to restore the "master" databases whilst in single user mode. In a moment of stupidity I thought I'd try doing this by altering the setting mentioned above rather than my usual method.

Improve this question
5
  • 5
    Obligatory note that Windows Server 2003 and SQL Server 2005 have been end of life for 8+ years; it is long past time you got that environment upgraded by now.
    – Thom A
    Commented Jul 3 at 12:20
  • 1
    login using a DAC and change the setting back. Btw, do you mean master backup as in the "master" database, which is a system database, or "master" as in "main database"?
    – siggemannen
    Commented Jul 3 at 12:25
  • @siggemannen is referring to learn.microsoft.com/en-us/sql/database-engine/configure-windows/… use sqlcmd command line for it, that way you only get one connection into it rather than multiple. But it's probably actually set in the command line arguments for the service itself, using the -m arg, see learn.microsoft.com/en-us/sql/database-engine/configure-windows/… just remove that arg in services.msc.
    – Charlieface
    Commented Jul 3 at 13:24
  • 1
    To expand on the "end of life" note: these systems have not had any updates in all that time... not even critical security patches. It's dangerous and irresponsible to continue using them; the kind of thing a lawyer is likely to eventually refer to as "gross negligence".
    – Joel Coehoorn
    Commented Jul 3 at 14:03
  • For further context, one of those (big) "critical security patches" that @JoelCoehoorn references is TLS1.2. Only SQL Server 2008 onwards got a patch for that; SQL server 2005 has no support for TLS1.2 or later. TLS 1.1 and prior are as good as considered compromised/insecure nowdays.
    – Thom A
    Commented Jul 3 at 14:29

1 Answer 1

Reset to default
2

Set the firewall on the server to block incoming connections for port 1433 from outside (need to still allow from localhost), and then restart the SQL Server service. This will keep others from grabbing that one available connection. Now you should be able to use SQL Server Management Studio (or gasp Enterprise Manager) to connect to the database to fix the configuration issue. Then put the firewall back to normal when you're done. If you have to, you could also disconnect the machine from the network and do this at the console.

But you have a MUCH LARGER ISSUE HERE. It's NOT AT ALL OKAY to keep running these out-of-support systems. Migrating to a newer (supported!) system that still gets regular security patches needs to be a priority.

Everyone gets stuck now and then with a system that goes a little past date. The project to replace it runs long, the funds get pulled by higher ups for another emergency, it was included with a vendor app and somehow missed inventory cycles so you didn't know you had it... lots of things can make this happen. I (finally) retired my last Server 2008 R2 machine last week, and this was more than a year past when I wanted. But Server 2003 is excessive. It's not okay.

You also might not have direct control of the resources needed to make this happen, but it seems like you're at least in a position where it is your job to help those above you understand why this is critical.

Improve this answer