Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Accessing AD from SQL Server 2005 on Windows server 2003 Expand / Collapse
Author
Message
Posted Thursday, July 9, 2009 8:02 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 405, Visits: 1,140
Hi hopefully someone can help me with this as well.

as above, I'm trying to query my LDAP server.

This is what I have setup:

I have access to our Active Directory and can view users and machines in active directory

- SQL Server 2005 Develper Edition installed on my machine.

- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service

- OpenQuery is enabled
(I can run open query commands to other systems)

- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')

where the domain is MyDOMAIN.NET.

I get the error:


Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".



What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??

Sorry - I know this has been covered several times, but I' struggling to see what the issue is!


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #750253
Posted Thursday, July 9, 2009 8:04 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:23 PM
Points: 405, Visits: 1,140
Hi hopefully someone can help me with this as well.

as above, I'm trying to query my LDAP server.

This is what I have setup:

I have access to our Active Directory and can view users and machines in active directory

- SQL Server 2005 Develper Edition installed on my machine.

- All services started.
SQL Server, SQL Server Agent, SSAS, SQL Server Browser, SQL Full Text Search & SQL Server VSS Writer all started as Local System
SSIS is started as Network Service

- OpenQuery is enabled
(I can run open query commands to other systems)

- I have defined a AD linked server as follows:
EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

- Having configure the linked server I have run the following query:
select * from openquery
(ADSI,'SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" ')

where the domain is MyDOMAIN.NET.

I get the error:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT cn, mail, co, distinguishedName, displayName
FROM "LDAP://DC=MyDOMAIN,DC=net" where objectClass = "User" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".


What is: MSSQLServerADHelper - should this be started??
How do I know if SQL Server is operating in Kerboros mode - where do I change it?
Have I missed anything else??

Sorry - I know this has been covered several times, but I' struggling to see what the issue is!


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #750254
Posted Wednesday, August 5, 2009 2:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 4:50 AM
Points: 5, Visits: 232
.
Post #765296
Posted Wednesday, August 5, 2009 2:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 4:50 AM
Points: 5, Visits: 232
Correction to above post...

I had your issue, and managed a temp fix as below (at least it will work)....

In SSMS, browse to your linked server: ADSI.

Right-click ADSI, select properties, click "security" on left.
Change security from "Be made using login's current security context"
to-> "Be made using this security context"

Use a domain account (of same domain you are querying),
supply the password- which you would need to know.

UserName format: DOMAIN_NAME\USERNAME

Click OK.

Run queries supplied in above posts..

NOTE: this is not the proper solution, this will just highlight the fact that if this change works it means:
1) the security context under which SQL runs does not have permission to connect to the domain controller
2) you need to change SQL server service to use an account which does have permission to the domain controller
3) once option 2 is applied, change security back to "Be made using login's current security context"

Best of luck!
Hope you resolve the issue.
Post #765297
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse