How to log on using Windows credentials

#1

Hi there,

I'm attempting to logon to SQL Server remotely. I'm passing my windows
credentials via the connection string (using the User ID and Password
keywords) but I see this in the server's event log afterwards:

"An attempt to login using SQL authentication failed. Server is configured
for Windows authentication only."

Is there a way to log on using my Windows credentials. I have a Windows
account on the Server. Thank you.

#2

On 2009-12-14 0:47, Michael wrote:

Quote:

I'm attempting to logon to SQL Server remotely. I'm passing my windows
credentials via the connection string (using the User ID and Password
keywords) but I see this in the server's event log afterwards:

"An attempt to login using SQL authentication failed. Server is configured
for Windows authentication only."

Is there a way to log on using my Windows credentials. I have a Windows
account on the Server. Thank you.

When using Windows authentication, you do not (and cannot) pass a username

and password at all. The server will take care of authentication for you
"under water".

This, however, requires that you either have a domain account if you want to
log on remotely, or that the machine you log on from has the "same" local
account (identical username/password) as the account on the SQL Server and
you are logged on as that account (I haven't tester whether the latter
works, but it should). Usually, if you have only local access, you would set
up a Remote Desktop session to the machine instead and access SQL Server
from there.

--
J.

#3

Quote:

When using Windows authentication, you do not (and cannot) pass a username
and password at all. The server will take care of authentication for you
"under water".

This, however, requires that you either have a domain account if you want
to log on remotely, or that the machine you log on from has the "same"
local account (identical username/password) as the account on the SQL
Server and you are logged on as that account (I haven't tester whether the
latter works, but it should). Usually, if you have only local access, you
would set up a Remote Desktop session to the machine instead and access
SQL Server from there.

Thanks for the feedback. This is what I figured but I don't understand why
it doesn't allow the explicit passing of alternate Windows credentials. It
will implicitly grab my credentials from the current thread whether I'm
logged into a domain or a local account, but not allow me to explictly pass
those credentials in. In my case the server is not part of a domain so it
forces me to create a matching account name and password on my current
machine and log onto to that account (or impersonate it), instead of simply
allowing me to pass those credentials in on-the-fly. I don't see why this
restriction exists. In any case, thanks again.

#4

Because the server isn't configured for Mixed Mode.

"Michael" <_no_spam (AT) _no_spam (DOT) com> wrote

Quote:

When using Windows authentication, you do not (and cannot) pass a
username and password at all. The server will take care of authentication
for you "under water".

This, however, requires that you either have a domain account if you want
to log on remotely, or that the machine you log on from has the "same"
local account (identical username/password) as the account on the SQL
Server and you are logged on as that account (I haven't tester whether
the latter works, but it should). Usually, if you have only local access,
you would set up a Remote Desktop session to the machine instead and
access SQL Server from there.

Thanks for the feedback. This is what I figured but I don't understand why
it doesn't allow the explicit passing of alternate Windows credentials. It
will implicitly grab my credentials from the current thread whether I'm
logged into a domain or a local account, but not allow me to explictly
pass those credentials in. In my case the server is not part of a domain
so it forces me to create a matching account name and password on my
current machine and log onto to that account (or impersonate it), instead
of simply allowing me to pass those credentials in on-the-fly. I don't see
why this restriction exists. In any case, thanks again.

#5

On 2009-12-14 1:28, Jay wrote:
[SQL server won't accept a login and password for Windows authentication]

Quote:

Because the server isn't configured for Mixed Mode.

Mixed mode just allows SQL authentication to coexist with Windows

authentication. Passing in a login name and a password will still only work
for SQL accounts, and Windows accounts and SQL accounts do not correspond.

That's not to say mixed mode couldn't be a solution for this particular
scenario. If your machine isn't joined to a domain, Windows authentication
is really not that much better, except for relieving applications of the
need to store explicit credentials (which is still nothing to scoff at).

--
J.

#6

On 2009-12-14 1:19, Michael wrote:

Quote:

When using Windows authentication, you do not (and cannot) pass a username
and password at all. The server will take care of authentication for you
"under water".

This, however, requires that you either have a domain account if you want
to log on remotely, or that the machine you log on from has the "same"
local account (identical username/password) as the account on the SQL
Server and you are logged on as that account (I haven't tester whether the
latter works, but it should). Usually, if you have only local access, you
would set up a Remote Desktop session to the machine instead and access
SQL Server from there.

Thanks for the feedback. This is what I figured but I don't understand why
it doesn't allow the explicit passing of alternate Windows credentials.

Basically, just because Windows authentication doesn't support this
directly. You cannot log on remotely using a local account. It works if you
set up a "mirror" local account, but that's the only circumstance under
which that will work. I couldn't tell you how exactly all of this works out
security-wise.

If SQL Server invented its own mechanism for passing the credentials...
Well, it did, it's called SQL authentication, but you already know that. :-)
If it reinvented the wheel in the same way for Windows accounts, it'd be
opening up a security hole.

Quote:

It will implicitly grab my credentials from the current thread whether
I'm logged into a domain or a local account, but not allow me to
explictly pass those credentials in.

If by "it" you mean SQL Server, then no. This is done by the local and
remote security systems, not SQL Server. And the "credentials" being grabbed
are not your user name and password stored somewhere -- they're tokens,
verified by a server. This is why your token is only good if it's backed by
a domain account: it couldn't be verified by the remote machine otherwise.
The whole "mirror account" shenanigan only works because both machines come
to the same conclusion as to the validity of the token if both are using the
same credentials (or at least, that's how it should work). Passing in
explicit credentials (as in username and password) is a big no-no in the
Windows authentication world.

--
J.

#7

The OP said:

"An attempt to login using SQL authentication failed. Server is configured
for Windows authentication only."

So, Mixed Mode would solve his problem. If that is, the DBA will allow it.

"Jeroen Mostert" <jmostert (AT) xs4all (DOT) nl> wrote

Quote:

On 2009-12-14 1:28, Jay wrote:
[SQL server won't accept a login and password for Windows authentication]
Because the server isn't configured for Mixed Mode.

Mixed mode just allows SQL authentication to coexist with Windows
authentication. Passing in a login name and a password will still only
work for SQL accounts, and Windows accounts and SQL accounts do not
correspond.

That's not to say mixed mode couldn't be a solution for this particular
scenario. If your machine isn't joined to a domain, Windows authentication
is really not that much better, except for relieving applications of the
need to store explicit credentials (which is still nothing to scoff at).

--
J.

#8

On 2009-12-14 2:16, Jay wrote:

Quote:

The OP said:

"An attempt to login using SQL authentication failed. Server is configured
for Windows authentication only."

No, that's what the OP said that *SQL Server* said. The OP intended to log

in using alternate Windows credentials, which SQL Server happened to
interpret as an attempt to log on using SQL authentication. Turning on mixed
mode will not suddenly make his authentication attempts work.

Quote:

So, Mixed Mode would solve his problem.

Don't mean to be splitting hairs here, but mixed mode could be used as a
solution to support a similar *scenario* to what the OP wants. It's not the
solution to his exact *problem*. There can be good reasons for (not) using
mixed-mode authentication and (not) using Windows accounts, other than
wanting to pass in alternate credentials.

--
J.

#9

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Quote:

Thanks for the feedback. This is what I figured but I don't understand why
it doesn't allow the explicit passing of alternate Windows credentials.

Correct, SSMS doesn't have this ability. One can only wonder why, but my
guess is that the developers/designers of SSMS didn't want to duplicate
functionality in the OS (Run As). If you feel strongly for this feature, I
suggest you say this at connect.microsoft.com.

"Michael" <_no_spam (AT) _no_spam (DOT) com> wrote

Quote:

When using Windows authentication, you do not (and cannot) pass a
username and password at all. The server will take care of authentication
for you "under water".

This, however, requires that you either have a domain account if you want
to log on remotely, or that the machine you log on from has the "same"
local account (identical username/password) as the account on the SQL
Server and you are logged on as that account (I haven't tester whether
the latter works, but it should). Usually, if you have only local access,
you would set up a Remote Desktop session to the machine instead and
access SQL Server from there.

Thanks for the feedback. This is what I figured but I don't understand why
it doesn't allow the explicit passing of alternate Windows credentials. It
will implicitly grab my credentials from the current thread whether I'm
logged into a domain or a local account, but not allow me to explictly
pass those credentials in. In my case the server is not part of a domain
so it forces me to create a matching account name and password on my
current machine and log onto to that account (or impersonate it), instead
of simply allowing me to pass those credentials in on-the-fly. I don't see
why this restriction exists. In any case, thanks again.

#10

Quote:

Basically, just because Windows authentication doesn't support this
directly.

Actually it does. There are various Windows functions that accept passwords.
See "NetUseAdd()" as one simple example. In these cases the password isn't
sent across the wire however. It's encrypted one way or another (read on)

Quote:

You cannot log on remotely using a local account. It works if you set up a
"mirror" local account, but that's the only circumstance under which that
will work. I couldn't tell you how exactly all of this works out
security-wise.

It depends on the security provider in use such as NTLM, Kerberos, or
whatever. In the case of NTLM for instance (an old protocol now), the simple
story is that the remote machine sends a "nonce" (a 64 bit challenge number)
to the local machine which encypts it using a one-way hash of your password.
The encrypted value is then sent back to the remote machine along with the
account name and security authority (local machine name for a local account
or domain name for a domain account). The same encrypted value will then be
computed either by the remote machine's own LSA (Local Security Authority),
in the case where it doesn't recognize or trust my security authority (if
I'm using a local account that is - the same account and password must
therefore exist on the remote machine), or by a trusted domain controller's
LSA in the case of a domain account (if the remote machine and I are members
of the same domain that is, or if I belong to a domain that the remote
machine's domain trusts). If these two computed (encrypted) values match, I
then get successfully logged on to the remote machine. Notice however that
my password was never directly sent anywhere and the password doesn't have
to originate from my current logon session (as SQL Server demands).

Quote:

If by "it" you mean SQL Server, then no. This is done by the local and
remote security systems, not SQL Server. And the "credentials" being
grabbed are not your user name and password stored somewhere -- they're
tokens, verified by a server.

If by "token" you you mean access tokens, these are never passed to any
server. Access tokens only make sense on the local machine where they're
created. They contain elements that only make sense on the local machine
such as your privileges and local groups on that machine (AKA aliases).
They're always constructed locally and never sent anywhere, though they will
contain the domain-based groups you belong to as well (if you log in using a
domain account).

Quote:

This is why your token is only good if it's backed by a domain account: it
couldn't be verified by the remote machine otherwise.

Again, tokens aren't sent. The remote machine's own LSA can validate me for
a matching account and password.

Quote:

The whole "mirror account" shenanigan only works because both machines
come to the same conclusion as to the validity of the token if both are
using the same credentials (or at least, that's how it should work).
Passing in explicit credentials (as in username and password) is a big
no-no in the Windows authentication world.

As described, passwords aren't sent directly and I think you may be a little
confused about how security works (a machine doesn't operate with one set of
credentials for instance - many programs can be running on the same machine
simultaneously, all with different credentials). With appropriate
encryption, I should be able to send my password for Windows authentication
no differently than I can send it for SQL Server authentication.