November 9, 2018 at 1:11 pm
Eilert,
Thank you so much for writing and publishing this! This is brilliant piece of work!
Your code is incredibly clean, versatile and easy to use.
We have already integrated your CLR function into our production servers that automate communication with Slack and Zendesk.
It was invaluable in replacing our existing Powershell scripts which we had to run using xp_cmdshell (and thus a huge security risk. Now we can finally get rid of it and turn off xp_cmdshell!).
Thank you for providing the source code too, I had to make a few changes to it, to make it support authentication with Zendesk (which requires the TLS1.2 security protocol).
I even published the modified project into a GitHub repository here:
https://github.com/EitanBlumin/ClrHttpRequest
I gave you all the credit I could, but please let me know if you want me to change anything or even collaborate on this open-source project!
I would be delighted!
Best Regards,
Eitan
December 13, 2018 at 4:53 pm
I gave in and threw this on a 2017 SQL installation and it was working great- right up until it didn't.
I'm having issues with the following two URLs:
https://www.uniquevintage.com/ generates the following error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_http_request":
System.Net.WebException: The underlying connection was closed: An unexpected error occurred on a send. ---> System.IO.IOException: Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. ---> System.Net.Sockets.SocketException: An existing connection was forcibly closed by the remote host
System.Net.Sockets.SocketException:
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
System.IO.IOException:
at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.FixedSizeReader.ReadPacket(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Security.SslState.StartReceiveBlob(Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.CheckCompletionBeforeNextReceive(ProtocolToken message, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ForceAuthentication(Boolean receiveFirst, Byte[] buffer, AsyncProtocolRequest asyncRequest)
at System.Net.Security.SslState.ProcessAuthentication(LazyAsyncResult lazyResult)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Net.TlsStream.ProcessAuthentication(LazyAsyncResult result)
at System.Net.TlsStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.ConnectStream.WriteHeaders(Boolean async)
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at UserDefinedFunctions.clr_http_request(String requestMethod, String url, String parameters, String headers, Int32 t...
and https://www.tobi.com generates:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_http_request":
System.Net.WebException: The request was aborted: Could not create SSL/TLS secure channel.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at UserDefinedFunctions.clr_http_request(String requestMethod, String url, String parameters, String headers, Int32 timeout, Boolean autoDecompress, Boolean convertResponseToBas64)
.
I can use xp_cmdshell and curl to retrieve the pages, but that's much less elegant than your solution, and seems to periodically drop lines in the result.
The code is literally just:SELECT
[dbo].[clr_http_request]
(
'GET'
, 'https://www.tobi.com/'
, NULL
, NULL
, 300000, 1, 0
);
Thanks in advance!
December 13, 2018 at 5:07 pm
Update- it looks like it was a TLS thing- I uninstalled your component, and installed the one Eitan put on github (https://github.com/EitanBlumin/ClrHttpRequest), and I have them working now.
I had to fake some headers for one of the sites, but I'm back in business.
December 13, 2018 at 5:14 pm
Yep, I've run into the same issue and have worked a solution into a new version that I hope to post soon.
Basically to fix the issue, this line needs to be implemented in the C# function:System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
December 14, 2018 at 11:19 am
I created a project on github for this function: https://github.com/eilerth/sqlclr-http-request
Note at the time I'm writing this there are breaking changes that will cause examples in this article to stop working. However, the changes future proof it a bit. Specifically, it now includes an "options" parameter for things like timeouts, base64 encoding, etc.
I intend to tweak the article to bring it up to date with the project in github.
@Eitan, I'd prefer to keep this function in the github project I created. I'm happy to collaborate on the project with bug fixes or feature enhancements. The documentation you put together there for your changes so far are helpful.
The TLS thing has been addressed in the version I created.
The basic authorization could be done by doing the base64 encoding in SQL and just passing the string (see https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql). However, I get the convenience of putting it in the function. I'm conflicted because I don't necessarily want the function to get bloated with too many things that can be relatively easily worked around. For example, let's say we then run into an API that is set up slightly unconventionally and their Authorization header is like "Authorization: Basic-base64string" instead of "Authorization: Basic base64string" (note the hyphen). Then things can start to get carried away handling different possibilities. I know that's probably not the greatest example. Like I said, I'm conflicted 🙂
The network authorization is a great addition that can't be worked around without a change to the function to handle it, though. Nice!
December 14, 2018 at 12:17 pm
Eilert Hjelmeseth - Friday, December 14, 2018 11:19 AMI created a project on github for this function: https://github.com/eilerth/sqlclr-http-requestNote at the time I'm writing this there are breaking changes that will cause examples in this article to stop working. However, the changes future proof it a bit. Specifically, it now includes an "options" parameter for things like timeouts, base64 encoding, etc.
I intend to tweak the article to bring it up to date with the project in github.
@Eitan, I'd prefer to keep this function in the github project I created. I'm happy to collaborate on the project with bug fixes or feature enhancements. The documentation you put together there for your changes so far are helpful.
The TLS thing has been addressed in the version I created.
The basic authorization could be done by doing the base64 encoding in SQL and just passing the string (see https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql). However, I get the convenience of putting it in the function. I'm conflicted because I don't necessarily want the function to get bloated with too many things that can be relatively easily worked around. For example, let's say we then run into an API that is set up slightly unconventionally and their Authorization header is like "Authorization: Basic-base64string" instead of "Authorization: Basic base64string" (note the hyphen). Then things can start to get carried away handling different possibilities. I know that's probably not the greatest example. Like I said, I'm conflicted 🙂
The network authorization is a great addition that can't be worked around without a change to the function to handle it, though. Nice!
Thanks Eilert, I appreciate it.
Whatever you decide to do it's okay. It's your code originally, after all.
Regarding the base 64 thing. Your suggested solution in TSQL (from stack overflow) seems rather overly complicated to me, but I understand your concern regarding non standard headers.
I have an idea in this regard, tell me what you think:
Add a utility function within the assembly just for the base64 conversion. Then this function could be easily used to construct the authentication headers however the user needs.
Should be much easier and cleaner than the XML alternative.
Also, did you notice that i implemented an additional authentication method in the assembly, using NetworkCredentials?
I don't have an actual use case for it yet, but I saw it as one of the possible authentication method in webrequest so I figured it would have some use.
August 26, 2020 at 8:38 pm
Hello,
I correctly registered the file but when I run the GET query I receive this error:
Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_http_request":
System.Net.WebException: The request was aborted: Could not create SSL/TLS secure channel.
System.Net.WebException:
at System.Net.HttpWebRequest.GetResponse()
at UserDefinedFunctions.clr_http_request(String requestMethod, String url, String parameters, String headers, Int32 timeout, Boolean autoDecompress, Boolean convertResponseToBas64)
.
I'm on SQL Server 2019, I believe the C# request needs to be updated
August 27, 2020 at 4:05 am
Hello Francesco,
Are you using the latest version from Eilert's GitHub or mine?
If not, please refer here:
https://github.com/eilerth/sqlclr-http-request
Or here:
August 27, 2020 at 6:21 am
I'm facing different errors for both.
Overall I can say that the deployment process is not straightforward.
I will follow each error on your GitHub page. Thank you for your help
September 11, 2020 at 11:01 pm
It looks like a few others have come across the error where the CLR cannot create a secure connection. In my experience this is due to not forcing a TLS 1.2 connection.
Add the below line into the clr_http_request class at the top of the class. It forces a TLS 1.2 connection if possible and a TLS 1.1 connection otherwise. Assuming the computer is updated enough to handle either of those protocols, it should fix this error.
//See https://stackoverflow.com/questions/28286086/default-securityprotocol-in-net-4-5
System.Net.ServicePointManager.SecurityProtocol = System.Net.SecurityProtocolType.Tls11 | System.Net.SecurityProtocolType.Tls12;
September 12, 2020 at 1:20 am
I apologize for delays responding, but this is something I solved for awhile ago in an updated version of the function. I haven't made the time to update this article yet, but the github page has the new version of the function and some documentation for it:
https://github.com/eilerth/sqlclr-http-request
There is an "options" parameter where you can pass in the "security_protocol" option. Example is included on the github page.
December 6, 2021 at 5:35 pm
You did the whole job (no mean feat), put your time aside to document the ins and outs, and placed your skill on display for all (pros and cons) to evaluate. My hat is off to you for putting this well-researched, designed, and executed explanation, code, and process for me and others to consume. Thanks much. To add to your effort, I would direct others to another selfless expert Niels Berglund who writes about 'Whitelisting' CLR assemblies to deal with the CLR strict on, signing, and other bits and pieces of this helpful technology.
https://nielsberglund.com/2017/07/23/sql-server-2017-sqlclr---whitelisting-assemblies/
Thanks again for digging me out of a hole that I had dug myself into.
Cheers,
Jim
December 14, 2021 at 12:32 am
Hello, thank you for this function. I'm trying to use it with an API that requires headers like the below to connect to a specific database and to include an access token. Using Postman it works fine and displays the raw request headers like this:
X-Database: Database 123
Authorization: Bearer 987654321abcdefgh
However I can't seem to get it to work with the function, I assume because the function requires XML formatting. I tried e.g:
<Header Name="X-Database">Database 123</Header>
but the API server rejects it with Internal Server Error 500, and the logs say "Unable to locate Database" . What would be the correct way to format headers like this in the function?
December 15, 2021 at 7:18 pm
Hello, thank you for this function. I'm trying to use it with an API that requires headers like the below to connect to a specific database and to include an access token. Using Postman it works fine and displays the raw request headers like this:
X-Database: Database 123
Authorization: Bearer 987654321abcdefghHowever I can't seem to get it to work with the function, I assume because the function requires XML formatting. I tried e.g:
<Header Name="X-Database">Database 123</Header>but the API server rejects it with Internal Server Error 500, and the logs say "Unable to locate Database" . What would be the correct way to format headers like this in the function?
In case it helps anyone in the future...I couldn't get this working for my needs, so I found the below code sample from Microsoft. It does require some steps to setup but it's pretty straightforward.
https://github.com/microsoft/sql-server-samples/tree/master/samples/features/sql-clr/Curl
July 8, 2022 at 12:34 pm
I apologize for delays responding, but this is something I solved for awhile ago in an updated version of the function. I haven't made the time to update this article yet, but the github page has the new version of the function and some documentation for it:
https://github.com/eilerth/sqlclr-http-request
There is an "options" parameter where you can pass in the "security_protocol" option. Example is included on the github page.
Hello, thank you for this function. I'm trying to use it with Proxy but I ran into a problem when I need to send requests through a Proxy.
Unfortunately, my knowledge is not enough to do this via C#.
Could you refine the function with the ability to use a proxy?
Below is an example of my code that I tried to add to your project, but unfortunately it didn't work
// Set the ProxyServer if provided as an option
if (options.ContainsKey("ProxyServer"))
{
var ProxyServer = (options["ProxyServer"]);
if (options.ContainsKey("ProxyPort"))
{
var ProxyPort = int.Parse(options["ProxyPort"]);
request.Proxy = new
WebProxy(ProxyServer, ProxyPort);
}
}
request.AllowAutoRedirect = false;
debugXml.Add(GetDebugStepXElement("Handled Option 'ProxyServer'"));
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply