How do I Authencate and Authorize Users Using AD and SQL App Role

Discussion in 'ASP .Net Security' started by AuntieAuntieAuntie, Nov 26, 2008.

  1. I have an application that is being developed for our Intranet.

    Which will require authentication of users that are members in an Active
    Directory group; TIRES Users, TIRES Super, TIRES Admin.

    Additionally, I created three SQL Application Roles, TIREAdminCRUD,
    TIRESuperRU, TIRESUser, with three distinct passwords.
    Each group was given execute right to various stored procedures with the
    schema as 'dbo'.

    *********
    My web.config is:
    <system.web>
    <authentication mode="Windows"/>
    <roleManager enabled="true"
    defaultProvider="AspNetWindowsTokenRoleProvider"/>
    <authorization>
    <allow users ="TIRES Users, TIRES Super, TIRES Admin"/>
    <deny users="?"/>

    <add name="TIREConnectionString" connectionString="Data
    Source=DOMAIN\INSTANCE;Initial Catalog=Tires;Integrated

    Security=True";providerName="System.Data.SqlClient"/>

    The goal of my application is to authenticate each user which I do using the
    following code:
    In my asp.net (VB) Page Load I have the following which is working.

    Dim strAdmin As String
    strAdmin = " TIRES Admin"

    If (Roles.IsUserInRole(strAdmin)) Then
    'continue
    Else
    Response.Redirect(http://tires/home.asp)
    End If
    *********
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    This is not working
    Dim sqlcon As New
    SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TiresConnectionString").ToString)

    'Open connection
    sqlcon.Open()

    SqlClient.SqlConnection.ClearPool(sqlcon)

    Dim Command As New SqlClient.SqlCommand

    Command.Connection = sqlcon

    Command.CommandText = "EXEC sp_setapprole ' TIREAdminCRUD',
    'password'"

    Command.ExecuteNonQuery()
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    But I am running into a problem when they try to connect to the SQL server;
    the following error message appears:
    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    *********

    How do I now authorize each user based on their assigned AD group to allow
    the execution of the stored procedures to which they have permissions?
    I am working with:
    SQL Server 2005 is installed as Mixed Mode Security.
    The web server is on a different machine than SQL.
    Anonymous login is unchecked.
    MS Visual Studio 2005.

    If I use <Identity> UserName, Password
    </Identity>
    Will this override the security established for the Application Roles?

    This is week three for me trying to get this working; I have read over a
    dozen web sites regarding this issue to no avail. You assistance in achieving
    success is more
    than greatly appreciated, to say the least!
    AuntieAuntieAuntie, Nov 26, 2008
    #1
    1. Advertising

  2. Re: How do I Authencate and Authorize Users Using AD and SQL App Role

    On Nov 26, 6:01 pm, AuntieAuntieAuntie
    <> wrote:
    > I have an application that is being developed for our Intranet.
    >
    > Which will require authentication of users that are members in an Active
    > Directory group; TIRES Users, TIRES Super, TIRES Admin.
    >
    > Additionally, I created three SQL Application Roles, TIREAdminCRUD,
    > TIRESuperRU, TIRESUser, with three distinct passwords.
    > Each group was given execute right to various stored procedures with the
    > schema as 'dbo'.
    >
    > *********
    > My web.config is:
    >     <system.web>
    >         <authentication mode="Windows"/>
    >         <roleManager enabled="true"
    > defaultProvider="AspNetWindowsTokenRoleProvider"/>
    >         <authorization>
    >       <allow users ="TIRES Users, TIRES Super, TIRES Admin"/>
    > <deny users="?"/>
    >
    > <add name="TIREConnectionString" connectionString="Data
    > Source=DOMAIN\INSTANCE;Initial Catalog=Tires;Integrated
    >
    > Security=True";providerName="System.Data.SqlClient"/>
    >
    > The goal of my application is to authenticate each user which I do using the
    > following code:
    > In my asp.net (VB) Page Load I have the following which is working.
    >
    > Dim strAdmin As String
    > strAdmin = " TIRES Admin"
    >
    > If (Roles.IsUserInRole(strAdmin)) Then
    > 'continue
    > Else
    > Response.Redirect(http://tires/home.asp)
    > End If
    > *********
    > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    > This is not working
    >             Dim sqlcon As New
    > SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TiresConnec tionString").ToString)
    >
    >             'Open connection
    >             sqlcon.Open()
    >
    >             SqlClient.SqlConnection.ClearPool(sqlcon)
    >
    >             Dim Command As New SqlClient.SqlCommand
    >
    >             Command.Connection = sqlcon
    >
    >             Command.CommandText = "EXEC sp_setapprole ' TIREAdminCRUD',
    > 'password'"
    >
    >             Command.ExecuteNonQuery()
    > ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    > But I am running into a problem when they try to connect to the SQL server;
    > the following error message appears:
    > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
    > *********
    >
    > How do I now authorize each user based on their assigned AD group to allow
    > the execution of the stored procedures to which they have permissions?
    > I am working with:
    > SQL Server 2005 is installed as Mixed Mode Security.
    > The web server is on a different machine than SQL.
    > Anonymous login is unchecked.
    > MS Visual Studio 2005.
    >
    > If I use <Identity> UserName, Password
    > </Identity>
    > Will this override the security established for the Application Roles?
    >
    > This is week three for me trying to get this working; I have read over a
    > dozen web sites regarding this issue to no avail. You assistance in achieving
    > success is more
    > than greatly appreciated, to say the least!


    To use the role manager API, you must enable role manager. With
    Windows authentication, you can use the built-in
    AspNetWindowsTokenRoleProvider, which uses Windows groups as roles. To
    enable role manager and select this provider, add the following
    configuration to your Web.config file.

    See more at: http://msdn.microsoft.com/en-us/library/ms998358.aspx

    Hope this helps
    Alexey Smirnov, Nov 30, 2008
    #2
    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. Jesper Stocholm
    Replies:
    2
    Views:
    8,064
    John Saunders
    Aug 23, 2003
  2. Liet Kynes
    Replies:
    0
    Views:
    473
    Liet Kynes
    Nov 26, 2003
  3. =?Utf-8?B?cm9kY2hhcg==?=

    sql server app role

    =?Utf-8?B?cm9kY2hhcg==?=, Apr 23, 2007, in forum: ASP .Net
    Replies:
    0
    Views:
    314
    =?Utf-8?B?cm9kY2hhcg==?=
    Apr 23, 2007
  4. bitshift
    Replies:
    1
    Views:
    527
    bruce barker
    Jun 22, 2007
  5. Kursat
    Replies:
    1
    Views:
    299
    Dominick Baier
    May 7, 2007
Loading...

Share This Page