Adding Users to Active Directory from a SQL Server 2005 table

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
LVL 3
dtripp7Asked:
Who is Participating?
 
Jim P.Commented:
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
 
RiteshShahCommented:
0
 
dtripp7Author Commented:

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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
RiteshShahCommented:
I guess, there is some permission issue.
0
 
dtripp7Author Commented:
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
 
RiteshShahCommented:
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
 
dtripp7Author Commented:
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
 
dtripp2000Commented:
Thanks - this post helped me solve the problem.

Dave
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.