Execute Stored Procedure Against Active Directory

I want to have a web page where we can enter a user id and get back a list of AD groups and this stored procedure gives me exactly what I need. I just need to be able to run this from a web page.

EXEC GetLdapUserGroups <USERID>
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Stephan_SchrandtCommented:

Imports System.Data.SqlClient
Imports System.Data

Sub GetUsers

Dim cmd as string
Dim adp As New SqlDataAdapter
Dim ds As New DataSet
cmd = "GetLdapUserGroups"
cmd = New SqlClient.SqlCommand(Procnames(Transaction), SQLCon)
cmd.CommandType = CommandType.StoredProcedure
SqlClient.SqlCommandBuilder.DeriveParameters(cmd)
cmd.Parameters("@UserID").Value = TheUserID
adp.SelectCommand = cmd
adp.Fill(ds)
'the dataset is filled with the group records
End Sub

Open in new window

0
Stephan_SchrandtCommented:
and in c#
using System.Data;
using System.Data.SqlClient;
public void GetUser()
{
	string cmd = null;
	SqlDataAdapter adp = new SqlDataAdapter();
	DataSet ds = new DataSet();
	cmd = "GetLdapUserGroups";
	cmd = new System.Data.SqlClient.SqlCommand(Procnames(Transaction), SQLCon);
	cmd.CommandType = CommandType.StoredProcedure;
	System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);
	cmd.Parameters("@UserID").Value = TheUserID;
	adp.SelectCommand = cmd;
	adp.Fill(ds);
	//the dataset is filled with the group records
}

Open in new window

0
Carla RomereDirector of Information TechnologyAuthor Commented:
Thanks for the really quick response!

I'm using the C# version and I'm getting this error:
CS0103: The name 'Procnames' does not exist in the current context

Is there some other assembly I need to include?
0
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Stephan_SchrandtCommented:
Sorry, forgot about the connection. What server do you use? Please refer to http://www.connectionstrings.com/ to get the correct one. Also you have to replace TheUserID with a real userid

public void GetUsers()
{
      SqlCommand cmd = new SqlCommand();
      SqlDataAdapter adp = new SqlDataAdapter();
      DataSet ds = new DataSet();
      string cs = null;
      cs = "YourConnectionStringHere";
      SqlConnection SQLCon = new SqlConnection(cs);
      cmd = new System.Data.SqlClient.SqlCommand("GetLdapUserGroups", SQLCon);
      cmd.CommandType = CommandType.StoredProcedure;
      System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);
      cmd.Parameters("@UserID").Value = TheUserID;
      adp.SelectCommand = cmd;
      adp.Fill(ds);
      //the dataset is filled with the group records
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carla RomereDirector of Information TechnologyAuthor Commented:
Ok, I really appreciate your help on this and I am making progress, but getting another error now. I've tried every incatation of adding the sql parameter that I can think of, but still not quite there.

The scenario is I have a textbox on the aspx page with a submit button. I want to use the contents of the textbox as the value for the @userid variable and on the button click, return the list of groups for that user.

On the cmd.Parameters(@UserID" line I have this currently:
      cmd.Parameters("@UserID").Value = txtUserID.Text;
and I get this error:
Compiler Error Message: CS0118: 'System.Data.SqlClient.SqlCommand.Parameters' is a 'property' but is used like a 'method'

I've looked up trying to set that parameter to the textbox contents, but nothing I've tried has worked.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
Ok - I got past that error, but not sure if I'm headed in the right direction or not.
I've included my current code behind below. The error that I'm getting now is:

DeriveParameters requires an open and available Connection. The connection's current state is closed.

This error is on this line:
        System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);

I'm sure it's something simple I'm missing now.
protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string val = txtUserID.Text.Trim();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adp = new SqlDataAdapter();
        DataSet ds = new DataSet();
        string cs = null;
        cs = "Server=sdwineng01;Database=master;Trusted_Connection=True;";
        SqlConnection SQLCon = new SqlConnection(cs);
        cmd = new System.Data.SqlClient.SqlCommand("GetLdapUserGroups", SQLCon);
        cmd.CommandType = CommandType.StoredProcedure;
        System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);
        cmd.Parameters.Add("@UserID");
        cmd.Parameters["@UserID"].Value = val;
       adp.SelectCommand = cmd;
        adp.Fill(ds);
        //the dataset is filled with the group records
    }

Open in new window

0
Stephan_SchrandtCommented:
sorry, you have to call sqlcon.open() after line 9
0
Carla RomereDirector of Information TechnologyAuthor Commented:
Ok - that part is fixed. Now I've hit another snag. The parameter defined within the stored procedure that I'm running here is called @LdapUsername and is a nVarChar(256) so I modified the parameter definition as follows:

        cmd.Parameters.Add(@LdapUsername, SqlDbType.NVarChar(256));
        cmd.Parameters["@LdapUsername"].Value = val;

and now I get this error:
CS0103: The name 'LdapUsername' does not exist in the current context

I've checked the capitalization and spelling and it's defined here exactly as it's defined in the stored procedure. ARGH! Is it a use-quotes or don't-use-quotes issue?
0
Stephan_SchrandtCommented:
You missed 2 quotes:
        cmd.Parameters.Add("@LdapUsername", SqlDbType.NVarChar(256));
        cmd.Parameters["@LdapUsername"].Value = val;

But I don't think you need Parameters.add after calling deriveparameters (I'm even not sure if they work together)

0
Carla RomereDirector of Information TechnologyAuthor Commented:
Ok - if I comment out the cmd.Parameters.Add line, I get this error:
The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.
An error occurred while preparing the query "             SELECT distinguishedName              FROM 'LDAP://DC=sd_corp,DC=local'             WHERE                  objectClass = 'user' AND                 sAMAccountName = 'cromer'         " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

If I leave it in play and add the quotes, I get this error:
CS0118: 'System.Data.SqlDbType.NVarChar' is a 'field' but is used like a 'method'

So, it seems to me like you may be right about them not working together and that it's a permissions issue on the stored procedure itself. I've made sure that the user I'm using to log in to the sql connection has execute permission on the sproc. What else do I need to do to enable the specified user to run this sproc?
0
Stephan_SchrandtCommented:
The first error message tells you, that the command was called, but does not have the permission to execute. Check your connectionstring and make sure, the user in the connectionstring got the right to execute stored procedures.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
Ok the stored procedure does have permissions to run for the user cryrpt. However, now I wonder about the "linked server" comment. The server this query is running on is just one of our sql servers and the active directory server is defined in the stored procedure itself. So now pondering that. The stored prodedure runs correctly when I run it on the server. I will play with the user in the definition and see what I can figure out. I will post back shortly.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
I do think it has to do with the linked server to the ADSI, but I have no clue what I'm looking for. It appears to be set up correctly, and in looking at the code for the stored procedure, it's the sub query that's blowing. When I run this sp directly on the sql server I don't get any errors. Any suggestions as to what permissions or settings that would cause this error?
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[GetLdapUserGroups]    Script Date: 01/05/2012 17:00:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetLdapUserGroups]
    ( @LdapUsername NVARCHAR(256)
    )
AS 
BEGIN
    DECLARE @Query NVARCHAR(1024),
        @Path NVARCHAR(1024)
    SET @Query = 'SELECT @Path = distinguishedName FROM OPENQUERY(ADSI, ''             SELECT distinguishedName FROM ''''LDAP://DC=sd_corp,DC=local'''' WHERE objectClass = ''''user'''' AND sAMAccountName = '''''
        + @LdapUsername + '''''         '')     '
    EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(1024) OUTPUT',
        @Path = @Path OUTPUT
    SET @Query = 'SELECT name AS LdapGroup FROM OPENQUERY(ADSI,'' SELECT name              FROM ''''LDAP://DC=sd_corp,DC=local'''' WHERE objectClass=''''group'''' AND                 member='''''
        + @Path + '''''         '') ORDER BY name'
    EXEC SP_EXECUTESQL @Query
END

Open in new window

0
Stephan_SchrandtCommented:
So you have 2 (or more) SQL Servers, Server 1 is the instance you connect to and Server 2 is a linked instance in server 1?
A couple of questions:
1. If you execute the procedure on the server directly, on what server do you do that?
2. Can you connect to the server with Enterprise Management Console using the account data that you provided in the connectionstring of asp.net and successfully execute the stored proc?
3. Please post your connectionstring (anonymize the pw, but leave the user)

0
Carla RomereDirector of Information TechnologyAuthor Commented:
We have several sql servers. On sdwineng01, there is a linked server set up to access active directory and it's called ADSI. I connect to sdwineng01 as MYSELF and the sproc runs correctly. I granted execute permission to the sproc to the user cryrpt, but if I connect as cryrpt, the sproc does not run. It seems to hang up on the subquery inside the sproc.

Here is my connection string:
        cs = "Data Source=sdwineng01;Initial Catalog=master;User Id=cryrpt;Password=xxxxx;";

The stored procedure was created in the master database. I added the user cryrpt to the master database and granted execute permissions for the sproc.

On this provider (ADsDSOObject) these items are checked under properties:
Dynamic Parameter, Nested Queries and Allow Inprocess.

I think I may have fixed the permissions issue by forcing the server itself to use my login when no login impersonation is defined and I could log in as cryrpt and ran the sproc correctly. Now when I run the sproc from the web page, I don't get any errors at all, but the list of groups never shows up on the page.
0
Carla RomereDirector of Information TechnologyAuthor Commented:
I don't get any errors when I run this page now, but the list of groups doesn't print out on the page. What do I need to add to get the groups to actually get listed?
Code behind the button:
    protected void btnSubmit_Click(object sender, EventArgs e)
    {
        string val = txtUserID.Text.Trim();
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter adp = new SqlDataAdapter();
        DataSet ds = new DataSet();
        string cs = null;
        cs = "Data Source=sdwineng01;Initial Catalog=master;User Id=cryrpt;Password=cryrpt;";
        SqlConnection SQLCon = new SqlConnection(cs);
        cmd = new System.Data.SqlClient.SqlCommand("GetLdapUserGroups", SQLCon);
        SQLCon.Open();
        cmd.CommandType = CommandType.StoredProcedure;
        System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);
       // cmd.Parameters.Add("@LdapUsername", SqlDbType.NVarChar(256));
        cmd.Parameters["@LdapUsername"].Value = val;
        adp.SelectCommand = cmd;
        adp.Fill(ds);
        //the dataset is filled with the group records
    }

This is the main page:
    <form id="form1" runat="server">
    <div>
        User ID:
        <asp:TextBox ID="txtUserID" runat="server"></asp:TextBox>
        <br />
        <br />
        <asp:Button ID="btnSubmit" runat="server" Text="Get User's Groups" OnClick="btnSubmit_Click" />
        <br />
        <br />
    </div>
    </form>

Open in new window

0
Stephan_SchrandtCommented:
You need a datalist or gridview to populate the result. Add a gridview to the page and this lines of code behind adp.Fill(ds):

 GridView1.AutoGenerateColumns = true;
 GridView1.DataSource = ds;
 GridView1.DataBind();
0
Carla RomereDirector of Information TechnologyAuthor Commented:
Absolutely perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.