David.Poole (5/1/2016)
I'd be curious to know which ports and protocols have to be open in the firewall to allow SQL Server to use web services.You transmit on a port relevant to your service but have to open up ephemeral ports to get an appropriate response. Doesn't this mean that as well as having to open up TCP1433 we have to open up the full ephemeral range 1024 to 65535?
Mechanically I can see that making web service calls direct from SQLServer would be convenient and possibly the most efficient mechanical path. Security wise I'm not so sure
There is nothing inherently different about making Web Service calls from CLR hosted in SQL Server vs CLR hosted on the Windows OS. The outgoing calls should be on ports 80 and 443 depending on if you are using HTTP or HTTPS. But there is no listener so no incoming calls. I have never heard of anyone doing any special configuration in order to call an external Web Service via SQLCLR.
The default port for SQL Server native communication, 1433, has nothing to do with Web Service calls.
Security-wise there are no issues as it is outgoing-only.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR