asked on

MS SQL OpenQuery problem

I have MS SQL 2012 installed on a W2K8 (64bit) server. I have a 'view' that queries Active Directory Users. I've attached the LinkServer setup and the OpenQuery view.

The view works fine when it is issued from the same server that the database is on. When I try to run it from another server I get an error. Here is the SQL statement I'm using:

select * from dbo.uvw_ActiveDirectoryUsersList

Here is the error I get:

Msg 7320, Level 16, State 2, Line 2
Cannot execute the query "SELECT samaccountname,
givenName,
sn,
displayName,
title,
physicaldeliveryofficename,
department,
useraccountcontrol,
profilepath
FROM 'LDAP://OU=WCC Users,DC=wcc,DC=net'
WHERE objectCategory = 'Person' AND
objectClass = 'user'" against OLE DB provider "ADSDSOObject" for linked server "ADSI".

I have tried this using SSMS to connect remotely and from an application on a server that has only the SQL 2012 Native Client installed. I need to be able to run this query from an application server that has just the SQL 2012 Native Client installed, if possible.

BTW, I have domain admin privileges.

What am I missing?

Thanks in advance
OpenQuery.sql
LinkedServer.sql

Brian Crowe

found this at http://www.sqlservercentral.com/Forums/Topic265745-149-1.aspx ...

SELECT * FROM OPENQUERY( [SERVER1],
'SELECT sAMAccountName, userPrincipalName, company, department, Name, givenName, SN, Mail, telephoneNumber, mobile,
l, physicalDeliveryOfficeName, postalCode, streetAddress, facsimileTelephoneNumber, msExchHideFromAddressLists, distinguishedName, info
FROM ''LDAP://SERVER1/ DC=subsubdomain,DC=subdomain,DC=domain''
WHERE
objectClass = ''user''
AND
objectCategory = ''Person''
AND (mobile = ''*'' OR telephoneNumber = ''*'' OR facsimileTelephoneNumber = ''*'' OR mail = ''*'' )
ORDER BY userPrincipalName
')

The difference lies in a space between "LDAP://SERVER1/" and "DC=subsubdomain,DC=subdom..."
and in the where clause. Don't know if that will make the difference?

ASKER CERTIFIED SOLUTION

softbreeze

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

softbreeze

ASKER

I figured it out finally. I had to add a rmtuser and password to the sp_addlinkedserver statement:

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',
@useself=N'True', <--- N'False'
@locallogin=NULL,
@rmtuser=NULL, <--- N'wcc\wccadmin'
@rmtpassword=NULL <--- N'secretpassword'

softbreeze

ASKER

Even though I figured it out, you get the points for the quick reply that sent me in the right direction.