SQLServer uses ADSI to perform Distributed Query of activedorecloud objects

Source: Internet
Author: User
Tags how to use sql samaccountname
SQLServer uses ADSI to perform Distributed Query of activedorecloud objects.

SQLServer uses ADSI to perform Distributed Query of activedorecloud objects.

Step 1: Creating a Linked Server.
EXEC sp_addmediaserver 'adsi', 'active Directory Services 100', 'adsdsoobject', 'adsdatasource'
Step 2: Creating a SQL Server Authenticated Login
EXEC sp_add1_srvlogin @ rmtsrvname = N 'adsi', @ locallogin = NULL, @ useself = N 'false', @ rmtuser = N 'domain \ account', @ rmtpassword = N 'Password'
For SQL Server authorized logon, you can use sp_addjavassrvlogin system stored procedure configuration to connect to the appropriate logon/password for the Directory Service.
See here: http://blogs.msdn.com/euanga/archive/2007/03/22/faq-how-do-i-query-active-directory-from-sql-server.aspx
If SQLServer uses Windows-authorized logon, you only need to use self- ing to access AD by using SQL Server Security delegation. Simply put, you can directly run the Step 3 Statement.
Step 3: Querying the Directory Service.
The Code is as follows:
-- Query for a list of User entries in an OU using the SQL query dialect
Select convert (varchar (50), [Name]) as FullName,
Convert (varchar (50), Title) as Title,
Convert (varchar (50), TelephoneNumber) as PhoneNumber
From openquery (ADSI,
'Select Name, Title, TelephoneNumber
From ''ldap: // OU = Directors, OU = Atlanta, OU = Intellinet, DC = vizability, DC = intellinet, DC = com''
Where objectClass = ''user ''')
-- Query for a list of Group entries in an OU using the SQL query dialect
Select convert (varchar (50), [Name]) as GroupName,
Convert (varchar (50), [Description]) GroupDescription
From openquery (ADSI,
'Select Name, Description
From ''ldap: // OU = VizAbility Groups, DC = vizability, DC = intellinet, DC = com''
Where objectClass = ''group ''')

Reference:
Http://msdn2.microsoft.com/en-us/library/aa772380.aspx
Http://www.atlantamdf.com/presentations/AtlantaMDF_111201_examples.txt

Note: by default, 1000 objects are queried. How can this problem be solved?
Method 1: cycle through letters. See the following:
The Code is as follows:
Create table # tmpADUsers
(EmployeeId varchar (10) NULL,
SAMAccountName varchar (255) not null,
Email varchar (255) NULL)
GO
/** // * AD is limited to send 1000 records in one batch. In an ADO interface you can define this batch size, not in OPENQUERY.
Because of this limitation, we just loop through the alphabet.
*/
DECLARE @ brief STR varchar (255)
DECLARE @ nAsciiValue smallint
DECLARE @ sChar char (1)
SELECT @ nAsciiValue = 65
WHILE @ nAsciiValue <91
BEGIN
SELECT @ sChar = CHAR (@ nAsciiValue)
EXEC master .. xp_sprintf @ brief str output, 'select employeeId, SAMAccountName, Mail from openquery (ADSI, ''select Mail, SAMAccountName, employeeID FROM ''' LDAP: // dc = central, dc = mydomain, dc = int ''' WHERE objectCategory = ''' Person ''' AND SAMAccountName = ''' % s *'''''') ', @ sChar
INSERT # tmpADUsers
EXEC (@ brief Str)
SELECT @ nAsciiValue = @ nAsciiValue + 1
END
Drop table # tmpADUsers

The above method is derived from:

My recommended method: I found it at Microsoft.How to Use NTDSUtil to modify the maxPageSize limit for the server

Modifying policy settings

Source:



How to use SQL to query the Active Directory object Syntax:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.