During Advanced Database class today I figured out a new way of breaking SQL server. Well, technically I found two ways but who’s counting, right? If all you read is this introduction I’ll cut straight to the TLDR summary: If you have SQL Server installed on a domain server (or installed in a domain), do not remove/delete the domain. If you have any experience with SQL server you may know what I’m getting at but I bet you didn’t guess that I broke it in two separate ways all in one go! If you’re curious,
First, a little background on the situation. One of the things we had to do for the beginning of the course was to install Windows Server 2003. This was no problem, I had been doing it all week due to problems with my server at home anyways. After we had Server 2k3 installed, we had to install two instances of Microsoft SQL Server. To make things more complicated for myself, I decided that I wanted to upgrade my computer to a Domain Controller and see if we could get some replication and/or mirroring going across a few of the servers. When I first set up my domain, I decided that I would make it a subdomain of my main drackir.com domain. I have direct access to the DNS records for the domain so it wouldn’t have been a problem…if there was a way into the school network from the internet. I had a few assignments to do for class, so I set up my SQL Server instances and went about doing them.
As it turns out, there was not a way into the school network from the internet site and we ended up having huge headaches trying to set up a local DNS and getting the domain to propagate properly. So, after a little googling, I found out that you can uninstall a domain by using
dcpromo a second time (who knew?).
At this point, I feel that I should mention that I also set up one of my server instances to use a “Local Account” for running the service and the other one with some users I created under my domain.
Along I went on my merry way of removing the domain. It took a while (and a reboot or two) but finally I had my server back up and was attempting to install a single-label domain (ie. systemroot instead of systemroot.ca) in hopes that that would fix my domain and DNS issues. When installing a single-label domain, it asks you to install a DNS server on the same configures it for you. Before I had a chance to start messing around with the other servers, we received some new labs to work on. So I went, once again, on my merry way to start up SSMS and log into my DBMS’s. I noticed immediately that the remembered connection URL’s were not correct (they were from the old domain). I went ahead and changed them and to my surprise (almost) I couldn’t connect!
What did you do this time?
So here’s the overall deal: two instances of SQL Server installed, both using Windows Authentication. One of which, we’ll call it SQL1, uses a “Local Account” to run from. The other, SQL2, uses (read: used) a user account from the original domain to run.
SQL1: Attempting to connect to SQL1, it seems my login information is not correct, though the server is there. What could this be? Oh wait! That’s right the user accounts were stored in active directory on the old domain…yeah that one that’s gone. Ok, well at least I still have the second server which I’m fairly certain I set up with a backup “sa” account in mixed login mode.
SQL2: Attempting connection to SQL2…no connection, seems the server is turned off. After a quick jump into Configuration Manager I noticed that the services for SQL2 are not running. Attempting to run them generates an error. It seems that since the user who is supposed to run SQL2 was in the old domain, they do not exist and the only way to change it to a different user is to map that user to the new one…IN SSMS!
So there you have it. I broke two SQL Server instances simply by running dcpromo again. I should also mention that even if I could have connected to SQL2, there’s a distinct possibility that I didn’t to set up the “sa” password.
Great, now how do we fix this?
Well, I have no idea. I ended up just reinstalling two more instances of SQL Server. I would love to know if anyone else knows what one would do in this situation. If so drop me a line here.
Note: I don’t think recreating the domain and users would work because when you create a user they are assigned a GUID by the system. The second user’s name and domain may be the same but this identifier will not be.
Oh, I forgot to mention; the reason that I didn’t uninstall the other instances of SQL Server (the broken ones) was because I couldn’t! It seems the user issues propagated all the way to the uninstaller as well. 😀