Options

Credential problem with SQL CLR project

ssinglassingla Member Posts: 2,973
edited 2011-11-01 in NAV Three Tier
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.
CA Sandeep Singla
http://ssdynamics.co.in

Comments

  • Options
    ara3nara3n Member Posts: 9,255
    Did you change the service tier setting xml file to allow NTLM?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    ssinglassingla Member Posts: 2,973
    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>
    
    CA Sandeep Singla
    http://ssdynamics.co.in
  • Options
    ara3nara3n Member Posts: 9,255
    I suggest to create a std c# project in VS and copy and paste your code and see if it work.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    ssinglassingla Member Posts: 2,973
    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.
    CA Sandeep Singla
    http://ssdynamics.co.in
Sign In or Register to comment.