I'm assuming you tried running the report query using the same login that you
used in Query Analyzer. Otherwise we're comparing apples to oranges. If we
rule that out, it sounds like you might be having a problem with your
security account delegation.
If your IIS box is not the same as the SQL Server box that has the RS
databases, then reports running with Windows authentication are already using
security delegation from the web server to the database server. For security
reasons, you can not keep hopping from server to server, so your
authentication to the domain controller may be being denied. You can run in
the same problem with only SQL Servers if you exec a stored procedure A that
calls a SP on linked server B, which calls a SP on linked server C....
To test this out, change the security settings for your ADSI linked server.
The default is to use the login's current security ontext. Try specifying an
explicit security context and see if that fixes the problem. To really make
sure the problem I'm talking about isn't happening, set up the linked server
security so a SQL standard (non-Windows) login successfully runs your query
in Query Analyzer, then set your report to use that standard login.
I haven't tried doing what you're doing, so I'm not sure if this is your
problem.
HTH,
Ted
[quoted text, click to view] "PCTC_IT" wrote:
> Everything works fine from Query analyzer, but when i try to select from the
> view in the Reporting Services Designer DataSouce it gives me the following
> error.
>
> "Could not generate a list of fields for the query.
> Check the query syntax or click Refresh Fields on the query toolbar.
> An error occurred while preparing a query for execution against OLE DB
> provider 'ADSDSOObject'."
>
> I've tried it with SQL Authentication as well as Integrated Window
> Authentication.
> My Query is as follows using the SQL OLE Provider.
> Select * from vADSI
>
> My View is as Follows.
> CREATE VIEW dbo.vADSI
> AS
> SELECT * FROM OpenQuery( ADSI,
> '<LDAP://DC=myDomain,DC=com>;(objectClass=user);name, adspath,
> title;subtree')
>
> Seem simple enough. Like i said, works fine from query analyzer. Bombs in
> Reporting Services.
>
> Please help
Thanks, I solved the problem using sp_addlinkedsrvlogin as stated in this
article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
Now it uses a domain account any time it connects to the linked server.
[quoted text, click to view] "Ted K" wrote:
> I'm assuming you tried running the report query using the same login that you
> used in Query Analyzer. Otherwise we're comparing apples to oranges. If we
> rule that out, it sounds like you might be having a problem with your
> security account delegation.
>
> If your IIS box is not the same as the SQL Server box that has the RS
> databases, then reports running with Windows authentication are already using
> security delegation from the web server to the database server. For security
> reasons, you can not keep hopping from server to server, so your
> authentication to the domain controller may be being denied. You can run in
> the same problem with only SQL Servers if you exec a stored procedure A that
> calls a SP on linked server B, which calls a SP on linked server C....
>
> To test this out, change the security settings for your ADSI linked server.
> The default is to use the login's current security ontext. Try specifying an
> explicit security context and see if that fixes the problem. To really make
> sure the problem I'm talking about isn't happening, set up the linked server
> security so a SQL standard (non-Windows) login successfully runs your query
> in Query Analyzer, then set your report to use that standard login.
>
> I haven't tried doing what you're doing, so I'm not sure if this is your
> problem.
>
> HTH,
> Ted
>
> "PCTC_IT" wrote:
>
> > Everything works fine from Query analyzer, but when i try to select from the
> > view in the Reporting Services Designer DataSouce it gives me the following
> > error.
> >
> > "Could not generate a list of fields for the query.
> > Check the query syntax or click Refresh Fields on the query toolbar.
> > An error occurred while preparing a query for execution against OLE DB
> > provider 'ADSDSOObject'."
> >
> > I've tried it with SQL Authentication as well as Integrated Window
> > Authentication.
> > My Query is as follows using the SQL OLE Provider.
> > Select * from vADSI
> >
> > My View is as Follows.
> > CREATE VIEW dbo.vADSI
> > AS
> > SELECT * FROM OpenQuery( ADSI,
> > '<LDAP://DC=myDomain,DC=com>;(objectClass=user);name, adspath,
> > title;subtree')
> >
> > Seem simple enough. Like i said, works fine from query analyzer. Bombs in
> > Reporting Services.
> >
> > Please help
Don't see what you're looking for? Try a search.
