locked
Cannot access instance RRS feed

  • Question

  • Hi, 

    We have an instance of SQL that we cannot access (from the server).  There is a login trigger preventing access.  Tried disabling via DAC however this gives an error saying that the SQL browser is not running or the port is wrong.  The browser is running and I'm now checking the port/

    Major problem is that sa is disabled and nobody else is in the sysadmin role. 

    I can't enabled 'sa' because of the trigger and can't disable the trigger because of the lack of sysadmin access. 

    Are we in big trouble? 

    Help :-( 

    Tuesday, January 13, 2015 11:00 AM

Answers

All replies

  • Hi, 

    We have an instance of SQL that we cannot access (from the server).  There is a login trigger preventing access.  Tried disabling via DAC however this gives an error saying that the SQL browser is not running or the port is wrong.  The browser is running and I'm now checking the port/

    Major problem is that sa is disabled and nobody else is in the sysadmin role. 

    I can't enabled 'sa' because of the trigger and can't disable the trigger because of the lack of sysadmin access. 

    Are we in big trouble? 

    Help :-( 

    Tuesday, January 13, 2015 10:58 AM
  • Hello,

    First open ErrorLog file in a text Editor, at the beginning you should find one of those entries:

    Dedicated admin connection support was established for listening locally on port 52760.
    
    Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. 
    If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806.
    This is an informational message only. No user action is required.

    If you find the first, then DAC should work, if it's the second then you have use the mentioned trace flag to enable DAC.

    Then see Connect to SQL Server When System Administrators Are Locked Out for how to connect with admin permissions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 13, 2015 11:07 AM
  • Try this blog to add another user as sa and see if it helps. http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    I am not sure if DAC will help as it needs sysadmin role.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, January 13, 2015 11:07 AM
  • Hi 

    Try this may be hlep full  Click Here

    Thanks

    Tuesday, January 13, 2015 11:14 AM
  • I tried that link but cannot access because 

    a) the browser is not running - it is running. 

    b) I'm just checking DAC now. 

    I do wonder if this is going to be unrecoverable.  There are no enabled sysadmin accounts and none can enabled because if the trigger, can't disable the trigger because there are no admin accounts. 

    It's SQL Express 2008R2 if that makes any difference. 

    Tuesday, January 13, 2015 11:36 AM
  • Try this blog to add another user as sa and see if it helps. http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

    This fails because of the trigger. 
    Tuesday, January 13, 2015 11:37 AM
  • Try this 

    http://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/

    Thanks

    Tuesday, January 13, 2015 11:51 AM
  • you mean to say you cant add a new user or login to sql fails due to trigger while in single user mode?

    If its the new login creation fails, could you try if you can disable the trigger directly when logged in under single user mode? because while in single user you are sysadmin.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, January 13, 2015 12:27 PM
  • Please follow steps according to link posted by Ashwin and then follow below steps to disable trigger.

    select [Name] from sys.server_triggers

    Once you have name of trigger then execute below

    DISABLE TRIGGER <Trigger name from previous query> ON ALL SERVER 

    Once trigger is disable then please go ahead and add new user. Good Luck!!

    Alternatively, Please follow procedure from below link - I will recommend to follow below as it will disable all triggers.

    https://support.microsoft.com/kb/2002062


    Regards,
    Vishal Patel
    Blog: http://vspatel.co.uk
    Site: http://lehrity.com


    Tuesday, January 13, 2015 12:48 PM
  • Thanks for all responses.  The problem is now fixed.  

    Basically we had to start in single user mode and create a new user, add this user to the sysadmins role and then 

    drop the offending trigger from SSMS, was not possible to disable or drop via sqlcmd - don't know why. 

    DAC absolutely did not work with SQL Express. 

    Tuesday, January 13, 2015 2:01 PM
  • You dont really have to create a new user and add it to sysadmins role, because when you connect in single user mode you are already connecting as a sysadmin.

    DAC will only work if you have sysadmin accounts. More over you need to enable DAC In express editions. Check this link

    http://www.mssqltips.com/sqlservertip/2538/enabling-dedicated-administrator-connection-in-sql-server-2008-express-edition/


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Tuesday, January 13, 2015 2:10 PM
  • DAC absolutely did not work with SQL Express. 

    It works, but by default it is disabled in Express Edition.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, January 13, 2015 2:11 PM
  • Is this production sql server instance ? If this is not a PROD then try the below... you can create one sql temp login and you can do all your work to fix this issue

    http://blogs.technet.com/b/sqlpfeil/archive/2013/07/22/the-scenario-of-a-missing-sa-password.aspx


    Raju Rasagounder Sr MSSQL DBA

    Tuesday, January 13, 2015 8:01 PM