bng0005
asked on
Linked Active Directory problem
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
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
Perhaps you could run Fliemon and Regmon and see if you can identify if its a file/registry permissions issue.
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.
Make sure you have the login that has permissions to run the sp ....
Run the properties in sql analyzer first.
ASKER
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.
I will try FileMnn/RegMon later today and see what I can come up with.
i believe you have to have the linked server ADSI set on 2nd server.
whats filemnn regmon..
ASKER
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
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
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?
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?
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
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
ASKER
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
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
nmc, I tried 1, same error as before.
My query is:
SELECT *
FROM OPENQUERY(ADSI,
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, department, msExchHideFromAddressLists
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
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Using the stored procedure to insert into a table worked. Thanks a ton to both of you for the help.