posted on Friday, April 11, 2008 9:46 AM
by
drodabaugh
Deleted All Logins to SQL Server 2005 and Lost sa Password: You Did WHAT? And BizTalk 2006 Is Using It?
It's been a while since I blogged but I had a circumstance yesterday that I thought was noteworthy. Recently my employer cut our staff to about 1/4 of what it was nine months ago, so all of us in "management" are now hands-on. I've inherited DBA work (since we cut the DBA), something of which I've not done much in the past eight years during a BI career. I have always appreciated what competent DBA's do for those of us in BI development. I'm sure I'll appreciate it more as time progresses.
As the title suggests, I committed an egregious error. I was working on a dev server and needed to reload a database which hasn't yet gone to production. I inherited the database and the application from a consultant (yup, cut him too) and it hasn't been fully loaded. As part of the work, I thought I'd build an "initial load" procedure and add it to the documentation. One of the methods for initialization is to create the database with a script, so I had SSMS write a script. I told it to script the database logins. Did I read the script before punching the go-giddy button? No. In a few seconds, the script errored out saying that I didn't have permission to create logins. Huh? Of course I do! I'm a sysadmin. A few more minutes of sleuthing revealed the disaster: I had deleted every single login in that server. Every last one, including myself. I had even whacked Builtin/administrators.
Shortly thereafter I realized we didn't have the sa password. I had completely locked myself out of my own server. What to do? I considered a full rebuild of the server, but there were 17 user-defined database on the server. Additionally, the majority of those databases were for a dev instance of BizTalk 2006, and while I'm not an experienced DBA, I worked with our DBA during initial installation of BizTalk 2006 and knew that it wasn't backed up or restored conventionally. Plus, there was a dev SharePoint installation using the SQL Server as well and I knew even less about the restore procedures for that. I was felling a little sick at that point.
A quick Web search revealed that Microsoft has left a trap door in SQL Server 2005 for this problem. You may find a simple and correct procedure for resetting a lost sa password when you cannot otherwise get access to the server, in the following link: http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx. I did exactly what Raul Garcia told me to do, and it worked! Restart the server in maintenance mode and then add a user to the the sysadmin role with sp_addsrvrolemember.
Next, I ran only the portions of the offending script which created logins. But was I done? Hardly. Inspection of the script (in which I asked for descriptive headers) told me that all SQL Server logins would be recreated with a random password and automatically disabled. I only had four of those, but I had to find the application passwords and make them match, and then enable the accounts. I also noticed that the associations between those logins and their database users seemed to be incorrect. I reset those with SSMS and they seemed to be working fine.
Next, I noticed that none of my backup jobs were running. SQL Agent could not access SQL Server. I use the same domain account to start both the SQL Server service and SQL Agent, but I had wiped out the permissions of that domain account. I fixed the problem by adding a login for the service account to SQL Server and gave it sa rights. I'm sure this is not the best possible way I could have solved that problem, but I can go back and fix it later. The highest priority of any DBA is to safeguard the company's data and this was only a dev server.
Finally, the SQL Server log showed that the BizTalk accounts could not log on to SQL Server. The message shown in the log was something like this:
Login failed for user '<domain>\<domain user>'. [CLIENT: 10.1.2.17]
Error: 18456, Severity: 14, State: 16
State 16 seems to indicate that a login has no permissions in a database which it is querying. Running sp_change_users_login with the "Report" option showed nothing. All of the BizTalk users had proper associations in their databases, just as SSMS said they did. Furthermore, we got the Windows password for one of the BizTalk Windows accounts and logged in to the server. We were able to connect to all the proper databases and query tables, just the permissions said we would. In other words, BizTalk was having no real problem connecting to SQL Server and querying its databases.
So, what kept causing the problem? We noted that the errors only occurred after restarting the SQL Server service, and then only one time. A ha! Is it possible that BizTalk was trying to connect to SQL Server and access its databases before the restart recovery is complete? Yes! Had I been more observant, I would have seen this in the log file. We put this to the test by stopping the BizTalk services on the BizTalk dev server and restarting the SQL Server service. Sure enough, the errors disappeared. It was nothing more than an order of operations issue. BizTalk tries to connect very quickly when it loses its SQL Server connection. It was trying to access a database before recovery was complete.
So that's the saga. Here's what we learned:
- Don't let Dave be your DBA on production servers if he can't stop destroying dev servers.
- Always have current backups. We did, so I knew I could get back if the method described herein didn't work.
- BizTalk is a very complicated system. You'd better practice restoring it because if you really have to do it, it could take hours just to know the process for recovery.
- Always record your sa passwords and securely store them.
- Read database creation scripts BEFORE you execute them. Five minutes of reading may save four hours' work.
- Microsoft has provided a way to create a new admin account on SQL Server even if you are boneheaded enough to completely sever access to SQL Server.