Archive
Login failed for user SQL Server, Error: 18456 – Troubleshoot steps
In my SQL server I have a Login account ‘sa’ with ‘SQL Server Authentication’.
Even though all the properties provided properly, I was unable to login with below login exception.
Troubleshooting steps:
SQL Server Authentication:
- Open ‘SQL Server’ properties window
- Make sure SQL server’s ‘Server Authentication’ set to ‘SQL and Windows Authentication mode’
- Restart the SQL Services if you change.
Enable Login for ‘sa’ account:
- ‘Login’ feature has to be enabled for ‘sa’ account
- Open the ‘sa’ account by double click.
- In the ‘Staus’ tab make sure ‘Login’ is enabled and set ‘Permission to connect to database engine’ to ‘Grant’
🙂
Host a SSRS report which gets data from external Data Source – CRM on-premise
We got a requirement to host a SSRS report in CRM, which gets data from external data source (i.e., Data Base from another SQL server).
CRM normally uses Shared Data Source when you upload any SSRS report which pulls data from CRM.
In our case we had to
- Create a new Data Source
- Override the CRM Shared Data Source with a new Data Source
Below are the steps.
Develop and Deploy Report in CRM
- Design the report with Data Source connected to external Database.
- Preview and make sure you are getting data properly.
- Save the .rdl
- Open CRM application
- Create a new report and upload the .rdl
Configure the Report’s DB Connection
Now we got report in CRM and we need to point the report to Custom DataBase by following steps below
- Connect to the SSRS server
- Open the browser and type (http://servername/Reports) (You can also get URL from “Reporting Services Configuration Manager à Report Manager URL” tab)
- In the Home page, click on ‘New Data Source’
- Provide the Connection String and Credentials and Save
- Go back to the report server home page.
- Open the folder by name (YourCRMORG_MSCRM)
- Go to ‘CustomeReports’ folder and select your report (Refer ‘Description’ column for Report Name)
- Choose Manage from context menu
- Choose ‘Data Sources’ tab and select newly created ‘Data Source’
- Click on ‘Apply’
- Close and re-run the report to get the changes.
🙂
Post XML over HTTP and capture the response – C#
Recently we got a requirement to post the data from ‘CRM Plug-in’ to an external API and capture response.
External API was built as XML over HTTP (i.e., Its not a SOAP based and no WSDL).
In this approach we post the Request XML to a URL and in return we will get the Response.
Refer below test client with Request and Response XML’s.
Request XML
Response XML
C# Code:
- In XML over HTTP we will post the Request XML to a URL and get the Response.
- So the below C# function accepts URL and RequestXML as Parameters and return the Response.
public static XmlDocument SubmitXmlRequest(string apiUrl, string reqXml){
XmlDocument xmlResponse = null;
HttpWebResponse httpWebResponse = null;
Stream requestStream = null;
Stream responseStream = null;// Create HttpWebRequest for the API URL.
var httpWebRequest = (HttpWebRequest)WebRequest.Create(apiUrl);try{
// Set HttpWebRequest properties
var bytes = System.Text.Encoding.ASCII.GetBytes(reqXml);
httpWebRequest.Method = “POST”;
httpWebRequest.ContentLength = bytes.Length;
httpWebRequest.ContentType = “text/xml; encoding=’utf-8′”;//Get Stream object
requestStream = httpWebRequest.GetRequestStream();
requestStream.Write(bytes, 0, bytes.Length);
requestStream.Close();// Post the Request.
httpWebResponse = (HttpWebResponse)httpWebRequest.GetResponse();// If the submission is success, Status Code would be OK
if (httpWebResponse.StatusCode == HttpStatusCode.OK){
// Read response
responseStream = httpWebResponse.GetResponseStream();if (responseStream != null){
var objXmlReader = new XmlTextReader(responseStream);// Convert Response stream to XML
var xmldoc = new XmlDocument();
xmldoc.Load(objXmlReader);
xmlResponse = xmldoc;
objXmlReader.Close();
}
}// Close Response
httpWebResponse.Close();
}
catch (WebException webException)
{
throw new Exception(webException.Message);
}
catch (Exception exception)
{
throw new Exception(exception.Message);
}
finally
{
// Release connections
if (requestStream != null){
requestStream.Close();
}if (responseStream != null){
responseStream.Close();
}if (httpWebResponse != null){
httpWebResponse.Close();
}
}// Return API Response
return xmlResponse;
}
Capture and Read the Response
XmlDocument response = SubmitXmlRequest(uri, xmlString);
Console.WriteLine(“Inner Text – ” + response.InnerText);
Console.WriteLine(“Inner XML – ” + response.InnerXml);
🙂
CRM 2016 Web API – Impersonate User
In CRM, Impersonation is possible in Plug-ins (using ‘Run As’ while registering Plug-in step) or using ‘CallerId’ property while instantiating OrganizationServiceProxy.
What if I have to impersonate ‘Retrive’ operation from Jscript? This was not possible.
With advent of CRM Web API with CRM 2016 we can impersonate in Jscript.
What is a Web API :
- The Web API implements the OData (Open Data Protocol), version 4.0.
- The new Web API provides better capabilities over the OData service introduced with CRM 2011 and OData service will be deprecating with this release.
- It provides a modern, RESTful web service you can use to interact with data in CRM using a wide variety of platforms, programming languages and devices
- The Web API will provide parity with the existing organization service (SOAP endpoint).
- You can perform all operations using HTTP requests with the Web API located at [organization uri]/api/data/v8.0/
Impersonate another User using Web API:
- To impersonate a user, add a request header named MSCRMCallerID with a GUID value equal to the impersonated user’s systemuserid before sending the request to the web service.
Sample Script with impersonation to create an Account Record
function createAccount() {
var clientURL = Xrm.Page.context.getClientUrl();
var impersonateUserId = “7eb682f1-ca75-e511-80d4-00155d2a68d1”;// GUID
var req = new XMLHttpRequest()
req.open(“POST”, encodeURI(clientURL + “/api/data/v8.0/accounts”), true);
req.setRequestHeader(“Accept”, “application/json”);
req.setRequestHeader(“Content-Type”, “application/json; charset=utf-8”);
req.setRequestHeader(“OData-MaxVersion”, “4.0”);
req.setRequestHeader(“OData-Version”, “4.0”);
// Set the Caller ID; GUID of User
req.setRequestHeader(“MSCRMCallerID”, impersonateUserId);
req.onreadystatechange = function () {
if (this.readyState == 4 /* complete */) {
req.onreadystatechange = null;
if (this.status == 204) {
var accountUri = this.getResponseHeader(“OData-EntityId”);
alert(“New account created; URI: ” + accountUri)
}
else {
var error = JSON.parse(this.response).error;
alert(“Error creating Account – ” + error.message);
}
}
};// Set Account record properties
req.send(JSON.stringify({ name: “Rajeev Pentyala”, description: “Account created using Web API”, revenue: 5000000 }));
}
🙂