User on trusted domain does have permission to access linked server on AD deployed in another domain

_{Friday, September 28, 2007 9:05 PM}

Hi,

We have the  followoing:

-A "master domain" AD, a  "sub domain" AD, a trust relationship between the two (sub trust master)

-A sql server 2005 on a win server 2003 in "sub domain" AD

-A linked server to "sub domain" AD

-A linked server login using  a "sub domain" admin acccount

-A view to this linked server

-A grant on masterDomain/Domain Users to the database  

-A grant on subDomain/Domain Users to the database   

-We want all connections done through "Windows Authentication" not "Database Authentication".

 

Queries on the view work fine using "sub domain" user accounts.

Queries on the view fail using "master domain" user accounts (including master domain admin accounts)

"Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation."

 

All connections are done through "Windows Authentication" not "Database Authentication".

 

Can we establish cross domain connectivity with "Windows Authentication" ?

 

Below are details of the implementation:  

SELECT     TOP (100) PERCENT *
FROM         OPENQUERY(ADSI,
                      'SELECT displayname, givenName, sn, cn (etc...)
    FROM ''LDAP://OU=PEOPLE,DC=subDomain,DC=com''
    WHERE objectCategory = ''Person'' AND objectClass = ''user'' ')

EXEC sp_addlinkedsrvlogin @rmtsrvname ='ADSI', @useself='false',

@rmtuser='subDomainAdminAccnt', @rmtpassword='sunDomainAdminAccntPassword';

In SQL Server Mngt Studio in Server Objects/Linked Servers/Providers/ ADSI properties security tab I have:

"connections will: <be made using this security context> Remote login:'subDomainAdminAccnt' With password: 'subDomainAdminAccntPassword'

Error:

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "SELECT displayname, givenName, sn, cn

FROM 'LDAP://OU=PEOPLE,DC=subDomain,DC=com'

WHERE

objectCategory = 'Person'

AND objectClass = 'user'

" against OLE DB provider "ADsDSOObject" for linked server "ADSI".

_{Thursday, October 4, 2007 1:50 PM ✅Answered}

Verify if you have access on this registre key: "HKEY_CLASSES_ROOT\CLSID\

Use the tool "Process monitor" to verify where your windows are denied access.

make a download and execute it when you run the SQL setup:

http://www.microsoft.com/technet/sysinternals/utilities/processmonitor.mspx

if you find where to give access, then, allow access for your user and try to install again.

_{Saturday, September 29, 2007 5:22 AM}

The subdomain might trust the master, but does a domain user in the master domain have the authority to interrogate the PEOPLE OU in the subdomain?

_{Monday, October 1, 2007 2:54 PM}

Hi Michael,

in our policies, a MASTERDOMAIN/domainAdminAccount has that authority to interrogate the sub domain. However, when we tested it, we received that same error!!!

 

Anyway, the linked server definition tells SQL to request AD using the option "Be made using this security context" with a SUBDOMAIN/domainAdminAccount and no other "local server login" are defined.

 

Hence, I would expect SUBDOMAIN/domainAdminAccount to always be the one who access AD and not any other account.

 

 

Any idea?

_{Tuesday, October 2, 2007 2:21 PM}

Hi, try this whay:

On the remote sql server create a local group with security context, add the global users group from other domain what contain your users to local group you created,then on sql server create a login with this local group and configure the apropriate permission on sql server, then try to use a linked server.

_{Thursday, October 4, 2007 12:39 PM}

Hi,

we already have such a setup with some of our administration accounts but I tried it again just to make sure and we still get that same permission denied!!!

 

Thanks for your support.

_{Friday, February 4, 2011 7:34 PM}

Hello,

I was recently experiencing this same issue for a Cross-Forest Two-Way Trust and the solution was to create a Local Domain User Account in the other Domain and use a Linked Server specifically for Non-Local Domains.

First, SQL Server 2005 is more restrictive from a Security Perspective. So, on the Security page of the Linked Server Properties, you must Select the 'Be made using the security context:' Radio button and enter a Domain User Account in one of the following forms ADSIUSER@DOMAIN.COM or DOMAIN\ADSIUSER into the 'Remote Login:' field, and the password for the User into the 'With password:' field.

I.E. Local Domain is LOCAL.COM and the user account specified in the Linked Server is: LOCAL\ADSILOC, the External Domain to query via ADSI is TEST.COM. I received the following:

'Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "ADSDSOObject" for linked server "ADSILOC" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "%ADSIQUERY%" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSILOC".  for this Linked Server.'

When I changed the Credentials to TEST\ADSITST, the Linked Server was able to query ADSI.

SQL Server does not seem to pass permissions cross-domain well, using a Local Domain User Account for each Forest we connect to has resolved our issue.