澳门金沙vip 19

Querying Active Directory Data from SQL Server

Step 1:Creating a Linked Server. EXEC sp_addlinkedserver ‘ADSI’,
‘Active Directory Services 2.5’, ‘ADSDSOObject’, ‘adsdatasource’ Step
2:Creating a SQL Server Authenticated Login EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’ADSI’, @locallogin = NULL , @useself = N’False’,
@rmtuser = N’domain\Account’, @rmtpassword = N’Password’ 对于 SQL
Server 授权登录,可以使用sp_addlinkedsrvlogin
系统存储过程配置用于连接到目录服务的适当的登录/密码. 参考这里:
如果SQLServer使用Windows 授权登录,只需自映射就足以通过使用 SQL Server
安全委托来访问AD。简单点说就是直接运行第三步语句即可. Step 3:Querying
the Directory Service. 复制代码 代码如下:
— Query for a list of User entries in an OU using the SQL query dialect
select convert(varchar(50), [Name]澳门金沙vip,) 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”’) 引用: _111201_examples.txt
说明:但是这样默认查询出来的是1000个对象.怎么办呢?
方法一,通过字母来循环.见以下: 复制代码
代码如下: 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 @cmdstr
varchar(255) DECLARE @nAsciiValue smallint DECLARE @sChar char(1) SELECT
@nAsciiValue = 65 WHILE @nAsciiValue 91 BEGIN SELECT @sChar=
CHAR(@nAsciiValue) EXEC master..xp_sprintf @cmdstr 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( @cmdstr ) SELECT @nAsciiValue = @nAsciiValue + 1 END
DROP TABLE #tmpADUsers

问题

Step 1

 

Start the server in Directory Services
Restore Mode

 

Windows Server 2003/2008 Directory Service
opens its files in exclusive mode. This means that the files cannot be
managed while the server is operating as a domain controller. To perform
any files movement related activities using ntdsutil, we need to start
the server in Directory Services Restore Mode.

 

To start the server in Directory Services
Restore mode, follow these steps:

    Restart the
computer.

    After the BIOS
information is displayed, press F8.

    Use the DOWN
ARROW to select Directory Services Restore Mode, and then press
ENTER.

澳门金沙vip 1

 

Log on with your local administrative
account and password. (Not Domain Administrative
account)

 

澳门金沙vip 2

Note: using service control (SC.exe) you can
verify quickly ntds services are running or stopped. In command prompt
type SC query ntds

 

澳门金沙vip 3

 

Step 2

 

How to Move Active Directory Database and
Logs

 

You can move the Ntds.dit data file to a new
folder. If you do so, the registry is updated so that Directory Service
uses the new location when you restart the server.

 

To move the data file to another folder,
follow these steps:

    Click Start,
click Run, type ntdsutil in the Open box, and then press
ENTER.

 

澳门金沙vip 4

At the Ntdsutil command prompt, type
activate instance ntds, and then press ENTER.

 

澳门金沙vip 5

At the Ntdsutil command prompt, type files,
and then press ENTER.

 

澳门金沙vip 6

At the file maintenance command prompt, type
move DB to <new location> (where new location is an existing
folder that you have created for this purpose) and then press
ENTER.

 

In this case, the new location for database
is C:\AD\Database

澳门金沙vip 7

 

Now to move logs , at the file maintenance
command prompt, type move logs to <new location> (where new
location is an existing folder that you have created for this purpose)
and then press ENTER. In our case, the new location for database is
C:\AD\Logs

 

澳门金沙vip 8

To quit file maintenance, type quit. Again
to Ntdsutil, type quit to close the prompt

Restart the computer. AD database and Logs
are moved successfully to new location.

                                                                                                                                                   

Problem

My boss is asking for a list of email addresses and phone numbers for
all users in the company. I know this data exists in Active Directory,
so how can I access this data from SQL Server?  In this tip we walk
through how you can query Active Directory from within SQL Server
Management Studio.

以上方法源自于:#bm231954

问题2

澳门金沙vip 9

Solution

In this tip I’ll show you how to query Active Directory using linked
servers and the OPENQUERY command.

我推荐的方法:在微软搜索到的.如何通过 NTDSUtil为服务器修改限制
maxPageSize

问题3

澳门金沙vip 10

 

To create an application directory partition
named AppPartition in the contoso.com domain, complete the following
steps:

 

1.  
To open an elevated Command Prompt window,
click Start, point to All Programs, click Accessories, right-click
Command Prompt, and then click Run as
administrator.

2.  
Type: ntdsutil

3.  
Type: Ac in ntds

4.  
Type: partition management

5.  
Type: connections

6.  
Type: Connect to server
DC_Name

7.  
Type: quit

8.  
Type: list

 

The following partitions will be
listed:

0
CN=Configuration,DC=Contoso,DC=com

1 DC=Contoso,DC=com

2
CN=Schema,CN=Configuration,DC=Contoso,DC=com

3
DC=DomainDnsZones,DC=Contoso,DC=com

4
DC=ForestDnsZones,DC=Contoso,DC=com

 

9.  
At the partition management prompt, type:
create nc dc=AppPartition,dc=contoso,dc=com
ConDc1.contoso.com

10.
Run the list command again to refresh the
list of partitions.

 

Create Linked Server

First thing we’ll do is create our linked server, Active Directory
Service Interface also known as ASDI, to Active Directory using the code
below:

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\USER',@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

Make sure you change the @rmtuser and @rmtpassword variables to a login
and password that has access to your Active Directory.


1.

问题4

 

To
run GPResult on your own computer:

1.    Click Start,
Run, and enter cmd to open a command window.

2.   Type gpresult
and redirect the output to a text file as shown in Figure 1 below:

澳门金沙vip 11

 Figure 1.
Directing GPResult data to a text file

 

3.   Enter notepad
gp.txt to open the file. Results appear as shown in the figure below.

澳门金沙vip 12

 Figure 2.
Verifying policies with GPResult

 

Administrators
can also direct GPResult to other users and computers.

 

Querying Active Directory

Once the linked server is created we can now setup our query to return
the information we need.

First, you’ll need to ask your Network/Systems Administrator for your
LDAP info then we can continue to the query. 

Here is how the LDAP connection is broken down:

  • For our example it looks like this:
    LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com
  • LDAP://Domain.com – is the name of a domain controller
  • /OU=Players – this is the Organization Unit, in our case (Players)
  • ,DC – this is the Domain Name broken up by domain and extension name
  • So….LDAP://DomainControllerName.com/OU=OrganizationalUnit,DC=DOMAIN,DC=NAME

According to the problem, this user needs to return the companies email
addresses and phone numbers. To do this we can use the code below:

(note – you will need to change your domain information for this to
work)

SELECT * FROM OpenQuery (    ADSI,     'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber    FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''    WHERE objectClass =  ''User''    ') AS tblADSI ORDORDER BY displayname

As you can see this query will return Active Directory’s Display Name,
Telephone Number, Email Address, Mobile Number, and Fax Number. Also
note, that when you query Active Directory it actually creates the
SELECT statement backwards. I started the SELECT statement with SELECT
displayname… but in the results pane it displayed displayName last as
shown below.

澳门金沙vip 13

If you wanted to view more columns for each user we can use the below
code to display fields such as: FirstName, Office, Department, Fax,
Mobile, Email, Login, Telephone, Display Name, Title, Company, Pager,
Street Address, and more.

SELECT * FROM OpenQuery   (    ADSI,     'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName,    mail, mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname    FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''   WHERE objectClass =  ''User''    ') AS tblADSI ORDER BY displayname

澳门金沙vip 14

You can also filter out columns using a WHERE clause. In this example I
only want to return results where users have a fax number.

SELECT * FROM OpenQuery   (    ADSI,      'SELECT streetaddress, pager, company, title, displayName, telephoneNumber, sAMAccountName, mail,     mobile, facsimileTelephoneNumber, department, physicalDeliveryOfficeName, givenname   FROM  ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com''      WHERE objectClass =  ''User''    ') AS tblADSI WHERE facsimileTelephoneNumber IS NOT NULL ORDER BY displayname

澳门金沙vip 15

Click Start, and then click Run.

问题5

1.
Log on to an administrative workstation that has ADSIEdit installed.
ADSIEdit is installed by default on domain controllers that run Windows
Server 2008 or Windows Server 2008 R2. On Windows Server 2003 you must
install the Resource Kit Tools.

 

2.
Click Start, click Run, type ADSIEdit.msc, and then click
OK.

 

3.
Click Action, and then click Connect to.

 

4.
Click Select a well known Naming Context, select Configuration in the
list of available naming contexts, and then click OK.

 

5.
Double-click Configuration, and then double-click
CN=Configuration,DC=forest_root_domain where forest_root_domain is
the distinguished name of your forest root domain.

 

6.
Double-click CN=ForestUpdates.

 

7.
Right-click CN=ActiveDirectoryUpdate, and then click
Properties.

 

澳门金沙vip 16

8.
If you ran adprep /forestprep for Windows Server 2008 R2, confirm that
the Revision attribute value is 5, and then click OK.

澳门金沙vip 17

9.
Click ADSI Edit, click Action, and then click Connect to.

 

10.
Click Select a Well known naming context, select Schema in the list of
available naming contexts, and then click OK.

 

11.
Double-click Schema.

 

12.
Right-click CN=Schema,CN=Configuration,DC=forest_root_domain, and then
click Properties
.

澳门金沙vip 18

13.
If you ran adprep /forestprep for Windows Server 2008 R2, confirm that
the objectVersion attribute value is set to 47, and then click
OK.
 

澳门金沙vip 19

Next Steps

  • To view all the Active Directory attributes click
    here
  • To view how to get Active Directory Users and Groups with SSIS check
    out this
    tip
    from Ray Barley

2.

问题6

This
article contains descriptions of various security-related and auditing-
related events, and tips for interpreting them.

These
events will all appear in the Security event log and will be logged with
a source of “Security.”

 

Event
ID: 529

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: Unknown user name or bad password

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
530

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: Account logon time restriction violation

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
531

       Type: Failure
Audit

Description:
Logon Failure:

            
Reason: Account currently disabled

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
532

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: The specified user account has expired

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
533

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: User not allowed to logon at this computer

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
534

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: The user has not been granted the requested
logon

             type
at this machine

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
535

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: The specified account’s password has
expired

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

 

   Event ID:
536

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: The NetLogon component is not active

             User
Name: %1             
Domain: %2

             Logon
Type: %3             Logon
Process: %4

            
Authentication Package: %5 Workstation Name: %6

 

   Event ID:
537

       Type:
Failure Audit

Description:
Logon Failure:

            
Reason: An unexpected error occurred during logon

             User
Name: %1             
Domain: %2

             Logon Type:
%3            
Logon Process: %4

            
Authentication Package: %5 Workstation Name: %6

In the Open text box, type ntdsutil, and then press ENTER. To
view help at any time, type ? at the command prompt.

问题7

1.
Import-Module ActiveDirectory

2.
import-csv e:\users\newusers.csv |

3.
New-ADUser -path “ou=test1,dc=contoso,dc=com” -passthru
|

4.
ForEach-Object {

5.    $_ |
Set-ADAccountPassword -Reset -NewPassword (ConvertTo-SecureString
-AsPlainText “Pa$$w0rd” -Force)

6.    $_ |
Enable-ADAccount }