none
Digitally Sign a SOAP Message from a CLR Procedure

    질문

  • I have created a CLR-based stored procedure in Visual Studion 2008 "Database Project" connecting to a SQL Server 2008 R2 Standard database. I can sucessfully communicate with the web service in question. However the service that I need to communicate with apparently requires digitally signed SOAP content. The exact error message is "SOAP body must be signed for signature-based authentication". I've found lots of examples of how to accomplish this through Google, but they all use variations of the Web Service Extenstions (WSE).  These require references to DLLs such as Microsoft.Web.Services3.Security.Tokens which are not available (as far as I can tell) in the VS Database Project. When I go to add references these are not an option and there does not appear to be anyway to add them.

    Is there a way to call SOAP services that require a digital signature from a CLR procedure?

    2012년 2월 23일 목요일 오전 12:08

답변

  • If you don’t see an assembly in the database project references dialog, this means it’s not supported by SQLCLR. To use such an assembly, you can use an ordinary Class Library project and manual deployment. Since the assembly isn’t supported, the SQLCLR assembly that uses it must be marked as “permission_set = unsafe”. You must first catalog the dependent assembly (in your case the WebServices3 assembly, and all its dependencies) as unsafe, then the assembly that uses it.
     
    Because it’s unsupported there’s no guarantee that this workaround will work.
     
    Hope this helps,
    Bob
    • 답변으로 표시됨 Stephanie Lv 2012년 3월 8일 목요일 오전 7:51
    2012년 2월 23일 목요일 오후 8:09

모든 응답

  • Hi Dannil,
    You may consider creating an assembly with digital signature for SQL Server CLR stored procedure. To do this, it is required to switch the clr enabled option to 1 to allow assembly execution.  This article may be helpful to you to work around this issue: Using Signed Assemblies for SQLCLR: Doing the Safety Dance.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

    Stephanie Lv

    TechNet Community Support

    2012년 2월 23일 목요일 오전 8:58
  • Sorry Stephanie, but I'm still a little confused. You seemed to describe how I might sign an assembly and get it added to the database. That might lead me in the right direction, but I'm not sure. Perhaps I'm missing a step.

    Will getting the Microsoft.Web.Services3.dll loaded into my SQL Server instance then allow me to import it from the VS 2008 Database Project where I am writing my CLR enabled stored procedure? At the moment, I can't import this class into the project - there are no options to do so and just adding calls to use the class prevents the project from compiling.

    2012년 2월 23일 목요일 오후 6:23
  • If you don’t see an assembly in the database project references dialog, this means it’s not supported by SQLCLR. To use such an assembly, you can use an ordinary Class Library project and manual deployment. Since the assembly isn’t supported, the SQLCLR assembly that uses it must be marked as “permission_set = unsafe”. You must first catalog the dependent assembly (in your case the WebServices3 assembly, and all its dependencies) as unsafe, then the assembly that uses it.
     
    Because it’s unsupported there’s no guarantee that this workaround will work.
     
    Hope this helps,
    Bob
    • 답변으로 표시됨 Stephanie Lv 2012년 3월 8일 목요일 오전 7:51
    2012년 2월 23일 목요일 오후 8:09