RE: [SQLDownUnder] Script in SQL2005

[Greg Wood]

Hi Kristen,

 

Make sure your SQL Agent account is running as a domain user, as Active Directory will need to authenticate.

 

Here is a script I use that works on both SQL 2k and 2k5

 

sp_addlinkedserver 'INFRA_ADlookup', 'Active Directory Service Interfaces',
'ADSDSOObject', 'adsdatasource'
go

--through enterprise manager go and set the user to connect to Ad in the box at the bottom -

SELECT     *
FROM         OPENQUERY(INFRA_ADlookup,
                      'SELECT sAMAccountName,name,whenCreated,homeDirectory,scriptPath,displayName,telephonenumber,department FROM
''LDAP://ou=users,dc=Addomain,dc=net'' WHERE
objectClass= ''user''')

 

Greg

---

> Subject: RE: [SQLDownUnder] Script in SQL2005
> Date: Mon, 27 Mar 2006 13:54:58 +1100
> From: greg.low@xxxxxxxxxxx
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
>
> Hi Kristen, 
>
> Are you running it on the same system (is 2k5 on the same system as the
> 2k)? If not, are you sure the ADSI OLEDB provider is installed? 
>
> Regards,
>
> Greg
>
>
> Dr Greg Low
> Readify - Senior Consultant
> M: +61 419 201 410
>
>
> -----Original Message-----
> From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx
> [mailto:SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx] On Behalf Of Kristen
> Harvey
> Sent: Monday, 27 March 2006 12:13 PM
> To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
> Subject: [SQLDownUnder] Script in SQL2005
>
> Hi All,
>
> I'm having a bit of trouble with a script.  The script below works fine
> on SQL2000, but when I run it on SQL 2005 I get the below error.
>
> SELECT *
>   INTO TMP_AD
>   FROM OpenQuery(
> ADSI,'<LDAP://Server/OU=User
> Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));
> sAMAccountName, givenName, sn, company, title, mail, department,
> adspath;subtree')
>
> Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing the query "<LDAP://Server/OU=User
> Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));
> sAMAccountName, givenName, sn, company, title, mail, department,
> adspath;subtree" for execution against OLE DB provider "ADSDSOObject"
> for linked server "ADSI". 
>
> I run this first on both scripts
>
> EXEC sp_addlinkedserver
> 'ADSI',
> 'Active Directory Services 2.5',
> 'ADSDSOObject',
> 'adsdatasource'
>  
> EXEC sp_addlinkedsrvlogin
> 'ADSI',
> 'false',
> NULL,
> NULL,
> NULL
>
> Regards
> Kristen
>
> ------------------------------------------------------------------------
> ---
> This email is private and confidential and may contain legally
> privileged and/or commercially sensitive information.
>
> If this communication is not intended for you and you are not an
> authorised recipient of this email, you are prohibited from dealing with
> or relying on the email or any file attachments.  This prohibition
> includes reading, printing, copying, re-transmitting, disseminating,
> storing or in any other way dealing or acting in reliance on the
> information.  If you have received this email in error, we request you
> contact the sender immediately by return email and then destroy any
> electronic or paper copy of this message.
>
> Reasonable steps have been taken to ensure the accuracy and the
> integrity of this communication but the sender and his/her employer
> accepts no liability for materials transmitted or for results of any
> actions taken or not on the basis of the information in this
> communication.  Privilege in the contents of the email or any file
> attachments is not waived if the email is inadvertently sent to the
> wrong address.  This email has been scanned through virus scanning
> software, but no guarantee is given that this email and any file
> attachments are virus free.
> ------------------------------------------------------------------------
> ---
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed
> by http://www.readify.net
>
>
>
>
> to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net
>
>

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net