Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Adding Users to Active Directory from a SQL Server 2005 table

Posted on 2009-05-11
9
Medium Priority
?
1,464 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Here's a look at newsworthy articles and community happenings during the last month.
Wouldn't it be nice if objects in Active Directory automatically moved into the correct Organizational Units? This is what AutoAD aims to do and as a plus, it automatically creates Sites, Subnets, and Organizational Units.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

581 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