Sql-server – SQL server DAC connection to mssqlsystemresource database

dacsapsap-hanasql server

I have a problem while connecting to the mssqlsystemresource database.

When I try to connect with DAC connection (without restarting SQL server with -m option) it works, but I can't use the mssqlsystemresource database, because the server is not in Single User mode. The only way to use this database is to restart the SQL server in Single user mode.

But once I restart the server with -m option I can no longer connect with DAC connection. I get this error:

the server is on Single-user mode, only an admin can connect to the
server at this moment, SQL error 18461

I'm using SSMS on the same server so no need to verify the firewall
I have SQL Browser service running
I'm using a member of the sysadmin group
I have no other DAC connection to this server

I don't know what to do next. Any help, please?

Best Answer

I just managed to connect to my SQL Server using DAC, where the SQL Server was started in single user mode. I verified single user mode by looking in the errorlog file.

Using SSMS is not a reliable way to use the DAC or single user mode. SSMS has a tendency to open several connections, and you never know which one comes first - even for a single query window. I used SQLCMD. I managed to connect using SSMS single query window as well, but again, sometimes it works sometimes not depending on the orders for the (background) connections you happen to get.

Use SQLCMD.EXE instead of SSMS.

Related Solutions

Sql-server – DAC connection error

Check the SQL Server error log to determine the remote DAC port number. It will be contained in the message Dedicated admin connection support was established for listening remotely on port nnnn.. Verify that port is allowed through the firewall.

You can verify TCP port connectivity with TELNET or the Powershell one-liner below, specifying the port number as the argument piped to the echo command.

nnnn | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }

SQL Server – No DAC Connection on Default Port: Ensure DAC is Enabled

From Using a Dedicated Administrator Connection:

By default, the connection is only allowed from a client running on the server. Network connections are not permitted unless they are configured by using the sp_configure stored procedure with the remote admin connections option.

From that latter page:

SQL Server provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server to execute diagnostic functions or Transact-SQL statements, or to troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a SQL Server Database Engine connection. By default, the DAC is only available from a client on the server. To enable client applications on remote computers to use the DAC, use the remote admin connections option of sp_configure.

I think the page you read is just a little light on the details, that's all.

As for why a local DAC connection might not work, it's possible that it's because you're running SQL Server Express, which requires TF 7806 to enable. Another possibility is the network protocols that are enabled.

Best Answer

Related Solutions

Sql-server – DAC connection error

SQL Server – No DAC Connection on Default Port: Ensure DAC is Enabled

Related Question