Cannot access instance

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 :-( 

Answer 1

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 :-( 

Answer 2

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]

Answer 3

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

Answer 4

Double post: https://social.msdn.microsoft.com/Forums/en-US/74678086-2aec-4825-adc0-8ae9706fe9be/cannot-access-instance?forum=sqlsecurity

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Hi 

Try this may be hlep full  Click Here

Thanks

Answer 6

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. 

Answer 7

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. 

Answer 8

Try this 

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

Thanks

Answer 9

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

Answer 10

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

Answer 11

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. 

Answer 12

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

Answer 13

DAC absolutely did not work with SQL Express. 

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

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 14

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