Solved

Linked Active Directory problem

Posted on 2006-07-20
14
614 Views
Last Modified: 2008-07-01
Hey experts,

I created a link to our AD server using sp_addlinkedserver, and then created a view of the information I wanted to pull. This part worked fine. Where I'm running into trouble now is I'm trying to create an asp.net website that will pull information from that view, but I get the following error:

An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.

If I'm on the server, I can open the view without any problems, but if I use EM from my local computer to connect to the database, I get the above error if I try to open the view. Using windows integrated security, same login for both computers. SQL Server 2000, AD 2003.

I've been trying to figure out this problem for the last 2 days and I'm out of ideas. Any help is appreciated.

Bryan
0
Comment
Question by:bng0005
  • 6
  • 5
  • 3
14 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17150586
Perhaps you could run Fliemon and Regmon and see if you can identify if its a file/registry permissions issue.
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17164819
Need a linked server with ADSI in it....
Make sure you have the login that has permissions to run the sp ....
Run the properties in sql analyzer first.
0
 
LVL 1

Author Comment

by:bng0005
ID: 17177487
I have the linked server set up, and if I am logged into the server and use EM there, I can run the SP just fine, as well as open and see the view I create for the information. Using the same account login on a different computer and using EM to connect to the server, I get the above error.

I will try FileMnn/RegMon later today and see what I can come up with.
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17179887
i believe you have to have the linked server ADSI set on 2nd server.


0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17179888
whats filemnn regmon..
0
 
LVL 1

Author Comment

by:bng0005
ID: 17180009
filemon and regmon look to be monitoring tools, decent info, but both being new to me didn't help me with the problem.

By the 2nd server comment, do you mean set up asdi on my local machine? If so, it is already showing up under the linked servers in EM
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17181384
Keep those tools in mind. When you are in a real bind and there is no useful error messages it will be worth learning to use them.



It probably doesn't have anything to do with your issue but keep in mind you only have to run sp_addlinkedserver once to add the server definition. You never have to run it again.


Whats happens if you


1. Try it from the remote machine, logged in as the domain administrator (to identify if it is a security issue)
2. In EM on the remote machine, go to the security node and pick out your linked server and try to view the AD catalog in there.


What does your query look like?

Can you run an extremely simple query and work your way up?






0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17186223
I think i know what your doing.


We get all our information form active directory store into a table called ACTIVE DIRECTORY.
We run this each night....

Then i the web wants it we just go into ACTIVEDIRECTORY table that got updated.....

This may be better fo ryou then the Web is not going from IIS to SQL to Active Directory to SQL back again to IIS.....

I try and ask the web guy here what the code is
0
 
LVL 1

Author Comment

by:bng0005
ID: 17187220
Got swamped today with everything breaking so haven't had too much time to spend working on this

nmc, I tried 1, same error as before.

My query is:

SELECT     *
FROM         OPENQUERY(ADSI,
                      'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, department, msExchHideFromAddressLists, mobile, mail, lastLogon, mailNickname, sn FROM ''LDAP://ou=myou,DC=mydc,DC=com'' where objectClass = ''User''')

Tracey,
that would be just as good, and if it worked without too much hassle, even better.

Thanks again for the help so far.


Bryan
0
 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17188789
let me get our dts job for you tomorrow and how we write it to a table..
then all you do is the view....

i asked our web guy for the connection string and the query that is used.....i get that if he pulls his finger out.
0
 
LVL 30

Assisted Solution

by:nmcdermaid
nmcdermaid earned 250 total points
ID: 17189447
>> If I'm on the server, I can open the view without any problems

So if you run a job on the SQL server that transfers that view into a table overnight then you can implement TraceyMary's solution.

The job would just be something like this:



TRUNCATE TABLE YourTable


INSERT INTO YourTable (F1,F2,F3)
SELECT V1,V2,V3
FROM YourView

0
 
LVL 1

Author Comment

by:bng0005
ID: 17190333
that could work, was thinking about just exporting out of AD to a csv file and importing through a job into a table. I'll have to try it tomorrow, thanks for the idea
0
 
LVL 7

Accepted Solution

by:
TRACEYMARY earned 250 total points
ID: 17194826
here u are..the table..
if not exists (select * from dbo.sysobjects where id = object_id(N'[ActiveDirectory2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table ActiveDirectory2 (
distinguishedname varchar(255),
employeeid varchar(8),
sn varchar(255),
middlename varchar(255),
givenname varchar(255),
displayname varchar(255),
samaccountname varchar(255),
mail varchar(255),
cn varchar(255),
telephonenumber varchar(50),
homephone varchar(50),
mobile varchar(50),
FacsimileTelephoneNumber varchar(50),
title varchar(255),
company varchar(255),
department varchar(255),
location varchar(255),
manager varchar(255),
physicalDeliveryOfficeName varchar(255),
streetAddress varchar(255),
st varchar(15),
c varchar(50),
notes varchar(255)
)


script........................(You can create a sp called   XXX) and then just set this up as a job to run each night


TRUNCATE TABLE ActiveDirectory2

DECLARE @MaxChar nvarchar(1)
DECLARE @MinChar nvarchar(1)
DECLARE @SelectNextAD nvarchar(800)
DECLARE @InsertNextAD nvarchar(800)

--Grab first 1000 rows from AD and put into temp table
SELECT *
INTO ##temp_AD
FROM
OpenQuery( ADSI, 'SELECT  
title, company, department, location, manager, physicalDeliveryOfficeName, FacsimileTelephoneNumber, homephone, mobile, streetAddress, st, c, notes,
distinguishedname, employeeid, sn, middlename, givenname, displayname, samaccountname, mail, cn, telephonenumber
 FROM ''LDAP://DC=domain,DC=(MYDC),DC=com''
 WHERE objectCategory = ''Person'' AND objectClass= ''user''
 AND sn >=''a''')

--@MinChar = first initial letter from the group
SELECT
@MinChar =
LEFT(MIN(sn),1)
FROM ##temp_AD

--@MaxChar = last initial letter from the group
SELECT
@MaxChar =
LEFT(MAX(sn),1)
FROM ##temp_AD

--Delete names with the greatest initial letter from the group
DELETE
FROM ##temp_AD
WHERE LEFT(sn,1) = @MaxChar

-- Populate "ActiveDirectory2" table from Temp table
SET @InsertNextAD = '
-- Populate "ActiveDirectory2" table from Temp table
INSERT INTO ActiveDirectory2
(title, company, department, location, manager, physicalDeliveryOfficeName, FacsimileTelephoneNumber, homephone, mobile, streetAddress, st, c, notes,
distinguishedname, employeeid, sn, middlename, givenname, displayname, samaccountname, mail, cn, telephonenumber)  
SELECT
title, company, department, location, manager, physicalDeliveryOfficeName, FacsimileTelephoneNumber, homephone, mobile, streetAddress, st, c, notes,
distinguishedname, employeeid, sn, middlename, givenname, displayname, samaccountname, mail, cn, telephonenumber
FROM ##temp_AD

--Clear Temp Table
TRUNCATE TABLE ##temp_AD
'
EXEC(@InsertNextAD)


-- Loop: Begin while @MinChar <> Z
WHILE (@MinChar <> 'Z')
BEGIN
      --Grab next 1000 rows from AD and put into temp table
      SET @SelectNextAD = '
      --Grab next 1000 rows from AD and put into temp table
      INSERT INTO ##temp_AD
      SELECT *
      FROM
      OpenQuery( ADSI, ''SELECT  
      title, company, department, location, manager, physicalDeliveryOfficeName, FacsimileTelephoneNumber, homephone, mobile, streetAddress, st, c, notes,
      distinguishedname, employeeid, sn, middlename, givenname, displayname, samaccountname, mail, cn, telephonenumber
       FROM ''''LDAP://DC=domain,DC=MYDC,DC=com''''
       WHERE objectCategory = ''''Person'''' AND objectClass= ''''user''''
       AND sn >= '''''+@MaxChar+''''' '')'
      
      EXEC (@SelectNextAD)
      
      --@MinChar = first initial letter from the group
      SELECT
      @MinChar =
      LEFT(MIN(sn),1)
      FROM ##temp_AD
      
      --@MaxChar = last initial letter from the group
      SELECT
      @MaxChar =
      LEFT(MAX(sn),1)
      FROM ##temp_AD
      
      --Delete names with the greatest letter from the group
      DELETE
      FROM ##temp_AD
      WHERE LEFT(sn,1) = @MaxChar
      
      EXEC(@InsertNextAD)

END
--Loop: End

EXEC (@SelectNextAD)
EXEC(@InsertNextAD)

GO




Change        FROM ''''LDAP://DC=domain,DC=MYDC,DC=com''''
to you rname for the DC


Then you have a table built of the active directory........then
create your sp...

CREATE PROCEDURE sp_Get_Active_Directory_Info

@Username nvarchar(100)

AS

select employeeid
from ACTIVEDIRECTORY2 where
samaccountname = @Username
GO


Oh there is a madness in the script above because of the limitation of returning 1000 rows and we have more than 1000.

Give it a whirl.....(if you get it working with asp straight to Active directory let me know also) but ...
this way its quicker on the web pages.
0
 
LVL 1

Author Comment

by:bng0005
ID: 17195480
Using the stored procedure to insert into a table worked. Thanks a ton to both of you for the help.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

18 Experts available now in Live!

Get 1:1 Help Now