7

I have done extensive research on querying LDAP Server but so far has not been able to work around. Therefore, asking experts help

Background: I have a client whose LDAP Server is OnPremise (though facing publicly) and I have a SQL Server(2012 SE) inside the firewall. The communication between the External LDAP and Internal SQL Server are fine as I can ping each other. I also created a remote domain account and used this account when created Linked Server on SQL Server. I also have our internal Domain controller that has Domain Trust Relationship. Now, I have created 2 Linked Servers using Domain account but I have not been able to query LDAP Server on both internally and externally. I hope this is making sense. Anyways, Here are the definitions of my Linked Server and errors I am receiving

[Solved] Linked Server to an Internal LDAP Server: This was resolved by using ADuser@domain.com instead of domain/ADuser

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active 
Directory Service Interfaces', @provider=N'ADSDSOObject', 
@datasrc=N'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain/ADUser',@rmtpassword='########'

Error:

Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "SELECT *
FROM 'LDAP://Domain.com/OU=Accounts,DC=Domain,DC=com' --WHERE objectClass = 'User' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

Linked Server to an External LDAP Server

USE [master]
GO


EXEC master.dbo.sp_addlinkedserver @server = N'ADSIE', @srvproduct=N'Active 
Directory Service Interfaces', @provider=N'ADSDSOObject', 
@datasrc=N'CustomerIPAdress'

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,
@rmtuser=N'Domain/ADUser',@rmtpassword='########'

Error:

Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "ADsDSOObject" for linked server "ADSIE" 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 * FROM 'LDAP://Customer IP Adress' WHERE objectClass = 'User' " for execution against OLE DB provider "ADsDSOObject" for linked server >"ADSIE".

Any help would be appreciated.Thanks in advance.

  • One part of it is solved. I am still facing issues when I am querying external LDAP Server. Offcourse, If I resolved it then I will be more than happy to provide the solution. – Sid Oct 10 '17 at 19:17
  • Have you considered going with an SSIS package? You may want to take a look at my answer to stackoverflow.com/questions/8594775/…. Also, even if you stick with your current method, there's some good information in the other answers here that may help. – JamieSee Oct 16 '17 at 23:12
  • Yes, I have already tried the solution in the link. However, I have always ended up with "Unable to Build Query" or "Syntax error". I have made sure syntax is fine but I do not know the reason why it still keep throwing those errors – Sid Oct 18 '17 at 12:36
  • Are you executing the query against the linked server via a SQL Agent Job step or in a query window using SSMS? If it's being executed in an SSIS SQL Agent Job step, you may have to create a proxy tied to a domain user account that your AD admin has given appropriate rights to read AD. If it's executing in a T-SQL job step you may have to change the job owner. See technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx – JamieSee Oct 18 '17 at 15:48
  • The error in question above appears when I execute the query from SSMS. – Sid Oct 18 '17 at 16:28
1

What stands out the most in your second error message is the message below:

The provider indicates that the user did not have the permission to perform the operation

This tells me that the user in your linked server configuraiton doesn't have access to the AD / LDAP server to perform that query. Here is my ADSI configurations along with an example query. You may need to engage your SA team to ensure the account configured in your linked server has the correct access to query AD / LDAP.

    USE [master]
GO

/****** Object:  LinkedServer [ADSI]    Script Date: 11/10/2017 3:46:31 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'domain\accountname',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Here is an example of using the the ADSI linked server using openquery, but you need to understand the OU structure and the domain structure of AD before building the LDAP connection string.

SELECT sn, displayName, givenName, mail, telephoneNumber, ipPhone, sAMAccountName
FROM OPENQUERY(ADSI, 
'SELECT sAMAccountName, displayName, telephoneNumber, ipPhone, mail, givenName, sn 
FROM ''LDAP://DC=test,DC=intranet,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''user''')
AS derivedtbl_1

NOTE: I changed the DC name in the LDAP string for security reasons, but you should be able to get the idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.