Search code examples
c#sql-serverweb-serviceswcfsqlclr

Using SQL CLR to Call a Web Service

I followed this guys walk-through, which I've seen posted in other Stack Overflow posts: https://code.msdn.microsoft.com/Calling-WCF-Service-from-a-8071ceaa

And I get an error when running my stored procedure which calls out to the class library which has the web service reference which is supposed to call the web service:

The settings property xxxx was not found

I think from what I can gather from this vague message is the end point is not being found in the config for the web service client to call out to it.

Unfortunately, the person who wrote the aforementioned blog post has nothing in it about this, and searching for hours on this particular message has gotten me nowhere.

Solution

  • I got this working using the web service client object inside the dll which is referenced by the SQL Server project dll. I was trying to be pro-active about security and granted my asymmetric key login external use rights instead of unsafe. Once I granted the login unsafe rights, it was able to call the web service correctly.

    Thank you @Solomon for the good tips on security. I figured I will post my experience here for anyone else who might have an issue.

    You can use this walk-through (https://code.msdn.microsoft.com/Calling-WCF-Service-from-a-8071ceaa), but I will try to add the bits this article left out about how to use a signature on your project and create the asymmetric key inside SQL.

    1. Create a Visual Studio Project of type Library and add a web reference to the WCF service. Then create a method (Make sure it is static) which creates your web service client object and calls out to your service.

    Compile this project when you have added any static methods for calling your web service(s).

    1. Create a Visual Studio Project of type SQL Server Database project and add a reference to the dll of the previous library project. Use the link above to see how to set up this project. (NOTE As I did my referencing locally and then moved things to the server I made sure my SQL Server Database project had these properties for the dll reference of the Library project, Copy Local = true, Specific Version = false, Generate SQL Script = false, Is Visible = true, Model Aware = true, and permission set = external ( not sure if this last one matters as I had to grant unsafe access to my sql login anyway...)

    2. I used the sn.exe tool in the windows SDK to create my .snk file, this is used in sql to create your login and it is also used to sign the previous 2 VS projects. (Or if you already under stand creating a signed assembly, do it straight through Visual Studio, then use that .snk in SQL Server)

    3. I used this link https://www.codeproject.com/Articles/290249/Deploy-Use-assemblies-which-require-Unsafe-Externa (Method 2) to learn how to use the sn.exe tool and also how to setup the asymmetric key and login for use in SQL Server. NOTE(The location for the sn.exe tool was not the same for me as in this article, for me I had to go to location: c:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools this may vary for others as well.

    4. One of the last things you will have to do is reconfigure your database server to allow CLR, which is done in SQL Server with the command:

      EXEC sp_configure 'clr enabled', '1'
RECONFIGURE

    5. As to enabling clr, I am not sure if this is something you should turn on in your stored procedure, then turn off again, (Security ideas anyone?).