Hi - I am trying and failing to run a simple Report against our local Active Directory from Report Builder 3.0.

I can get it to work in SQL Server 2008R2 using OPENQUERY - but not in Report Builder

When i attempt to run the report, Report Builder returns this error: "Query Execution failed for 'Dataset'. (rsErrorExecutingCommand)".

Here's what i've done:

1. I've created a Data Source in Report Builder that connects successfully to the local AD

2. I've created a Dataset that uses this Data Source - In Expression builder i've used the exact same query (below) that i used successfully with OPENQUERY. I only changed the single/double quotes so that it'll work in Report Builder.

="SELECT Name FROM 'LDAP://DC=ABC,DC=COM' WHERE givenName='John' "

3. I've manually added the 'Name' field because Report Builder does not pick it up automatically.

4. In Report Builder i created a Table with 1 column in a report that is populated from the 'Name' field. But when i run the report i get that error.

I've tried everything and i can't figure it out. It's probably something small like a missing single or double quote.

Any suggestions would be really appreciated. Thanks in advance.

Hi, 

According to your description, when running the report in Report Builder, you get an error Query Execution failed for 'Dataset, right?

In Reporting Services, we can use LDAP to pull data from Activity Directory database. In your scenario, if you want to use LDAP, the data source type must be OLE DB. Then since the data source is created, you need to use the expression builder because the Query Designer does not interpret LDAP queries. For achieving your goal, please refer to this PPT: http://sqlwes.com/wp-content/uploads/2014/06/QueryingActiveDirectoryFromSSRS.pptx

If you have any question, please feel free to ask. 

Regards, 
Shrek Li

Hi Shrek Li,

Thanks for taking the time to reply to my question.

Yes - i get the error when i attempt to run the report. The Data Source connects successfully but something is preventing the report from running. It is OLE DB.

I had used that PPT you refer to when building my report. This was the only resource i could find online. But something must be wrong with the query i have written. It looks right but i still tried different versions of it with single/double quotes and that didn't work.

If you can thing of anything else let me know. Thanks again.

Hi, 

Could you share the whole error message? Thanks for your cooperation. 

Regards, 
Shrek Li

Hi,

The error message is:

For more information about this error navigate to the report server on the local server machine, or enable remote errors
----------------------------
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

Hi, 

Can "SELECT Name FROM 'LDAP://DC=ABC,DC=COM' WHERE givenName='John'"  this query executed successfully in SSMS? Or in SSMS, the query executed successfully with OPENQUERY? Please try to add OPENQUERY to the Exrepssion Builder to see if it is executed properly.

Regards, 
Shrek Li

Hi,

I created the OPENQUERY script as a Stored Proc in a SQL Server DB. It looks like this:

Create Procedure [dbo].[AD_LOOKUP]
AS
BEGIN
SELECT Name
FROM OPENQUERY(ADSI,'SELECT Name FROM ''LDAP://DC=ABC,DC=COM'' WHERE givenName=''John'' ')
END

In SSMS if i run exec AD_LOOKUP this will run successfully and return data.

However if i call the Stored Proc from Report Builder - i can see the Stored Proc in the dropdown (connection is successful) but when i attempt to run it in Report Builder i get this error:

An error occurred while preparing the query "SELECT Name FROM 'LDAP://DC=ABC,DC=COM' WHERE givenName='John' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".  (.Net SqlClient Data Provider)

An error occurred while preparing the query "SELECT Name FROM 'LDAP://DC=ABC,DC=COM' WHERE givenName='John' " for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".  (.Net SqlClient Data Provider)


Program Location:


Server stack trace:
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.ReportingServices.DataExtensions.SqlCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
   at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.AsyncExecuteReader(IDbCommand command, CommandBehavior behaviour)
   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

Exception rethrown at [0]:
   at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
   at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
   at Microsoft.ReportingServices.QueryDesigners.MultiThreadedQueryResultsHelper.ExecuteReaderHandler.EndInvoke(IAsyncResult result)
   at Microsoft.ReportingServices.QueryDesigners.MultiThreadedMultiConnectionQueryResultsHelper.<>c__DisplayClass4.<ExecuteActiveQueryCallback>b__3()
   at Microsoft.ReportingServices.QueryDesigners.QueryResultsGrid.EndExecuteReaderInUiThread(GetDataReaderHandler getDataReaderCallback)

Hi,

I've got the OPENQUERY stored proc working now from Report Builder by changing the security settings on the Linked Server as per this post:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/083229e7-58f2-44cf-ae5d-c9acf366818c/sql-2005-ldap-query-error-msg-7321-level-16

So that's good. But i'd still like to find a way to connect directly to AD from Report Builder without having to use a Linked Server in a separate DB.

• Proposed as answer by Shrek LiMicrosoft contingent staff 4 hours 37 minutes ago

Hi,

Sorry for delay.

We are glad to hear your issue has been solved by using a linked server.

Regards,
Shrek Li