Set option 9 in SQL Server stored procedure using WinHttp.WinHttpRequest.5.1 for TLS 1.2
I am trying to connect to a web service that only supports TLS 1.2. I'm having trouble with the syntax for setting option9 (WinHttpRequestOption_SecureProtocols
) to use TLS 1.2
I've tried
EXEC sp_OASetProperty @objectID, 'Option', '2720', 9
but no dice.
Not because it's a good idea, but so that no one else has to figure out how to use the horrid sp_OAxxx stored procedures...
Here's an update to my ancient HTTP stored procedure to use both WinHttp and set that option. The Option property is an "indexed property" so calling it with sp_OASetProperty is wierd.
create or alter procedure get_http @url varchar(2000)
as
begin
/*
exec get_http 'https://www.bing.com'
*/
declare @hr int;
declare @win int;
declare @errorMessage varchar(2000);
begin try
EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0
begin;
set @errorMessage = concat('sp_OACreate failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
throw 60000, @errorMessage, 1;
end;
EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0
begin;
set @errorMessage = concat('Open failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
throw 60000, @errorMessage, 1;
end;
--Option is an indexed property, so newvalue = 2048 and index = 9
--sp_OASetProperty objecttoken , propertyname , newvalue [ , index... ]
EXEC @hr=sp_OASetProperty @win, 'Option', 2048, 9
IF @hr <> 0
begin;
set @errorMessage = concat('set Option failed ', convert(varchar(20),cast(@hr as varbinary(4)),1) );
throw 60000, @errorMessage, 1;
end;
EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0
begin;
set @errorMessage = concat('Send failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
throw 60000, @errorMessage, 1;
end;
declare @status int
EXEC @hr=sp_OAGetProperty @win,'Status', @status out
IF @hr <> 0
begin;
set @errorMessage = concat('get Status failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
throw 60000, @errorMessage, 1;
end;
if @status <> 200
begin;
set @errorMessage = concat('web request failed ', @status);
throw 60000, @errorMessage, 1;
end;
declare @response table(text nvarchar(max));
insert into @response(text)
EXEC @hr=sp_OAGetProperty @win,'ResponseText';
IF @hr <> 0
begin;
set @errorMessage = concat('get ResponseText failed ', convert(varchar(20),cast(@hr as varbinary(4)),1));
throw 60000, @errorMessage, 1;
end;
select *
from @response
EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win;
end try
begin catch
declare @error varchar(200) = error_message()
declare @source varchar(200);
declare @description varchar(200);
declare @helpfile varchar(200);
declare @helpid int;
exec sp_OAGetErrorInfo @win, @source out, @description out, @helpfile out, @helpid out;
declare @msg varchar(max) = concat('COM Failure ', @error,' ',@source,' ',@description)
EXEC @hr=sp_OADestroy @win;
--IF @hr <> 0 EXEC sp_OAGetErrorInfo @win;
throw 60000, @msg, 1;
end catch
end