Re: Windows authentication in domain

From: Steve Thompson (stevethompson_at_nomail.please)
Date: 08/17/04 

Date: Tue, 17 Aug 2004 14:23:13 -0400

Hi Dave,

If I completely understand what you are asking -

"When using SSPI for authentication for connectivity, if the connection
fails, why doesn't SQL Server use other credentials such as those that may
be logged in or cached on the remote server, or force a prompted dialog?"

If my understanding is correct, this would actually be a violation of
security, since realistically we would only want the current user (SID)
credentials passed and authenticated (via Windows Authentication). If you
need an alternate mechanism to cover instances where users need to connect
and they do not have proper credentials, this would likely be best handled
by; giving them proper security access (via NT Group membership), or
programmatically and perhaps by using application roles.

HTH,
Steve

"Dave Busch" <DaveBusch@discussions.microsoft.com> wrote in message
news:8F297C17-32F9-48C3-B808-C58C581460C6@microsoft.com...
> Hi Steve
>
> The way I understand the Integrated Security setting on a connection
string,
> setting it to true or SSPI will cause SQL to use the credentials of the
> current user. My question really is why doesn't SQL attempt to use
credman
> credentials if the current user's credentials fail?
>
> Using credman credentials works for example if you log off the workstation
> and log in as Administrator to the local machine account. If the
credentials
> for the local account on the SQL box are cached using credman, SQL will
> indeed attempt to use those credentials and the connection can be made.
>
> SQL seems to be saying in the case where the domain user is logged in, "OK
I
> know your identity, and you don't have rights". Why doesn't it behave
that
> way when the current user is <local machine>\Administrator?
>
>
>
>
> "Steve Thompson" wrote:
>
> > "Dave Busch" <Dave Busch@discussions.microsoft.com> wrote in message
> > news:81C5E126-190A-4634-807F-455941781161@microsoft.com...
> > > In a domain, where a user is logged in as Foo@SomeDomain.com, an
attempt
> > is
> > > made to connect to a SQL server on the domain using integrated
security
> > using
> > > the connection string:
> > >
> > > Initial Catalog=Northwind;Data Source=SomeServer;Integrated
Security=SSPI
> > >
> > > User "Foo" is not registered as a valid SQL login on "SomeServer", so
the
> > > connection fails. So far so good.
> > >
> > > Now, let's say a local user is added to SomeServer called
"SomeLocalUser",
> > > and this windows account is added to the SQL Server as a valid login.
> > Back
> > > at the workstation where Foo@SomeDomain.com is logged in, the
credentials
> > are
> > > added to that workstation for SomeServer\SomeLocalUser. When the
> > connection
> > > is attempted, it fails indicating that Foo@SomeDomain.com is not a
valid
> > user.
> > >
> > > I guess I would have expected SQL to attempt all credentials found on
the
> > > workstation rather than just the user who is currently logged in.
What is
> > > the expected behavior? Is there some way I can force SQL to use
alternate
> > > credentials manually?
> > >
> >
> > I'm not sure I completely understand your example. If you are using
SSPI,
> > then only the credentials of the user making the connection to SQL
Server
> > will be used, none other... The only other alternate way (I can think
of) is
> > to use the RunAs to start the application under another user security
> > context.
> >
> > Steve
> >
> >
> >

Relevant Pages

  • Re: Probably a simple question
    ... It will also work if you move SQL ... credentials do not cross the machine boundary. ... Oracle, Web Service, or whatever), the credentials of IIS process (not ... you must be aware of the associated security risks. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: ado.net 2.0 and MSSQL AppRoles
    ... There are as many security schemes as there are scandals in Washington. ... easily scaled since no SSPI authentication is required. ... >> Just consider that anytime you use SSPI security, the credentials must be ... >> revalidated each time the connection is opened. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Probably a simple question
    ... plain text credentials using something like Basic authentication and SSL. ... There are some good KB postings on Kerberos delegation that will be very ... Windows user credentials of the current logged on user to SQL, ... > Whenever I try using integrated security in my connection string I always ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Windows authentication in domain
    ... The way I understand the Integrated Security setting on a connection string, ... setting it to true or SSPI will cause SQL to use the credentials of the ... My question really is why doesn't SQL attempt to use credman ...
    (microsoft.public.sqlserver.security)
  • Re: Cached Logon
    ... Your browser is caching the credentials, ... be accessed (it's just the ASP page -> SQL Server that doesn't work, ... are you using a common connection string across all three pages (eg is ...
    (microsoft.public.sqlserver.connect)