Link to home
Create AccountLog in
Avatar of dilithiumtoys_dot_com
dilithiumtoys_dot_com

asked on

Correcting errors with System.Net.DnsPermission using C#?

Hi,

I am creating a C# CLR Function for SQL Server 2008 (just for background).

I am not having difficulties with the SQL Server at all, so I am not asking any questions regarding SQL Server. I am getting the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Function1":
System.Security.SecurityException: Request for the permission of type 'System.Net.DnsPermission, System, Version=2.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.Dns.GetHostEntry(String hostNameOrAddress)
   at UserDefinedFunctions.Function1(String ipaddress)

I have attached the code that generated this error. I have not used .Net before and I would like to know how to resolve this permission error.

Some background:

I have attempted to find the .Net MMC on my Win2K8 r2 server and I am unable to locate it.
I have added the application server role to the server.
I have successfully deployed working CLR applications to this server.
I have attempted to install the 3.5.1 framework, and it says that it is already installed and that I would need to use Server Manager in order to add or remove it.

Thanks in advance for your help.

using System;
using System.Net;
using System.Security.Permissions;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString Function1(string ipaddress)
    {
        // check string len
        if (ipaddress.Length < 7) return new SqlString("Unknown");
        // attempt a lookup
        try
        {
            // set 
            IPHostEntry ipEntry; // holds entry to lookup
            // request permission for dns lookup
            DnsPermission perm = new DnsPermission(PermissionState.Unrestricted);
            perm.PermitOnly();
            // attempt to resolve
            ipEntry = Dns.GetHostEntry(ipaddress);
            // catch data and return
            return new SqlString(ipEntry.HostName);
        }
        catch (System.Net.Sockets.SocketException se) {return new SqlString("Unknown");}
        catch (System.FormatException fe) {return new SqlString("Unknown");}
    }
};

Open in new window

Avatar of Rimvis
Rimvis
Flag of Lithuania image

You should mark your database as trustworthy.

Open SSMS, and execute this:


ALTER DATABASE <your db name goes here> SET TRUSTWORTHY ON
Avatar of dilithiumtoys_dot_com
dilithiumtoys_dot_com

ASKER

I followed your instructions and I still get the same error.
ASKER CERTIFIED SOLUTION
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you so much. This worked correctly!