LOGON Triggers RRS feed

  • Question

  • Hi Guys, 

    Version: 2012

    Edition: Express

    How can i drop LOGON trigger, All user including SA account can't login because of the created trigger.

    I tried to log-in using ADMIN:Servername\instance

    but i can't also log-in because SQL browser services is not exist.

    Thank you in advance!

    Friday, August 29, 2014 8:16 AM


All replies

  • Hello,

    Please login using DAC( dedicated administrator connection) or you can use -f parameter to start SQL Server in minimal configuration mode.

    Refer to below link for how to disable logon trigger using DAC


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Friday, August 29, 2014 8:30 AM
  • Hello,

    The only way to get Access to SQL Server is to use a DAC = Dedicated Admin Connection, as you already try.

    By Default remote DAC is disabled, only local DAC is enabled, so you must logon to the Server, where SQL Server is running and try to open a DAC there, e.g. with SqlCmd tool.

    If SQL Browser isn't running, then start it or use the IP port to connect to, like

    ADMIN:(local), 1433

    See How to: Use the Dedicated Administrator Connection with SQL Server Management Studio

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 8:32 AM
  • Hi Shanky_621,

    I tried to login using sqlcmd -A -S Server\Instance -D master (as a sysadmin) and it works to sql 2008R2 

    but when i tried to login to sql 2012 then i got an error

    -----------------------Error encountered-----------------------------------------------------

    Sqlcmd: Warning: '-D' is an obsolete option and is ignored.
    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Server Network Interfaces: An error occurred while obtaining the dedicated
    dministrator connection (DAC) port. Make sure that SQL Browser is running, or c
    eck the error log for the port number [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or i
    stance-specific error has occurred while establishing a connection to SQL Serve
    . Server is not found or not accessible. Check if instance name is correct and
    f SQL Server is configured to allow remote connections. For more information se
     SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.


    SQL Browser need to be setup? because i noticed that in SQL Config SQL Browser for SQL 2012 is not exist.

    Friday, August 29, 2014 10:20 AM
  • See sqlcmd Utility-A is the Switch for Admin connection. The Switches are case sensitive and we don't have an upper D Switch, only a lower d for the database Name =>

    sqlcmd -A -S Server\Instance -d master

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 10:29 AM
  • Hi Aj Daniel,

    According to your description,  as Olaf’s post, please make sure that the commands used to login in SQL Server are correct . If the commands are correct, the error might be caused by that the SQL Server Express does not listen on the DAC port.

    To work around this issue, you can add a trace flag 7806 in the startup  parameters of SQL Server Configuration Manager (SSCM). For more information, you can follow the steps in this similar blog: SQL SERVER – Dedicated Access Control for SQL Server Express Edition – An error occurred while obtaining the dedicated administrator connection (DAC) port.

    Best Regards,
    Lydia Zhang

    Monday, September 1, 2014 3:35 AM
  • Hi Aj Daniel,

    I’m writing to follow up with you on this post. Was the problem resolved after performing our action plan steps? If you are satisfied with our solution, I’d like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

    Lydia Zhang

    Monday, September 8, 2014 9:25 AM