| Author |
Topic |
|
Gopher
Yak Posting Veteran
83 Posts |
 Posted - 2008-05-19 : 07:19:19
|
| Hi AllI am trying to run a query against our 'Active Directory' and I keep getting errors. I have done the following:1. Set up Linked Server (using the OLE DB For Directory Services)2. Created a connection inf SSIS3. In My SSIS Package I have created a dataflow select the AD connection 'ADSI'. 4. Tried to run the following query:SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,department, title, sn, givenName, name, employeeNumber FROM''LDAP://OU=Main_Users, DC=DemoAD,DC=com'' WHERE objectCategory = ''Person'' AND objectClass= ''user''And it fails - I can get this working in SQL 2000 DTS by using an Open Query statement!Am I missing something?Thanks Gopher |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-19 : 07:20:44
|
What is the error message?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2008-05-19 : 07:24:20
|
quote:
Originally posted by Peso
What is the error message?
E 12°55'05.25"N 56°04'39.16"
TITLE: Microsoft Visual Studio------------------------------Error at Export From AD [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.------------------------------ADDITIONAL INFORMATION:Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------BUTTONS:OK------------------------------Does this help? It appears when I test the query! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-19 : 23:09:55
|
| Does it work in ssms query windows? Tried with openquery in ssis? |
|
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2008-05-20 : 02:55:32
|
| The error I get in SSMS is:Msg 7321, Level 16, State 2, Line 1An error occurred while preparing the query "SELECT samaccountname, adspath, sapUsername, mail, info, costCenter, pager, mobile, facsimileTelephoneNumber, telephoneNumber, company,department, title, sn, givenName, name, employeeNumber FROM'LDAP://OU=DEMO_Users, DC=DEMO,DC=com' WHERE objectCategory = 'Person' AND objectClass= 'user'" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI". This is really strange! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-20 : 03:09:21
|
Seems to me that the password is wrong.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2008-05-20 : 04:58:33
|
| When I do a connection test for the Linked Server, it says it is successful.i have run the following query to impersonate a Domain Admin:EXEC sp_addlinkedsrvlogin 'ADSI', False, 'UMAT_Usr','DOMAIN\JillSmith', 'password'I would assume it is ok? I wonder if there is another way to test?ThanksPhil |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-20 : 05:39:41
|
Under which context are you running the SSIS package?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2008-05-20 : 05:53:32
|
| I am just running it in Visual Studio under my own credentials, this will be a Domain Admins. |
|
|
|
Gopher
Yak Posting Veteran
83 Posts |
Posted - 2008-05-20 : 07:35:28
|
| FIXED - Basically everthing that was setup was fine, the only thing that was not was the location I was executing the package from!If I tried to run it on my client machine it failed - giving the error above!But, if I try to run it on the Server - it worked perfectly!I am not sure if this is a bug (or feature! ;) or just tight SQL security, but if anyone has a simple way arround this let me know.Thanks for all of your helpGopher |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-20 : 08:13:07
|
Yes, you changed the context. Ie you changed the account that was used to run the package.Security.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
Trying to Query AD!