Execute Stored Procedure Against Active Directory

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

Select allOpen in new window

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
}

Select allOpen in new window

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?

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.

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
    }

Select allOpen in new window

sorry, you have to call sqlcon.open() after line 9

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?

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)

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?

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.

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.

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

Select allOpen in new window

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)

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.

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>

Select allOpen in new window

Absolutely perfect!