Re: Active Directory and SQL Server Connection

You don't have to or even need to put a U/P in your web.config. The idea is,
when you say that you intend to use integrated security, the thread that the
user is accessing the remote resource on, the userid running that thread,
the thread inherits it's permissions. In this case, the thread your code was
running on, was being run by IIS_MachineName. Thats the issue, you need to
change that default behavior to something else. And that you can do by
creating a WindowsIdentity and calling Impersonate on that. Look at the code
I posted in my original reply.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------



"Lyners" <Lyners@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:776BC984-385B-4D2E-86FC-1342ABDDC0FF@xxxxxxxxxxxxxxxx
> Thanks Sahil,
> I am still not getting something here. :-(
>
> I went into my SQL server added a user "Test", added that to my web.config
> file by cutting out trusted connections and added uid, and pwd. And it
worked
> for getting data, but my active directory search failed:
>
> [COMException (0x80070035): The network path was not found]
> System.DirectoryServices.DirectoryEntry.Bind(Boolean throwIfFail) +704
> System.DirectoryServices.DirectoryEntry.Bind() +10
> System.DirectoryServices.DirectoryEntry.get_AdsObject() +10
> System.DirectoryServices.PropertyValueCollection.PopulateList() +234
> System.DirectoryServices.PropertyValueCollection..ctor(DirectoryEntry
> entry, String propertyName) +56
> System.DirectoryServices.PropertyCollection.get_Item(String
propertyName)
> +97
> System.Web.UI.Control.OnLoad(EventArgs e) +67
> System.Web.UI.Control.LoadRecursive() +35
> System.Web.UI.Page.ProcessRequestMain() +750
>
> Any suggestions on what i am doing wrong? i really don't want to put a uid
> and pwd in my web config, I would just like to say the user has been
> authenticated to the net, so they have access to the sql server.
>
> Thank you!
> "Sahil Malik [MVP]" wrote:
>
> > Lyners,
> >
> > I would look up MSDN help on WindowsIdentity.
> >
> > Your connection string is fine, it simply says that windows
authentication
> > should be used. The question is - who integrated auth for which user. Is
it
> > "IIS_MachineName" ? Is it ASPNET? Or is it the dude you selected in the
> > dropdown? So whichever it is, your code needs to impersonate that
particular
> > user's identity and then connect to Sql Server. Thats all :-)
> >
> > - Sahil Malik [MVP]
> > ADO.NET 2.0 book -
> > http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
>
> --------------------------------------------------------------------------
--
> > ---------------
> >
> >
> > "Lyners" <Lyners@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:B37464A2-A632-485A-B9EF-E72CEF859BF0@xxxxxxxxxxxxxxxx
> > > Thanks Sahil,
> > > I need a little more help with this. i am writing this in
VB.NET/ASP.NET,
> > my
> > > connection string is in my web.config file and looks like this;
> > >
> > > <add key="DsnSql" value="server=server\dev;integrated
> > security=SSPI;persist
> > > security info=False;Trusted_Connection=yes;database=datatables" />
> > >
> > > I tried figuring out the system.net.networkidentity, but had no luck.
Do
> > you
> > > have any further pointers?
> > >
> > > "Sahil Malik [MVP]" wrote:
> > >
> > > > You need to switch identities by using the WindowsIdentity class in
the
> > ..NET
> > > > framework.
> > > >
> > > > The bigger problem though is, SqlConnection doesn't expose an
instance
> > of
> > > > WindowsIdentity. Actually it's not so much of an issue, because you
> > really
> > > > need to Impersonate and you can get the new identity easily using a
> > class
> > > > such as (I think) System.Net.NetworkIdentity (I think).
> > > >
> > > > So say for instance, in your postback in your page_load, you could
have
> > code
> > > > that looks like as below -
> > > >
> > > > WindowsIdentity userIdentity = // Get the new identity here ;
> > > > WindowsImpersonationContext impContext =
userIdentity.Impersonate();
> > > > // Then do your regular SqlConnection stuff using this
> > > > switched/impersonated identity.
> > > >
> > > > This requires that you are in a Kerberos friendly network, which it
> > appears
> > > > that you are.
> > > >
> > > > Under the scenes basically when you impersonate, your web
application
> > would
> > > > get a kerberos service ticket on the behalf of the impersonated
user,
> > which
> > > > the SqlServer will recognize. This way, delegation will work the way
you
> > > > intend to make it work. This "ticket" based kerberos architecture is
> > > > necessary because passwords are usually never sent clear text, so if
you
> > > > know my password, and I know my password, I hash it (one way
> > encryption),
> > > > and we compare hashes. The problem is, the third machine that
neither
> > has my
> > > > password, nor my password's hash, will not be able to authenticate
me -
> > this
> > > > third machine in this case is the Sql Server, and the first two
machines
> > are
> > > > the machines the browser is running on, and the machine IIS is
running
> > on.
> > > >
> > > > - Sahil Malik [MVP]
> > > > ADO.NET 2.0 book -
> > > >
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
> > >
> >
> --------------------------------------------------------------------------
> > --
> > > > ---------------
> > > >
> > > >
> > > >
> > > >
> > > > "Lyners" <Lyners@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:0A9B7DA5-965F-4E43-865B-1DF4B92724D8@xxxxxxxxxxxxxxxx
> > > > > We have 2 servers running Windows 2003. One is the IIS server the
> > other is
> > > > a
> > > > > SQL server. We made a web page that gets the using users name
through
> > > > their
> > > > > logon from active directory. We then query the SQl server looking
for
> > > > > information about the user. Our problem, when we have anonymous
access
> > > > turned
> > > > > off and integration turned on, we get a login failed, not trusted
> > > > connection.
> > > > > If we add an anonymous user from the domain that has access to the
SQL
> > > > > server, we get data, but of the anonymous user, not the using
client.
> > It
> > > > > appears once we post to the server, the anonymous access takes
over
> > and
> > > > > everything is geared to that user ID.
> > > > >
> > > > > How do we do both?
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> >
> >


.

Relevant Pages

  • Re: Active Directory and SQL Server Connection
    ... Just use one of the constructors available on WindowsIdentity. ... > Hi Sahil, ... so they have access to the sql server. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Active Directory and SQL Server Connection
    ... > creating a WindowsIdentity and calling Impersonate on that. ... >> Thanks Sahil, ... so they have access to the sql server. ...
    (microsoft.public.dotnet.framework.adonet)
  • Please explain loss of token between web server box and sql box
    ... relating to Integrated Security with Asp.Net IIS 5 and Sql Server 2k. ... The problem arises when endeavouring to use Integrated Security at all ... grant the group Sql Server access and rights to the relevant database. ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Connecting to a sql server database
    ... You are trying to use integrated security in your app (as is shown in the ... line numbered code where the connection string is shown). ... sql server, hence integrated security fails. ... > causing the error message and what the correction for this. ...
    (microsoft.public.dotnet.general)
  • Re: Please explain loss of token between web server box and sql box
    ... The client user principal is a W2K AD domain member, the users accounts must be set up as "delegatable" in the AD. ... The IIS server's "machine account" is trusted for delegation in the AD. ... The SQL server SPN must be registered in the AD, or must run under "localsystem" identity. ... > relating to Integrated Security with Asp.Net IIS 5 and Sql Server 2k. ...
    (microsoft.public.dotnet.framework.aspnet.security)