Problem with a Web Service Task that uses a digital certificate

Hi every body... I will thank u the help that you could bring me...

This is my problem:

I am using a Web Service Task in Integration Services for access a Web Method, this method is not mine, it is a method provided for another company. This Web Method is published in a VPN (Virtual Private Network) and it is in a secured channel (https).

The properties of the Connection Manager that the Web Service Task uses are ok. The Server URL is the address of the asmx and the client certificate is the correct. In fact when I press Test Connection, the message Test Connection Succeed is shown. If I change the certificate, a connection error occurs (403 Forbidden), this means that the client certificate and the address is ok. I addition, I use this certificate to access another methods published by the same company in a Visual basic application (with WSE) and I don′t have problems.

When I execute the DTSX the error "403 Forbidden" occurs and I can't execute the method. I executed the DTS in the VS2005, SQL IDE and in a batch file (.bat) too, and the same error happened.

If I use the File Monitor tool (from SysInternal, that now belongs to Microsoft) I can see that when I test the connection (in the Connection Manager used by the Web Service Task), the process accesses the certificate's private key located in the RSA folder of my certificates. But when I run the DTSX (no matters which way), it never reads the private key, it only accesses the certificate repository.

I saw that the code of the DTSX includes the certificate in Base64, but I am thinking that this code doesn't include the private key (I don't know if it would have to do it)...

Well, the fact is that I can′t use this Web Service task. I am suspecting that is a bug of the Integration Service

Thanks for the help that could bring me...

Can you tell us a bit more about http connection manager's credentials? what user credentials are you using to access the web service?

|||

Sure ...

The properties of the connection manager (seen in the edition window) are:

Server URL: https://<server url>/<folder name>/webservice.asmx. Where <server url > is the direction of the server, <folder name> is the folder name where the webservice is and finally the webservice.asmx. The direction is ok because I am using another .asmx in the same direction with a .net windows service application and all works fine with my certificate.

Another important thing is that I don't need to use a special network user, or account, to access the web service from this .net windows service. In fact the windows service runs with the Local System Account. For that, I installed the certificate (with it's root certificate) in the Local Computer repository. For my tests I installed the certificate in the Current User repository too.

In the checkbox "Use Credentials" I have tried nothing and my network user, but the same error raises with both.

In the "Client Certificate" I chose the certificate.

In the Proxy settings (the another tab) I don't have to put information because the connection is not made between a proxy.

As you can see, the problem is very strange, because I can access the Web Service from the Internet Explorer and .Net applications.

Thanks for your time and help...

Problem when adding ssis packages from microsoft visual source safe 6.0

I have created a integration services project on machine A and have added to visual source safe 6.0

When i try to take the project from source safe and store that on a different machine B i have a problem with it....

I check in everything on Machine A

When i try to add on machine B it tries to add it with "Name of package(1)" which means that its adding a copy.But it dosent do for all only for few

Could anyone let me know what the problem is?

Any time you see an unexpected "Name of package(1)" package added to you project, means the folder where the packages are stored already have a file with that name. Depending in how you do it, a file that is not part of the project may still exists in the folder. Use a windows explore to check what I am saying.

Problem when adding ssis packages from microsoft visual source safe 6.0

I have created a integration services project on machine A and have added to visual source safe 6.0

When i try to take the project from source safe and store that on a different machine B i have a problem with it....

I check in everything on Machine A

When i try to add on machine B it tries to add it with "Name of package(1)" which means that its adding a copy.But it dosent do for all only for few

Could anyone let me know what the problem is?

Any time you see an unexpected "Name of package(1)" package added to you project, means the folder where the packages are stored already have a file with that name. Depending in how you do it, a file that is not part of the project may still exists in the folder. Use a windows explore to check what I am saying.

Problem when a IS tries to move data between servers

Hello,

I have a problem: I have created an Integration Services in SQL Server 2005 that moves data from a table in a server to another table in another server. I have set the protection level property to "don't save sensitive".

The problem is when I try to execute it with a dtexec command. I get this errors:

'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Communication link failure".'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "TCP Provider: An existing connection was forcibly closed by the remote host.'
'An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'xxxxx'.".'

I don't know why the Integration Services does not work, and what are this errors about. Maybe the problem is with the protection level property, because when I set it to "don't save sensitive" it has some warnings, but when I set the property to "Encrypt sensitive with user key" the error messages are the same.

Thanks,

Pablo Orte

Pablo,

What you are seeing, I think, it's an expected behavior. When you choose 'Don't save sensitive' NO sensitive info is saved; and that includes the password in connection strings. See here for more details: http://msdn2.microsoft.com/en-us/library/ms141747.aspx

You could avoid this situation on several ways; I personally use 'Don't save sensitive' and then assign connection strings at run time using 'package configurations' as described in the method 4 of this article: http://support.microsoft.com/kb/918760

Notice thta if you are using SQL Server database you could use Windows authentication that does not require explicitly saved pasword as a part of the connection string.

|||

Hi Rafael,

Thanks for your reply. We have an Scrypt Task at the start of the IS, that assign a connection string to the different servers. The values of the connection strings (user, password, server, and database) are passed as variables.

The thing is that when we execute the IS in the SQL Server Business Intelligence, It works fine and does everything ok. But when it is executed with the dtexec command it gives us that error.

Thanks,

Pablo Orte

|||

Using script task for this kind of task is something I have never done; Package configuration is the natural way to me to solve it. Is there something that prevents you to use them?

I am not sure, but your problem may be related with the order on which things occur when running the package; if I recall correctly package configuration go first; then the connection managers. Saying this, I don't know if by the time you script task attempts to replace the connection strings it's already too late.

BTW, when you say you are passing user/password/server via variables; are the values local in the package or they are received via package configuration?

|||

Well, the fist thing our IS do is setting the connection string. We do this with variables because our ISs do things in different servers, at different times. I mean, we have some servers, and we use the same IS to do things with them.

After this, our IS truncate a table and the call a data flow that do a select instruction from a server, convert the data a insert it into another server. Then, it truncates another table and finish. We think that this error may occur because the dataflow between servers is wrong or it doesn't set the connection string properly.

The variables are passed with the dtexec command and they are variables in the package.