Cannot Run Through SQL Agent

Nov 28, 2013 at 9:47 PM
Hi Alex, first let me tell you that you have done an amazing job. I have been fighting for years with SQL Scripts, ADO connections, and every possible alternative to extract data from AD with no luck. I have thousands of users in AD so I quickly run into the limitation of the 1000 records limit of AD. So your solution worked great and I was able to get everything from AD in minutes, when before it could take me hours.

Than being said, I need to ask you for some help. I was able to install the SQL SDK and the installed you've uploaded. I was able to add the LDAP source to BIDS and was able to make it work through BIDS. However, the moment I try to run the SSIS package through a SQL Agent job, I get the error below:

I am running SQL 2008 R2 on a Windows 2008 R2 Standard Server 64b.

I saw a related discussion about making it work in SQL 2012. I checked the c:\windows\assembly and I see the SsisLdapSource assembly there. I am looping through multiple domains as you described in your example (with a for each).

Do you think on anything else I could be missing?

Your help would be greatly appreciated!!

Message
Executed as user: EMEA\$App-EMEA-DBAdmin. ...0.4000.0 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 21:35:59 Error: 2013-11-28 21:35:59.41 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2013-11-28 21:35:59.46 Code: 0xC00490F7 Source: Get Users Get Users (LDAP Source [48]) Description: The managed pipeline component "SsisLdapSource.SsisLdapSource, SsisLdapSource, Version=1.4.0.6, Culture=neutral, PublicKeyToken=758d8a4b4299146a" could not be loaded. The exception was: Could not load file or assembly 'SsisLdapSource, Version=1.4.0.6, Culture=neutral, PublicKeyToken=758d8a4b4299146a' or one of its dependencies. The system cannot find the file specified.. End Error Error: 2013-11-28 21:35:59.46 Code: 0xC0047067 Source: Get Users Get Users (SSIS.Pipeline) Description: The "component "LDAP Source" (48)" failed to cache the component metadata object and returned error code 0x80131600. End Error Error: 2013-11-28 21:35:59.46 Code: 0xC004706C Source: Get Users Get Users (SSIS.Pipeline) Description: Component "component "LDAP Source" (48)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. End Error Error: 2013-11-28 21:35:59.46 Code: 0xC0048021 Source: Get Users Get Users (LDAP Source [48]) Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "". End Error Error: 2013-11-28 21:35:59.46 Code: 0xC004801F Source: Get Users Get Users (SSIS.Pipeline) Description: The component metadata for "component "LDAP Source" (48)" could not be upgraded to the newer version of the component. The PerformUpgrade method failed. End Error Error: 2013-11-28 21:35:59.47 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2013-11-28 21:35:59.48 Code: 0xC00490F7 Source: Get MultiValues Get MultiValues (LDAP Source [1]) Description: The managed pipeline component "SsisLdapSource.SsisLdapSource, SsisLdapSource, Version=1.4.0.6, Culture=neutral, PublicKeyToken=758d8a4b4299146a" could not be loaded. The exception was: Could not load file or assembly 'SsisLdapSource, Version=1.4.0.6, Culture=neutral, PublicKeyToken=758d8a4b4299146a' or one of its dependencies. The system cannot find the file specified.. End Error Error: 2013-11-28 21:35:59.48 Code: 0xC0047067 Source: Get MultiValues Get MultiValues (SSIS.Pipeline) Description: The "component "LDAP Source" (1)" failed to cache the component metadata object and returned error code 0x80131600. End Error Error: 2013-11-28 21:35:59.48 Code: 0xC004706C Source: Get MultiValues Get MultiValues (SSIS.Pipeline) Description: Component "component "LDAP Source" (1)" could not be created and returned error code 0xC0047067. Make sure that the component is registered correctly. End Error Error: 2013-11-28 21:35:59.48 Code: 0xC0048021 Source: Get MultiValues Get MultiValues (LDAP Source [1]) Description: The component is missing, not registered, not upgradeable, or missing required interfaces. The contact information for this component is "". End Error Error: 2013-11-28 21:35:59.48 Code: 0xC004801F Source: Get MultiValues Get MultiValues (SSIS.Pipeline) Description: The component met... The package execution fa... The step failed.
Coordinator
Nov 30, 2013 at 9:09 AM

This link should help in understanding the issue you have encountered: http://msdn.microsoft.com/en-us/library/ms141747.aspx

In a nutshell, here is what is most likely happening:

1. While logged-in as user X you created a package. As part of creating the package you provided the credentials (password) for the LDAP source component.

2. By default, SSIS will encrypt the password inside the package using the profile of user X.

3. SQL Agent runs in the security context of user Y, hence can’t decrypt the password in the package, this leads to the error you experienced.

The link I provided suggests to use EncryptSensativeWithPassword protection level, as opposed to EncryptSensativeWithUserKey. Details on how to set packages protection levels could be found here http://msdn.microsoft.com/en-us/library/904a5580-82ba-4a26-b0c5-d1c989975f61

I hope this helps.

Alex.

Nov 30, 2013 at 11:36 AM
Hi Alex

Thanks for your reply. I was able to fix the error by clicking on a checkbox in the sql agent job configuration that forces the job to run in 32 bit mode. By default it tries to run in 64 bit mode.

That made the difference and everything worked great.

On another subject, have you ever tried to pull the ThumbnailPhoto from Ad using your connector? That is the picture that users can upload to ad and is displayed in Outlook

It is a binary field but it looks like there is a limit in the buffer of the connector. If i try toadd it, the job fails.

All i really want to know is if the user has a picture in ad or not. In the adsi sql query i was getting the whole value and then i just validated if the field was null or not and the result was just a boolean. Have you came across this before?

Thanks!


Rodrigo Amigorena
Walt Disney International
Director Business Applications
+54 11 4814-8598




Coordinator
Dec 2, 2013 at 4:06 AM

Hi Rodrigo,

I am glad that you were able to work this out, and sorry for sending you in the wrong direction with my replay.

I will look into adding support for the ThumbNailPhoto attribute. Will let you know by Thursday.

Thanks

Alex.

Coordinator
Dec 4, 2013 at 12:35 AM
Hi Rodrigo,
It turns out that no code changes are required to accommodate the scenario of ThumbNailPhoto attribute.
You will need to make a change to the LDAP Source component configuration though:
  1. Open LDAP Source component using the Advanced Editor
  2. Switch to the Input and Output Properties tab
  3. Expand the Output Columns node
  4. Select the thumbnailPhoto attribute. Note, by default the DataType would be set to DT_Bytes, change this to image[DT_Image]. This will allow the column to accept wider inputs.
Note. On the SQL Server table column which accepts this attribute I had to set the Data Type to varbinary(MAX).

I hope this helps.
Thanks
Alex.
Dec 4, 2013 at 12:57 AM

Thanks Alex!!

I will try it and let you know if it works!

One more thing I forgot to mention in case you’ve seen it before. I am extracting the field AccountExipres which is bigint. You have created a great function which converts these int values to a datetime format to avoid having to manually convert them using functions like the one I have created (attached).

As you may well know, the Account Expire field is defined as:

The date when the account expires. This value represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). A value of 0 or 0x7FFFFFFFFFFFFFFF (9223372036854775807) indicates that the account never expires.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms675098(v=vs.85).aspx

So… the problem is with the records which have the value 9223372036854775807 as fails when trying to process them. What I have done instead is extract it as a standartd iNT field and then I apply the function I mentioned above to all records which don’t start with 9.

Would you be able to include this logic so that it can be automatically converted to a datetime only for those accounts that have a valid expiration date?

Thanks again! I am REALLY happy with the performance of your connector. You have done a GREAT job.

Regards

Coordinator
Dec 4, 2013 at 6:57 PM
Hi Rodrigo,
Unfortunately, I can't reproduce the issue you described regarding the accountExpires attribute.
I do have a logic in the connector to check for 9223372036854775807 (which is long.MaxValue) and return Date.Time.MaxValue in such cases.
So when importing an AD account where accountExpires is 9223372036854775807 the connector returns 9999-12-31:59:59:999 which I expect the logic in the package to interpret as never.
In cases where accountExpires is set by AD to 0, the connector returns 1601-01-01 00:00:00.000. I do this to be consistent with AD PowerShell cmdlets since this is what they would return in this case.
Could you send more details on the error you are getting?
Thanks.
Alex.
Dec 5, 2013 at 8:06 PM

Alex

These are the errors I get when I add the account expire

[OLE DB Destination [515]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Invalid date format".

[OLE DB Destination [515]] Error: There was an error with input column "accountExpires" (872) on input "OLE DB Destination Input" (528). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

[OLE DB Destination [515]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (528)" failed because error code 0xC020907A occurred, and the error row disposition on "input "OLE DB Destination Input" (528)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (515) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (528). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[LDAP Source [48]] Error: System.Runtime.InteropServices.COMException (0xC0047020): Exception from HRESULT: 0xC0047020

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSBuffer100.SetEndOfRowset()

at Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer.SetEndOfRowset()

at SsisLdapSource.SsisLdapSource.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] pipelineBuffers)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "LDAP Source" (48) returned error code 0xC0047020. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

For this test I created a very simple task containing just the Distinguished Name and the AccountExpires.

I run a report of the existing expiration dates running a distinct report and these are the results. I am showing the top and bottom results. The first column has the int value displayed in AD and the second one is the converted value I calculate with a script.

Do you think it could be overflowing with the second account in the list which expires in 2113?

Thanks for your help!!!

Coordinator
Dec 6, 2013 at 9:52 PM
Hi Rodrigo,
Just in case, I tested with an account which expires in 2113 and it worked fine.

Looking at the error message you provided:
[OLE DB Destination [515]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
It appears that the job fails on the destination and not on the source, in other words, LDAP Source probably gets the data, but SQL does not want to accept it. Try attaching a data viewer to figure-out what LDAP Source brings from AD.
What data type do you have on your destination SQL table for the accountExpires?
Thanks
Alex.
Dec 6, 2013 at 9:58 PM
Sql automatically created the field ad datetime. I'll try what you suggested. ..

Regards


Enviado desde Samsung Mobile



Dec 13, 2013 at 2:23 PM

Hi!!

When I try to add this date to a table I get this error: Could it be a SQL limitation?

Another question J. Do you know if there is a way to increase the time out to the AD domain controller? Sometimes I get timeout errors. I retry the job and then it works so I guess there must be some kind of traffic peak at that point. Alternatively, is there a way to force the connection to a specific Domain controller?

I really appreciate all your help. You don’t imagine how helpful this solution has been to me!!

Dec 13, 2013 at 2:30 PM

One more finding:

If I enter the date as 9999-12-31 23:59:59.000 it works

However if I enter is as 9999-12-31 23:59:59.999 as it comes from the LDAP source, it overflows…

From: Amigorena, Rodrigo
Sent: Friday, December 13, 2013 11:23 AM
To: '[email removed]'
Subject: RE: Cannot Run Through SQL Agent [adssis:471451]

Hi!!

When I try to add this date to a table I get this error: Could it be a SQL limitation?

Another question J. Do you know if there is a way to increase the time out to the AD domain controller? Sometimes I get timeout errors. I retry the job and then it works so I guess there must be some kind of traffic peak at that point. Alternatively, is there a way to force the connection to a specific Domain controller?

I really appreciate all your help. You don’t imagine how helpful this solution has been to me!!

Dec 13, 2013 at 4:52 PM

Problem Fixed…

http://www.karaszi.com/sqlserver/info_datetime.asp

SQL 2008 maximum value for datetime is 9999-12-31 23:59:59.997

I have now changed it to DateTimeOffSet and it worked…


Regards

From: Amigorena, Rodrigo
Sent: Friday, December 13, 2013 11:30 AM
To: '[email removed]'
Subject: RE: Cannot Run Through SQL Agent [adssis:471451]

One more finding:

If I enter the date as 9999-12-31 23:59:59.000 it works

However if I enter is as 9999-12-31 23:59:59.999 as it comes from the LDAP source, it overflows…

From: Amigorena, Rodrigo
Sent: Friday, December 13, 2013 11:23 AM
To: '[email removed]'
Subject: RE: Cannot Run Through SQL Agent [adssis:471451]

Hi!!

When I try to add this date to a table I get this error: Could it be a SQL limitation?

Another question J. Do you know if there is a way to increase the time out to the AD domain controller? Sometimes I get timeout errors. I retry the job and then it works so I guess there must be some kind of traffic peak at that point. Alternatively, is there a way to force the connection to a specific Domain controller?

I really appreciate all your help. You don’t imagine how helpful this solution has been to me!!

Coordinator
Dec 13, 2013 at 11:44 PM
Hi Rodrigo,
First, thanks a lot for figuring this out. I will consider removing the fractions of a second from these types of attributes in the next release.

Regarding the time-out, it is technically possible for me to expose this setting in the configuration of the connector (currently it is not). Though my experience shows, that any network related issue manifests itself as a time-out (ex, if you misspelled the name of your host in hostOrDomainName property, the error would be time-out). In other words, unless you are connecting to a DC over a slow WAN link, then issue is probably not with the time-out setting (.NET has a pretty generous time-out as is). At any rate, I will consider this as a feature for a next release, since I have been asked for this a few times.

Regarding forcing the connector to use a specific DC, just specify DC's name in the HostOrDomainName property of the component (ex. dc01.fabrikam.com).

Thanks again.
Alex.
Dec 17, 2013 at 2:50 AM

Thanks Alex

Is there any alternative way to increase the timeout? The same package with the exact configuration some days It works and some days it fails and the error says it’s a timeout problem.


Thanks!

Coordinator
Dec 18, 2013 at 12:44 AM
Hi Rodrigo,
I am not aware of any alternative ways to increase the timeout.

Personally, I would start with performing simple pings from the SSIS server to the domain controllers. Note the latency that PING returns. Perhaps, create a script that pings domain controllers throughout the day just to see if the latency varies. As a next step, you may want to consider scripting a simple Get-ADObject cmdlet (again from the SSIS server) just to see if this would fail at different times of the day.
If in fact, PING demonstrated extremely high latency (over 1 second) then increasing the time-out would be the way to go.

As I mentioned already, .NET LDAP library returns only one network related error (Time-out). In other words, any network related issue will manifest itself as a time-out exception, so I don't trust this error message myself.
In the end, this errors you are getting indicate an issue on your network, I think the only way to fix this would be to find the root cause. The default .NET TCP/IP timeout should sufficient in most cases. For example, I was able to pull data using the connector while connected over VPN using my Phone as a modem, without having to adjust the time-out (it was very slow but it worked).
I hope this helps a bit.
Alex.