Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Dec 02, 2007 04:19 AM by b471code3
May 28, 2004 08:45 AM|LINK
May 29, 2004 09:03 AM|LINK
Nov 07, 2006 11:31 PM|LINK
I have a similar problem -- and I don't.
I have created:
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:
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.
SQL Server 2005
Microsoft Server 2003
Microsoft SQL Server 2005
Nov 21, 2006 02:50 PM|LINK
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.
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
Microsoft SQL Server 2005
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
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.