Recently you may have read my article about some hidden functions in SQL Server. In that article you learned that those functions were in some DMOs and that you could get at them through the resource database.
Today I found myself learning more about the resource database. Due to what I had learned in my prior foray into the resource database, I was curious if certain other functions might call some hidden functions in that database.
Sadly – they did not. But in my travels I did happen across something else that is in that database. Those items are called system base tables. Unlike the trio of functions from the last article – you can get to these but it is STRONGLY advised to not do it.
Naturally, I want to check these tables out – especially since the MSDN article does say how to get to them. I will write about some adventures into looking at these tables in the future. I already found one interesting thing that seemed odd – but first I will need to login using the DAC and start testing to confirm a hypothesis.
For now, I want to cover how to create a Dedicated Administrator Connection. This should be something that DBAs know how to do. It isn’t difficult, and I will only cover one method and leave the other method to the Microsoft documentation.
You can create a DAC through either SSMS or through SQLCMD. You can create one remotely, but you will need to enable that option since it is disabled by default. You can find the method for creating this connection via SQLCMD here.
To create a connection through SSMS, it is rather easy as well. You simply add (case insensitive) “admin:” to the beginning of your server as shown in this image.
In order for this to work, you will need to have the browser service running. If it is not running, you will get an error message. This error message is informative if you read it. It will provide a clue to look at the browser service.
Once you have successfully created this connection, you can now use it when necessary to perform administrative tasks or for some learning opportunities. If you open a query using this connection you will see something like this next image in your query tab.
You can see in the tab of this query tab that there is the label “ADMIN:”. This is your DAC connection. You are limited to one of these at a time – period.
If you try to create a second connection, you will get a nasty message. The message is not entirely informative – just understand that you are getting it because you already have a DAC open.
It is a good idea to become familiar with how to connect via the DAC. I have a connection saved for quick access. Luckily I have a development server which I can test and use for learning opportunities. As the warning MSDN states: “Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.” If you venture into the system base tables via the DAC – Microsoft will not support it if you break it.
