Suppose that I wish to run the following

/* Check for any pending configurations, just in case. */
SELECT *
FROM sys.configurations
WHERE value  value_in_use;
GO
 
EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

Is there any risk, such as clearing the plan cache or blocking queries, to running this during a busy day? I know that the remote admin connection is safe. I'm concerned about sp_configure.

I get the following error sometimes: > Could not connect because the maximum number of '1' dedicated > administrator connections already exists. Before a new connection can > be made, the existing dedicated administrator connection must be > dropped, either by logging off or ending the process DAC is not enabled on the SQL server. What can cause this error?

I'm connecting to SQL Server (2016 and 2017 latest builds) via the Dedicated Administrator Connection (DAC) from PowerShell. The following error message is recorded in the SQL Server Error Log: > Date 4/2/2019 1:59:13 PM > Log SQL Server (Current - 4/2/2019 1:59:00 PM) > > Source Logon > > Message > Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1] The query runs successfully. Numerous connection string management iterations have been attempted; this one is the most robust so far. A solution exists on Stack Exchange that involves killing the spid before closing the connection, but that also throws a nuisance message into the SQL Server Error Log, so no joy there. Examining sys.dm_exec_sessions reveals nothing of interest; no connections remain open using this technique. The below PowerShell has a dummy query in it, I can't talk about why we are connecting this way because it is proprietary, but it is 100% necessary, it is a very quick connection, and I need to do it once every 10 minutes. This error is just noise. The DAC query runs and works as expected. The error is recorded *every time* even with a fresh restart on a quiesced system. There are NO other DAC connections in use - if there was, Powershell would throw an obvious error message at the command prompt. Interestingly, when using sqlcmd, no error message is recorded in the SQL Server Error Log. #begin powershell script $SqlServerName = "server\instance" $DbQuery = " INSERT INTO master.dbo.sometable(value1,value2) values ('test14','testtest14');" function Get-SqlConnection { param ( [String] $SqlServerName ) $sqlConnection = $null try { $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "data source=admin:$SqlServerName;Integrated Security=True; pooling=false" $sqlConnection.Open() } catch { if ($sqlConnection) { [void] $sqlConnection.Dispose() } throw } $sqlConnection } try { $sqlCommand = New-Object System.Data.SqlClient.SqlCommand $sqlConnection = Get-SqlConnection -SqlServerName $SqlServerName $sqlCommand.Connection = $SqlConnection $sqlCommand.CommandText = $dbQuery [void] $sqlCommand.ExecuteNonQuery() } finally { if ($sqlCommand) { [void] $sqlCommand.Dispose() } if ($sqlConnection) { [void] $sqlConnection.Dispose() } } No connections are shown when I run this query on the server:

SELECT
    CASE
        WHEN ses.session_id= @@SPID THEN 'It''s me! '
        ELSE '' 
    END 
    + coalesce(ses.login_name,'???') as WhosGotTheDAC,
    ses.session_id,
    ses.login_time,
    ses.status,
    ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on
    en.endpoint_id=ses.endpoint_id
where en.name='Dedicated Admin Connection';

Using SQL 2016 Enterprise Edition The default instance will listen on 1433 and the DAC port will listen on TCP 1434. SQL Browser will listen on UDP 1434. Any subsequent named instances will get a dynamically allocated port from windows in the range 49152 to 65535 as will their DAC ports. Connection requests (including DAC) to those named instances will make a call to SQL Browser and told the port number. You can use the SQL Server Configuration Tool to change the port for the named instance to a static port number, but there is no option to set the DAC port to a static port number. When I suggested to the networks team that I'd like the entire port range opened up for remote DAC connectivity, they were less than enthusiastic. Is there any way to set the DAC port to a known, static port number for named instances?

Is it possible to connect to the DAC using SSMS? If I understand correctly, it should be possible by using "admin:" prefix before the server name, but I receive the following message: > Dedicated administrator connections are not supported via SSMS as it > establishes multiple connections by design. > (Microsoft.SqlServer.Management.SqlStudio.Explorer)

I'm trying to connect to SQL Server 2012 via DAC using Management Studio. I type admin: before my server name, yet I keep getting the same error: > A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The remote computer refused the network connection.) (Microsoft SQL Server, Error: 1225) What I've checked: - I can login with no problems when using regular connection(non DAC) - I can also login when forcing tcp: in connection string - user is a sysadmin (sa) - I am logging in from a local server (physical sql machine) - SQL Server browser is enabled - Remote connections are enabled and firewall was disabled - TcpDynamicPorts in regedit was set to 59036 - I've executed this: exec sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE exec sp_configure 'remote admin connections', 1 RECONFIGURE WITH OVERRIDE So far, nothing has helped me.

If you check the setting in any of your Azure SQL databases, you will see that the value_in_use column value is zero for Remote admin connections. Meaning ‘Remote admin connections’ are not allowed from remote clients. There is no way to change that at the time of writing this question. sp_configure is not available for Azure SQL Database. SELECT * FROM sys.configurations WHERE NAME = 'remote admin connections' ORDER BY NAME; Does that mean Remote admin connections are not allowed for Azure SQL Databases?

I have configured one remote sql server with 2 instances A,B. I have enabled TCP/IP,Port 1434, remote connections, sql browser running and firewall inbound rule. I've enable DAC using sp_configure 'remote admin connections', 1; and reconfigure. However when I try to connect via SSMS or SQLCMD using admin:hostmachine\sqlinstance or sqlcmd -S hostmachine\sqlinstance -U sa -P pass -d master -A I'm getting the following error : *A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 - 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) (Microsoft SQL Server, Error: -1)* I can connect using DAC but only as admin:hostmachine or sqlcmd -S hostmachine -U sa -P pass -d master -A

DAC attracted my attention at the moment when I was unable to connect to the SQL Server using a traditional way (the reason was deleting logon trigger). I made some searches on the web and it turned out that the only way to access the Server was to use DAC connection which unfortunately was turned off by default. However, I was able to overcome the problem (I removed trigger) using just sqlcmd. My question is if I can connect to the SQL Server just using sqlcmd, what is the purpose of DAC then?

I recently enabled DAC and noticed this error when first trying to connect with object explorer in SSMS: > Cannot connect to admin:servername > > Dedicated administrator connections are not supported via SSMS as it > establishes multiple connections by design. > (Microsoft.SqlServer.Management.SqlStudio.Explorer) It works fine via sqlcmd or just doing File>New>Database Engine Query. My question is, how many default connections are made when you connect to a server in Object Explorer? I would guess if you have it configured to spawn a new query window that would be another connection? The SQL Server Agent? I understand this answer: https://dba.stackexchange.com/questions/17976/is-it-possible-to-estabilish-dedicated-administrator-connection-dac-using-ssms but I was just curious what connections are made by default. Thanks!

This morning I hopped on the DAC to check something out. Oops, I made a typo!

1> exec sp_msforeachdb N'
2~ use [?]
3~ select N'?', count(*) from sys.sysrscols;
4~ '
5> go
1>

Can you spot my error? Pretty obvious when you see it, right? It's even more obvious if you type that command into SSMS. Frustratingly, it took me a _long time_ to figure it out. You will note that **no error message is returned** from the batch **1** (indicated by the 1> on the newline after go) Maybe errors get handle differently in the DAC? No... doesn't look like it...

1> select 1/0 -- simple error
2> go
Msg 8134, Level 16, State 1, Server VANDIVIER, Line 1
Divide by zero error encountered.
1> select ( -- syntax error
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1> exec (N'select ('); -- syntax error in deferred command
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1> exec sp_executesql N'select ('; -- sp_executesql
2> go
Msg 102, Level 15, State 1, Server VANDIVIER, Line 1
Incorrect syntax near '('.
1>

So clearly the DAC returns most errors to the client in just the way we expect. What the heckin' heck is happening here?! --- **1** You will also note that even though rowcount is set to on, no rowcount was returned to the client - which should have been a clue in hindsight.

I am having a strange issue when trying to connect to my SQL Server instance remotely from my client computer. I am able to do this on my other instances by opening up a new Database Engine Query and PREFIXING ADMIN: in front of my server name. It only doesnt work on one instance. When I open up the Error Log, it states that "Dedicated admin connection support was established for listening remotely on port 55632." SQL Server Browser is running (and Restarted). When I try to connect to DAC on this one instance, I get an error saying to look in my SQL error logs.this is where I am getting confused: I had installed a Monitoring solution from SolarWinds (DPA Free) last week. I didnt find it very useful so I uninstalled it and deleted the database where it stores the metrics (ignite_DBAnalyzer). I double-checked that this DB does not exist on this instance. This is the same DB, ignite_DBAnalyzer, it is claiming it cannot explicitly open (See line 1 of error log). I checked my user Mappings and that DB is not anywhere to be found. I checked my default DB to connect to when i start a new connection and that is also fine. Has anyone seen this kind of behavior before? Is there cache somewhere I need to clean out so it forgets this DB and can verify that it has been deleted?

We started getting Alerts: >Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1] With a help of SQL job and holding table, we managed to find out that it's the SQL Server Service Account that tries to connect, approx every 4 hours e.g. 12:00 16:00 and so on, with a program name: Net SqlClient Data Provider. I've added the host_process_id column as per jadarnel27 suggestions, identified the PID, but there were no processes with the same PID in the task manager. Oddly enough, there are no entries in the log: "Dedicated admin connection support was established", just the following ones: >**16:00:05** Login succeeded for user 'sql service user'. Connection made using Windows authentication. [CLIENT: ip address of Windows Failover Cluster Virtual Adapter] > >**16:00:05** Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1] > >**16:00:11** Dedicated administrator connection has been disconnected. This is an informational message only. No user action is required. No clues in Windows Event Viewer logs or Windows Cluster logs. This is only happening on an active node of AlwaysOn Availability Group. No jobs/port scanning configured to run at those times, nor any other tasks. I have seen How do we find who used Dedicated Admin Connection but we know what account has used DAC, just don't understand why. How can we troubleshoot the issue further? ***EDIT: I've found the cause of the problem, it was a powershell script that synchronises server objects for Availability Groups, it was set to run on port 1433 and I have no idea why it tried to connect to DAC, however simple reboot has solved the issue! Thanks ever so much to everyone who replied!***

Is it possible to Connect MS SQL Server 2012 Express Through DAC. After moving from SQL Server 2012 Enterprise to MS SQL Server 2012 Express, some performance problem were there. When i have checked out using this TSQL select * from sys.dm_os_schedulers; The schedular is not showing DAC (Dedicated administrator connections) scheduler ID. Any idea or suggestion will be appreciate.

Starting working with Dedicated Administrator Connections on SQL Server 2008 R2 servers. All is working fine (once port 1434 was opened), but am getting severity 20 errors when I disconnect the admin connection. From within SSMS, I am opening a new query window then connecting via the DAC using connection string, "admin:". All is well. However, when I disconnect, my SQL Alert for Severity 20 errors fires, alerting me to it. The description of the alert reads: > "Could not connect because the maximum number of ‘1’ dedicated > administrator connections already exists. Before a new connection can > be made, the existing dedicated administrator connection must be > dropped, either by logging off or ending the process. [CLIENT: > XX.XX.XX.XX]" The IP address is mine. I'm confused as what could be attempting the connection at the same time I am disconnecting from the DAC. Or, is it something else. No connections to the same server from my SSMS via any query window or Object Explorer. Other users may be connecting to this SQL Server (its a dev box) but not on DAC (only opened port 1434 before testing). Any thoughts?

Is there any way to find who used Dedicated Admin Connection? Not active connection but the previous one which is already closed?

Well, luckily I got this error on my test environment. I'm so scared about this error that I disabled all logon triggers I was using on production environment. I created the famous logon trigger, and I put a database in offline mode just to mess with something else. Then I discover that because the trigger can't find the table, I can't access the instance anymore. There are plenty of questions related to this, but even doing everything I could find, I still can't acces the instance, not even with SQLCMD. I'm doing this: sqlcmd -S server\instance -U sa -P -A If I don't use -A I receive the famous error: logon failed for sa due trigger execution. If I use -A: > Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : 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 the port number [xFFFFFFFF]. . > Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout > expired. Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : 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.. Why i'm not able to connect to the sql server sql server express here. I could enable SQL Browser, but I still can't access it. I don't think this question is duplicated because is not about the trigger, is about Why I can't access the server with SQLCMD. I pretty much know how to fix this. I just don't know how can I acces sql server without triggering the trigger.

On Linux, whenever I run use mssqlsystemresource to get access to [Resource Database](https://learn.microsoft.com/en-us/sql/relational-databases/databases/resource-database) , I get an error. How do I resolve this. 1> use mssqlsystemresource 2> go Msg 911, Level 16, State 1, Server x230, Line 1 Database 'mssqlsystemresource' does not exist. Make sure that the name is entered correctly. When I try to log in with sqlcmd with DAC (admin:) targeting the database with -d $ sqlcmd -I -S admin:localhost -U sa -d mssqlsystemresource -P Password0 Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Cannot open database "mssqlsystemresource" requested by the login. The login failed.. Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'sa'.. When I try to log in with mssql-cli's -A, I get $ mssql-cli -A -d mssqlsystemresource Username (press enter for sa): Password: Connection did not succeed. Error message: Cannot open database "mssqlsystemresource" requested by the login. The login failed. Login failed for user 'sa'. Unable to connect. Please try again

[sqlcmd](https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility) is documented to have, > Login-Related Options > > -A Logs in to SQL Server with a Dedicated Administrator Connection (DAC). This kind of connection is used to troubleshoot a server. This will only work with server computers that support DAC. If DAC is not available, sqlcmd generates an error message and then exits. For more information about DAC, see Diagnostic Connection for Database Administrators. The -A option is not supported with the -G option. When connecting to SQL Database using -A, you must be a SQL server administrator. The DAC is not availble for an Azure Active Directory adminstrator.

Playing around with SQL Server, I came across a [semi-cryptic error](https://dba.stackexchange.com/q/193333/2639) . Essentially, I only need to log into DAC. Is there any general advice for when DAC is needed? How do I know I need it to access sys.sysobjvalues without googling for the error message? What all does DAC provide access to?