Post reply

Cannot connect to instance after disabling account

  • January 23, 2014 at 9:48 am

    #396152

    Hi

    I have my 'practice' laptop with sql2012 installed and I have one windows account called 'laptop\Myaccount' which I use to connect to the instance with sysadmin privileges.

    I disabled the account.

    I gave the sa account a new password as i couldn't remember it so I could connect again to re enable my account.

    The sa account does not connect - either I am typing a wrong password or the server is not set to mixed mode authentication ( I presume sa needs this mode ?) (or the password didn't reset because I wasn't actually connected after disabling my windows account???)

    I had previously enabled the DAC but when I try to connect with this it seems to try and use my windows account as i get an error : Login failed for user 'laptop\Myaccount' as the account is disabled ? I thought the dac used a separate admin account ?

    How can I connect to re-enable my windows sys admin account ?

  • January 23, 2014 at 9:56 am

    #1682859

    You can check the authentication mode in the registry. If it's set to Windows only, change it to Mixed and try sa again. If that doesn't work, you can log on to the computer as a local administrator and start the instance in single user mode. You will then have admin access to the instance, which you can use to re-enable your normal account.

    John

  • January 23, 2014 at 9:58 am

    #1682861

    Fantastic ! Thanks very much for this info - so all is not lost then ?? 🙂

    I'll have to google how to do what you suggested but thanks very much

    🙂

  • January 23, 2014 at 11:58 am

    #1682894

    i love this script from Microsoft for this kind of situation:

    http://archive.msdn.microsoft.com/Project/Download/FileDownload.aspx?ProjectName=addselftosqlsysadmin&DownloadId=9198

    it connects to your local or named instance, and adds the current windows user as a sysadmin on the machine in question.

    it's perfect for the situation where you have physical access to the host machine, but are locked out of SQL server on that machine.

    you pretty much just enter the instance name and let it rip...it stops and starts teh services as required to get you access again.

    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!

  • January 23, 2014 at 3:01 pm

    #1682952

    Lowell - thanks for the input. I have managed to fix without using the script you mentioned but it does look good and I will be checking it out and will keep it handy for such situations.

    I did manage to fix this changing the servers authentication mode from Windows to mixed mode so I could use the sql login account 'sa'.

    At first I changed the registry setting 'LoginMode' from 1 to 2 (HKEY LOCAL MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer and I still could not log on with the sa account.

    To fix I needed to open the windows services and stop the sql server service, change the registry, restart the service and then I could log on.

    Thanks for all you help guys 🙂

  • January 24, 2014 at 3:24 am

    #1683060

    PearlJammer1 (1/23/2014)

    At first I changed the registry setting 'LoginMode' from 1 to 2 (HKEY LOCAL MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer and I still could not log on with the sa account.

    yes, a service restart is necessary after changing this value

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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