Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Adding Users to Active Directory from a SQL Server 2005 table

Posted on 2009-05-11
9
Medium Priority
?
1,461 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
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

 
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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

In the absence of a fully-fledged GPO Management product like AGPM, the script in this article will provide you with a simple way to watch the domain (or a select OU) for GPOs changes and automatically take backups when policies are added, removed o…
It’s time for spooky stories and consuming way too much sugar, including the many treats we’ve whipped for you in the world of tech. Check it out!
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

610 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