February 27, 2006 at 12:54 pm
So while I was on vacation for a week, the network admins decided to change our domain, from
However, there are several SQL instances that they did not mess around with (such as on my personal box, and maybe the new cluster--I'm too scared to ask). The question is, how to I regain SysAdmin access to these boxes?
- The old domain *and it's logins* is gone, kaput, no longer extant.
- SQL Server and SQL Agent were running under logins from the old domain; new accounts with the same names and passwords were created, but the security tokens are of course different--so they are for all intents different accounts.
- Several NT logins and groups had SysAdmin access, but of course they're gone too
- No new domain accounts were configured on these instances with access (SysAdmin or otherwise).
- Builtin Administrators was long since dropped from the box.
- I changed the SA password to something long, unguessable, and forgettable, and intentionally forgot it.
- I never created a SQL Authenticated "backdoor" SysAdmin login, 'cause I didn't want the security risk. (If I had had any inclination that they were going to do this, I would have. But hey, it only takes a day or two to plan and execute a domain migration like this, right? What the heck, it's a learning experience for them.
So I'm at the age old ultimate security problem: what can I do to log in to a SQL Server instance with SysAdmin rights? More info may be available as I delve into this.
(I'm really hosed, aren't I?)
Philip
February 27, 2006 at 1:18 pm
February 27, 2006 at 4:16 pm
The chance of that is zero. There is the 20 foot cable option, whereby a given server can be attached to the old domain controller and (a reboot or two later) the old domain re-applied, allowing me to put a "backdoor" sql authenticated SysAdmin on, so when it's moved back to the new domain I can fix things up... but that won't help much with our off-site servers. (I'm just glad I've only got the 5 database servers [+1 cluster that I've yet to get full admin rights to] to manage.)
I'm labelling the files and notes generated by this work with a "cf" header. If I hadn't just had a week in Disneyland, I wouldn't be taking this nearly so well.
Philip
February 27, 2006 at 6:27 pm
Do not if this will work:
1. Shut down the SQL Server instance.
2. Rename the master data and log files.
3. Copy the master data and log files from an instance where you do know the password to a sysadmin login
4. Start up SQL Server
5. Attach the user databases
Next, try to attach the old master database under a different database name and then query the syslogin view to get the old login names.
Good Luck.
SQL = Scarcely Qualifies as a Language
February 27, 2006 at 7:41 pm
Oh yes, the old brain transplant trick. Haven't had to do that in years. I'll give that a try. The thing is, the most useful stuff I have on the one instance is in MSDB. Hopefully I can get by with only substituting the master db files...
Philip
February 28, 2006 at 4:46 am
Hi Philip,
Tough one for you.
Maybe Active Directory can come to your rescue. There's an attribute named SIDHistory in Win2003. If this attribute of your new useraccount can be filled with the old SID nr of your previous' domain sysadmin account, then you will be the 'sysadmin' of the database. You can find the SID nr on disk (hopefully) if you've logged on on the machine. There will be a profile dir with permissions (which will show the SID), or maybe the recycled folder will show yoy a sid.
I'm not sure if you can directly type in the SID using ADSIEDIT (or that you need to convert it to hex or whatever other format...)
Maybe you're lucky and the network guys migrated some accounts and filled the sidhistory already?
Hope this helps...
JP
February 28, 2006 at 6:26 am
"Maybe you're lucky and the network guys migrated some accounts and filled the sidhistory already".
Maybe you're even luckier and the Network guys have saved you the bother and tendered their resignations.....
February 28, 2006 at 9:12 am
JP,
Thanks, but I lack the expertise to perform deft and esoteric (at least to me) manipulation of AD, as does everyone else here. (I could figure it out eventually, but the time would not be commensurate to the gain.)
I'm now thinking of installing a named instance of SQL 2000 on the box, then "brain transplanting" those system DBs over to the default instance--but I bet there are specific "named instance" values stored in the master database, making that screwy. I don't have any extra boxes to mess around with here, alas. (Hmm, maybe make copies the system db files, uninstall, reinstall, then selectively reinstate? Interesting experimentation ahead. Pity I'm not doing productive work...)
Philip
February 28, 2006 at 9:33 am
Don't know if this will work or not, so I highly suggest that you try it on your own personal box or a test box first. Try changing the security mode from mixed to NT authentication only. Shut down MSSQL then set the value of HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode to 1 in the registry. Start MSSQL back up and shut down again. Then change the value back to 2 for mixed mode. The 'sa' account should have no password so you should be able to connect and start fixing things. I've never tried this so please test....
February 28, 2006 at 1:57 pm
Hi,
Have you tried unplugging the network card and logging on with stored credentials? That is what I do when I have logon issues...
Michael
February 28, 2006 at 8:00 pm
Flipping from Mixed to NT only and back again: this would not work. The password is stored in sysLogins, and would not be affected. (Otherwise everyone would use this exploit, and MS SQL Security would be nil.)
Unplugging the card/stored credentials: Would that work after reboots and reallignments? Once the "new" credentials were loaded, wouldn't the old ones be gone?
Philip
March 1, 2006 at 8:40 am
Is the SQL Server sa password the same on all boxes (including the one you do have access to)? If so, you can run SQLCrack or some similar tool to get the sa password.
Also, I've not tried attaching a master database as a user database to another system, but if that's possible and you can read the syslogins table, you may be able to extract the password. Should you be able to, you could apply a crack program.
K. Brian Kelley
@kbriankelley
March 1, 2006 at 8:42 am
One other thing stood out to me. When you say changed domains, why did they change domains if all they were doing is an upgrade from a Win2K domain to a Win2k3 domain? It makes sense to do a side-by-side for some cases of NT4 to AD, but even that isn't all cases...
Sorry, I have my AD hat on as the AD technical lead for the last almost 5 years. I'm puzzled by why they would do that when the upgrade from Win2k to Win2k3 should be relatively painless and Microsoft has plans and procedures on how to do so safely.
K. Brian Kelley
@kbriankelley
March 1, 2006 at 1:14 pm
The SA password is different on ever box.
After talking with some friends--if I've got the terminology right--I believe that they did a migration, not an upgrade. I think this was done because they wanted to move the domain controller to new hardware. (Would the better way be to upgrade the old boxes, then move the domain controller to new hardware?)
Just now I can't access databases or email, so I'll be hacking away at my local SQL instance. I'll post results.
Philip
March 1, 2006 at 2:10 pm
If they did a migration to a new domain simply because they wanted new hardware... they could have brought up new DCs on new hardware, moved over the FSMO roles, dcpromo'ed the "old" DCs back to member servers, and then decommissioned them as they would any other server. If they were looking to go from Win2k to Win2k3 AD, there is plenty of documentation on how to do such.
As for getting your servers back online... surely they have backup tapes of the old domain, right? If so, they could bring up an old DC (whatever was the PDC or PDC Emulator), establish a trust between the two domains, and as long as the groups used to grant administrative access weren't domain local, you should be able to come back on-line. At the very least you could reset the password for the SQL Server service account (assuming it was domain level) and log in to SQL Server as it, since that account requires sysadmin rights.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply