I want to create a view in SQL populated with users from our Active Directory. I have learnt that this can be done using linked server. I have tried using the following: sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
go sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'false', @locallogin = 'sa', @rmtuser = 'lok_applications', @rmtpassword = '9dfFfG374GoiAo6yxxc8oZ' SELECT * FROM OpenQuery( ADSI, 'SELECT * FROM "LDAP://194.22.1.18/DC=lok,DC=net"') I keep getting this
error no matter what I try: An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'. OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14]. Any ideas why??
2 SQLExpress installations on two different machines:
A Windows XP Pro machine (within my domain)
A Windows Server 2003 machine (in the same domain)
The installations are identical (apart form the OS that they're operating on) and both use Windows Integrated authentication.
In both servers, I've creaded a linked server to Active Directory and I've called the linked servers, "ADSI" (Again...identical)
On machine "A" (the XP Pro machine) I have installed SQL Server Management Studio Express.
I can open SSMSE (the management studio express) and connect to running instance of SQLExpress on the WinXP machine I can successfully execute the following query:
SELECT displayName FROM OPENQUERY( ADSI, 'SELECT displayName FROM ''LDAP://DC=myDomainName,DC=mySuffix'' WHERE objectCategory = ''Person'' AND objectClass = ''User'' AND sAMAccountName = ''my.Username''')
Note: The query above also works with this syntax:
But I've decided to take Microsoft's advice and not bind to a specific domain controller -- hence the earlier syntax.
Here's the problem: When I connect to the running instance of SQLExpress on the Server2003 machine, the query fails with "An error occured while preparing the query..." etc.
I have tried to compare the configuration which works and the configuration which does not work and the only two differences I can see are these:
Obviously, the operating systems are different. I'm logged in to both machines under the same domain account.
And the WinXP Pro machine belongs to a different organizational unit than the Server2003 machine (one is "Computers_Staff" and another is "Computers_Servers". However, both of them belong to the "Computers" unit.
I've looked through policies of those two different organizational units and can't see any obvious difference which would cause one to work and one NOT to work.
So... the problem boils down to the fact that I can't operate the above query through my ADSI linked server in SQLExpress. But the reason I've talked about the both of my installations is simply to show that my query is appropriate and does in fact work
through query analyzer with our Active Directory store -- but the same darn thing doesn't work in both settings -- so I'm stumped.
Any ideas?
SQL Server 2005SQL PermissionsConnnection StringMicrosoft Server 2003Microsoft SQL Server 2005Connection String
To follow-up on my previous post, I've discovered a solution and have a hint as to the problem I was experiencing.
The problem I was experiencing is summarized above in this sentence: "When I connect to the running instance of SQLExpress on the Server2003 machine, the query fails with 'An error occured while preparing the query...' etc." This problem was frustrating
because I was able to connect successfully on ONE of two duplicate machines (why did it work on one machine and not on the other?)
The solution was simple: While on my local XPPro machine, I can connect through the ADSI linked server to read information from Active Directory. And while on the Server2003 (if I log on locally and use SSMESE on that machine as a local user) then I can
connect successfully and read info from Active Directory. But if I'm running SSMESE on my WinXP machine, and connect (via network) to the SQLExpress instance on the Server2003 machine, then I CANNOT connect through the linked server to Active Directory information.
Like this:
Me > open SSMESE on WinXP machine > connect to SQLExpress on Server2003 machine > run an OpenQuery which uses the ADSI linked server = ERROR.
Me > open SSMESE on WInXP machine > connect to SQLExpress on WinXP machine > run an OpenQuery which uses the ADSI linked server = It WORKS!
Me > open SSMESE on Server2003 machine > connect to SQLExpress on same machine > run a query = It WORKS!
The problem then appears to be a network issue and perhaps rooted in the way SSMESE impersonates (or doesn't) the user on each machine. I resolved this problem when I discovered that the ADSI linked server on the Server2003 machine was in fact working just
fine -- even though errors would occur if I was connected to it via my local WinXP machine.
Geez, I hope that all makes sense.!?
Remote Connection Enabled Sql 2005SQL Server 2005SQL PermissionsConnnection StringMicrosoft SQL Server 2005SQL
This was quite an old post. But I'll just confirm that the issue is probably due to the service account that MSSQLSERVER service runs under needs to have domain access to query ADSI. Use a domain account to run the service and you should be fine, see info
here: http://www.codeproject.com/aspnet/LikeDataStores.asp
Is this true for any service which has the (MSSQLSERVER) after it? As of right now I only have the top one (SQL SERVER) running on a domain access account.
Remember to mark as answer if this post answered or solved your problem.
abjork
Member
110 Points
22 Posts
Using Linked Server to query Active Directory from SQL??
May 28, 2004 08:45 AM|LINK
pkr
Star
9197 Points
1860 Posts
Re: Using Linked Server to query Active Directory from SQL??
May 29, 2004 09:03 AM|LINK
David.Sabine
Member
30 Points
6 Posts
Re: Using Linked Server to query Active Directory from SQL??
Nov 07, 2006 11:31 PM|LINK
I have a similar problem -- and I don't.
I have created:
- A Windows XP Pro machine (within my domain)
- A Windows Server 2003 machine (in the same domain)
The installations are identical (apart form the OS that they're operating on) and both use Windows Integrated authentication.I can open SSMSE (the management studio express) and connect to running instance of SQLExpress on the WinXP machine I can successfully execute the following query:
Note: The query above also works with this syntax:
But I've decided to take Microsoft's advice and not bind to a specific domain controller -- hence the earlier syntax.
Here's the problem: When I connect to the running instance of SQLExpress on the Server2003 machine, the query fails with "An error occured while preparing the query..." etc.
I have tried to compare the configuration which works and the configuration which does not work and the only two differences I can see are these:
I've looked through policies of those two different organizational units and can't see any obvious difference which would cause one to work and one NOT to work.
So... the problem boils down to the fact that I can't operate the above query through my ADSI linked server in SQLExpress. But the reason I've talked about the both of my installations is simply to show that my query is appropriate and does in fact work through query analyzer with our Active Directory store -- but the same darn thing doesn't work in both settings -- so I'm stumped.
Any ideas?
SQL Server 2005 SQL Permissions Connnection String Microsoft Server 2003 Microsoft SQL Server 2005 Connection String
David.Sabine
Member
30 Points
6 Posts
Re: Using Linked Server to query Active Directory from SQL??
Nov 21, 2006 02:50 PM|LINK
Hello All,
To follow-up on my previous post, I've discovered a solution and have a hint as to the problem I was experiencing.
The problem I was experiencing is summarized above in this sentence: "When I connect to the running instance of SQLExpress on the Server2003 machine, the query fails with 'An error occured while preparing the query...' etc." This problem was frustrating because I was able to connect successfully on ONE of two duplicate machines (why did it work on one machine and not on the other?)
The solution was simple: While on my local XPPro machine, I can connect through the ADSI linked server to read information from Active Directory. And while on the Server2003 (if I log on locally and use SSMESE on that machine as a local user) then I can connect successfully and read info from Active Directory. But if I'm running SSMESE on my WinXP machine, and connect (via network) to the SQLExpress instance on the Server2003 machine, then I CANNOT connect through the linked server to Active Directory information.
Like this:
Me > open SSMESE on WinXP machine > connect to SQLExpress on Server2003 machine > run an OpenQuery which uses the ADSI linked server = ERROR.
Me > open SSMESE on WInXP machine > connect to SQLExpress on WinXP machine > run an OpenQuery which uses the ADSI linked server = It WORKS!
Me > open SSMESE on Server2003 machine > connect to SQLExpress on same machine > run a query = It WORKS!
The problem then appears to be a network issue and perhaps rooted in the way SSMESE impersonates (or doesn't) the user on each machine. I resolved this problem when I discovered that the ADSI linked server on the Server2003 machine was in fact working just fine -- even though errors would occur if I was connected to it via my local WinXP machine.
Geez, I hope that all makes sense.!?
Remote Connection Enabled Sql 2005 SQL Server 2005 SQL Permissions Connnection String Microsoft SQL Server 2005 SQL
FrodeD
Member
6 Points
16 Posts
Re: Using Linked Server to query Active Directory from SQL??
Oct 25, 2007 11:19 AM|LINK
This was quite an old post. But I'll just confirm that the issue is probably due to the service account that MSSQLSERVER service runs under needs to have domain access to query ADSI. Use a domain account to run the service and you should be fine, see info here: http://www.codeproject.com/aspnet/LikeDataStores.asp
b471code3
Star
13877 Points
2598 Posts
Re: Using Linked Server to query Active Directory from SQL??
Dec 02, 2007 04:19 AM|LINK
Is this true for any service which has the (MSSQLSERVER) after it? As of right now I only have the top one (SQL SERVER) running on a domain access account.