Thursday, January 30, 2020

Never Leave Connection Open


Today, we hit upon an issue where our App Service did intermittently hang up. We checked the performance level attributes like Memory and CPU which were on ordinary levels and so as Application Insights which did not highlight any major exceptions apart from the routine ones. What really helped us narrow down the problem is the Azure App Service’s Diagnose and Solve Problem. We ran a few quarries on “Web App Down” and “Web App Slow” and it eventually pointed out that Multiple sessions were trying to call a new connection, every time it initiated. Further, we narrowed down data, saw that one particular function which updates real-time revenue constantly was the culprit for the error; It was a SQLDataReader which was not closing up its connection and subsequently, multiple instances got created, leaving with large amounts of connections piling up and eventually falling victim for time-outs. We also noticed that there were 114 active connections which were way too big to have. Furthermore, we also saw more unique sessions and users compared to ordinary days which is also another reason to this error to prop up other than on earlier days.
After resolving the issue, we were able to bring down the active connection to 21 and the Web app to perform well.

I fixed the issue by bringing the following piece of code in our Model

datareader = Sqlcmd.ExecuteReader(CommandBehavior.CloseConnection);

and made sure that datareader.close() was called after the operation is finished.

What we used for this operation

  1. Executing Azure SQL Server’s system Stored Proc to getting active connection: exec sp_help and exec sp_help2
  2. Azure App Service’s Diagnose and solve Problems function; Select Health Check under Availability and Performance

a.       Under this Select Web App Down
b.       Then Select Slow Request Stack Tracking
c.       Which leads us to the Stack Traces.

Source: https://stackoverflow.com/questions/20469899/c-sharp-invalid-attempt-to-call-read-when-reader-is-closed/20470976?noredirect=1#comment106092515_20470976

Tuesday, July 17, 2018

Migrating Data from Oracle to SQL Server

We were indulged in an another challenge during our cover of Azure. We had used the App Service (A platform as service) to host our Apps and Database, then we used the Logic Apps to automate process now we are laid with another challenge of  migrating our archive data, from an Obsolete Oracle 9i to the cloud.

Challenges
  1. Database was more than 12Gigs and had Big Tables (Some went to extent of having more than 10 million records)
  2. Ensure what Source had is what the destination got
  3. Network interruptions

What did we use

Microsoft came to assist us and they offered us to use the SQL Server Migration Assistant 7.8 for this task.

Observation

Although its not a successful task, but we were able to fix issues manually and migrated the data. Manual task went to the extent of even removing constraints, then resolve the issue and finally recreate them. This issue did haunt us the most. For integrity we had to ensure the Row count is equal in Source and Destination.



Thursday, September 14, 2017

Restoring Data in Azure Sql Server

I successfully restored my data after I have accidently updated it incorrectly. For the restoration process I referred the following article:

The Super Article Here

You would require SQL Database Migration Wizard. However its stored in Codeplex which in due course will be shutdown. I have attached the File for convenience.

SQL Database Migration Wizard


Wednesday, May 24, 2017

Digest Authentication with RestSharp


This original source of the content was available at www.ifjeffcandoit.com, but ironically the website was not operational by the time of writing. This article helped me a lot in solving the trouble I had in Digest Authentication. Since I could not reach author for permission, for the benefit others, I did a copy and paste of the Cached version.


The orginal content goes as:

The Situation

Recently I worked on a project that involved integration between a data center and a 3rd party via a RESTful API. For securing the service, the 3rd party had used:
  • SSL-Encryption
  • Digest Authentication
I did some research for REST clients that would work with .NET and decided that RestSharp was a good option for our purposes.

The problem

Out of the box, Restsharp does not support Digest authentication but it allows for you to write your own implementation of IAuthenticator. The Internet was my friend again as I found this link which gave the simplest implementation of digest authentication for Restsharp imaginable. These few lines of code worked for my initial tests.

BUT… when I needed to integrate querystring values into a GET call, the authentication would fail.
The reason for the failure was because hash being included in the Authentication portion of the digest authentication within Restsharp did not include the the querystring.
For example:

In this URI http://example.com/authors?subject=princesses only the “http://example.com/authors” was included in the authentication header (I used fiddler to glean the values).

How to get around this?

Some more research showed that I wasn’t the only one who had come across this problem. The most helpful link I found was here: http://stackoverflow.com/questions/3109507/httpwebrequests-sends-parameterless-uri-in-authorization-header.

The comments by Andomar and Gerfboy were what lead me to my “solution”.
Andomar posted the C# code for DigestAuthFixer which I slightly modified to include Gerfboy’s changes.

Gerfboy pointed out that the “opaque” value was required – the 3rd party’s server was sending this value as part of the auth response. Sending this value back in my request was required.

One other change I had to make was an “if” in my authentication method. I only wanted to do this “fix” if it was a GET – the post/put actions would fail otherwise. I’m assuming these failed because my “fixer” was doing a GET, and not matching the action of the call.

Below is the code I needed to add to my project to get Digest Authentication to work with RestSharp.

// The fix was found here:
// http://stackoverflow.com/questions/3109507/httpwebrequests-sends-parameterless-uri-in-authorization-header
// I needed to call the service directly to get the nonce and opaque values.  Once I could get those
// the header could be built and sent with the request.

public class DigestAuthenticator : IAuthenticator
{
private readonly string _user;
private readonly string _pass;

public DigestAuthenticator(string user, string pass)
{
_user = user;
_pass = pass;
}

public void Authenticate(IRestClient client, IRestRequest request)
{
request.Credentials = new NetworkCredential(_user, _pass);

// TODO: Figure out how to remove the if.. currently PUT does not work if the DigestAuthFixer is in place
if (request.Method == Method.GET)
{
var url = client.BuildUri(request).ToString();
var uri = new Uri(url);

var digestAuthFixer = new DigestAuthFixer(client.BaseUrl, _user, _pass);
digestAuthFixer.GrabResponse(uri.PathAndQuery);
var digestHeader = digestAuthFixer.GetDigestHeader(uri.PathAndQuery);
request.AddParameter("Authorization", digestHeader, ParameterType.HttpHeader);
}

}
}

public class DigestAuthFixer
{
private static string _host;
private static string _user;
private static string _password;
private static string _realm;
private static string _nonce;
private static string _qop;
private static string _cnonce;
private static string _opaque;
private static DateTime _cnonceDate;
private static int _nc;

public DigestAuthFixer(string host, string user, string password)
{
_host = host;
_user = user;
_password = password;
}

private string CalculateMd5Hash(
string input)
{
var inputBytes = Encoding.ASCII.GetBytes(input);
var hash = MD5.Create().ComputeHash(inputBytes);
var sb = new StringBuilder();
foreach (var b in hash)
sb.Append(b.ToString("x2"));
return sb.ToString();
}

private string GrabHeaderVar(
string varName,
string header)
{
var regHeader = new Regex(string.Format(@"{0}=""([^""]*)""", varName));
var matchHeader = regHeader.Match(header);
if (matchHeader.Success)
return matchHeader.Groups[1].Value;
throw new ApplicationException(string.Format("Header {0} not found", varName));
}

public string GetDigestHeader(
string dir)
{
_nc = _nc + 1;

var ha1 = CalculateMd5Hash(string.Format("{0}:{1}:{2}", _user, _realm, _password));
var ha2 = CalculateMd5Hash(string.Format("{0}:{1}", "GET", dir));
var digestResponse =
CalculateMd5Hash(string.Format("{0}:{1}:{2:00000000}:{3}:{4}:{5}", ha1, _nonce, _nc, _cnonce, _qop, ha2));

return string.Format("Digest username=\"{0}\", realm=\"{1}\", nonce=\"{2}\", uri=\"{3}\", " +
"algorithm=MD5, response=\"{4}\", opaque=\"{8}\", qop={5}, nc={6:00000000}, cnonce=\"{7}\"",
_user, _realm, _nonce, dir, digestResponse, _qop, _nc, _cnonce, _opaque);
}

public void GrabResponse(
string dir)
{
var url = _host + dir;
var uri = new Uri(url);

var request = (HttpWebRequest)WebRequest.Create(uri);

// If we've got a recent Auth header, re-use it!
if (!string.IsNullOrEmpty(_cnonce) &&
DateTime.Now.Subtract(_cnonceDate).TotalHours < 1.0)
{
request.Headers.Add("Authorization", GetDigestHeader(dir));
}

HttpWebResponse response;
try
{
response = (HttpWebResponse)request.GetResponse();
}
catch (WebException ex)
{
// Try to fix a 401 exception by adding a Authorization header
if (ex.Response == null || ((HttpWebResponse)ex.Response).StatusCode != HttpStatusCode.Unauthorized)
throw;

var wwwAuthenticateHeader = ex.Response.Headers["WWW-Authenticate"];
_realm = GrabHeaderVar("realm", wwwAuthenticateHeader);
_nonce = GrabHeaderVar("nonce", wwwAuthenticateHeader);
_qop = GrabHeaderVar("qop", wwwAuthenticateHeader);

_nc = 0;
_opaque = GrabHeaderVar("opaque", wwwAuthenticateHeader);
_cnonce = new Random().Next(123400, 9999999).ToString(CultureInfo.InvariantCulture);
_cnonceDate = DateTime.Now;
}

}
}

Client Code:

public class WebClientProxy
{
private readonly string _url;
private readonly string _userId;
private readonly string _password;
private readonly int _merchantId;

public WebClientProxy(string url, string userId, string password, int merchantId)
{
_url = url;
_userId = userId;
_password = password;
_merchantId = merchantId;
}

public T Execute<T>(RestRequest request) where T : new()
{
var client = new RestClient
{
BaseUrl = _url,
Authenticator = new DigestAuthenticator(_userId, _password),

};

var response = client.Execute<T>(request);

if (response.ErrorException != null)
{
throw response.ErrorException;
}
return response.Data;
}

public Somethings SomethingGetAllValuesSince(DateTime sinceDate)
{
var request = new RestRequest
{
Resource = "something/{SomethingId}/accounts",
RequestFormat = DataFormat.Json,
Method = method
};
request.AddParameter("SomethingId", _somethingId, ParameterType.UrlSegment);
request.AddParameter("since", sinceDateValue, ParameterType.GetOrPost);
return Execute<Somethings>(request);
}

}

Cached version of the content can be seen here: http://webcache.googleusercontent.com/search?q=cache:Sm-c6n7aGN8J:www.ifjeffcandoit.com/2013/05/16/digest-authentication-with-restsharp/+&cd=1&hl=en&ct=clnk&gl=lk

Thursday, April 27, 2017

Friday, February 21, 2014

Deploying ClickOnce™ Project without Microsoft Visual Studio


Environment Used:


 

What you will need:


  • Visual Studio for Build the project; For developer environment
  • MageUI.exe Graphical Tool (You need to install the latest Windows SDK) for Deployment environment

 
MageUI.exe is available under C:\Program Files\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools

 

Steps:


  1. In Visual Studio, go to the Project Properties > Application Tab > Under Resources > Select “Create application without Manifest” from the drop down
  2. Build your project.
  3. [1]Follow the steps from the MSDN’s walkthrough: Manually Deploying a ClickOnce Application > To Deploy an application with MageUI.exe graphical tool

Things MageUI will not be creating, but created in Visual Studio Publish Wizard are:

  • Setup.exe
  • Publish Web Page

 

Resources:



 

[1] In the Clause 3 under To deploy an application with the MageUI.exe graphical tool: Copy all the files in Project’s bin folder to deployment directory.