SecurityException error when calling CLR function

Question

I have written a simple user defined function in C# that gets the IP address and returns host name against it code is as follows:

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString getname(String ipAddress)
    {
        System.Data.SqlClient.SqlClientPermission pSql = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
        pSql.Assert();

        System.Security.PermissionSet psql1 = new PermissionSet(System.Security.Permissions.PermissionState.Unrestricted);
        psql1.Assert();

        System.Net.DnsPermission psql = new DnsPermission(System.Security.Permissions.PermissionState.Unrestricted);
        psql.Assert();

        System.Security.Permissions.SecurityPermission psql2 = new SecurityPermission(System.Security.Permissions.PermissionState.Unrestricted);
        psql2.Assert();

        IPHostEntry host = Dns.GetHostEntry(ipAddress);
        return new SqlString(host.HostName.ToString());
    }
}

now I have deployed it but when I try to call this function in sql as: select dbo.getname('10.10.10.10')

It returns this error:

Msg 6522, Level 16, State 2, Line 7
A .NET Framework error occurred during execution of user-defined routine or aggregate "getname": 
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException: 
   at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)
   at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)
   at System.Security.CodeAccessPermission.Assert()
   at UserDefinedFunctions.getname(String ipAddress)

I have crawled the internet but found nothing on it. Please suggest.

how are you registering your assembly? is it flagged for external access? — StrayCatDBA
– StrayCatDBA, Commented Apr 24, 2013 at 3:46
I'm getting the exact same error from a very similar CLR function. I'm using WindowsIdentity.Getcurrent() to return user information. My assembly is registered in SQL WITH PERMISSION_SET = External_Access. Anybody ever resolve this? — Mark Buckley
– Mark Buckley, Commented Oct 24, 2014 at 14:58

Russell Hart · Accepted Answer · 2015-04-18 17:05:49Z

7

You must create the assembly with external access permission set. The assembly must also be signed or the database set as trustworthy and the database owner granted the external access assembly permission. The permission and assert code above is not required.

E.g.

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
using System.Net;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.None)]
    public static SqlString getname(String ipAddress)
    {
        IPHostEntry host = Dns.GetHostEntry(ipAddress);
        return new SqlString(host.HostName);
    }
}

Save the code to C:\resolve\resolve.cs and compile it...

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe /t:library /out:C:\resolve\resolve.dll C:\resolve\resolve.cs

Copy the .dll to the server if not a local instance. Then create the objects...

USE [master]
GO
CREATE DATABASE [clr]; 
GO
ALTER AUTHORIZATION ON DATABASE::[clr] TO [sa]; 
GO
ALTER DATABASE [clr] SET TRUSTWORTHY ON; 
GO
USE [clr]; 
GO
CREATE ASSEMBLY [assResolve] FROM 'C:\resolve\resolve.dll' 
WITH PERMISSION_SET=EXTERNAL_ACCESS; 
GO
CREATE FUNCTION [fnGetHostName](@ipAddress nvarchar(255)) RETURNS nvarchar(255) 
AS EXTERNAL NAME [assResolve].[UserDefinedFunctions].[getname]; 
GO
SELECT [dbo].[fnGetHostName](N'8.8.8.8') as [host]; 
GO
USE [master]

answered Apr 18, 2015 at 17:05

Russell Hart

1,85113 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wavydavy Over a year ago

I had to change the create assembly to alter assembly and remove the from, but other than that, thanks very much.

Collectives™ on Stack Overflow

SecurityException error when calling CLR function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related