Allow remote connections on SQL Server 2012 Express .. - Stack

SQL 2012 Remote Connections

Connection / August 5, 2019

There’s a DAC in SQL Server that you should know and love. Sadly, you probably either haven’t met or you’ve forgotten to turn it on. Check out how it works in this video, or scroll on down to read the details.

DAC? What’s That?

First, a little disambiguation. The acronym ‘DAC’ is too popular.

SQL Server implements a totally unrelated DAC as well: a data tier application or DAC package. We’re not talking about that here.

What Can DAC Do For You?

Have a SQL Server that’s in crisis? The DAC can help you stage an intervention.

The Dedicated Admin Connection was built to help you connect and run basic troubleshooting queries in cases of serious performance problems. This is your opening to grab a ‘Get Out of Jail’ card, but since you don’t use this on a regular basis it’s easy to forget how to use it. It’s also easy to forget to enable access to the DAC remotely.

How the Dedicated Admin Connection Works

The DAC uses a special reserved scheduler which has one thread for processing requests. This essentially means that SQL Server is keeping a backdoor open to processor resources available just for you.

Don’t be too tempted to abuse this privilege. That one thread is just one thread— there’s no parallelism for queries running on the DAC. Two percent of you will be tempted to use this for your maintenance jobs on busy systems. Seriously, just don’t go there. The DAC was not designed for high performance.

How to Enable the DAC for Remote Connections and Clusters

By default the DAC is only enabled for accounts logged onto the local machine. For production servers, that means it only works for remote desktop sessions to non-clustered SQL Server instances. If your instance is clustered or if you’re connecting over TCP/IP, you’re out of luck unless you change one setting. That setting is ‘Remote Admin Connections’.

If SQL Server is unresponsive and the DAC listener is not enabled, you might have to restart SQL Server to connect with the DAC.

For clusters, sign me up! I’m in favor of enabling it for other instances as well. In times of trouble, you want to minimize the amount of time you spend using remote desktop on a server having problems. You want to use that only to gather information you can’t get another way.

Enabling the DAC for remote connections is easy as pie. It is controlled by the ‘Remote Admin Connections’ setting. To enable it, you simply run this bit of code:

EXEC sp_configure 'remote admin connections', 1; GO RECONFIGURE GO

There’s always a catch. You may need to get firewall ports opened as well, depending on your environment and where you intend to connect from. This will probably be port 1434, but that will vary depending on your configuration. (Books Online has got your back: read more in the “DAC Port” section here.)

Only One Sysadmin Can Ride This Horse At a Time

This isn’t a party line, only one sysadmin at a time can use the DAC. Also, you should only run simple, quick queries using the DAC.

Is this a party line?

In other words, only connect to the DAC when you really need to. When you’re done wipe the seat make sure to disconnect.

How to Connect to the DAC

You can connect to the DAC using the command line. Use the “-A” option with SQLCMD.exe.

I find it more convenient to connect in Management Studio itself. You do this by prefixing the instance name you are connecting to with “Admin:”.

One FYI: Object Explorer can’t connect to the DAC. If you open SSMS and have Object Explorer connecting by default, the first connection prompt you see with be for that. If you try to tell that to connect to the DAC, it’ll fail. That’s a good thing, we wouldn’t want the power to go to Object Explorer’s head.

Who’s Been Sleeping in My DAC? How to Tell Who’s using the Dedicated Admin Connection.

If you try to connect to the DAC when someone is already connected, you’ll see a connection error. It probably won’t tell you straight out that someone’s connected to the DAC already, but if you check the SQL Server Error log you should see the 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.

So if you can’t get the DAC, how can you tell who’s using it?

Here’s a hint— the DAC uses a Dedicated TCP Endpoint. It’s even endpoint #1. The DAC is ranked #1 by endpoints everywhere!

This makes finding someone using the DAC nice and simple, because endpoint_id is listed in sys.dm_exec_sessions. So you’ll just need a little something like this:

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'

Your Mission: Get Back with your DAC Today

Take a few minutes today to connect with the DAC in a test environment. Check whether you have remote admin connections enabled in your environments, and talk about what the impact of that might be.

Source: www.brentozar.com