Fix : An error occurred while obtaining the dedicated administrator connection (DAC) port

Troubleshooting DAC Issues

We may encounter below error messages while working with DAC.

An error occurred while obtaining the dedicated administrator connection (DAC) port.

Resolution :
1. Make sure TCP/IP is enabled.
2. The browser service should be in running mode.
3. While connecting using DAC pass port number along with Instance name if it is named instance.


Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Note  - In DAC mode you wont be able to access object explorer

Resolution :
1. If you are trying to connect to DAC using like below
SSMS - Connect Datbase Engine - Server Name  - Admin:ServerName -- Then it throws error because it was trying to open object explorer whereas DAC doesn't have access to object explorer

2. You need to try like this
SSMS - New Query -  Admin:ServerName -- It allows you to login using DAC Connection.

 

DAC -- Dedicated Administrator Connection

 

SQL Server introduced DAC on SQL Server 2005 which is a special type of connection where database administrators can connect to SQL Server when regular connections are not possible. In other words it is a backdoor to access to SQL Server resources to troubleshoot some serious issues. 

 

There is one reserved scheduler which allows us to login into SQL Server using DAC. There is one specific endpoint reserved for DAC that is DAC Endpoint and it is #1.

 

The default behavior of SQL Server DAC is it wont allow other clients to login into servers using DAC, to do so we need to configure 'remote admin connections' to 1 from 0.

 

Note : Only one connection made using DAC, also only Sysadmin has right to connect to DAC

 

To connect using DAC

Open SSMS -- New Query (On ServerName Type ADMIN:ServerName)
Server Name  -- ADMIN:ServerName (or)
Server Name  -- ADMIN:ServerName\NamedInstance (or)
Server Name  -- ADMIN:ServerName[\NamedInstace], PortNumber

 

(OR)

 

cmd > sqlcmd -SServerName[\NamedInstance] -E -A

 

-S  -- ServerName
-A -- Enable DAC
-E -- Windows Autentication
-U -- UserName
-P -- Password

 

Run the command to enable DAC to client connections

sp_configure 'remote admin connections',1
go

Reconfigure

 

(OR)

 

Right click on server name -- Facets -- Select Surface Area Configuration from Facet dropdown
Remote DAC Enabled  -- True

 

-- Query will return 1 row, if you are connected using DAC.

SELECT ec.login_name, ep.name, ec.session_id, ep.endpoint_id
FROM sys.dm_exec_sessions ec, sys.endpoints ep
WHERE ec.endpoint_id = ep.endpoint_id AND session_id=@@spid

 

The Execute Permission was denied on object 'sp_start_job', database 'msdb', schema 'dbo'

The Execute Permission was denied on object 'sp_start_job', database 'msdb', schema 'dbo'

To execute any job in SQL Server Agent you must have the below three database roles on MSDB Database,

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

The user has all the above three db roles on MSDB eventhough that user is getting permission deined error while executing agent jobs.

Use the below query to verify if there are any issues with permissions


use msdb
go
SELECT  OBJECT_NAME(so.object_id) AS obj_Name, spr.name, sdp.permission_name, sdp.state_desc
FROM sys.database_permissions sdp, sys.objects so, sys.database_principals spr
WHERE sdp.major_id = so.object_id AND spr.principal_id = sdp.grantee_principal_id
and so.name = 'sp_start_job'
If no rows returned then we need to execute the below command to assign execute permission on sp_start_job to SQLAgentUserRole

Grant execute on sp_start_job to SQLAgentUserRole

If two rows returned with Grant for SQLAgentUserRole and Deny for TargetServerRole, then

MSDB - Security - Users - Properties - Add Target Server Role to that user
Grant execute on sp_start_job to TargetServerRole

Now execute the job it will be executed by that user.