Get the the db connection string from the Dynamics CRM SDK

Take the example of finding all customers with more than one open phone call. In SQL a HAVING clause gets you most of the way there. In the CRM SDK it's going to be a bit harder, and I bet there will be a load more data sent over the network just for the CRM server to process.

So I'd like my plugins, apps, custom workflows to read the database through the filtered views, and then change what it needs to through the web service.

Question: How do I get the database connection string for the database behind the organisation 开发者_开发百科from the SDK?

Sure I know what it is right now, and can hard code it or configure it. But I want the user to find their CRM instance using the discovery service and the app to take care of the rest.

Just from a quick test, the simple SQL

SELECT subject, phonenumber, createdon FROM FilteredPhoneCall WHERE activityid = @phoneCallID

is about 4 times faster than the SDK's Retrieve method.

As far as I can tell, there is no way to get the Database server name from the Organization Service. You can retrieve it using the Deployment Service (see http://worldofdynamics.blogspot.com/2011/12/dynamics-crm-2011-c-code-for-retrieving.html), but that service is useless unless you are a deployment administrator - even for retrieval.

That I am aware of, the only way to view the database info is when you export a view / Advanced Find to a dynamic spreadsheet - the connection info is embedded in the Excel XML.

I developed a static method that, given an Org Url, will download a dynamic spreadsheet and parse out the connection string.

    /// <summary>
    /// Given a Dynamics CRM Org URL, retrieve the Database Connection string
    /// </summary>
    /// <param name="crmOrgUrlBase"></param>
    /// <returns></returns>
    private static string GetCrmDatabaseConnectionString(string crmOrgUrlBase)
    {
        string cleanurl = crmOrgUrlBase.ToLowerInvariant().Trim().Replace("/xrmservices/2011/organization.svc", "");
        {
            int mainPos = cleanurl.IndexOf("/main.aspx");
            if (mainPos > 0)
            {
                cleanurl = cleanurl.Substring(0, mainPos);
            }
        }

        string requestPayload = @"xdpi=96&exportType=list&useSqlQuery=1&fetchXml=%3Cfetch+distinct%3D%22false%22+no-lock%3D%22false%22+mapping%3D%22logical%22+page%3D%221%22+count%3D%2250%22+returntotalrecordcount%3D%22true%22%3E%3Centity+name%3D%22systemuser%22%3E%3Cattribute+name%3D%22systemuserid%22%2F%3E%3Cattribute+name%3D%22fullname%22%2F%3E%3Cattribute+name%3D%22fullname%22%2F%3E%3Corder+attribute%3D%22fullname%22+descending%3D%22false%22%2F%3E%3C%2Fentity%3E%3C%2Ffetch%3E%0D%0A&layoutXml=%3Cgrid+name%3D%22excelGrid%22+select%3D%220%22+icon%3D%220%22+preview%3D%220%22%3E%3Crow+name%3D%22result%22+id%3D%22systemuserid%22%3E%3Ccell+name%3D%22fullname%22+width%3D%22100%22%2F%3E%3C%2Frow%3E%3C%2Fgrid%3E%0D%0A";
        string url = cleanurl + "/_grid/print/export_live.aspx";

        string response = null;


        using (var wc = new System.Net.WebClient())
        {
            wc.UseDefaultCredentials = true;
            wc.Headers.Add("Accept-Encoding", "gzip, deflate");
            wc.Headers.Add("Content-Type", "application/x-www-form-urlencoded");
            wc.Headers.Add("DNT", "1");
            response = wc.UploadString(url, requestPayload);
        }


        var xe = System.Xml.Linq.XElement.Parse(response);


        System.Xml.Linq.XNamespace nn = "urn:schemas-microsoft-com:office:spreadsheet";
        System.Xml.Linq.XNamespace nn2 = "urn:schemas-microsoft-com:office:excel";


        var connectionEl = xe.Element(nn + "Worksheet").Element(nn2 + "QueryTable").Element(nn2 + "QuerySource").Element(nn2 + "Connection");

        string rawString = connectionEl.Value;

        Regex rex = new Regex("SERVER=([^;]+);DATABASE=([^;]+)", RegexOptions.Compiled);

        var m = rex.Match(rawString);

        string server = m.Groups[1].Value;
        string database = m.Groups[2].Value;

        return "DATA SOURCE=" + server +";INITIAL CATALOG=" + database + ";Integrated Security=SSPI";
    }

It uses a simple query against Users - the query doesn't really matter, because all we need is the database connection string.

My original blog post is here: http://www.shulerent.com/2015/01/19/get-the-sql-server-and-database-name-from-dynamics-crm/

I don't know of any ways to access that information from the SDK/WebServices, I usually save the connection string in a config file or project settings.

There are a lot of factors that could change depending on the installation setup:

• DB server name
• Installation type (on premise, IFD, etc.)
• DB authentication (Integrated, password, etc.)

However, if you were using Integrated security for an on premise deployment you could probably get away with creating the connection string on the fly...

string dbServerName = "SERVERNAME";
string orgName = getOrgNameFromDiscoveryService();

string connectionString = String.Format("Data Source={0};Initial Catalog={1}__MSCRM;Integrated Security=True", dbServerName, orgName);

//create sql connection