Solved

Adding Users to Active Directory from a SQL Server 2005 table

Posted on 2009-05-11
9
1,454 Views
Last Modified: 2012-05-06
Hi,

I need to add some users to Active Directory (Windows 20003 R2 SP2) from a table in SQL Server 2005.  I have done this before by creating a linked server but that was using Enterprise Manager in SQL 2000.  How do I do this in SQL Server 2005 Management Studio? (details please)

Thanks
dT
0
Comment
Question by:dtripp7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24361671
0
 
LVL 3

Author Comment

by:dtripp7
ID: 24363686

Thanks for the links - I was able to create a linked server (ADSI) using the third link above.  It tests successfully.  The link also provides a query to select from the Active Directory but it gives me an error:
---------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM OPENQUERY (ADSI,
'SELECT givenName, sn FROM LDAP:// DC=yourcompany,DC=com'

---------------------------------------------------------------------------------------------------------------------------
I get the following error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'SELECT givenName,sn FROM LDAP://DC=litwareinc,DC=com'.

---------------------------------------------------------------------------------------------------------------------------
I created a query based on this example (added a close ')' and an ou for Users):

SELECT *
FROM OPENQUERY(ADSI,
'SELECT sn FROM LDAP://ou=Users,DC=litwareinc,DC=com')

---------------------------------------------------------------------------------------------------------------------------
I get a different error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT sn FROM LDAP://ou=Users,DC=litwareinc,DC=com" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
---------------------------------------------------------------------------------------------------------------------------
I also need to add users to Active Directory per my original question - this method states that it cannot insert or update (see below).  So I also need an answer on how I can add users to Active Directory using a linked server or some other method.  

Thanks
dT
---------------------------------------------------------------------------------------------------------------------------
From the third link stating that Insert, Update or Delete queries cannot be used:

Querying Active Directory
The usual four-part naming convention used with linked servers to databases (for example, OTHERSERV.MyDatabase.dbo.Employees) can not be used. Instead, write a pass-through query using the OPENQUERY function. In addition, only SELECT statements can be sent; INSERT, UPDATE, and DELETE statements are not allowed through ADSI.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24363738
I guess, there is some permission issue.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 3

Author Comment

by:dtripp7
ID: 24363818
Thanks for the quick response - I have logged into SQL Management Studio as the Administrator using Windows Authentication and as 'sa' using SQL authentication.  I get the same error either way.  I am logged onto the server as the Administrator.

Can you give me some permission areas I should check?

thanks
dT
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24363838
right problem may be between your SQL Server and ADSI communication. may be AD is not able to recognize your SQL Server's user. try impersonating in linked server property by giving powerful userid/pass of AD there
0
 
LVL 3

Author Comment

by:dtripp7
ID: 24364112
I have tried several combinations in the Security tab of ADSI.  (In all cases, the radio button "Not be made" is checked):

-----------------------------------------
Local login: Administrator
Impersonate: checked
Remote: sa
Password: pass@word1

-----------------------------------------
Local login: sa
Impersonate: checked
Remote: Administrator
Password: pass@word1

-----------------------------------------
Local login: LITWAREINC\Administrator
Impersonate: checked
Remote: sa
Password: pass@word1

-----------------------------------------
Local login: LITWAREINC\Administrator
Impersonate: checked
Remote: sa
Password: pass@word1

They connect (at least I do not get a connection error when clicking OK) but the query still gves my the same error.  
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
ID: 24380004
The SA user id is a SQL Server user id and not a domain user.

When you use the SA on the SQL Server -- it has the permissions granted by the Log On As userid seen in the Services.msc. So if your SQL Server is running as the LITWAREINC\DatabaseMgr id who is a plain Domain User -- that is what your privileges to the domain will be.

Have you tried flipping the  
Local login: sa
Impersonate: checked
Remote: LITWAREINC\Administrator
Password: AdminP@ssw0rd

0
 

Expert Comment

by:dtripp2000
ID: 24415622
Thanks - this post helped me solve the problem.

Dave
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 24415747
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the method of using the Resultant Set of Policy Tool to locate Group Policy that applies a particular setting.
Group policies can be applied selectively to specific devices with the help of groups. Utilising this, it is possible to phase-in group policies, over a period of time, by randomly adding non-members user or computers at a set interval, to a group f…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question