Assembly in sql server with .net dll

Question

Hi all

Here I create a class library with below code.

using System;

using System.Collections.Generic;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Data.SqlClient;

using System.Text;

using System.Diagnostics;

using System.Net;

using System.IO;

using Microsoft.SqlServer.Server;

 

namespace TestingSms

{

    public class Class1

    {

        public static void testingSms()

        {

            string strConnection = "Data Source=USER2;Initial Catalog=MyDatabase;Integrated security=SSPI;persist security info=True;Trusted_Connection=Yes";

            StringBuilder str = new StringBuilder();

 

            SqlConnection conn = new SqlConnection(strConnection);

            SqlCommand cmd = new SqlCommand();

            cmd.Connection = conn;

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.CommandText = "birthday";

 

            conn.Open();

 

            SqlDataReader dr = cmd.ExecuteReader();

            if (dr.Read())

            {

                str.Append(dr["mobile_no"].ToString());

            }

 

            string mob = str.ToString();

            string message = "xxxxxx Wishing you Happy Birthday";

 

            string url =                  "http://xxx.xx.xxx.xx:xxxx/xxxxx/xxxxxxx.jsp?usr=xxxxxx&pass=xxxxxxxx&msisdn=" + mob + "&msg=" + message + "&sid=xxxxxxx&fl=0&mt=0";

 

            try

            {

                HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);

                HttpWebResponse resp = (HttpWebResponse)req.GetResponse();

                Stream st = resp.GetResponseStream();

                StreamReader sr = new StreamReader(st);

                //string buffer = sr.ReadToEnd();

 

                sr.Close();

                st.Close();

 

               

 

 

            }

            catch (Exception Ex)

            {

                //Response.Write(Ex.Message);

                throw Ex;

            }

 

        }

    }

}

 

 

After creating  dll for this class library

I created assembly in sql server 2005 for the above dll and after that I create a procedure for created assembly after that I am trying to execute the procedure its giving below error

Msg 6522, Level 16, State 1, Procedure ToSendSms, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'ToSendSms':

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, 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.PermissionSet.Demand()

   at System.Data.Common.DbConnectionOptions.DemandPermission()

   at System.Data.SqlClient.SqlConnection.PermissionDemand()

   at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

   at TestingSms.Class1.testingSms()

 

 

 

below is my sql server code for creating assembly and procedure.

1)exec dbo.sp_configure 'clr enabled',1 RECONFIGURE WITH OVERRIDE

 

2)alter database MyDatabase SET TRUSTWORTHY ON

GO

 

3)create assembly SMStest

AUTHORIZATION dbo

from 'E:\Babu\TestingSms\TestingSms\bin\Debug\TestingSms.dll'

with permission_set = safe

go

 

 

4)create procedure ToSendSms

as external name SMStest.[TestingSms.Class1].testingSms

go

 

5)exec ToSendSms

 

 

Please help me out……………