Connecting/Accessing to MS Exchange

Question

Post reply

Connecting/Accessing to MS Exchange

dick_rider

SSC Enthusiast

Points: 101
More actions
November 4, 2003 at 8:00 am

#64692

Thanks in advance for your responses.
I am attempting to retrieve info MS Exchange.
I have created a Linked Server on my local box:
EXEC
sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
I can see the linked server from the SQL EM tree
I have created a linked server login on my local box where "theuseid"/"thepassword" is a valid usercode/password to login to server Comp0005 which house MS Exchange:
EXEC sp_addlinkedsrvlogin 'ADSI', 'false', NULL, 'theuserid', 'thepassword'
I have attempted a simple query from my local box to server Comp0005 which houses MS Exchange who's Console Root looks like:
+Active Directory Users and Computers [Comp0001.CompanyUSA.com]
+ Dallas
The following SQL:
CREATE VIEW viewADContacts
AS
SELECT *
FROM OPENQUERY
(
ADSI, 'SELECT Name FROM ''LDAP://Comp0005/ OU=Dallas,DC=Comp0001,DC=CompanyUSA,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''contact'''
)
GO
returned the following error message:
Server: Msg 7321, Level 16, State 2, Procedure viewADContacts, Line 3
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].
I have attempted various select combination in the OPENQUERY (no OU, different OU, no WHERE Clause) to no avail.
Any ideas/suggestions?
Thanks
Dick
dick rider

dick rider

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

are you using Windows Authentication?

1- You should try SQL Authentication to connect to the SQL box.

2- Map the Sql User To the Account you authorized on the Linked Server Proprties

and it should work

* Noel

dick_rider SSC Enthusiast Points: 101 More actions · Answer 2

Thanks for the response.

Yes I am using Win Auth.

SQL is not installed on box Comp0005 - Should it be installed and if not, where do I map the SQL acccount?

Thanks again

dick rider

noeld SSC Guru Points: 96590 More actions · Answer 3

You don't need to have SQL on the Comp0005 box.

If you are using Win Auth. Make sure the SQL server Account has the "Account can be trusted for delegation" checked on your AD.

if you plan to use SQL authentication the linkserver Security properties on EM will have the mappings you are looking for

Does that makes sense?

* Noel

noeld SSC Guru Points: 96590 More actions · Answer 4

I look closer to your query and you should

replace '' for " like in:

quote:
SELECT *
FROM OPENQUERY
(
ADSI, 'SELECT Name FROM ''LDAP://Comp0005/ OU=Dallas,DC=Comp0001,DC=CompanyUSA,DC=com'' WHERE objectCategory = ''Person'' AND objectClass = ''contact''')

SELECT *

FROM OPENQUERY

( 'SELECT Name FROM "LDAP://Comp0005/ OU=Dallas,DC=Comp0001,DC=CompanyUSA,DC=com" WHERE objectCategory = "Person" AND objectClass = "contact"')

* Noel

dick_rider SSC Enthusiast Points: 101 More actions · Answer 5

Noel, thanks for the reply

Still no go:

I made the quotes around LDAP... DC=COM and Person and Contact a single character double quote and get the same error message if they are enclosed in two single quotes

I removed the sp_addlinkedsrvlogin thinking that would insure that I was using Win Auth - didn't help

Both box my box and Comp0005 have the "Trust Computer for delegation" check in the Exchange AD tree (under the server or computer in the General tab)

The account that starts SQL server on my box (which is the account that I am logged in as) has full admin privileges. Wasn't quite sure what you meant by "Make sure the SQL sever Account has the Account can be trusted fro delegation check on you AD" I and/or Admin guy can't find that.

Any suggestions how I can trace this either on my box or Comp0005?

Thanks again

dick rider

dick_rider SSC Enthusiast Points: 101 More actions · Answer 6

noel,

after playing with a VB applet, I got and ADO connection to retrieve info from exchange via LDAP.

I moved the syntax to SQL

CREATE VIEW viewADContacts

AS

SELECT

[Name]

FROM

OPENQUERY

( ADSI, '<LDAP://ou=demo,dc=CompanyUSA,dc=com>;;name;onelevel'

)

and it retrieved data, soooo now I can hack away at the finer points.

Thanks for your help.

dick rider

noeld SSC Guru Points: 96590 More actions · Answer 7

I am glad you solved the problem even if I still could not figure out why it wasn't working?

* Noel