Unable to access SQL DBEngines that have been moved to a new domain

Question

Post reply

Unable to access SQL DBEngines that have been moved to a new domain

Coypu

SSC Rookie

Points: 33
More actions
September 3, 2010 at 8:52 am

#226521

I have a new clean SBS 2003 R2 fully patched server. It has the same PC and Domain name as my previous SBS 2003 server which "died". I also have a WinServer 2003 running SQL 2008 R2 in windows authentication mode that was in the previous domain. It has been successfully joined via http://server/connectcomputer to the new domain but I cannot access its SQL DB engine via SSMS. I'm getting "Login failed", even for the local admin user.
I have the same problem not being able to access SQL Express on PC's that have moved to the new domain.
Where did I go wrong and how do I regain access to these DBE's?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I believe that you can connect as sa(if you know the password), and then add the the right groupsto the sysadmin group.

otherwise as a local administrator, you need to connect to the Dedicated Admin Connection

http://msdn.microsoft.com/en-us/library/ms178068.aspx, which allowsthe local admin to connect as an admin, even though it might not be able to normally;

then from there, you can add the local admin/domain admins etc to grant access.

for the express isntance, I'm not sure other than as 'sa', since an express isntance would not have a DAC available....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Coypu SSC Rookie Points: 33 More actions · Answer 2

Thanks Lowell. I'll give that a try early tomorrow and report back.

Coypu SSC Rookie Points: 33 More actions · Answer 3

Itried that but not sure why it didn't work for me.

However I installed another instance, this time using mixed authentication and a strong password for SA. I stopped the original instance which I can't login to and moved the MDFs and LDFs to the new data folder. In the new instance I Attached to the MDF's and LDF's, and set the relevant roles etc. Back in business now 🙂

Was that the only or best solution and will having SA save me next time if there is a domain / AD problem?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 4

The problem is that the SQL 2008 box does not have local admins/domain admins a part of the sysadmin database server role by default like previous versions did. This is intentional and is a security measure. To avoid this in the future either use mixed mode security or add the required logins to SQL of the accounts/groups administering the machine in the new domain before joining the machine to the new domain.

Joie Andrew
"Since 1982"