Errors and optional solutions while querying LDAP

ERROR:

An error occurred while preparing the query ... for execution against OLE DB provider ADSDSOObject for linked server.

Optional Solutions/problems:

Add the server name to the LDAP address: LDAP://CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 --> LDAP://SERVERNAME/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2.

ERROR:

The OLE DB provider "ADSDSOObject" for linked server "adsi_test" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Optional Solutions/problems:

Run the query with user that have relevant permissions in the LDAP.

ERROR:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsi_test".

Optional Solutions/problems:

Maybe the data from the LDAP is wrong.

ERROR:

An error occurred while preparing the query "

...........

" for execution against OLE DB provider "ADSDSOObject" for linked server "adsi_test". 

Optional Solutions/problems:

- Wrong syntax of the query.

- Maybe there is a problem to run the query from other machine that it's not the SQL Server.

Querying LDAP through SQL Server

In order to querying LDAP (Active Directory, LDS, etc.) through SQL Server, we need to create a Linked Server to the LDAP, and use OPENROWSET statement to select data from it.

-- create new linked server

exec sp_addlinkedserver  

  @server = 'adsi_test',-- linked server name

  @srvproduct = 'LDAP',  

  @provider ='ADSDSOObject',-- provider name

  @datasrc = ''  

go

-- create linked server login for sa

exec sp_addlinkedsrvlogin

  @rmtsrvname = 'adsi_test',-- linked server

  @useself = false,

  @locallogin = 'sa',-- sqlUser

  @rmtuser = 'domain\master_user',-- domainName\userName

  @rmtpassword = 'password'-- domain User Password

go

-- 2 options of query from LDAP:

--1:

select * FROM OPENQUERY(adsi_test, '<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;(&(name=*));name,distinguishedName;subtree

')

--2:

select *  

from  openquery(adsi_test, '  

select  userPrincipalName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person'' and objectClass = ''user''  

')

• CN is containers that are usually defined as users, contacts, groups.
• OU is organizational units that usually contain objects such users, contacts, groups and other OUs.
• DC is domain containers that are created by separating the full internal domain name.
• LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 = LDAP:// SERVER NAME : port / Root of the LDS/AD tree
• (&(name=*)) = WHERE NAME = * (no filter by name)
• ;name,distinguishedName - fields to select from LDS
• ;subtree - include all the tree from the declared root

Examples:

-- select all groups:

select distinguishedName from  openquery(adsi_test, '  

select name, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Group''  

and  objectClass = ''Group''

')

-- select all users of group 'Managers' in AAA

select *

from  openquery(adsi_test, '  

select objectGUID, name, givenname, sn, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person''  

and  objectClass = ''user'' 

AND memberOf = ''cn=Managers,CN=AAA,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''

')

-- list of the groups that the user itaig is a member of: (the function 'member')

select * FROM OPENQUERY(adsi_test, 

'<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;

(&(objectCategory=group)(member=CN=itaig,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2));name,distinguishedName,objectGUID

')


Search Filter Syntax (msdn):
http://msdn.microsoft.com/en-us/library/Aa746475

Permanently deletion in case of instead of delete trigger

Permanently deletion is possible also in case of 'instead of delete' trigger on the table:

Delete statement in the 'instead of delete' trigger performs permanently deletion:

• The original deletion statement will not be executed - the code in the trigger will be executed instead.
• Deletion statement in the trigger will be execute as regular DELETE without the trigger code (and will not start infinite loop).

The common use of deletion from 'instead of delete' trigger is to write a row in LOG table before the deletion.

For example:

CREATE TABLE [dbo].[DelTests]

(

DelId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

DelStr nvarchar(50) NULL

)

GO

CREATE TRIGGER  [dbo].[trg_DelTests_delete] ON [dbo].[DelTests]

   instead of delete

AS 

BEGIN

DECLARE @Str nvarchar(50) = 'row that inserted from trg_DelTests_delete instead of'

INSERT INTO DelTests

select @Str from deleted

DELETE  DelTests WHERE DelId IN (select DelId from deleted)

END

GO

INSERT INTO DelTests (DelStr) VALUES ('DelStr1')

INSERT INTO DelTests (DelStr) VALUES ('DelStr2')

INSERT INTO DelTests (DelStr) VALUES ('DelStr2_1')

GO

select * from DelTests

GO

DELETE FROM DelTests WHERE DelStr = 'DelStr1' -- 1 row

GO

select * from DelTests

GO

/*

-- results:

DelStr2

DelStr2_1

row that inserted from trg_DelTests_delete instead

*/

DELETE FROM DelTests WHERE DelStr Like '%DelStr2%' -- 2 rows

GO

select * from DelTests

GO

/*

-- results:

row that inserted from trg_DelTests_delete instead

row that inserted from trg_DelTests_delete instead

row that inserted from trg_DelTests_delete instead

*/

Reporting Execution Log

SELECT * 

FROM [ReportServer].[dbo].[ExecutionLog2] 

ORDER BY TimeStart desc

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx

CONCAT_NULL_YIELDS_NULL

CONCAT_NULL_YIELDS_NULL controls whether or not concatenation results are treated as null or empty string values.

declare @strNull nvarchar(50) = null

SET CONCAT_NULL_YIELDS_NULL ON

select (@strNull + 'aaa') as str_result -- result: NULL

SET CONCAT_NULL_YIELDS_NULL OFF

select (@strNull + 'aaa') as str_result -- result: 'aaa'

http://msdn.microsoft.com/en-us/library/aa259202(v=sql.80).aspx

Can not connect SQL Server from SSMS

When you Can not connect SQL Server from Management Studio (SSMS), Try:

1. Enable TCP/IP in the SQL Server Configuraion Manager:

2. Check Firewall settings on the server. If you can connect to the server when The firewall is disabled, the problem is the Firewall settings.