User authentication

[Ted]

I have figured out how to use MS SQL Server Management Studio for SQL
Server 2005, including how to create users.

At my client's offices, on my own LAN (a one man office with two
computers in a peer to peer LAN - machines running the professional
edition of WXP), and on my colleagues computers (stand alone
notebooks), SQL Server was installed allowing both local and remote
connections, and support for both Windows and SQL Server
authentication.

At my client's offices, both local and remote connections work. On our
respective notebook computer/workstations only local connections work,
unless we're connecting to our client's network, in which case remote
connections work.

So far, it would seem all is well. However, there are two issues.

1) With regard to local connections on our notebooks, I can connect to
the default server using SQLCMD without specifying any command line
arguments, while my colleages have to specify at least the machine and
server name. I don't understand why.

2) On my peer to peer LAN, MS SQL Server Management Studio can not find
the other machine that is running SQL Server, and using SQLCMD, I can't
connect to the other server when I provide the proper credentials for a
user I set up on it to use SQL Server authentication. What should I
look at in order to find out why this is and how to fix it?

Thanks

Ted

[Ted]

Erland Sommarskog wrote:

Ted (r.*********@ro gers.com) writes:

As you can see, remote access to the default instance works fine, but I
get an error when I try to access the named instance.

I have used the SQL Server Management Studio to verify that mixed
authentication, and remote connections, are enabled on the named
instance (and the uid was create in the same way, using the same pwd,
on both instances).

Mangement Studio?

A better place to look is the SQL Server Configuration Manager or the
Surface Area Configuration tool. You need to enable the new instance
for remote connections as well.

Also, a named instance may require that the SQL Browser Service is running.

It looks like I needed all three. I verified that all machines and
instances use TCP/IP and that they are enabled for remote connections.
And on my desktop, I noticed that SQL Browser Service had somehow been
stopped, so I restarted it. Using all three tools, I checked all of
the security and connection properties on both machines, and I made
sure that all installed servers/services/instances are running.

The outcome is that I CAN now connect to the named instance on my
desktop using SQLCMD on my notebook without difficulty, now at least.
However, I can not connect to the named instance on my notebook using
SQLCMD on my desktop. In this case, I get the same error that I
reported previously. This isn't a showstopper since I can work with
the connections going in only one direction, but it makes me nervous
since, not understanding why connections from my desktop to my notebook
don't work, it seems possible that when we set this up on our client's
network, similar problems may arise. I will need to write up a
procedure that my colleagues can use to create and set up named
instances as the need arises.

Any ideas?

Ted

[Erland Sommarskog]

Ted (r.*********@ro gers.com) writes:

The outcome is that I CAN now connect to the named instance on my
desktop using SQLCMD on my notebook without difficulty, now at least.
However, I can not connect to the named instance on my notebook using
SQLCMD on my desktop. In this case, I get the same error that I
reported previously. This isn't a showstopper since I can work with
the connections going in only one direction, but it makes me nervous
since, not understanding why connections from my desktop to my notebook
don't work, it seems possible that when we set this up on our client's
network, similar problems may arise. I will need to write up a
procedure that my colleagues can use to create and set up named
instances as the need arises.

Any ideas?

Obviously, when you try to debug connection issues on machines that you
don't see, you will have to rely on what you are told about the machines.
I can really only suggest that you review the desktop machine in the same
manner, and also verify that there is no firewall in the way.

If you are really desperate, you can get the port number for the
instance from SQL Configureation Manager, and then connect with:

SQLCMD -S MACHINE,2056

where you replace 2056 with the actual port number.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx