Thursday, March 15, 2012

How to connect to the Dedicated Administrator Connection [DAC] in SQL Server


1.     To Enable the Remote Admin Connections :-

Start the SSMS, Connect to the Database Instance, Open a new Query editor window,
Issue the command sp_configure to see if the config_value and run_value for 'remote admin connections'  are set to 1.

if you are unable to see this option under sp_configure, Try running the command below :-

Sp_configure 'show advanced options' , '1'
reconfigure
go


then run sp_configure again to see the advanced options,

If the above said values for 'remote admin connections'  are not set to 1, use the below command to enable it.

sp_configure 'remote admin connections', '1'
reconfigure
go


Run sp_configure to verify the same.

Enable the DAC [Dedicated Administrator Connection]:-

Go to, SQL Server Surface area configuration to enable the remote DAC,

Click Start , Choose All Programs, Locate Microsoft SQL Server 2005, Point to Configuration Tools, Click on Surface area configuration  to get to this window.





Choose Surface Area Configuration for Features option, Expand the instance, expand the Database Engine, Click on DAC and on the right panel check the box against Enable remote DAC.

This will prompt you to restart the SQL Server Service for the changes to take effect. go ahead and restart the

2.     Enable the Remote admin connections with appropriate protocols :-


Click Start , Choose All Programs, Locate Microsoft SQL Server 2005, Point to Configuration Tools, Click on Surface area configuration
In the Surface area configuration window Click on Surface area Configuration for Services and Connections
Then expand the instance, expand the database engine , Click on Remote Connections
Choose Local and Remote Connections and with the appropriate protocols ( as shown in the pic ).


3.     Make sure to have the Browser Service running :-

Click Start , Choose All Programs, Locate Microsoft SQL Server 2005, Point to Configuration Tools, Click on Surface area configuration
In the Surface area configuration window Click on Surface area Configuration for Services and Connections, Expand the SQL Server Browser on the left pane below the instance name and  click on Service to change the SQL Server Browser settings accordingly.

4.     Add port in the Windows Firewall Exceptions list :-

Click Start > Choose Control Panel > Double Click on Windows Firewall > Choose the Exceptions tab and Click on Add Exceptions to add SQL Server into the list.
Sqlserver.exe will be generally located at :-
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
Also, you need to add the SQL Browser service to have the DAC connected. Browser service will be located at :-
C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe

After adding the above two services, Please add the DAC port to the exception list to complete the process.

you can check in the error logs from which port the DAC is listening :-
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

Once you get the port number, go ahead and add it in the exception list.

5.     Connecting to the DAC using the command prompt :-
Start > Run > cmd
run the below command in the command prompt :-

Sqlcmd -Sadmin:<servername>/<instancename>

You should get a > sign below the command, This indicates that you have successfully connected to the DAC, you can also make sure by running the below command

Select  *  from sys.dm_exec_connections  A  JOIN sys.endpoints B on (A.endpoint_id = B.endpoint_id) where B.name='Dedicated Admin Connection'  

You will get to see your connection.

Else, If you get the below error while connecting to the DAC,

HResult 0xFFFFFFFF, Level 16, State 1
SQL 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 the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.


Check whether you have enabled the appropriate port, also, you need to check if the browser service is running and the sqlserver.exe service added in the exception list is of the same instance you are intending to use.

NOTE:- if you try connecting the Instance using the SSMS like this :-

admin:<servername>/<instancename>
you will get an error message:-

Dedicated administrator connections are not supported(object explorer)

This is because DAC is a privileged connection and wouldn't want to be allowed on the Object explorer , Microsoft states in one of its article as follows :-
 

DAC connections are only allowed for the query window because they are special highly-privileged connections used when the server would normally reject the connection. The idea is to run sp_who in the query editor and kill any rouge process. Using the DAC connection for the Object Explorer could make the problem worse.

You can connect to the instance normally, so, open a new query and then change the connection to admin:<servername>\<instancename>. This works., Then, you can verify the DAC connection either from the logs or by running the above query for DAC.

Happy Connecting.

~Cheers,
Deepak.P.



No comments:

Post a Comment

Multiple Linear Regression

Multiple Linear Regression is a process that uses multiple explanatory variables to predict the outcome of a response variable . The pu...