November 10, 2016 at 11:32 am
I have combed the forums for an answer to this but cannot find one. I have written an OPENQUERY statement which works perfectly on my local SQL Server installation on my computer. First I create the linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADsDSOObject', @datasrc=N'adsdatasource', @provstr=N'ASDSOObject'
Then I use my admin account (ADSI security is to use the logins security context) to run this query (changed the OU and DC names for this post):
SELECT * FROM OPENQUERY
(ADSI, 'SELECT ADsPath
FROM ''LDAP://OU=ClientGroup,DC=accounts,DC=top,DC=second,DC=third''
WHERE cn = ''*Workstation-Client*''
')
15 rows are returned. Cool.
Then I add a linked server using same SP above on our development server using the same admin account, and test the connection. All OK still.
Then I run the exact same query on the development server with no changes and get the error: "Cannot execute the query "<entire SELECT query is here>" against the OLE DB provider "ADsDSOObject" for linked server "ADSI".
I change the ADSI security context to the fourth option to specify my admin account and password (in case there was a Kerberos double hop issue) and get this error:
"Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI"."
Notes: The servers are locked down so that OPENROWSET is not allowed, only OPENQUERY is allowed, so I cannot do any OPENROWSET operations.
Only 15 rows are returned so I am not running into the 1000 row limit.
Since I am able to access the domain and successfully return records from my PC on the domain, I figure the only thing could be some sort of policy or limitation on the Servers themselves.
I do not have sysadmin on the SQL Servers in which the query fails, but do on my local PC in which the query succeeds. However, another member of my team DOES have sysadmin and runs into the same errors as me.
Any ideas?
November 10, 2016 at 12:44 pm
any differences in the sql server's service account itself? just curious, I've only used the ldap queries a few times and they worked from the get go but was curious whether they ultimately used the logged in person's credentials or maybe instead used the service account's credentials.
November 10, 2016 at 1:14 pm
An excellent thought. I thought maybe the Service account on my local installation may have been different but alas, the service accounts for both my local installation and all the servers are the same domain account.
November 11, 2016 at 11:20 am
SunglassesAtNight (11/10/2016)
An excellent thought. I thought maybe the Service account on my local installation may have been different but alas, the service accounts for both my local installation and all the servers are the same domain account.
That domain account would have to have the right to query AD via LDAP. That's an AD permissions thing. You may not have been running the query under your service account on your machine, but may HAVE to run it that way on the server. Try running it as a SQL Agent Job on your local instance, and be sure that your SQL Agent Service runs under that same domain account. If you get the same failure, then it's probably that domain account having insufficient AD permissions to run an LDAP query.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 11, 2016 at 12:03 pm
sgmunson (11/11/2016)
SunglassesAtNight (11/10/2016)
An excellent thought. I thought maybe the Service account on my local installation may have been different but alas, the service accounts for both my local installation and all the servers are the same domain account.That domain account would have to have the right to query AD via LDAP. That's an AD permissions thing. You may not have been running the query under your service account on your machine, but may HAVE to run it that way on the server. Try running it as a SQL Agent Job on your local instance, and be sure that your SQL Agent Service runs under that same domain account. If you get the same failure, then it's probably that domain account having insufficient AD permissions to run an LDAP query.
If you do encounter the situation that the AD login doesn't have permission to query AD, your domain admin may likely be of help. If you explain what you're trying to do, they may be able to grant the proper permissions. As for any bureaucracy associated with getting the permissions, you'll just have to endure whatever your organization has in place.
November 11, 2016 at 12:23 pm
also check networking:
microsoft ldap port 389
I run into this sort of networking often, with today's vlans and other segmented mess, any given route in your organization can block a port, or broadcasts might not make it across a subnet, etc etc, one machine works, another one on a different vlan might not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply