Query a test AD instance with SQL user Authentication??

[wil...@noclient.net]

On my new test AD server I can query the AD from SQL server when
logged in with integrated security logged in as the box
administrator.
However, when on my client which is on the production domain where I
don't have Administrator rights and becuase my test AD is on a
different Domain, I'm having to log into the DB using SQL
authentication.

the below script works on the server when Administrator

select * from openquery
(
DEVPLAYADSI,'SELECT samaccountname
FROM ''LDAP://dc=xxxxxxxxx,dc=zzzzzz''
WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

But gives me this error when loggined in SA remotely:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "DEVPLAYADSI"
reported an error. The provider indicates that the user did not have
the permission to perform the operation.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samaccountname
FROM 'LDAP://dc=xxxxxxxxxxxx,dc=yyyyyyyyyyy'
WHERE objectCategory = 'Person' AND objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked
server "DEVPLAYADSI".

I guess my question is.. what do I need to do or create in my Test AD
so that a particular SQL user can query it? Will a domain account be
requred and then authentication into that account?

Thanks for any help or information.

[ML]

In SQL Server 2005 you can create a CREDENTIAL that will allow a SQL Login to
access resources outside SQL Server.

Look up CREATE CREDENTIAL, CREATE LOGIN and LATER LOGIN in Books Online.

ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

[jc]

Thank you..

I tried this:

CREATE CREDENTIAL AdminCred WITH IDENTITY = 'MYDOMAIN\Administrator',
SECRET = 'mypass';

ALTER login devplay with credential=AdminCred

Made sure devplay had access to the linkserver entry DEVPLAYADSI(some
confusing combination of options there .. impersonate, remote user/
password, security context login. I attempted every combination
entering the AD domain administrator and password for devplay..

But still.. this:

select * from openquery
(
DEVPLAYADSI,'SELECT samaccountname

FROM ''LDAP://dc=xxxxxx,dc=xxxxxx''

WHERE objectCategory = ''Person'' AND objectClass = ''user''
')

produces this:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "ADsDSOObject" for linked server "DEVPLAYADSI"
reported an error. The provider indicates that the user did not have
the permission to perform the operation.
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT samaccountname

FROM 'LDAP://dc=xxxxxx,dc=xxxxxxxx'

WHERE objectCategory = 'Person' AND objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked
server "DEVPLAYADSI".

What's odd though is If log in as devplay (SQL authentication) on the
server directly I am able run the query and it works. So the only
difference is being remote or being local. Again, remotely my client
is on a different domain with no domain trusts.

I am able to run a very similar query in production (remotely) using
windows authentication to our production AD which is on the same
domain and it works fine remotely. I can't do the same thing for
development because it's a different domain and it's untrusted.

I'm going through all of this because I need to be able to run test
code through Visual Studio on my client using SQL connection and know
it will fail if this is failing.

Thank you Again for any help or information!

[jc]

Sorry .. nevermind all that.. I just had to reconnect and it worked.
Very nice indeed. thank you.