Credential problem with SQL CLR project

ssingla

Taking inspiration from Rashed's idea of replacing NAS with SQL CLR projects I am developing a small concept oriented SQL CLR project to create customer in NAV.
One of the problems which Rashed mentioned in the blog gave me a lot of pain but I have been able to solve it i.e. Additional dll which needs to be handled manually when NAV webservice is referenced in project because SQL cannot handle dynamics serialization. I am able to use pre and post deployment script to automate the deployment of second dll.

Now instead of using a codeunit I am trying to insert a customer record but getting the following error:

Running [dbo].[StoredProcedure1].

A .NET Framework error occurred during execution of user-defined routine or aggregate "StoredProcedure1":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, 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.CredentialCache.get_DefaultCredentials()
at System.Web.Services.Protocols.WebClientProtocol.set_UseDefaultCredentials(Boolean value)
at CLRTest.Customer.CustCreate_Service.set_UseDefaultCredentials(Boolean value)
at CLRTest.Customer.CustCreate_Service..ctor()
at SP.StoredProcedure1()

I have written the following code in SQL CLR project

using System;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRTest.Customer;


public partial class SP
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void StoredProcedure1()
    {
        string login = "administrator"; string password = "Savita1"; string domain = "SSD";
        
        CustCreate_Service Myservice = new CustCreate_Service();
        
        CredentialCache mycred = new CredentialCache();
        NetworkCredential netcred = new NetworkCredential(login,password,domain);
        mycred.Add(new Uri(Myservice.Url), "NTLM", netcred);
        Myservice.Credentials = mycred;
            
        CLRTest.Customer.CustCreate cust = new CLRTest.Customer.CustCreate() { No = "abc01" };
        Myservice.Create(ref cust);
    }
};

Can anybody help me in solving this.

ara3n

Did you change the service tier setting xml file to allow NTLM?

ssingla

ara3n wrote:

Did you change the service tier setting xml file to allow NTLM?

As I mentioned this is conceptual work so I have Windows Server 2008 R2 Enterprise with SQL Server 2008 R2 and NAV installation on the same machine.
The CustomSetting file in C:\Program Files (x86)\Microsoft Dynamics NAV\60\Service have the following element:

<add key="WebServicesUseNTLMAuthentication" value="true"></add>

ara3n

I suggest to create a std c# project in VS and copy and paste your code and see if it work.

ssingla

I found out the problem. I had setup everything on 1 system and was using "localhost" in webservice address. Normal C# application was fine with it but SQL does not like it. Replaced it with machine name and it worked.