Post reply

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

  • 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?

  • September 3, 2010 at 9:21 am

    #1216235

    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!

  • September 3, 2010 at 10:09 am

    #1216270

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

  • September 4, 2010 at 8:20 am

    #1216529

    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?

  • September 5, 2010 at 4:41 pm

    #1216725

    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"

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

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