Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
CRM 2011 | Plugin: How to connect to external Database RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi there,

    I have a situation where I want to connect to an external database on the same domain on the same server.

    After instantiating the Sqlconnection reference, when the plug-in comes to myConnection.open(); it throws an exception and here is the details:

    System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
       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.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at CrmFinancialIntegrator.FinacialIntegrator.PostTransaction()

     

    Is there any problem about permissions? Any ideas how to connect?

    Monday, April 11, 2011 10:32 AM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    I've just come across the WebPermission Security Exception and found this article helpful:

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

    Sandboxed plug-ins can access the network through the HTTP and HTTPS protocols. This capability provides support for accessing popular Web resources like social sites, news feeds, Web services, and more. The following Web access restrictions apply to this sandbox capability.

    • Only the HTTP and HTTPS protocols are allowed.

    • Access to localhost (loopback) is not permitted.

    • IP addresses cannot be used. You must use a named Web address that requires DNS name resolution.

     

    I had used localhost as the url for the WCF service in my dev environment. Changing it to the machine name solved the problem.

    • Proposed as answer by BSimons Thursday, September 29, 2011 6:44 PM
    • Marked as answer by Jamie MileyModerator Tuesday, March 6, 2012 2:59 PM
    Tuesday, September 27, 2011 9:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    remove the "Trusted_Connection=true;" part of your connection string.  It is in conflict with specifying a sql user and password.  Trusted connection = true means to use windows authentication for the windows user application context, which would be the app pool security account which is probably Network service and that is your problem.

    Try it with a SQL account without the trusted connection part and see if you get the same error.

     

    Jamie Miley
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Monday, April 11, 2011 2:15 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    This error relates to code access permissions, and should have nothing to do with the logins used.

    I would expect this error if the plugin were running in the Sandbox (as stated by Gayan), but not otherwise.

    I think it is possible that the machine.config (or maybe enterprise.config) settings could cause the error - I think it's possible to use these files to, for example, prevent use of the System.Data classes in code based on where it's loaded from. There's an interesting question about where the .Net Framework would think a plugin is loaded from if it's deployed in the CRM database - it's probably worth changing the deployment between Database, Disk or GAC to see if this makes a difference

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Friday, May 27, 2011 12:57 PM
    Moderator

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    1) Plugins run in the worker process of IE, under the user account you seleceted when you installed CRM. Typically this would be Network Service or a service account
    2) Plugins run in the context of either the calling user, or the deployment admin (for CRM 4.0), depending on how the proxy is created. This is mostly relevant for calls and operations within CRM

    3) There's a lot of restrictions if you use CRM Online, but I assume you use an On-Premise deployment.

    I would examine which user credentials that are actually sendt to the database, and verify that dete database server has a correct logon for that user, with sufficient rights.

    If that fails, check the setup on the SQL Server.

    I assume that plugins are allowed to call external databases, but if I'm wrong you should create a WCF service on top of your database and integrate it into your plugin. This will make maintenance and reuse far easier as well

    Monday, April 11, 2011 12:28 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If you use SQL Server Authentication and specify your creds in the connection string you should be alright.  I am guessing you are using windows authentication and that is causing your problems.

    The WCF method suggested above is also a good way to go too.

    Jamie Miley
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Monday, April 11, 2011 1:12 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you for replying.

    The Crm is on-premise and  this is how I set the connection string:

     SqlConnection myConnection = new SqlConnection("Server=<servername>;database=<DBname>;User ID=<username>;Password=<password>;" +
                                                         "Trusted_Connection=true;");

    I tried to connect using my AD username and password, then I tried the SA account (SQL account), then I created a new user in SQL management studio and

    I added the user to both CRM database and my external Database, but unfortunately I couldn't  connect. I keep getting the security permission exception with different users.

     

    Monday, April 11, 2011 1:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    remove the "Trusted_Connection=true;" part of your connection string.  It is in conflict with specifying a sql user and password.  Trusted connection = true means to use windows authentication for the windows user application context, which would be the app pool security account which is probably Network service and that is your problem.

    Try it with a SQL account without the trusted connection part and see if you get the same error.

     

    Jamie Miley
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!

    Monday, April 11, 2011 2:15 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    I removed "Trusted_Connection=true;" from the connection string and still having the same exception.

    I tried different users such as sa account.

     

     
    Tuesday, April 12, 2011 10:05 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Have you checked that there's actually a login for the credentials you're trying to use, and that the plugin indeed uses those credentials? Using the sa login should work given that the credentials you supply are actually passed.

    Have you tried stepping through the code and verified that the connection is opened correctly? Or if it fails on connection.Open(), have you examined if there's some innerexception or details that could give you a clue towards the problem.

    As a workaround: How about creating a WCF service that fetches the data for you in the database, and retrieve those data from the service in the plugin instead? It won't take long to implement, you'll avoid time-consuming debugging, and if you're database moves you won't need to update your plugin

    Tuesday, April 12, 2011 10:21 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Did you get your answer for this?
    Jamie Miley
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Thursday, May 26, 2011 12:45 AM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote
    The issue is you have your plugin registere under the sandbox, if you set it to 'None' it'll work fine.
    Kids don't try this at home!
    • Proposed as answer by Lo0 Thursday, October 11, 2012 8:42 AM
    Friday, May 27, 2011 1:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    I have the same issue. Did you ever find a solution? I'd really rather not have to write a WCF service if possible.

     

    Gayan, my plugin is not registered under the sandbox.

    Torstein, I have checked that there is indeed a login for the credentials I supply. In fact, I use the exact same credential string in a console application, and that works perfectly!

    Friday, May 27, 2011 11:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    This error relates to code access permissions, and should have nothing to do with the logins used.

    I would expect this error if the plugin were running in the Sandbox (as stated by Gayan), but not otherwise.

    I think it is possible that the machine.config (or maybe enterprise.config) settings could cause the error - I think it's possible to use these files to, for example, prevent use of the System.Data classes in code based on where it's loaded from. There's an interesting question about where the .Net Framework would think a plugin is loaded from if it's deployed in the CRM database - it's probably worth changing the deployment between Database, Disk or GAC to see if this makes a difference

    Microsoft CRM MVP - http://mscrmuk.blogspot.com  http://www.excitation.co.uk
    Friday, May 27, 2011 12:57 PM
    Moderator
  • Question
    Sign in to vote
    0
    Sign in to vote

    When i do WCF call from plugin CRM2011 i get

    System.Security.SecurityException: Request for the permission of type 'System.Net.WebPermission, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

     

    Thursday, July 7, 2011 4:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    You will probably have to post your code.  Is this CRM Online or On-Premise Sandbox?  That shouldn't be a problem, but you might have to do things a little differently, there are small issues you run into like the X509Certificate class is not allowed in the sandbox but the X509Certificate2 class is fine. Stuff like that.
    Jamie Miley
    Check out my about.me profile!
    http://mileyja.blogspot.com
    Linked-In Profile
    Follow Me on Twitter!
    Thursday, July 7, 2011 5:20 PM
    Moderator
  • Question
    Sign in to vote
    1
    Sign in to vote

    I've just come across the WebPermission Security Exception and found this article helpful:

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

    Sandboxed plug-ins can access the network through the HTTP and HTTPS protocols. This capability provides support for accessing popular Web resources like social sites, news feeds, Web services, and more. The following Web access restrictions apply to this sandbox capability.

    • Only the HTTP and HTTPS protocols are allowed.

    • Access to localhost (loopback) is not permitted.

    • IP addresses cannot be used. You must use a named Web address that requires DNS name resolution.

     

    I had used localhost as the url for the WCF service in my dev environment. Changing it to the machine name solved the problem.

    • Proposed as answer by BSimons Thursday, September 29, 2011 6:44 PM
    • Marked as answer by Jamie MileyModerator Tuesday, March 6, 2012 2:59 PM
    Tuesday, September 27, 2011 9:14 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Ty, this worked for me :)
    Thursday, October 11, 2012 8:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thank you Sean, 

    Your info helped me a lot.

    I've just come across the WebPermission Security Exception and found this article helpful:

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

    Sandboxed plug-ins can access the network through the HTTP and HTTPS protocols. This capability provides support for accessing popular Web resources like social sites, news feeds, Web services, and more. The following Web access restrictions apply to this sandbox capability.

    • Only the HTTP and HTTPS protocols are allowed.

    • Access to localhost (loopback) is not permitted.

    • IP addresses cannot be used. You must use a named Web address that requires DNS name resolution.

     

    I had used localhost as the url for the WCF service in my dev environment. Changing it to the machine name solved the problem.


    Oğuz Erdeve

    Wednesday, December 19, 2012 4:03 PM