Solved

Adding Users to Active Directory from a SQL Server 2005 table

Posted on 2009-05-11
9
1,444 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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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 from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now