SQL SERVER – Dedicated Access Control for SQL Server Express Edition – An error occurred while obtaining the dedicated administrator connection (DAC) port.

Recently I had faced very interesting situation. Due to some reason we were not able to login into the production server for one of client. The reason for the same was that server was very busy, we had to login into the system and bring server to normal situation. When all the attempts failed, I decided to login using Dedicated Administrator Connection (DAC). However when I attempted to connect using DAC it threw following error for me.

C:\Users\pinald>sqlcmd -A -d master -S .\SQLEXPRESS

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for t he port number [xFFFFFFFF]. .Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

I was bit taken a back as I knew that my commands are correct to login and if DAC does not work, there should be some serious reason for it. When inquired further about the SQL Server version I learned that it was SQL Server Express version deployed. To conserve resources, SQL Server Express does not listen on the DAC port. There is an additional step to be done if SQL Server Express has to be used with DAC. Enable TRACEFLAG on SQL Server Express will enable the connection by DAC possible. Here is the quick methods how one can enable DAC on SQL Server Express.

Go to Start >> All Program >>Microsoft SQL Server (your version) >> Configuration Tools >> SQL Server Configuration Manager.

Click on SQL Server Services >> Select your SQL Server Express version >> Right Click Properties >> select Startup Parameters

Once on the Startup Parameter add the Startup parameter which is TRACEFLAG -T7806. Click on OK and RESTART SQL Server Express edition. Now once again try to connect to SQL Server Express edition and it will work just fine.

This is absolutely documented method on BOL and SQL Server Express needs to be restarted.

Watch SQL in Sixty Seconds video to Resolve Connection Error:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Dedicated Access Control for SQL Server Express Edition – An error occurred while obtaining the dedicated administrator connection (DAC) port.

    • Hi Pinal,

      You r doing a great job..
      I have a problem ,hope to get expert advice from you,
      i have sql server 2005 with sp4..whenever a sms jobs runs our server gets slow and in does not respond to any new query and throw timeout expiration error .
      Pls help

      Like

  1. Pingback: SQL SERVER – Resolving SQL Server Connection Errors – SQL in Sixty Seconds #030 – Video « SQL Server Journey with SQL Authority

  2. hi pinal
    i am facing the same problem…but i don’t have provision to add a new start up parameter unlike above.I am using sql server 2008 R2(SP3) Express edition. Please suggest a solution.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s