Re: Windows authentication in domain

From: Dave Busch (DaveBusch_at_discussions.microsoft.com)
Date: 08/19/04 

Date: Wed, 18 Aug 2004 17:35:03 -0700

Hi Steve

Your explanation seems reasonable and based on my observations what I
expected to hear. It makes sense from Windows perspective that your domain
credentials take precedence identifying who you are, and therefore its very
reasonable in an environment that utilites a domain rather than a workgroup
the domain user must be explicitly listed a SQL login.

Thank for your help.

D

"Steve Thompson" wrote:

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