Windows Authentication in asp.net 2005 to SQL Server?

Discussion in 'ASP .Net Security' started by John, Mar 2, 2006.

  1. John

    John Guest

    Sorry if this sounds like a novice question but we've been using SQL Server
    logins and passwords from our home made .asp login page with the whole
    connection string and app wrapped in https for individual users to access
    the databases on our SQL Server. I'm looking into moving towards asp.net
    2005 and heard using Windows authentication is a better practice in
    accessing SQL Server. I was wondering if anyone could tell me how would one
    go about it in that there would be an initial login page or login pop up
    (like the Windows authentication popup) where individual users would enter
    in their valid windows login information which would allow them to proceed
    to use the .net 2005 web app that connects to our SQL Server. Just taking a
    guess...would it be good to use Windows Basic authentication where it would
    pop up and the individual user can enter in their info and use https to
    encrypt the database and app communication since it's in clear text?
     
    John, Mar 2, 2006
    #1
    1. Advertising

  2. You can definitely use Basic authentication here (along with SSL) to
    authenticate the users. You might also consider using IWA as well if you
    want the ability for your users to authenticate automatically.

    Note that to authenticate users in your domain, your web server and SQL
    server must be members of your Active Directory domain.

    Additionally, to use IWA, you will need to implement Kerberos delegation so
    that the web app can successfully forward the user's credentials to SQL
    server. This can be tricky, but there are some great documents out there
    specifying how to implement it.

    If you don't have a domain, you might not want to go down this path.
    ASP.NET membership might work better for you with one of the SQL-based
    stores. You could possibly still use SQL authentication to SQL or maybe
    Windows auth. Out of curiosity, do your connection strings to SQL use a
    fixed set of credentials or insert the authenticated user's credentials?
    The former is called a "trusted subsystem" model and the latter is called a
    "delegated" model. This is because with a trusted subsystem, a single
    trusted account is used to perform actions against the backend on behalf of
    all of the users of the app, while in a delegated model, the user's
    credentials are used for accessing both the frontend and backend resources.
    Kerberos delegation is just a way of delegating credentials using built-in
    features of Windows security.

    Joe K.

    "John" <> wrote in message
    news:...
    > Sorry if this sounds like a novice question but we've been using SQL
    > Server logins and passwords from our home made .asp login page with the
    > whole connection string and app wrapped in https for individual users to
    > access the databases on our SQL Server. I'm looking into moving towards
    > asp.net 2005 and heard using Windows authentication is a better practice
    > in accessing SQL Server. I was wondering if anyone could tell me how
    > would one go about it in that there would be an initial login page or
    > login pop up (like the Windows authentication popup) where individual
    > users would enter in their valid windows login information which would
    > allow them to proceed to use the .net 2005 web app that connects to our
    > SQL Server. Just taking a guess...would it be good to use Windows Basic
    > authentication where it would pop up and the individual user can enter in
    > their info and use https to encrypt the database and app communication
    > since it's in clear text?
    >
     
    Joe Kaplan \(MVP - ADSI\), Mar 3, 2006
    #2
    1. Advertising

  3. John

    John Guest

    Thanks a bunch Joe for your reply. Been looking around forever and a few
    similar posts and finally some helpful info thanks to you :)

    I guess I would say my connection string would be the 'delegated' type since
    we need to have individual logins for auditing and not just one generic
    account to do the backend data stuff on our SQL Server. Also all examples
    I've seen out there are usually for the IIS and SQL Server to be on the same
    machine and/or same domain but like you mentioned our set up is a little
    different and appears to be tricky like I had estimated. Our SQL Server and
    web server are on separate machines and are on different domains also. So
    for this reason I have been using SQL authentication in my connection
    strings and not Windows authentication because of the double hop, different
    domain and machines. I had tried to create a domain user account that our
    web server is on a while back and then tried to have that windows domain
    user account log in to our SQL Server with Windows authentication but kept
    getting connections errors. Would Windows Basic authentication wrapped in
    https going from our web server trying to connect to our SQL Server which
    are separate computers and separate domains possible?

    Thanks Joe.

    J



    "Joe Kaplan (MVP - ADSI)" <> wrote
    in message news:eIE$...
    > You can definitely use Basic authentication here (along with SSL) to
    > authenticate the users. You might also consider using IWA as well if you
    > want the ability for your users to authenticate automatically.
    >
    > Note that to authenticate users in your domain, your web server and SQL
    > server must be members of your Active Directory domain.
    >
    > Additionally, to use IWA, you will need to implement Kerberos delegation
    > so that the web app can successfully forward the user's credentials to SQL
    > server. This can be tricky, but there are some great documents out there
    > specifying how to implement it.
    >
    > If you don't have a domain, you might not want to go down this path.
    > ASP.NET membership might work better for you with one of the SQL-based
    > stores. You could possibly still use SQL authentication to SQL or maybe
    > Windows auth. Out of curiosity, do your connection strings to SQL use a
    > fixed set of credentials or insert the authenticated user's credentials?
    > The former is called a "trusted subsystem" model and the latter is called
    > a "delegated" model. This is because with a trusted subsystem, a single
    > trusted account is used to perform actions against the backend on behalf
    > of all of the users of the app, while in a delegated model, the user's
    > credentials are used for accessing both the frontend and backend
    > resources. Kerberos delegation is just a way of delegating credentials
    > using built-in features of Windows security.
    >
    > Joe K.
    >
    > "John" <> wrote in message
    > news:...
    >> Sorry if this sounds like a novice question but we've been using SQL
    >> Server logins and passwords from our home made .asp login page with the
    >> whole connection string and app wrapped in https for individual users to
    >> access the databases on our SQL Server. I'm looking into moving towards
    >> asp.net 2005 and heard using Windows authentication is a better practice
    >> in accessing SQL Server. I was wondering if anyone could tell me how
    >> would one go about it in that there would be an initial login page or
    >> login pop up (like the Windows authentication popup) where individual
    >> users would enter in their valid windows login information which would
    >> allow them to proceed to use the .net 2005 web app that connects to our
    >> SQL Server. Just taking a guess...would it be good to use Windows Basic
    >> authentication where it would pop up and the individual user can enter in
    >> their info and use https to encrypt the database and app communication
    >> since it's in clear text?
    >>

    >
    >
     
    John, Mar 3, 2006
    #3
  4. If the domains do not trust each other, Windows authentication is not going
    to get you anywhere. Your SQL server needs to trust your web server's
    domain. It will also need to trust the domain that the user accounts are
    in.

    Basic authentication sometimes makes the need for Kerberos delegation go
    away (since the user logs on locally to the web server with the plaintext
    password), but it doesn't solve the overall Windows security issue above.
    You need to get that sorted out first.

    If I were you, I'd decide first whether you can and want to use Windows
    authentication for your user accounts and then start looking at what it will
    take to put that solution together (trusts and such). If your
    infrastructure can't be made to do this, then you should stick with SQL auth
    I suppose. In that case, I'd just use ASP.NET forms authentication and use
    the provided credentials to authenticate with SQL.

    Joe K.

    "John" <> wrote in message
    news:...
    > Thanks a bunch Joe for your reply. Been looking around forever and a few
    > similar posts and finally some helpful info thanks to you :)
    >
    > I guess I would say my connection string would be the 'delegated' type
    > since we need to have individual logins for auditing and not just one
    > generic account to do the backend data stuff on our SQL Server. Also all
    > examples I've seen out there are usually for the IIS and SQL Server to be
    > on the same machine and/or same domain but like you mentioned our set up
    > is a little different and appears to be tricky like I had estimated. Our
    > SQL Server and web server are on separate machines and are on different
    > domains also. So for this reason I have been using SQL authentication in
    > my connection strings and not Windows authentication because of the double
    > hop, different domain and machines. I had tried to create a domain user
    > account that our web server is on a while back and then tried to have that
    > windows domain user account log in to our SQL Server with Windows
    > authentication but kept getting connections errors. Would Windows Basic
    > authentication wrapped in https going from our web server trying to
    > connect to our SQL Server which are separate computers and separate
    > domains possible?
    >
    > Thanks Joe.
    >
    > J
    >
    >
    >
    > "Joe Kaplan (MVP - ADSI)" <> wrote
    > in message news:eIE$...
    >> You can definitely use Basic authentication here (along with SSL) to
    >> authenticate the users. You might also consider using IWA as well if you
    >> want the ability for your users to authenticate automatically.
    >>
    >> Note that to authenticate users in your domain, your web server and SQL
    >> server must be members of your Active Directory domain.
    >>
    >> Additionally, to use IWA, you will need to implement Kerberos delegation
    >> so that the web app can successfully forward the user's credentials to
    >> SQL server. This can be tricky, but there are some great documents out
    >> there specifying how to implement it.
    >>
    >> If you don't have a domain, you might not want to go down this path.
    >> ASP.NET membership might work better for you with one of the SQL-based
    >> stores. You could possibly still use SQL authentication to SQL or maybe
    >> Windows auth. Out of curiosity, do your connection strings to SQL use a
    >> fixed set of credentials or insert the authenticated user's credentials?
    >> The former is called a "trusted subsystem" model and the latter is called
    >> a "delegated" model. This is because with a trusted subsystem, a single
    >> trusted account is used to perform actions against the backend on behalf
    >> of all of the users of the app, while in a delegated model, the user's
    >> credentials are used for accessing both the frontend and backend
    >> resources. Kerberos delegation is just a way of delegating credentials
    >> using built-in features of Windows security.
    >>
    >> Joe K.
    >>
    >> "John" <> wrote in message
    >> news:...
    >>> Sorry if this sounds like a novice question but we've been using SQL
    >>> Server logins and passwords from our home made .asp login page with the
    >>> whole connection string and app wrapped in https for individual users to
    >>> access the databases on our SQL Server. I'm looking into moving towards
    >>> asp.net 2005 and heard using Windows authentication is a better practice
    >>> in accessing SQL Server. I was wondering if anyone could tell me how
    >>> would one go about it in that there would be an initial login page or
    >>> login pop up (like the Windows authentication popup) where individual
    >>> users would enter in their valid windows login information which would
    >>> allow them to proceed to use the .net 2005 web app that connects to our
    >>> SQL Server. Just taking a guess...would it be good to use Windows Basic
    >>> authentication where it would pop up and the individual user can enter
    >>> in their info and use https to encrypt the database and app
    >>> communication since it's in clear text?
    >>>

    >>
    >>

    >
    >
     
    Joe Kaplan \(MVP - ADSI\), Mar 3, 2006
    #4
  5. John

    John Guest

    Thanks Joe.

    J


    "Joe Kaplan (MVP - ADSI)" <> wrote
    in message news:...
    > If the domains do not trust each other, Windows authentication is not
    > going to get you anywhere. Your SQL server needs to trust your web
    > server's domain. It will also need to trust the domain that the user
    > accounts are in.
    >
    > Basic authentication sometimes makes the need for Kerberos delegation go
    > away (since the user logs on locally to the web server with the plaintext
    > password), but it doesn't solve the overall Windows security issue above.
    > You need to get that sorted out first.
    >
    > If I were you, I'd decide first whether you can and want to use Windows
    > authentication for your user accounts and then start looking at what it
    > will take to put that solution together (trusts and such). If your
    > infrastructure can't be made to do this, then you should stick with SQL
    > auth I suppose. In that case, I'd just use ASP.NET forms authentication
    > and use the provided credentials to authenticate with SQL.
    >
    > Joe K.
    >
    > "John" <> wrote in message
    > news:...
    >> Thanks a bunch Joe for your reply. Been looking around forever and a few
    >> similar posts and finally some helpful info thanks to you :)
    >>
    >> I guess I would say my connection string would be the 'delegated' type
    >> since we need to have individual logins for auditing and not just one
    >> generic account to do the backend data stuff on our SQL Server. Also all
    >> examples I've seen out there are usually for the IIS and SQL Server to be
    >> on the same machine and/or same domain but like you mentioned our set up
    >> is a little different and appears to be tricky like I had estimated. Our
    >> SQL Server and web server are on separate machines and are on different
    >> domains also. So for this reason I have been using SQL authentication in
    >> my connection strings and not Windows authentication because of the
    >> double hop, different domain and machines. I had tried to create a
    >> domain user account that our web server is on a while back and then tried
    >> to have that windows domain user account log in to our SQL Server with
    >> Windows authentication but kept getting connections errors. Would
    >> Windows Basic authentication wrapped in https going from our web server
    >> trying to connect to our SQL Server which are separate computers and
    >> separate domains possible?
    >>
    >> Thanks Joe.
    >>
    >> J
    >>
    >>
    >>
    >> "Joe Kaplan (MVP - ADSI)" <>
    >> wrote in message news:eIE$...
    >>> You can definitely use Basic authentication here (along with SSL) to
    >>> authenticate the users. You might also consider using IWA as well if
    >>> you want the ability for your users to authenticate automatically.
    >>>
    >>> Note that to authenticate users in your domain, your web server and SQL
    >>> server must be members of your Active Directory domain.
    >>>
    >>> Additionally, to use IWA, you will need to implement Kerberos delegation
    >>> so that the web app can successfully forward the user's credentials to
    >>> SQL server. This can be tricky, but there are some great documents out
    >>> there specifying how to implement it.
    >>>
    >>> If you don't have a domain, you might not want to go down this path.
    >>> ASP.NET membership might work better for you with one of the SQL-based
    >>> stores. You could possibly still use SQL authentication to SQL or maybe
    >>> Windows auth. Out of curiosity, do your connection strings to SQL use a
    >>> fixed set of credentials or insert the authenticated user's credentials?
    >>> The former is called a "trusted subsystem" model and the latter is
    >>> called a "delegated" model. This is because with a trusted subsystem, a
    >>> single trusted account is used to perform actions against the backend on
    >>> behalf of all of the users of the app, while in a delegated model, the
    >>> user's credentials are used for accessing both the frontend and backend
    >>> resources. Kerberos delegation is just a way of delegating credentials
    >>> using built-in features of Windows security.
    >>>
    >>> Joe K.
    >>>
    >>> "John" <> wrote in message
    >>> news:...
    >>>> Sorry if this sounds like a novice question but we've been using SQL
    >>>> Server logins and passwords from our home made .asp login page with the
    >>>> whole connection string and app wrapped in https for individual users
    >>>> to access the databases on our SQL Server. I'm looking into moving
    >>>> towards asp.net 2005 and heard using Windows authentication is a better
    >>>> practice in accessing SQL Server. I was wondering if anyone could tell
    >>>> me how would one go about it in that there would be an initial login
    >>>> page or login pop up (like the Windows authentication popup) where
    >>>> individual users would enter in their valid windows login information
    >>>> which would allow them to proceed to use the .net 2005 web app that
    >>>> connects to our SQL Server. Just taking a guess...would it be good to
    >>>> use Windows Basic authentication where it would pop up and the
    >>>> individual user can enter in their info and use https to encrypt the
    >>>> database and app communication since it's in clear text?
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    John, Mar 3, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    17,990
    Carlos Barini
    Jun 7, 2004
  2. Juan T. Llibre
    Replies:
    0
    Views:
    477
    Juan T. Llibre
    Jun 7, 2005
  3. =?Utf-8?B?c2NvdHRybQ==?=

    sql session state for .net v1.1/.net v2.0 and sql server 2005

    =?Utf-8?B?c2NvdHRybQ==?=, Feb 13, 2006, in forum: ASP .Net
    Replies:
    3
    Views:
    2,809
    Steven Cheng[MSFT]
    Feb 16, 2006
  4. XJ
    Replies:
    8
    Views:
    1,769
  5. JM_newsgroups
    Replies:
    2
    Views:
    836
    JM_newsgroups
    Dec 12, 2008
Loading...

Share This Page