I don’t approve of calling a WCF server from SQL Server, but there was a business requirement that had to be met. It concerned regulations regarding the safeguarding of certain data elements. Due to performance issues and the application’s infrastructure, calling the service from the application itself wasn’t an option.
Coding the CLR functions were the easy part. To do this, reference the Microsoft.SqlServer.Server and System.Data.SqlTypes namespaces (contained in System.Data.dll) and set the appropriate attributes on static methods contained within a static class. Also, it is probably best if you use the SqlTypes (SqlString, SqlDateTime, etc) for parameters and return values rather than rely on Sql Server to convert the values for you. Be sure to enable CLR on your SQL Server.
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public static class SqlFunctions
{
[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)]
public static SqlString HelloWorld()
{
return new SqlString("Hello World!");
}
}
I wanted to avoid pulling too many assemblies into SQL Server, so I limited my references to the bare essentials for working with WCF and Sql Server: System, System.Data, System.Runtime.Serialization, System.ServiceModel, and System.XML.
Here’s the script to drop the assemblies if they exist and add them back (double slashes are intentional to allow copy/paste with formatting to work). Because of dependencies, it is necessary to add more assemblies than directly referenced by the project. If you created this within a database project, you can deploy by right-clicking the project. I have previously posted instructions on how to convert a class library to a database project.
IF EXISTS (SELECT * FROM sys.assemblies asms
WHERE asms.name = N'Microsoft.Transactions.Bridge')
DROP ASSEMBLY [Microsoft.Transactions.Bridge]
GO
IF EXISTS (SELECT * FROM sys.assemblies asms
WHERE asms.name = N'System.IdentityModel.Selectors')
DROP ASSEMBLY [System.IdentityModel.Selectors]
GO
IF EXISTS (SELECT * FROM sys.assemblies asms
WHERE asms.name = N'System.IdentityModel')
DROP ASSEMBLY [System.IdentityModel]
GO
IF EXISTS (SELECT * FROM sys.assemblies asms
WHERE asms.name = N'System.Messaging')
DROP ASSEMBLY [System.Messaging]
GO
IF EXISTS (SELECT * FROM sys.assemblies asms
WHERE asms.name = N'System.Web')
DROP ASSEMBLY [System.Web]
GO
IF EXISTS (SELECT * FROM sys.assemblies asms
WHERE asms.name = N'SMDiagnostics')
DROP ASSEMBLY [SMDiagnostics]
GO
CREATE ASSEMBLY
SMDiagnostics from
'C:\Windows\Microsoft.NET\Framework\v3.0\
\Windows Communication Foundation\SMDiagnostics.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY
[System.Web] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\
\System.Web.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY
[System.Messaging] from
'C:\Windows\Microsoft.NET\Framework\v2.0.50727\
\System.Messaging.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY
[System.IdentityModel] from
'C:\Program Files\Reference Assemblies\Microsoft\
\Framework\v3.0\System.IdentityModel.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY
[System.IdentityModel.Selectors] from
'C:\Program Files\Reference Assemblies\Microsoft\
\Framework\v3.0\System.IdentityModel.Selectors.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY
[Microsoft.Transactions.Bridge] from
'C:\Windows\Microsoft.NET\Framework\v3.0\
\Windows Communication Foundation\Microsoft.Transactions.Bridge.dll'
with permission_set = UNSAFE
GO
You will need to add your own assembly to the list in a similar manner as the .NET assemblies. Notice that they are marked UNSAFE. This is a necessary evil if you wish to use WCF with SQL Server. Because of this, if you have not done so you will need to reconfigure your database to allow it.
ALTER DATABASE KodefuGuru SET TRUSTWORTHY ON
reconfigure
The interesting part about this is that more assemblies than those specified are pulled in. When you remove the ones you specifically added, these are removed as well. Here is the list of assemblies:
Accessibility
Microsoft.Transactions.Bridge
SMDiagnostics
System.Configuration.Install
System.Design
System.DirectoryServices
System.DirectoryServices.Protocols
System.Drawing
System.Drawing.Design
System.EnterpriseServices
System.IdentityModel
System.IdentityModel.Selectors
System.Messaging
System.Runtime.Remoting
System.Runtime.Serialization
System.Runtime.Serialization.Formatters.Soap
System.ServiceModel
System.ServiceProcess
System.Web
System.Web.RegularExpressions
System.Windows.Forms
System.Windows.Forms? What?! Some of the dependencies are truly mind boggling.
This should be just enough to allow you to put your service calls inside of CLR functions. I attempted to be as lightweight as possible and avoided pulling in extra libraries, though you certainly could pull in a massive programmatic infrastructure with this.
I only agreed to this because the service call was directly related to the integrity of the data and further restrictions prevented the use of a separate physical tier to handle it. If you’re in the same situation, I hope this helps.
Subscribe