CLR Stored Procedure connection exception

Question

CLR Stored Procedure connection exception

I have a CLR stored procedure in one database which tries to make a connection to another database on another server.  It fails on connection.Open() with the exception: "Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."

var sameDocTypeCount = 0;
using (var connection = new SqlConnection(solomon))
 {
         string sqlStmt = @"SELECT count(*) FROM xfmAttach (nolock) WHERE  KeyValue='" + workOrderId +"'";
         connection.Open();
         var command = new SqlCommand(sqlStmt, connection);
         sameDocTypeCount = Convert.ToInt16(command.ExecuteScalar());
  }

I've tried setting up SQL Server for remote connections 1433 / TCP enabled, using a variety of connection strings but nothing works.

connection strings:

@"Server=WIN7SERVER;Initial Catalog=FMGAPP;Integrated Security=True";

@"Persist Security Info=False;Integrated Security=False;Server=server;initial catalog=FMGAPP;user id=server\user;password=password;"

@"Server=WIN7SERVER;Database=FMGAPP;Trusted_Connection=True;Integrated Security=True";

@"Data Source=Win7Server;Initial Catalog=FMGAPP; User id=userid;Password=password";

Ideas?

Thanks in advance.

John McKelvey

Answer 1

Did you try setting the permission level?

Set permissions to EXTERNAL_ACCESS. Go to the properties of the project (right-click on the project node, choose Properties), choose the Database tab, and then from the Permission Level combo-box, choose External.

 

Pasted from <http://msdn.microsoft.com/en-us/library/ms345135(v=sql.90).aspx> 

Also, please check out the restrictions on the EXTERNAL_ACCESS security level:

http://msdn.microsoft.com/en-us/library/ms345101.aspx

Answer 2

Hello,

Follow the thread below. It may help you

http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/1505d7e6-59dc-4997-b707-9e9736a1f978

Answer 3

Hello,

Below threads might help you.

http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/ddc4409a-bfba-49ab-bb58-7dbf0b85b284

http://stackoverflow.com/questions/6901811/sql-clr-streaming-table-valued-function-results