Re: Linked Server and ADSI

From: Mike Hodgson (mwh_junk_at_hotmail.com)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 11:20:59 +1100

That's fine but you'll need some remote login credentials with which to
query AD (otherwise what security context is it going to use?). I've set up
links to AD from my SQL servers by adding the linked server (as you've done)
and then adding a remote login mapping so that all local logins use a
specific set of credentials to connect via ADSI (I'm not overly concerned
about oppressive security in this case because any user that connects to my
SQL box can also query AD anyway - this just makes multi-source queries a
little more transparent/seamless). Specifically I've set up the mapping so
that all local logins use the service account under which SQL is running
(ie. Windows account). That is,

exec sp_addlinkedsrvlogin 'ADSI', false, NULL,
'MyDomain\MySQLServiceAccount', 'MyServiceAccountPassword'

So any SQL client session that issues an ADSI query through that linked
server will query AD using the service account that SQL server is running
under (this assumes you're running your SQL server under a domain account,
not as System or a local SAM account). Works pretty well for me - the only
thing you have to bear in mind is you can only query the bits of AD that the
SQL service account has been granted permissions to query. 

-- 
Cheers,
Mike
"sfibich" <sfibich@pfgc.com> wrote in message 
news:u2w7zDOzEHA.1264@TK2MSFTNGP12.phx.gbl...
> Does anyone one know how to create a linked server to active directory on 
> a sql server that is not a domain controller?
>
> I have tried the following to add the linked server
> EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>
>
> but I can not issue any querys without getting the following error
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB 
> provider 'ADSDSOObject'.
> OLE DB error trace [OLE/DB Provider 'ADSDSOObject' 
> ICommandPrepare::Prepare returned 0x80040e14].
>
>
> This is running on a windows 2000 sql 2000 box.
>
> Thanks in advance.

Relevant Pages

  • Re: ntext getting truncated
    ... Tibor Karaszi, SQL Server MVP ... I applied the query as follows: ... select counthowmany, datalengthntextlength ...
    (microsoft.public.sqlserver.programming)
  • Re: upsizing to sql 2005
    ... which include MS SQL Server linked table. ... Query Name: Arcadia - ARC ... are still unable to upsize one of these queries, ...
    (microsoft.public.access.queries)
  • Re: VS.NET 2003, ADO.NET 1.1 and Access 2000: Getting a Concurrency violation
    ... I am using an Access update query, with parameters, for the ... Then my app goes against a SQL ... OleDbDataAdapter that I use to retrieve the original table from the ... So your named parameters for SQL Server suddenly become anonymous ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: upsizing to sql 2005
    ... A query in Access-Jet will be started and under the supervision of Jet, ... Jet syntax parsing and evaluation. ... so, in this case, into MS SQL Server. ...
    (microsoft.public.access.queries)
  • Re: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)