Solved

Linked Active Directory problem

Posted on 2006-07-20
14
620 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

828 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