2

Our support people changed the 'sa' password our SQL Server DB. They switched off Windows Authentication. Then the guy who did the work left.

So we have a production database that isn't being backed up and that we are locked out of. And over 10 years of production data in it.

I have tried the usual trick of adding new roles from the SQLCmd command line.

  1. Retart MSSQLSERVER in single-user mode

  2. from MS-DOS command line, issue "MSSql -S 127.0.0.1\MSSQLSERVER" and then at command-line:

    USE [master]
    GO
    
    CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    
    EXEC master..sp_addsrvrolemember @loginame = 'BUILTIN\Administrators', @rolename = 'sysadmin'
    GO 
    
  3. Restart MSSQLSERVER as normal

But in SQL Management Studio the new users and new role assignments are nowhere to be seen. Users

If I go to master.sys.server_principals I do not see the new logins. server_principals table

If I try to add the role to an existing user in Management Studio, it tells me I don't have permissions. I'm logged in as a Windows Administrator but not 'sa' (because that's the password I'm trying to recover or reset): No permission

I go back to MSSql command line and reissue the above commands and it says the principal already exists. principal exists

There is no other instance of SQL on the machine. There is not other server on our network. There are no error messages that I can see anywhere. I am doing the above with full Administrator rights on Windows Server 2012 R2.

How come MSSql command-line creates the logins & assigns roles, reporting it's OK, but there are no error messages and nothing visible in SQL Server Management Studio. I've restarted MSSQLSERVER. I've restarted Management Studio.

Think I'm going crazy. Anyone have any ideas what to do???

  • Stranger and stranger, the password_hash for the 'sa' account in the sql_logins table is NULL. What might this mean?? – Murky Sneaker Dec 23 '17 at 14:03
3

I would recommend following the steps I note below to regain access to your data.

  1. Set the SQL Server instance back to Mix mode authentication.

You can do this by stopping the SQL Server instance and adjusting the registry key. Changing this registry is all SSMS did when the form DBA changed it. There is no issue with changing this registry key to set it back, just makes sure the instance is not running.

The registry key path should be something like this: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQLSERVER\MSSQLServer\LoginMode. You should find this set to 1 now for Windows Authentication. Change this value to a 2 for mixed mode.

  1. Install PowerShell module on the server or your client machine from the PowerShell gallery called dbatools.

Once you have the module installed you can utilize the command: Reset-DbaAdmin. This will prompt you to reset the "sa" account and let you regain access to the instance again.

#Install from the gallery
Install-Module dbatools

# Import module and run command
Import-Module dbatools
Reset-DbaAdmin -SqlInstance YourServerName
  • Wow, cool idea. Will try it! – Murky Sneaker Dec 25 '17 at 12:13
0

That has worked for me the couple of times I've done it. Have you tried resetting the sa password while the server is in single use mode?

  • That's what I've detailed above. It failed! – Murky Sneaker Dec 25 '17 at 12:13
  • From what I'm reading, you're trying to add a new user/role. But that said, what does select @@servername return both when your in single use mode and not? That is, "trust but verify" that you're connected to the correct instance. – Ben Thul Dec 25 '17 at 15:41

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.