sql permissions: connect

Showing posts with label connect. Show all posts

Sunday, March 25, 2012

Default Database?

I got the following after trying to connect to SQLEXPRESS
from a remote computer:
Code:
SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
1> select * from users
1> go
MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
Invalid object name 'Users'
and, after querying the tables in the DB, I find that I am in
"maser", not my intended DB. I have changed (what I thought was) the
relevant Logins to "my_db" from "master", but I still get this error.
The Logins I have changed from master to my intended database are:
"sa"
"(servername)/aspnet"
"builtin/users"
"sqlserver2005ms..."
What am i doing wrong?
Thanks!> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
-E means you login through your windows account. You need to change default database for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>I got the following after trying to connect to SQLEXPRESS
> from a remote computer:
> Code:
> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> 1> select * from users
> 1> go
> MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> Invalid object name 'Users'
>
> and, after querying the tables in the DB, I find that I am in
> "maser", not my intended DB. I have changed (what I thought was) the
> relevant Logins to "my_db" from "master", but I still get this error.
> The Logins I have changed from master to my intended database are:
> "sa"
> "(servername)/aspnet"
> "builtin/users"
> "sqlserver2005ms..."
> What am i doing wrong?
> Thanks!
>|||Thanks.
Even when I take out " -E " the same thing happens. I get the Master
database.
Could you (somebody) kindly tell me what I need to do to change the
default DB
to my intended DB?
Thanks again.
Tibor Karaszi wote:
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> -E means you login through your windows account. You need to change default database for this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
> >I got the following after trying to connect to SQLEXPRESS
> > from a remote computer:
> >
> > Code:
> >
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> > 1> select * from users
> > 1> go
> >
> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> > Invalid object name 'Users'
> >
> >
> >
> > and, after querying the tables in the DB, I find that I am in
> > "maser", not my intended DB. I have changed (what I thought was) the
> > relevant Logins to "my_db" from "master", but I still get this error.
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> > "(servername)/aspnet"
> > "builtin/users"
> > "sqlserver2005ms..."
> >
> > What am i doing wrong?
> > Thanks!
> >|||When you take out the -E, you are still using -E (since that is the
default).
So, you still need to set sp_defaultdb for your windows login.
Or, use SQL authentication by specifying a username and password, and set
sp_defaultdb for that login.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177092683.312878.324120@.b58g2000hsg.googlegroups.com...
> Thanks.
> Even when I take out " -E " the same thing happens. I get the Master
> database.
> Could you (somebody) kindly tell me what I need to do to change the
> default DB
> to my intended DB?
> Thanks again.
> Tibor Karaszi wote:
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> -E means you login through your windows account. You need to change
>> default database for this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "pbd22" <dushkin@.gmail.com> wrote in message
>> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>> >I got the following after trying to connect to SQLEXPRESS
>> > from a remote computer:
>> >
>> > Code:
>> >
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> > 1> select * from users
>> > 1> go
>> >
>> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
>> > Invalid object name 'Users'
>> >
>> >
>> >
>> > and, after querying the tables in the DB, I find that I am in
>> > "maser", not my intended DB. I have changed (what I thought was) the
>> > relevant Logins to "my_db" from "master", but I still get this error.
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> > "(servername)/aspnet"
>> > "builtin/users"
>> > "sqlserver2005ms..."
>> >
>> > What am i doing wrong?
>> > Thanks!
>> >
>|||> The Logins I have changed from master to my intended database are:
> "sa"
I strongly recommend you change the default database for 'sa' back to
master:
ALTER LOGIN sa
WITH DEFAULT_DATABASE = master;
Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
mentioned. Also, you can specify an alternative database context using
SQLCMD -d command-line argument rather than relying on the login's default
database:
SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>I got the following after trying to connect to SQLEXPRESS
> from a remote computer:
> Code:
> SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> 1> select * from users
> 1> go
> MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> Invalid object name 'Users'
>
> and, after querying the tables in the DB, I find that I am in
> "maser", not my intended DB. I have changed (what I thought was) the
> relevant Logins to "my_db" from "master", but I still get this error.
> The Logins I have changed from master to my intended database are:
> "sa"
> "(servername)/aspnet"
> "builtin/users"
> "sqlserver2005ms..."
> What am i doing wrong?
> Thanks!
>|||Dan -
Thank you. I appreciate your advice.
I know how to access my default database.
I didn't know the command you suggested, but
it seems to be the same as using "use MyDefaultDB"
on the command line.
The problem is, I want "MyDefaultDB" to be the default
database when I connect from VisualWebDeveloper and
right now VisualWebDeveloper is telling me that the user
doesn't exist when he tries to log on which means that
it is searching for my USERS table in the master table.
So:
> I strongly recommend you change the default database
> for 'sa' back to master:
How can I do what you are saying and allow my users to
login?
Thanks.
Dan Guzman ote-wray:
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> I strongly recommend you change the default database for 'sa' back to
> master:
> ALTER LOGIN sa
> WITH DEFAULT_DATABASE = master;
> Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
> mentioned. Also, you can specify an alternative database context using
> SQLCMD -d command-line argument rather than relying on the login's default
> database:
> SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "pbd22" <dushkin@.gmail.com> wrote in message
> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
> >I got the following after trying to connect to SQLEXPRESS
> > from a remote computer:
> >
> > Code:
> >
> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
> > 1> select * from users
> > 1> go
> >
> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
> > Invalid object name 'Users'
> >
> >
> >
> > and, after querying the tables in the DB, I find that I am in
> > "maser", not my intended DB. I have changed (what I thought was) the
> > relevant Logins to "my_db" from "master", but I still get this error.
> > The Logins I have changed from master to my intended database are:
> >
> > "sa"
> > "(servername)/aspnet"
> > "builtin/users"
> > "sqlserver2005ms..."
> >
> > What am i doing wrong?
> > Thanks!
> >|||> The problem is, I want "MyDefaultDB" to be the default
> database when I connect from VisualWebDeveloper and
> right now VisualWebDeveloper is telling me that the user
> doesn't exist when he tries to log on which means that
> it is searching for my USERS table in the master table.
Just to be clear, are you getting the "Invalid object name 'Users'" message
in VisualWebDeveloper? Perhaps your VisualWebDeveloper connection is
specifying server or database context other than the one desired. Another
possible cause for the invalid object name error is that the object is not
in your default schema. The Best Practice is to schema-qualify object names
(e.g. SELECT * FROM dbo.Users).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177170707.388662.126830@.b58g2000hsg.googlegroups.com...
> Dan -
> Thank you. I appreciate your advice.
> I know how to access my default database.
> I didn't know the command you suggested, but
> it seems to be the same as using "use MyDefaultDB"
> on the command line.
> The problem is, I want "MyDefaultDB" to be the default
> database when I connect from VisualWebDeveloper and
> right now VisualWebDeveloper is telling me that the user
> doesn't exist when he tries to log on which means that
> it is searching for my USERS table in the master table.
> So:
>> I strongly recommend you change the default database
>> for 'sa' back to master:
> How can I do what you are saying and allow my users to
> login?
> Thanks.
> Dan Guzman ote-wray:
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> I strongly recommend you change the default database for 'sa' back to
>> master:
>> ALTER LOGIN sa
>> WITH DEFAULT_DATABASE = master;
>> Note that ALTER LOGIN is an alternative to the sp_defaultdb method Aaron
>> mentioned. Also, you can specify an alternative database context using
>> SQLCMD -d command-line argument rather than relying on the login's
>> default
>> database:
>> SQLCMD -d MyDatabase -E -S 192.168.0.10\SQLEXPRESS,2708
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "pbd22" <dushkin@.gmail.com> wrote in message
>> news:1177086809.684832.131880@.d57g2000hsg.googlegroups.com...
>> >I got the following after trying to connect to SQLEXPRESS
>> > from a remote computer:
>> >
>> > Code:
>> >
>> > SQLCMD -E -S 192.168.0.10\SQLEXPRESS,2708
>> > 1> select * from users
>> > 1> go
>> >
>> > MSG 208, Level 16, State 1, Server DISNYLAND\SQLEXPRESS, Line 1
>> > Invalid object name 'Users'
>> >
>> >
>> >
>> > and, after querying the tables in the DB, I find that I am in
>> > "maser", not my intended DB. I have changed (what I thought was) the
>> > relevant Logins to "my_db" from "master", but I still get this error.
>> > The Logins I have changed from master to my intended database are:
>> >
>> > "sa"
>> > "(servername)/aspnet"
>> > "builtin/users"
>> > "sqlserver2005ms..."
>> >
>> > What am i doing wrong?
>> > Thanks!
>> >
>|||> I didn't know the command you suggested, but
> it seems to be the same as using "use MyDefaultDB"
> on the command line.
No, it is not. If your user has a default database of 'foo' but the user
cannot access foo, they won't have a chance to say 'use bar' because they
won't be able to log in. It depends on the tool you are using, but this is
the case in many tools...|||Dan -
Thanks for your reply. I thought I had fixed this (hence the delay)
but the error
has returned.
> Just to be clear, are you getting the "Invalid object name 'Users'" message
> in VisualWebDeveloper?
I "am" getting the "Invalid object name "Users" " error when I
try to connect using the SQLCMD. It is obvious that the command is
trying to
validate my login using the master DB and there is no Users table
there.
My connection string looks like the following:
<add name="myConnectionString" connectionString="Data
Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
Integrated Security=True; uid=sa; pwd=mypassword;"
providerName="System.Data.SqlClient"/>
I am using the 'sa' account for access and, if the 'sa' default
database is master
I am still a bit lost as to how to validate against a table that isn't
in the master DB.
> Another possible cause for the invalid object name error is that the object is
> not in your default schema. The Best Practice is to schema-qualify object
names
> (e.g. SELECT * FROM dbo.Users).
When I am in the database, I can use select * from users or select *
from dbo.users - they both work.
This is taking me a while but - how do i change the default database
for when
my users login?|||> trying to
> validate my login using the master DB and there is no Users table
> there.
I think you're confusing a users table that you created with SQL Server's
internal tables for maintaining login and user information.
> This is taking me a while but - how do i change the default database
> for when
> my users login?
Take a look at sp_defaultdb in Books Online.|||> This is taking me a while but - how do i change the default database
> for when
> my users login?
As Aaron mentioned, you can specify the default database for a login with
sp_default_db. Since you are using SQL 2005, you can alternatively use DCL:
ALTER LOGIN MyLogin
WITH DEFAULT_DATABASE = MyLogin;
However, the login's default database is used only if you do not specify a
database context when connecting. Try this, which I would expect this to
succeed as long as your Windows account has access to the Trezoro database:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT * FROM
dbo.users"
GO
If the above failes with error "invalid object name dbo.users", run the
following to verify database and user context:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT DB_NAME(),
USER"
If the database and user are as expected, verify the table actually exists:
SQLCMD -E -S -d Trezoro 192.168.0.10\SQLEXPRESS,2708 - Q"SELECT name FROM
sys.tables WHERE name = 'users'"
> <add name="myConnectionString" connectionString="Data
> Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
> Integrated Security=True; uid=sa; pwd=mypassword;"
> providerName="System.Data.SqlClient"/>
> I am using the 'sa' account for access and, if the 'sa' default
> database is master
> I am still a bit lost as to how to validate against a table that isn't
> in the master DB.
Because you specified 'Integrated Security=True' in the connection string,
the user and password specification are ignored. The connection is instead
done under the context of a Windows account. I'm not an web guy but I
believe your Windows account is used when you run from the IDE. This
doesn't explain why you would get the invalid object error, though. I would
first troubleshoot the problem with SQLCMD before you try the application.
In any case, you should never use 'sa' for routine development or
application access.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"pbd22" <dushkin@.gmail.com> wrote in message
news:1177879467.944332.115100@.n59g2000hsh.googlegroups.com...
> Dan -
> Thanks for your reply. I thought I had fixed this (hence the delay)
> but the error
> has returned.
>> Just to be clear, are you getting the "Invalid object name 'Users'"
>> message
>> in VisualWebDeveloper?
> I "am" getting the "Invalid object name "Users" " error when I
> try to connect using the SQLCMD. It is obvious that the command is
> trying to
> validate my login using the master DB and there is no Users table
> there.
> My connection string looks like the following:
> <add name="myConnectionString" connectionString="Data
> Source=192.168.0.10\SQLEXPRESS,2708;Initial Catalog=Trezoro;
> Integrated Security=True; uid=sa; pwd=mypassword;"
> providerName="System.Data.SqlClient"/>
> I am using the 'sa' account for access and, if the 'sa' default
> database is master
> I am still a bit lost as to how to validate against a table that isn't
> in the master DB.
>> Another possible cause for the invalid object name error is that the
>> object is
>> not in your default schema. The Best Practice is to schema-qualify
>> object
> names
>> (e.g. SELECT * FROM dbo.Users).
> When I am in the database, I can use select * from users or select *
> from dbo.users - they both work.
> This is taking me a while but - how do i change the default database
> for when
> my users login?
>sql

default database for opening a .sql file?

I am using SQL Server Management Studio. Whenever I open a .sql file, it
does 2 annoying things:
1) prompts me to connect, yet again.
2) sets the database drop down to master again.
The second one, in particular, is really annoying. Is there a way to change
the database that it connects to when I open a .sql file?
Note: setting the default database for the server worked for windows created
with "New Query" but not for opening .sql files.
Heh, 2 seconds after posting I stumble across it. When it prompts me to
connect, there is an Options button. I clicked it and on the Connection
Properties tab it had the same "connect to database" dropdown that I used to
set the default for new query windows. Now whenever I open a .sql file it
goes to the right database.
"Adam J. Schaff" <aschaff@.cascodev.com> wrote in message
news:%23dCE6D4IHHA.4928@.TK2MSFTNGP06.phx.gbl...
>I am using SQL Server Management Studio. Whenever I open a .sql file, it
>does 2 annoying things:
> 1) prompts me to connect, yet again.
> 2) sets the database drop down to master again.
> The second one, in particular, is really annoying. Is there a way to
> change the database that it connects to when I open a .sql file?
> Note: setting the default database for the server worked for windows
> created with "New Query" but not for opening .sql files.
>
|||>I am using SQL Server Management Studio. Whenever I open a .sql file, it
>does 2 annoying things:
> 1) prompts me to connect, yet again.
SQL Server 2005 Service Pack 2 will fix this problem. We just released a
December CTP that has this improvement and many more.
http://www.microsoft.com/downloads/details.aspx?FamilyID=d2da6579-d49c-4b25-8f8a-79d14145500d&DisplayLang=en
Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/
"Adam J. Schaff" <aschaff@.cascodev.com> wrote in message
news:%23dCE6D4IHHA.4928@.TK2MSFTNGP06.phx.gbl...
>I am using SQL Server Management Studio. Whenever I open a .sql file, it
>does 2 annoying things:
> 1) prompts me to connect, yet again.
> 2) sets the database drop down to master again.
> The second one, in particular, is really annoying. Is there a way to
> change the database that it connects to when I open a .sql file?
> Note: setting the default database for the server worked for windows
> created with "New Query" but not for opening .sql files.
>

Wednesday, March 21, 2012

Dedicated Administrator Connection

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

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

|||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

Dedicated Administrator Connection

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

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).

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:

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

|||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.

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).

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

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

|||

However thanks for all the suggestions.

|||

Hi,

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

Dedicated Administrator Connection

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

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).

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:

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

|||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.

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).

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

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

|||

However thanks for all the suggestions.

|||

Hi,

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

sql

Dedicated Administrator Connection

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

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).

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:

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

|||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.

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).

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

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

|||

However thanks for all the suggestions.

|||

Hi,

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

Saturday, February 25, 2012

Debugging stored procedures in SQL2005

I am getting following error message while trying to step into stored procedue:

"Unable to start T-SQL Debugging. Could not connect to computer 'xxx'. The referenced account is currently locked out and may not be logged on to"

I use VS 2005 Professional edition. SQL Server Authentication is used to create data connection in Server Explored. SQL/CLR debugging is enabled for the connection. I followed all steps from this article:

Setting Up SQL Debugging http://msdn2.microsoft.com/en-us/library/s4sszxst(VS.80).aspx

Any help?

Thanks

Slava

It sounds like your password has expired. You can change your password using SQL Server management studio, or you can ask a system administrator to do it for you.

Tuesday, February 14, 2012

Debug in QA Quit after SP2

Three development team members connect to SQL server 2000 on a 2003 server
within a windows domain. Used QA to debug stored procedures for several
months. Two of the three have installed XP SP2 on local machine. Now,
debug stops working on all three and receive the following message.
************************************************** *******
Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on CSTSMAIN (Error = 0x80070005). Ensure that client-side components, such
as SQLDBREG.EXE, are installed and registered on CJAD502KR41. Debugging
disabled for connection 67.
************************************************** **********
Regards,
Michael Mach
no fix but check below
http://support.microsoft.com/default.aspx?kbid=839280
Andy.
"Michael Mach" <Michael.Mach@.cmaaccess.com> wrote in message
news:e8pTgahiEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Three development team members connect to SQL server 2000 on a 2003 server
> within a windows domain. Used QA to debug stored procedures for several
> months. Two of the three have installed XP SP2 on local machine. Now,
> debug stops working on all three and receive the following message.
> ************************************************** *******
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to
debugger
> on CSTSMAIN (Error = 0x80070005). Ensure that client-side components, such
> as SQLDBREG.EXE, are installed and registered on CJAD502KR41. Debugging
> disabled for connection 67.
> ************************************************** **********
>
> Regards,
> Michael Mach
>
|||This is a known issue with WinXP SP2. A fix for this has shipped in hotfix
944 on 6/2/04. It shipped in 32-bit only originally. Any later hotfix for
32-bit or 64-bit also includes this due to the cumulative nature of our
hotfixes.
We will be updating the following KB to reflect this:
BUG: SQL debugging does not work in Visual Studio .NET after you install
ID: 839280
To get a hotfix you need to contact PSS.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Mach" <Michael.Mach@.cmaaccess.com> wrote in message
news:e8pTgahiEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Three development team members connect to SQL server 2000 on a 2003 server
> within a windows domain. Used QA to debug stored procedures for several
> months. Two of the three have installed XP SP2 on local machine. Now,
> debug stops working on all three and receive the following message.
> ************************************************** *******
> Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to
debugger
> on CSTSMAIN (Error = 0x80070005). Ensure that client-side components, such
> as SQLDBREG.EXE, are installed and registered on CJAD502KR41. Debugging
> disabled for connection 67.
> ************************************************** **********
>
> Regards,
> Michael Mach
>
|||Euan,
I plead ignorant. How does one contact PSS?
--Scott
slbrook at hotmail.com
"Euan Garden[MS]" <euang@.online.microsoft.com> wrote in message
news:%23vw%230L8jEHA.704@.TK2MSFTNGP12.phx.gbl...
> This is a known issue with WinXP SP2. A fix for this has shipped in hotfix
> 944 on 6/2/04. It shipped in 32-bit only originally. Any later hotfix for
> 32-bit or 64-bit also includes this due to the cumulative nature of our
> hotfixes.
> We will be updating the following KB to reflect this:
> BUG: SQL debugging does not work in Visual Studio .NET after you install
> ID: 839280
> To get a hotfix you need to contact PSS.
> --
> -Euan
> Please reply only to the newsgroup so that others can benefit. When
> posting,
> please state the version of SQL Server being used and the error
> number/exact
> error message text received, if any.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Michael Mach" <Michael.Mach@.cmaaccess.com> wrote in message
> news:e8pTgahiEHA.1652@.TK2MSFTNGP09.phx.gbl...
> debugger
>
|||Nevermind.
I found it.
Thanks for the tip.
--scott
"Scott Brookhart" <slbrook@.hotmail.com> wrote in message
news:uJcqlPSkEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Euan,
> I plead ignorant. How does one contact PSS?
> --Scott
> slbrook at hotmail.com
> "Euan Garden[MS]" <euang@.online.microsoft.com> wrote in message
> news:%23vw%230L8jEHA.704@.TK2MSFTNGP12.phx.gbl...
>

Sunday, March 25, 2012

Default Database?

Default Database?

Default Database?

default database for opening a .sql file?

Wednesday, March 21, 2012

Dedicated Administrator Connection

Dedicated Administrator Connection

Dedicated Administrator Connection

Dedicated Administrator Connection

Saturday, February 25, 2012

Debugging stored procedures in SQL2005

Tuesday, February 14, 2012

Debug in QA Quit after SP2

sql permissions

Blog Archive

About Me