April 13, 2008 at 1:35 am
Dear Friends,
select name As Name, sAMAccountName As UserName, title As Title,
physicalDeliveryOfficeName As Office, telephoneNumber As PhNumber,
mobile As CellPhone, facsimileTelephoneNumber As FaxNumber, mail As Email,employeeid as emp_no from openquery
(
ADSI,'SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM ''LDAP://fp1-srvr.sidf.gov.sa''
WHERE objectCategory = ''Person''AND objectClass = ''user''
')
on sql server 2000 it was working but when migration to sql server2005
I got the following error when trying to excute on query analyzer:
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT name, sAMAccountName, title, physicalDeliveryOfficeName, mobile, facsimileTelephoneNumber, telephonenumber, mail ,employeeid
FROM 'LDAP://fp1-srvr.sidf.gov.sa'
WHERE objectCategory = 'Person'AND objectClass = 'user'
" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
Could you please help me in that
April 14, 2008 at 5:16 am
obviously you need to have setup the provider first and with various settings
below script from SSMS on my DEV box - hope it helps you setup your environment
- change the user+password security details first !
Dick
use master
go
/****** Object: LinkedServer [ADSI] Script Date: 04/14/2008 12:09:52 ******/
EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
EXEC sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
EXEC sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
EXEC sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
EXEC sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true'
April 14, 2008 at 6:31 am
Thanks for your reply.
But I already have the provider script and it's there as ADSI linked server still the problem there.
script for creation:
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Services 2.5',
@provider=N'ADsDSOObject', @datasrc=N'fp1-srvr.sidf.gov.sa'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation', @optvalue=N'true
still getting same problem
April 14, 2008 at 7:35 am
If you have the SQL configs correct, then it is looking more like a security issue with the credentials you [or middleware] are presenting to AD. perhaps relates to what the SQLservice is running as [eg LocalSystem], although should be whatever you state in the security tab (properties on ADSI context menu).
I suggest you try some simple Windows scripting host [which won't go through SQL] just to help diagnose the issue. Have a look at these URLs
http://technet.microsoft.com/en-gb/library/bb742578.aspx
and be sure to use some priv account [or get some empowered IT person to do it for you] to be sure no access barriers.
You could also try ADSIEDIT (from your PC and/or the db-server) which is part of the Windows Support Tools
Dick
April 17, 2008 at 8:38 am
Dear Friends
I finaly found the solution :
The one that seems to trip up most users is
that the service account used by the sql agent service has to be a member of
the DatabaseMailUserRole in the MSDB database
this the silte:
Thanks for your help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply