Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

The underlying connection was closed: An unexpected error occurred on a send. #20

Closed
ahmedarafaa opened this issue Feb 10, 2020 · 26 comments
Closed

The underlying connection was closed: An unexpected error occurred on a send. #20

ahmedarafaa opened this issue Feb 10, 2020 · 26 comments
Assignees
@geral2

Comments

@ahmedarafaa
Copy link

ahmedarafaa commented Feb 10, 2020 •
edited
Loading

Hello :),
I use APIConsumer since 1/1/2020
from 5/2/2020 I get this error

The underlying connection was closed: An unexpected error occurred on a send.

I use sql 2014

exec  [dbo].[APICaller_POST]
    	@URL = @URLPath
    	,@JsonBody = @Query

I tried the API from the postman it's working fine
@geral2 geral2 self-assigned this Feb 10, 2020
@geral2
Copy link
Owner

geral2 commented Feb 10, 2020

Hey, that's weird I haven't updated any code since October 16th 2019.
What version of .net framework are you using?
Is you URL HTTPS or HTTP?

@ahmedarafaa
Copy link
Author

thanks for your replay
I'm using .net framework 4.0.30319 and using https

@lwal73
Copy link

lwal73 commented Feb 13, 2020

I have been using APIConsumer for a long time.
I had an error at the same time (05/02/2020).
Request was aborted: Failed to create SSL / TLS secure channel.
request example:
exec dbo.APICaller_POST
@url='https://api.telegram.org/bot5xxxxxxxxxxxxxxxxxxxxxx/sendMessage'
,@JsonBody='{"chat_id":"592xxxx","text":"test"}'

Help.

@geral2
Copy link
Owner

geral2 commented Feb 13, 2020 •
edited
Loading

Hello,

I wasn't able to reproduce this issue. Let me give a try using Telegram API.

Btw, what SQL Server version and .Net framework are you using?

@lwal73
Copy link

lwal73 commented Feb 13, 2020

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64) Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 6.3 (Build 14393: )
What information do you provide for a simpler solution path for you ??
Until 05/02/2020 everything worked fine.

@lwal73
Copy link

lwal73 commented Feb 13, 2020

I'm from Ukraine

@geral2
Copy link
Owner

geral2 commented Feb 13, 2020

Just as a random test, Could you please try calling RoutinNumbers api below:

-- How to consume GET API -- How to show Json results.

DECLARE @RoutingNumber AS VARCHAR(50) = '122242597'

--Public API: routingnumbers.info
DECLARE @Url  VARCHAR(200) = CONCAT('https://www.routingnumbers.info/api/name.json?','rn=',@RoutingNumber) 

DECLARE @Results AS TABLE
(
	Context varchar(max)
)

DECLARE @Result AS VARCHAR(MAX)

--INSERT INTO @Results
EXEC  [dbo].[APICaller_GET] @Url

@lwal73
Copy link

lwal73 commented Feb 13, 2020

the request does not work only with telegrams
With checked on other services, everything works out well.
and there are no telegrams (((
I can check on your request.
just could you please drop the request text, not the picture.

@lwal73
Copy link

lwal73 commented Feb 13, 2020

DECLARE @object AS INT;
DECLARE @responseText AS VARCHAR(8000);
DECLARE @Body AS VARCHAR(8000) = '{"chat_id":"592xxxxxxxx","text":"привет"}'

EXEC sp_OACreate 'MSXML2.XMLHTTP', @object OUT;
EXEC sp_OAMethod @object, 'open', NULL, 'post','https://api.telegram.org/bot5xxxxxxxxxxxxxxxxxxxx/sendMessage', 'false'

EXEC sp_OAMethod @object, 'setRequestHeader', null, 'Content-Type', 'application/json'
EXEC sp_OAMethod @object, 'send', null, @Body

EXEC sp_OAMethod @object, 'responseText', @responseText OUTPUT
SELECT @responseText

EXEC sp_OADestroy @object
That's how it works, but for me it’s not very convenient.

@lwal73
Copy link

lwal73 commented Feb 13, 2020

не могли бы вы написать мне письмо на еьфшд lwal@и.ua, я вам отправлю полные данные , что-бы вы могли сами проверить и увидеть ошибки.

@lwal73
Copy link

lwal73 commented Feb 13, 2020

The answer to your request worked perfectly.
{"message": "OK", "rn": "122242597", "code": 200, "name": "FIRST CITIZENS BANK"}

@lwal73
Copy link

lwal73 commented Feb 13, 2020

Could you write me a letter at [email protected], I will send you complete data so that you can check and see the errors yourself.

@geral2
Copy link
Owner

geral2 commented Feb 13, 2020

Sure!, thank you!

@lwal73
Copy link

lwal73 commented Feb 13, 2020

Thank you, I will wait for a test letter from you.

geral2 pushed a commit that referenced this issue Feb 14, 2020
Fix related SSL issue #20.
d9c1ba0
@geral2
Copy link
Owner

geral2 commented Feb 14, 2020

Hey @lwal73 @ahmedarafaa Could you give a try with this version that I just loaded (2.0.0.4)?

@lwal73
Copy link

lwal73 commented Feb 14, 2020 via email

@ahmedarafaa
Copy link
Author

Hello @geral2
I try to configure this version 2.0.0.4 with fresh database fresh configuration and i get this error in this step

image

CREATE ASSEMBLY [API_Consumer] AUTHORIZATION dbo FROM N'C:\CLR\APIConsumer.dll' WITH PERMISSION_SET = UNSAFE

Warning: The Microsoft .NET Framework assembly 'system.net.http, version=4.0.1.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 6586, Level 16, State 1, Line 1
Assembly 'System.Net.Http' could not be installed because existing policy would keep it from being used.

@lwal73
Copy link

lwal73 commented Feb 14, 2020 via email

@ahmedarafaa
Copy link
Author

@lwal73
In the new version, the dll name changed from "API_Consumer" to "APIConsumer"

from where you get this part are you compile it by your self or what? more explanation, please
FROM N'C:\CRL\API_Consumer.dll'

@lwal73
Copy link

lwal73 commented Feb 14, 2020 via email

@geral2
Copy link
Owner

geral2 commented Feb 14, 2020

@lwal73 you're welcome. Would you mind starting the project?.

@ahmedarafaa Is you check the last commit uploaded, I did not rename that dll.
You could rebuild the project in your environment in order to get a new API_Consumer generated, or you can use the version in the release script API_Consumer_2.0.0.4.

@lwal73
Copy link

lwal73 commented Feb 14, 2020

what should I do ?

@geral2
Copy link
Owner

geral2 commented Feb 14, 2020

Just click here

image

@geral2
Copy link
Owner

geral2 commented Feb 14, 2020

Btw, this issue was caused due TLS < 1.2 would be deprecated on March 2020. All servers will have to serve their contents using TLS 1.2 at least in order to keep compatibility with the main browsers.

So, since we are using 4.0 Framework, the changed was adding the code below:

 ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls
                                                 | SecurityProtocolType.Ssl3 
                                                 | (SecurityProtocolType)3072;

This specific part (SecurityProtocolType)3072;. Which correspond to SecurityProtocolType.Tls12

@ahmedarafaa
Copy link
Author

Works for me :D
Thanks @geral2

@ahmedarafaa
Copy link
Author

the last thing please could you please add "Go" below this line of code

PRINT N'Creating [dbo].[APICaller_GET_Headers_BODY]...';
GO --Please add Go in this line

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@geral2 geral2

Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@geral2 @ahmedarafaa @lwal73