Credential problem with SQL CLR project

ssingla
Member Posts: 2,973
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:
I have written the following code in SQL CLR project
Can anybody help me in solving this.
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
http://ssdynamics.co.in
0
Comments
-
Did you change the service tier setting xml file to allow NTLM?0
-
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.in0 -
I suggest to create a std c# project in VS and copy and paste your code and see if it work.0
-
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.in0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions