I am trying to call an external webservice in visual studio but I am getting the error.
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.ServicePointManager.set_ServerCertificateValidationCallback(RemoteCertificateValidationCallback value)
The following is the program to invoke the webservice.
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
[return: SqlFacet(MaxSize = -1)]
public static SqlString NYP_RestGet(SqlString uri)
{
String document;
System.Net.ServicePointManager.ServerCertificateValidationCallback +=
delegate(object sender, System.Security.Cryptography.X509Certificates.X509Certificate certificate,
System.Security.Cryptography.X509Certificates.X509Chain chain,
System.Net.Security.SslPolicyErrors sslPolicyErrors)
{
return true; // **** Always accept
};
// Set up the request, including authentication
WebRequest req = WebRequest.Create(Convert.ToString(uri));
((HttpWebRequest)req).UserAgent = "CLR web client on SQL Server";
req.ContentType = "application/xml";
((HttpWebRequest)req).Accept = "application/xml";
WebResponse resp = req.GetResponse();
Stream dataStream = resp.GetResponseStream();
StreamReader rdr = new StreamReader(dataStream);
document = (String)rdr.ReadToEnd();
rdr.Close();
dataStream.Close();
resp.Close();
return (document);
}
};
For network-related requests by themselves you would need to set your Assembly to
PERMISSION_SET = EXTERNAL_ACCESS
. However, usingSystem.Net.ServicePointManager.ServerCertificateValidationCallback
unfortunately requiresPERMISSION_SET = UNSAFE
. If you do not expressly need to override the handling of SSL Certificates, then you should get rid of that delegate since it would be better for the Assembly to be set toEXTERNAL_ACCESS
.Unfortunately, Visual Studio / SSDT (SQL Server Data Tools) does not make it very easy to take the appropriate steps needed to allow your Assembly to be set to either
EXTERNAL_ACCESS
orUNSAFE
. They do, however, make it easy enough to set theTRUSTWORTHY
option toON
, which is mostly a bad idea.Please do not set
TRUSTWORTHY ON
unless absolutely necessary! And it should only be "necessary" when loading an Assembly that you did not build and cannot re-sign. And that mostly happens when loading .NET Framework libraries that aren't "supported" and hence not already in SQL Server's CLR host. Outside of those circumstances, you should not be setting the database toTRUSTWORTHY ON
as it opens up a security hole.Instead, it is much better to do the following:
The above only needs to be done once per Instance, per key. So if you use the same
snk
/pfx
file for all of your assemblies, then the steps shown above only need to be done once per SQL Server Instance; the number of Assemblies and databases containing those Assemblies does not matter.This approach allows you to keep better security on the database (by keeping
TRUSTWORTHY
set toOFF
) and allows for more granular control of which assemblies are even allowed to be set toEXTERNAL_ACCESS
and/orUNSAFE
(since you can separate by using different keys for signing and Logins based on those different keys).For a more detailed walk-through of the security options, please see the following article that I wrote on SQL Server Central: Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies) (free registration is required).
Other notes:
Convert.ToString(uri)
. All of theSql*
types have a.Value
property that returns the appropriate native type. So replace that withuri.Value
.DataAccess = DataAccessKind.Read
in theSqlFunction
attribute since you are not doing any data access. SettingDataAccess = DataAccessKind.Read
is a slight performance hit, so since you are not making use of it, just remove it.dataStream
andrdr
(being aStream
andStreamReader
respectively) are "disposable" objects, so you really need to be calling.Dispose()
on them, which you can probably do instead of the.Close()
.Dispose
method being called on both of those objects, your process could hold open the external network handle, and that might not get released until the App Domain recycles which might not be for a long time. You need to either use theusing
construct or properly structure atry
/catch
/finally
. This is rather dangerous code to be running within SQL Server without doing one of these two things.