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

[Erland Sommarskog]

Re: User authentication

Ted (r.ted.byers@ro gers.com) writes:

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.

I take it that at your client there is a domain controller, while your
and your colleague's LAN is only a workgroup? Things are usually much
easier with a domain. Myself, I can connect to SQL Server on my
machines, but I yet to figure out how to mount discs.

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.

Is he running SQL Server on port 1433? And does really have a default
instance?

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?

What happens when you connect? Do you get a message that login failed,
or a message that indicates that the server was not found at all?

Can you reach the machine for other operations, for instance to mount
network drives?

What about any firewall?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ted]

Re: User authentication


Erland Sommarskog wrote:

Ted (r.ted.byers@ro gers.com) writes:

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.

>
I take it that at your client there is a domain controller, while your
and your colleague's LAN is only a workgroup? Things are usually much
easier with a domain. Myself, I can connect to SQL Server on my
machines, but I yet to figure out how to mount discs.
>

That's right. The MIS there has set up three domains, and created
credentials for each of us in one of them.

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.

>
Is he running SQL Server on port 1433? And does really have a default
instance?
>

This I don't know, but can check tomorrow.

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?

>
What happens when you connect? Do you get a message that login failed,
or a message that indicates that the server was not found at all?
>

Specifying my UID/pwd along with the workstation's name (parameters -U,
-P, and -H) as arguments for SQLCMD, I get the following error message:

Msg 18452, Level 14, State 1, Server TEDSNEWACER, Line 1
Login failed for user 'TByers'. The user is not associated with a
trusted SQL Server connection.

I can not connect at all using SQL Server Management Studio. It does
not see the other workstation at all, and so I can't add a user,
defined on the other workstation, using it and configure it to use
Windows authentication.

Can you reach the machine for other operations, for instance to mount
network drives?
>

Yes. It appears in 'My Network Places', and I can easily move files
between the machines.

What about any firewall?
>

There are software firewalls on both machines, as well as a hardware
firewall on the router.

Thanks

Ted

[Erland Sommarskog]

Re: User authentication

Ted (r.ted.byers@ro gers.com) writes:

Specifying my UID/pwd along with the workstation's name (parameters -U,
-P, and -H) as arguments for SQLCMD, I get the following error message:
>
Msg 18452, Level 14, State 1, Server TEDSNEWACER, Line 1
Login failed for user 'TByers'. The user is not associated with a
trusted SQL Server connection.

That means that the server is not configured for SQL authentication.

I can not connect at all using SQL Server Management Studio. It does
not see the other workstation at all, and so I can't add a user,
defined on the other workstation, using it and configure it to use
Windows authentication.

Windows authentication is a difficult thing to get working in a workgroup.
I have not been successful with my machines at home.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ted]

Re: User authentication


Erland Sommarskog wrote:

Ted (r.ted.byers@ro gers.com) writes:

Specifying my UID/pwd along with the workstation's name (parameters -U,
-P, and -H) as arguments for SQLCMD, I get the following error message:

Msg 18452, Level 14, State 1, Server TEDSNEWACER, Line 1
Login failed for user 'TByers'. The user is not associated with a
trusted SQL Server connection.

>
That means that the server is not configured for SQL authentication.
>

Thanks. I finally found the part of SQL Server Management Studio where
I can change this. It wasn't where I'd expected it to be, but I found
it and changed it, and now all problems on my LAN have been resolved.
AND all my scripts (both SQL and Perl) run on my client's server as
they do on mine.

There are two, probably related, differences between my notebook
computer and my colleagues' notebooks. 1) They are using SQL Server
Express 2005 and I am using the developer's edition of SQL Server 2005.
2) I can connect to my server using SQLCMD without specifying any
command line arguments while they must specify the server machine and
instance (i.e. SQLCMD -S my_machine\an_i nstance). Probably related to
this, if I edit my perl script to provide the additional parameters
they have to submit, connection fails while the very same commandline
submitted on the commandline instead of through Perl works. i.e.
Submitting SQLCMD -S my_machine\an_i nstance on the commandline works
but connection fails if it is submitted from within a perl script.

Thanks again

Ted

[Erland Sommarskog]

Re: User authentication

Ted (r.ted.byers@ro gers.com) writes:

There are two, probably related, differences between my notebook
computer and my colleagues' notebooks. 1) They are using SQL Server
Express 2005 and I am using the developer's edition of SQL Server 2005.
2) I can connect to my server using SQLCMD without specifying any
command line arguments while they must specify the server machine and
instance (i.e. SQLCMD -S my_machine\an_i nstance).

By default Developer Edition installs a default instance. And by default
Express Edition installs as a named instance, SQLEXPRESS.

When you say SQLCMD only, this implies a default instance on the local
server. Thus, if you have a named instance, you need to specify this.
".\SQLEXPRE SS" should be sufficient.

Probably related to this, if I edit my perl script to provide the
additional parameters they have to submit, connection fails while the
very same commandline submitted on the commandline instead of through
Perl works. i.e. Submitting SQLCMD -S my_machine\an_i nstance on the
commandline works but connection fails if it is submitted from within a
perl script.

It's not entirely clear, but it sounds as if you specify the instance
name of the laptpos, when to connect the Perl scripts to your local instance
on your workstation. That will not fly, since you have a default instance.

Anyway, without error message and the Perl code, it's a bit difficult
to do more than to guess.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ted]

Re: User authentication


Erland Sommarskog wrote:

Ted (r.ted.byers@ro gers.com) writes:

There are two, probably related, differences between my notebook
computer and my colleagues' notebooks. 1) They are using SQL Server
Express 2005 and I am using the developer's edition of SQL Server 2005.
2) I can connect to my server using SQLCMD without specifying any
command line arguments while they must specify the server machine and
instance (i.e. SQLCMD -S my_machine\an_i nstance).

>
By default Developer Edition installs a default instance. And by default
Express Edition installs as a named instance, SQLEXPRESS.
>
When you say SQLCMD only, this implies a default instance on the local
server. Thus, if you have a named instance, you need to specify this.
".\SQLEXPRE SS" should be sufficient.
>

OK. So the ultimate cause of the differences in behaviour we're seeing
is the different default behaviour of the installation of SQL Express
version relative to the developer's, and workgroup (which I see
behaving exactly the same way on our client's machine as the
developer's edition on my machine), editions.

Can SQL Express be told to use a default instance? If so, how?

Where should I look in the documentation to learn how to create a named
instance?

Probably related to this, if I edit my perl script to provide the
additional parameters they have to submit, connection fails while the
very same commandline submitted on the commandline instead of through
Perl works. i.e. Submitting SQLCMD -S my_machine\an_i nstance on the
commandline works but connection fails if it is submitted from within a
perl script.

>
It's not entirely clear, but it sounds as if you specify the instance
name of the laptpos, when to connect the Perl scripts to your local instance
on your workstation. That will not fly, since you have a default instance.
>

On my laptop, I don't specify the instance, but my colleagues must do
so if they are to connect.

They error they get should they try to connect without specifying the
instance is:
=============== =============
C:\ >sqlcmd

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

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

Anyway, without error message and the Perl code, it's a bit difficult
to do more than to guess.
>

Here is the perl code I run on my machine:

print "\n\nLoadin g data to temporary tables.\n";
my $cmdline = "sqlcmd -i temporary_load_ script_file.sql -o
load.output1.tx t";
my $rv = qx/$cmdline/;
$now_string = localtime;
if (length($rv) == 0) {
print "The data has been loaded into the temporary tables now,
$now_string.\n" ;
print log_file "The data has been loaded into the temporary tables
now, $now_string.\n" ;
} else {
print "Loading data into the temporary tables failed now, at
$now_string, with the following error:\n$rv";
print log_file "Loading data into the temporary tables failed now, at
$now_string, with the following error:\n$rv";
exit;
}

When I run this, either on my workstation or on my notebook, or on my
client's machine, it works flawlessly.

If we extract the command and run it on my colleagues laptops, to get
it to run we have to change it to:

sqlcmd -S glaptop\instanc e -i temporary_load_ script_file.sql -o
load.output1.tx t

If we change the perl script to reflect this, it fails to run, and we
get the same error that we get if they try to run without specifying an
instance. Because of the error message, this seems to be related to
the default instance issue you described, but I don't understand why we
can get it to work if we modify the command to specify an instance and
run it from the commandline but not from within a perl script.

Cheers,

Ted

[Erland Sommarskog]

Re: User authentication

Ted (r.ted.byers@ro gers.com) writes:

Can SQL Express be told to use a default instance? If so, how?

Yes. When you install SQL Express you get a screen where you can choose
the name of your instance and also select a default instance. If memory
serves the Express install has a "Show advanced install options" or
somesuch. You may have select that to get that screen.

You cannot change an existing named instance to a default instance. (Well,
with a lot of registry hacking etc you may get away with it, but please
don't try it home. Or at work.)

Here is the perl code I run on my machine:
>
my $cmdline = "sqlcmd -i temporary_load_ script_file.sql -o
>...
sqlcmd -S glaptop\instanc e -i temporary_load_ script_file.sql -o
load.output1.tx t
>
If we change the perl script to reflect this, it fails to run, and we
get the same error that we get if they try to run without specifying an
instance.

Of course it fails. \ in a double-quoted string in Perl is an escape
character, so you get glaptopsqlexpre ss.

Double the backslash or use single quotes to delimit the string. Even
nice is maybe to make the instance an argument:

$server = '.' unless $server;
my $cmdline = "sqlcmd -S $sserver -i temporary_load_ script_file.sql -o

In this case there is you don't have to worry about the backslash.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ted]

Re: User authentication


Erland Sommarskog wrote:

Ted (r.ted.byers@ro gers.com) writes:

Can SQL Express be told to use a default instance? If so, how?

>
Yes. When you install SQL Express you get a screen where you can choose
the name of your instance and also select a default instance. If memory
serves the Express install has a "Show advanced install options" or
somesuch. You may have select that to get that screen.
>

OK I am running a test of this, and to do so, I created a named
instance. This is on my own LAN, so we're dealing with a peer to peer
WXP LAN, and the developer's edition of SQL Server 2005. With local
access, everything works fine, with mixed authentication. But the
following shows what happens when I try remote access.

===========sess ion start========== ======
C:\FVA\Code>sql cmd -S RNDWORKSTATION\ QAENV -U myuid -P mypwd
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified
[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.

C:\FVA\Code>sql cmd -S RNDWORKSTATION -U myuid -P mypwd
1exit

C:\FVA\Code>
=============== session end============ ===

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

I assume I missed something, but what, and where do I look in SQL
Server 2005 Management Studio to find the settings I need to change to
fix this?

Thanks.

Ted

[Erland Sommarskog]

Re: User authentication

Ted (r.ted.byers@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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Ted]

Re: User authentication


Erland Sommarskog wrote:

Ted (r.ted.byers@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]

Re: User authentication

Ted (r.ted.byers@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, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx