March 9, 2010 at 9:31 am
Hi,
I have a server with both SQL 2000 and SQL 2005. Both instance have Linked Servers to Active Directory
The following query runs in SQL 2000 but failes in SQL 2005. Any thoughts?
Thanks
Colin
select objectGUID,
cn,
displayName,
SUBSTRING(userPrincipalName, 1, CHARINDEX('@', userPrincipalName)-1),
sAMAccountname,
givenname,
sn,
distinguishedName,
SUBSTRING(mail, 1, CHARINDEX('@', mail)-1),
SUBSTRING(mail, CHARINDEX('@', mail)+1, 99),
MsExchMailboxGUID,
telephoneNumber,
mobile,
userAccountControl
FROM OpenQuery(ACTIVE_DIRECTORY,
'<LDAP://DC=EU, DC=aecomnet, DC=Com>;
(&(objectCategory=Person) (objectClass=User) (cn<=C));
objectGUID, cn, displayName, userPrincipalName,
sAMAccountname, givenname, sn, distinguishedName, mail,
MsExchMailboxGUID, telephoneNumber, mobile, userAccountControl')
ERROR
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "<LDAP://DC=EU, DC=aecomnet, DC=Com>;
(&(objectCategory=Person) (objectClass=User) (cn<=C));
objectGUID, cn, displayName, userPrincipalName,
sAMAccountname, givenname, sn, distinguishedName, mail,
MsExchMailboxGUID, telephoneNumber, mobile, userAccountControl" for execution against OLE DB provider "ADsDSOObject" for linked server "ACTIVE_DIRECTORY".
March 9, 2010 at 9:55 am
Is the SQL Service account for 2005 using the same account as the 2000 instance? If not does the 2005 account have domain rights to that OU you are trying to query?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 10, 2010 at 1:51 am
Both instances run under the same account
March 10, 2010 at 6:06 am
This SCC post seems to follow along the same error you are having.
I don't mess with linked servers to AD much, but quick view of the post above seems to point either to your service account or the account configured for the linked server to have access to AD.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
March 10, 2010 at 6:30 am
THANK YOU!
I changed the query to the formatbelow and enabled OPENROWSET under Surface Area Configuration. It has turned into quite a good day. 🙂
select objectGUID,
cn,
displayName,
SUBSTRING(userPrincipalName, 1, CHARINDEX('@', userPrincipalName)-1),
sAMAccountname,
givenname,
sn,
distinguishedName,
SUBSTRING(mail, 1, CHARINDEX('@', mail)-1),
SUBSTRING(mail, CHARINDEX('@', mail)+1, 99),
MsExchMailboxGUID,
telephoneNumber,
mobile,
userAccountControl
FROM OpenRowSet('ADSDSOObject',
'ACTIVE_DIRECTORY',
'<LDAP://DC=EU, DC=aecomnet, DC=Com>;
(&(objectCategory=Person) (objectClass=User) (cn<=C));
objectGUID, cn, displayName, userPrincipalName,
sAMAccountname, givenname, sn, distinguishedName, mail,
MsExchMailboxGUID, telephoneNumber, mobile, userAccountControl')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply