I've lost the ability to connect to my SQL 2019 instance

  • On my home Windows 10 Professional desktop PC, I've got SQL Server 2019 Developer Edition installed. I thought I had mixed mode but am not so sure. A month or two ago I had problems with my Windows profile - it became corrupted. So, I disabled it and created a new Windows Profile. I thought all was good, until Saturday when I tried to connect to my default SQL instance. That failed with error 18456. I've been trying to get back into it, with the help of others, since then. But so far it hasn't worked.

    It was recommended that I use the PowerShell dbatools's Reset-DbaAdmin command. That didn't work. It gave me the error:

    WARNING: [17:06:09][Reset-DbaAdmin] Remote PowerShell access not enabled on on MSSQLSERVER or access denied. Quitting.

    So, then I tried using instructions from this website Recover a lost SA Password. That looked promising, but I've run into errors doing this as well. Here's the error that gives:

    So, I've added what appeared in SSMS as my login, MYMACHINE\Rod. However, it immediately errors and complains that the login failed for user 'MicrosoftAccount\Rod@outlook.com'.

    I really do not understand why it's telling me that I can't connect with MicrosoftAccount\rod@outlook.com. I didn't add that in the SQLCMD commands suggested in the website I linked to above. I used my PC's name.

    What's going on? Why is SSMS trying to connect me to MicrosoftAccount? I'm guessing MicrosoftAccount is my Office 365 (now Microsoft 365) account, but it most certainly isn't a domain that I connect to. At least not in the way I understand connecting to it.

    My home network is a simple affair - it is NOT an Active Directory domain. I am not connecting to Azure Active Directory. I am, at this point, extremely confused as to what's going on, why it's going on, etc. I'm honestly wondering if I should just uninstall SQL 2019, then install it again.

    What are your thoughts/recommendations?

    Rod

  • Hi Rod,

    i would rebuild the system databases with the setup command.

    Regards

    marcus

  • The documentation Connect to SQL Server when system administrators are locked out should sort you out here, though the article you have linked to is similar, it covers a little bit more of the syntax though. I'm had to use it before for a acquisition we made at work, as none of the staff knew any of the logins for the instance, and it worked fine.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 3 posts - 1 through 3 (of 3 total)

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