Hi:
I am trying to connect to my local instance of SQL Server 2005 using Dedicated Administrator Connection by using
Admin:Servername\Instancename
and I end up with the following error.
Dedicated administrator connections are not supported.
I enabled the connection in the SQL Configuration Manager and still get the error.
Any ideas on how to resolve this error?.
Note: SQL Browser service is running.
Thanks
Ak
Which SKU of sql server 2005 are you running ?
Also, are the client and the server running on the same machine or different machines ?
|||Hi Asvin:
I am running SQL Server 2005 Standard Edition and both the client and server are running on the same machine.
Thanks
|||You can check the sqlserver log to see if DAC is enabled/listening (and on which tcp port).Use sqlcmd -A to test. Also, you cannot use DAC for Object Explorer connection. It only works on query window. So, connect normally first, then open a new query window with "admin:<server>\<instance>"|||
Hi OJ:
Thanks for your reply.
This is what my SQL Server log says.
Date 7/7/2006 4:56:11 PM
Log SQL Server (Current - 7/12/2006 12:00:00 AM)
Source Server
Message
Dedicated admin connection support was established for listening remotely on port 1771.
So DAC is listening on port 1771 right?. I tried your second method of connecting to through object explorer but I got the same error message.
Then I tried the following from command prompt:
sqlcmd -SMyMachine\SQL2K5 -A -E
I got the following error message:
HResult 0xFFFFFFFF, Level 16, State 1
SQL 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 SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings
SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Please let me know what I am doing wrong.
Thanks
|||MS Experts:
Can anyone please help me with this issue. I would really appreciate all your help.
Thanks
AK
|||Sorry for the late reply.I've seen the same error when the server\instance is incorrect. Since this is a local instance, please try one of the following:
sqlcmd -S".\SQL2K5" -A -E
sqlcmd -S"MyMachine,1771" -A -E
Be sure to start the instance first.
net start mssql$sql2k5
|||
Did you enable remote connections ?
http://www.sqlserver2005.de/SharedFiles/Remoteconnection.jpg
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Thanks OJ and Jens. I tried all options that you have suggested (my remote connections are enabled) but still get the same error.
The funny thing is when I try through the command prompt using sqlcmd (with the new commands you gave) it appears to be connected but when I connect using SQL Management Studio while the command window is still open, it allows me to establish a new connection through Management studio. I think once DAC is established i should not be able to make a new connection using management studio. DAC should allow only a single connection. This tells me that a DAC was not establised through SQLCMD from command prompt.
So to make sure that I can indeed connect using DAC I used the SQL Management Studioand tried the following:
ADMIN:.\SQL2K5 and end up with the same error.
Dedicated adminstrator connections are not supported.
This means I am not able to connect using DAC still.
I cant post the attachment or else I would have posted the attachment.
|||There can only be ONE admin connection. If you successfully connect via "sqlcmd -A" then you will not be able to make another admin connection via sqlwb (vice versa).Admin connection has nothing to do with regular user connection. So, it's normal that you still can connect via sqlwb. However, it will not be possible for you to open another admin connection (i.e. admin:\.sql2k5) via sqlwb if you already connect with sqlcmd.
As I have said before, you need to connect normally (without admin) when you first open up sqlwb. Once that's done, you can open a new query window (File -> New -> Database Engine Query) and connect as an admin connection.
If you still have problem, please post the entire sql log here.|||
Do you obtain this error message:
"Dedicated administrator connections are not supported. (ObjectExplorer)"
If so you try to connect the Object Explorer. Try to open a new query windows as oj told you.
|||
Thanks everyone. I seem to have connected through the cmd prompt through DAC and when I query using @.@.Servername I can see my machine name in the cmd prompt window. I am just wondering if there is a command in SQL Server 2005 that tells me whether I am connected using dedicated admin connection or that my present connection is a DAC. If so, I would appreciate if if anyone lets me know it.
Thanks
AK.
|||The query below should return 1 row if you are connected using dedicated admin connection. It should not return any rows if you are not connected using DAC.
select * from sys.dm_exec_connections ec join sys.endpoints e on (ec.endpoint_id=e.endpoint_id) where e.name='Dedicated Admin Connection' and session_id=@.@.spid
|||Thanks Ashvin. That did exactly confirm that I was connected using DAC. It returned one row. So my million dollar question is why cant I connect to DAC using ADMIN keyword. I tried all that have been suggested in this thread and still it says DAC connections are not supported (Object Explorer). Its a mystery!!!.
However thanks for all the suggestions.
|||Hi,
DAC is a feature which is disabled by default. Did you enable it so far ? I made a screencast for you showing how to enable this feature. This is the second of a series which I decided to make. So have Fun !
A link with the screencasts taken so far will be on my site as I will have time for that.
use this link here in the meantime: http://www.SQLServer2005.de/ScreenCasts/Enable_DAC.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
No comments:
Post a Comment