I am attempting to use the EWSMA 2.2 with my SQL Server 2016 (v 13.0.2164.0) to connect to an Exchange server 2010. The code I've written works perfectly as a console app, but when I try to turn it into a SQL CLR Assembly, it fails at runtime with the following error:
Msg 6522, Level 16, State 1, Line 4
A .NET Framework error occurred during execution of user-defined routine or aggregate "EXT_ExchangeCalendar":
System.TypeInitializationException: The type initializer for 'Microsoft.Exchange.WebServices.Data.ExchangeServiceBase' threw an exception. ---> System.ArgumentException: The path is not of a legal form.
I have moved my the Microsoft.Exchange.WebServices.dll and Microsoft.Exchange.WebServices.Auth.dll to the C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\ directory. Then I've added the assembly to SQL Server using this:
CREATE ASSEMBLY [microsoft.exchange.webservices] AUTHORIZATION dbo FROM 'C:\WINDOWS\Microsoft.NET\Framework64\v4.0.30319\Microsoft.Exchange.WebServices.dll' WITH PERMISSION_SET = UNSAFE
Upon execution it returns that error. I suspect that this has to do with the primary assembly being unable to load the dependancy (...Auth.dll) but I can't verify that.
My other suspicion is that CLR doesn't support the version of .NET that is required for this DLL? Any thoughts?
UPDATE: Here's the source code.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;
using System.Collections;
using System.Globalization;
using System.Text;
using System.Security.Cryptography.X509Certificates;
using Microsoft.Exchange.WebServices.Data;
public partial class UserDefinedFunctions
{
public class TableObj
{
public SqlString startdate;
public SqlString enddate;
public SqlString subject;
public SqlString appointmenttype;
public SqlString body;
public SqlString categories;
public SqlString location;
public TableObj(SqlString in_startdate, SqlString in_enddate, SqlString in_subject, SqlString in_appointmenttype, SqlString in_body, SqlString in_categories, SqlString in_location)
{
startdate = in_startdate;
enddate = in_enddate;
subject = in_subject;
appointmenttype = in_appointmenttype;
body = in_body;
categories = in_categories;
location = in_location;
}
}
[SqlFunction(
TableDefinition = "startdate nvarchar(max), enddate nvarchar(max), subject nvarchar(max), appointmenttype nvarchar(max), body nvarchar(max), categories nvarchar(max), location nvarchar(max)",
FillRowMethodName = "FillRow")]
public static IEnumerable EXT_ExchangeCalendar(DateTime startdate = default(DateTime)
, DateTime enddate = default(DateTime)
, String username = "myusername"
, String password = "mypassword"
, String domain = "mydomain"
, String url = "https://owa.mydomain.com/EWS/Exchange.asmx")
{
startdate = startdate == default(DateTime) ? DateTime.Now : startdate;
enddate = enddate == default(DateTime) ? DateTime.Now.AddDays(30) : enddate;
ServicePointManager.ServerCertificateValidationCallback = CertificateValidationCallBack;
ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2010);
service.Credentials = new WebCredentials(username, password, domain);
service.Url = new Uri(url);
ArrayList tableResults = new ArrayList();
const int NUM_APPTS = 5000;
CalendarFolder calendar = CalendarFolder.Bind(service, WellKnownFolderName.Calendar, new PropertySet());
CalendarView cView = new CalendarView(startdate, enddate, NUM_APPTS);
FindItemsResults<Item> instanceResults = service.FindItems(WellKnownFolderName.Calendar, cView);
foreach (Item item in instanceResults.Items)
{
item.Load(); //necessary to load the body into the appointment.
Appointment appointment = item as Appointment;
tableResults.Add(new TableObj(appointment.Start.ToString(), appointment.End.ToString(), appointment.Subject.ToString(), appointment.AppointmentType.ToString(), appointment.Body.ToString(), appointment.Categories.ToString(), appointment.Location.ToString()));
}
return new ArrayList(tableResults);
}
public static void FillRow(
object obj,
out SqlString startdate,
out SqlString enddate,
out SqlString subject,
out SqlString appointmenttype,
out SqlString body,
out SqlString categories,
out SqlString location)
{
TableObj myRow = (TableObj)obj;
startdate = myRow.startdate;
enddate = myRow.enddate;
subject = myRow.subject;
appointmenttype = myRow.appointmenttype;
body = myRow.body;
categories = myRow.categories;
location = myRow.location;
}
private static bool RedirectionUrlValidationCallback(string redirectionUrl)
{
// The default for the validation callback is to reject the URL.
bool result = false;
Uri redirectionUri = new Uri(redirectionUrl);
// Validate the contents of the redirection URL. In this simple validation
// callback, the redirection URL is considered valid if it is using HTTPS
// to encrypt the authentication credentials.
if (redirectionUri.Scheme == "https")
{
result = true;
}
return result;
}
private static bool CertificateValidationCallBack(
object sender,
System.Security.Cryptography.X509Certificates.X509Certificate certificate,
System.Security.Cryptography.X509Certificates.X509Chain chain,
System.Net.Security.SslPolicyErrors sslPolicyErrors)
{
// If the certificate is a valid, signed certificate, return true.
if (sslPolicyErrors == System.Net.Security.SslPolicyErrors.None)
{
return true;
}
// If there are errors in the certificate chain, look at each error to determine the cause.
if ((sslPolicyErrors & System.Net.Security.SslPolicyErrors.RemoteCertificateChainErrors) != 0)
{
if (chain != null && chain.ChainStatus != null)
{
foreach (System.Security.Cryptography.X509Certificates.X509ChainStatus status in chain.ChainStatus)
{
if ((certificate.Subject == certificate.Issuer) &&
(status.Status == System.Security.Cryptography.X509Certificates.X509ChainStatusFlags.UntrustedRoot))
{
// Self-signed certificates with an untrusted root are valid.
continue;
}
else
{
if (status.Status != System.Security.Cryptography.X509Certificates.X509ChainStatusFlags.NoError)
{
// If there are any other errors in the certificate chain, the certificate is invalid,
// so the method returns false.
return false;
}
}
}
}
// When processing reaches this line, the only errors in the certificate chain are
// untrusted root errors for self-signed certificates. These certificates are valid
// for default Exchange server installations, so return true.
return true;
}
else
{
// In all other cases, return false.
return false;
}
}
}
