0

We are migrating sql server instances from one domain to a different domain. New AD accounts/groups will be used in the new domain so SQL logins and database users in the source SQL instances will be unusable in the target instances.

Migration is by taking a snapshot backup of the host server and restoring to the target environment, which means the SQL instances have come across with the server images.

Having logged in to the migrated host server with new credentials, we now need to login to the SQL server to configure the security with new AD accounts.

Is there any other way of login in to the SQL server apart from doing so as sa with SQL authentication?

I am unable to use SSMS even in single user mode, as there is no valid AD login that can be used.

Does anyone have any other ideas please? Thank you so much.

7
  • You can use Run As on SSMS to run it under credentials from a different domain Mar 18, 2021 at 20:34
  • @Charlieface, I will try that thank you.
    – PTL_SQL
    Mar 18, 2021 at 20:59
  • 2
    You mean you're locked out of the instance? if that's the case, just check the Connect to SQL Server when system administrators are locked out doc.
    – Ronaldo
    Mar 18, 2021 at 21:22
  • @Ronaldo, thank you very much, I appreciate your suggestion too. Pasted below is a paragraph from that article:
    – PTL_SQL
    Mar 19, 2021 at 4:31
  • @Ronaldo, what I missed in my test was that I didn't start in single user mode. I tried it again in single user mode and I was able to login to the instance without any login on the instance. Thanks again for your response, much appreciated.
    – PTL_SQL
    Mar 19, 2021 at 5:03

1 Answer 1

0

To avoid this, I usually prefer to set up a fresh installation of an instance in the new domain. Then I restore the databases. Then I script proper instance configurations/agents/other things, adapting them to the new domain.

As suggested by @Ronaldo, you can enter in this emergency situation using:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-2017

Please, take a look at this guide in case of domain change: https://social.technet.microsoft.com/wiki/contents/articles/24960.migrating-sql-server-to-new-domain.aspx

It's quite old but the general overview is fine.

This guide to regaining access is also fine:

https://www.sqlservercentral.com/articles/regaining-access-to-sql-server-after-changing-the-domain

1
  • very helpful, thank you so much!
    – PTL_SQL
    Apr 7, 2021 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.