1

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.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
  • Did you verify that the .config file has that setting in it? Did you move the compiled dll somewhere and not copy its .config file with it? – 1.618 Jan 09 '18 at 21:37
  • Thank you both for the quick replies, just to clarify, I am using Visual Studio 2013, SQL Server 2014. I did verify that the config file has the setting in it and when i moved the compiled dll to the server hosting SQL Server I moved the config as well. @Solomon, I didn't follow that posts direction to turn on trustworthy, and found some MSDN docs on how to do what you suggested and did those things to keep security as strong as possible. Looks like I may have to just do it with the HttpWebRequest as I spent about my whole day at work today trying to get this to work using a service client. – Ryan Wilson Jan 09 '18 at 22:18
  • What I mean by using a service client, is I created a class library which did the call to the webservice, added the service reference as a web reference under advanced option instead of the traditional service reference, and my WCF service is running fine, tested it in that project to be safe. Then I did the second step, created a database project with the stored procedure to call the static function in the previously listed dll library, created the asymmetric key with ns.exe and signed both the library and database project. Compiled them, moved them to the SQL server, by using the sql command – Ryan Wilson Jan 09 '18 at 22:27
  • Then did the asymmetric key stuff in SQL Server, as @Solomon mentioned to keep it as secure as possible, but still get the previously mentioned issue where it can't find the setting. I imagine this is the exception message being returned when the CLR Procedure calls out to the dll library. Anyway, if anyone has anymore thoughts as to what may be the problem, I appreciate the feedback. – Ryan Wilson Jan 09 '18 at 22:29

3 Answers3

2

It helps to know what version of SQL Server you are on. But regardless, if you are relying on the ServiceModel library (or any other .NET Framework libraries that you need to manually load into SQLServer), then that isn't going to work. You can do web service calls in SQLCLR, but only using libraries in the Supported .NET Framework Libraries list.

For doing such things, I usually recommend using the plain 'ol HttpWebRequest / HttpWebResponse classes and handling the creation and parsing of the XML manually (well, using available .NET classes). It won't automatically give you a strongly typed object, but it is guaranteed to work across all version of SQL Server.

Also, the advice in that post to enable TRUSTWORTHY is bad advice. You should instead sign the Assembly, then create an Asymmetric Key in master from the DLL, then create a Login from that Asymmetric Key, and finally grant the EXTERNAL ACCESS ASSEMBLY permission to that Login. Accomplishing this can be done in a few ways and depends on what version of SQL Server you are using (SQL Server 2017 introduced a new complication) and if you are using Visual Studio / SSDT / SqlPackage`exe for deployment. I have written a series of tutorials on this topic showing 2 options that both work with all versions of SQL Server (well, 2005 through current – which is 2017 at the moment), starting with:

SQLCLR vs. SQL Server 2017, Part 1: “CLR strict security” – The Problem

Part 1 (linked directly above) explains the situation and why the new server-wide setting in SQL Server 2017 is a problem given the lack of built-in support for dealing with it via Microsoft-provided tools such as Visual Studio / SSDT. Parts 2 and 3 are the actual solutions.

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Thank you for the advice @Solomon, I appreciate it, please see my above 3 comments, had too much to type and had to add comment a few times. – Ryan Wilson Jan 09 '18 at 22:30
0

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?).

halfer
  • 19,824
  • 17
  • 99
  • 186
Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40
  • Glad you got it working. Not sure why `UNSAFE` is required. One of these days I'll have to test it myself. But good to know. Regarding the steps here, I would remove steps 3 - 6. Signing the Assembly can / should be done in Visual Studio, and you can automate deployment, even via Visual Studio / SSDT, and never use a DLL. I updated my answer with a link to the start of a series of posts I wrote showing how to do this. Enabling CLR is server-wide, not a DB setting. I wouldn't recommend the link in step 4 since step 5 doesn't need the `USE`. I wouldn't turn ON / OFF a server setting in a proc. – Solomon Rutzky Jan 10 '18 at 15:37
  • @Solomon, I believe the reason for step 3 was to create a custom key for signing my assemblies. And since I was not specific, I did go through Visual Studio to sign the assemblies, but I pointed to the custom .snk file I created in step 3. I then used that same file when adding the CLR to SQL Server. I didn't realize enabling CLR was a server wide function, I suppose that makes sense, but I wonder why they couldn't make that more granular and on a database level, might be more secure??. Updating my SQL statement. – Ryan Wilson Jan 10 '18 at 19:22
  • Ryan: you don't need step 3 manually. You can handle it all in Visual Studio...go to project properties and in one of the tabs is a "signing" button...you can create a new snk file (and password protect to become pfx) in one project, and then reference the pfx in the other project (or in 100 projects).. – Solomon Rutzky Jan 10 '18 at 19:26
  • Cool, but what about porting that over to SQL server then when creating the asymmetric key? use master; create asymmetric key CLRExtensionKey from file = 'D:\Sample CLR\SampleCLRKey.snk' encryption by password = '@Str0ngP@$$w0rd' , this part of Method 2 of the second link I posted. – Ryan Wilson Jan 10 '18 at 19:29
  • 1
    You need to look at the series of posts I linked in my answer. I cover all of the variations that allow you to automate this within Visual Studio....and since you are not using SQL Server 2017, you can go to the article linked in the first or second post (solution 1 at least) to the article I wrote on SQL Server Central that has a few less steps since the Certificate wasn't needed prior to 2017. But you don't need to create assemblies, asymmetric keys, or certificates from files. never create from files. Always have a fully self-contained SQL script with no external dependencies. – Solomon Rutzky Jan 10 '18 at 19:34
  • @Solomon Thank you. I will be sure to read through your posts when I get some free time. I appreciate your input. – Ryan Wilson Jan 10 '18 at 19:37
0

you could follow the same sample of this repository.... the only thing is that it's calling an Api. But it basically the same concept you would need to modify some stuff. Hope it helps you.

Take a look to this repository https://github.com/geral2/SQL-APIConsumer.

STEP 1

CREATE DATABASE TestDB; GO

STEP 2

USE TestDB GO sp_configure 'clr enabled',1 RECONFIGURE GO

STEP 3

ALTER DATABASE TESTDB SET TRUSTWORTHY ON GO

STEP 4

CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE--external_access GO

STEP 5

CREATE ASSEMBLY [Newtonsoft.Json] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE go
Geraldo Diaz
  • 346
  • 5
  • 7