Get the SQL Server and database name from Dynamics CRM

As far as I can tell, there is no way to retrieve the SQL Server name using the Organization Service. If you have the Organization Service URL, there really is no good way to get the database connection string.

(You can use the deployment service to get it, but that only works if you happen to be a deployment administrator)

No good way… but there is a way. When you download a dynamic spreadsheet, the database connection string is embedded in the xml. And you can programmatically download that spreadsheet using an entity that all CRM orgs have.

Using some fiddler experimentation, I was able to make a .NET method that will extract the database connection string, given the organization base url (https://crmserver/orgnam)

        /// <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 is technically unsupported to access export_live.aspx directly, so this may stop working in future releases of CRM.
I have tested this on CRM 2011 UR 11 and UR 17.

Leave a Reply