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

[AuntieAuntieAuntie]

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!

 

[Guest]

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