ORA-01017: invalid username/password; logon denied - From Java Application

Able to login to Oracle DB successfully using TOAD for Oracle Client where as from your Java application it is not connecting to the DB and giving "ORA-01017: invalid username/password; logon denied" error?

Able to connect to Oracle DB using a connection string which has different user name and password (which was created months ago)? Where as the same connection string with the new user name and password gives "ORA-01017: invalid username/password; logon denied" error while both the usernames and passwords are successfully connecting using TOAD for Oracle Client?

Well, your OJDBC Driver could be playing the trick. If the version supported by the OJDBC drivevr(.Jar) and the Oracle DB Version are different, this error can be thrown.

Get your driver upgraded. 

Connecting to an Azure Cloud Hosted SQL DB from Visual Studio 2017

Connecting to an Azure Cloud Hosted SQL DB from Visual Studio 2017 is as simple as this.

You must first get your SQL Server DB created in Azure Portal (You must have an Azure Subscription for this.)

If you dont have an Azure Subscription yet, get one here.

Go to SQL Databases in the left hand pane, click proceed and create a SQL DB. You will have to add a server (SQL Server) while doing this.

 Note down the Server Admin Login Credentials of SQL Server for further use.

Once done, Go to VS 2017 and open SQL Server Object Explorer.
Click "Add Server" icon highlighted in yellow.

Following highlighted default values will shown. Get those changed as per the next screenshot.

Change the Authentication to SQL Server Authentication. Provide the credentials of the server you had mentioned in Azure Portal.

All the available databases available in the server will get loaded in the Database Name dropdown.
Select the DB you wanted and Click Connect.
That simple it is.

Converting rows to columns in SQL

Converting columns into rows will be a usual scenario when we deal with databases. Here we can see a basic example of displaying the data stored in row as column.

Following is the table with Employee Id mapped to Transfer Id. This table stores only the employee id and transfer id.

Transfer Table

Transfer ID references employee transfer details of a detailed table given below. The detailed table (Approver Table) may or may not have approver employee id (APPRVR) for every transfer id in the Approver Table

Approver Table

The Approver Employee Ids (APPRVR) has to be displayed column wise. If there is no Approver Employee Id, it has to be marked as NULL.

EMP ID	TRNSFR ID	1STAPPRVR	2NDAPPRVR	3RDAPPRVR

Check the below query and see how it works.
--DROP #TEMP

CREATE  TABLE #TEMP(ID INT, TRNSFR_ID INT, [1STAPPRVR] INT, [2NDAPPRVR] INT, [3RDAPPRVR] INT)

INSERT INTO #TEMP

SELECT ID, TRNSFR_ID,

CASE WHEN RNO=1 THEN APPRVR END AS '1STAPPRVR' ,

CASE WHEN RNO=2 THEN APPRVR END AS '2NDAPPRVR' ,

CASE WHEN RNO=3 THEN APPRVR END AS '3RDAPPRVR' FROM (

SELECT

* FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY TRNSFR_ID ORDER BY TRNSFR_ID) AS 'RNO', * FROM APPROVER_TBL

) A

) B

--SELECT * FROM #TEMP

SELECT TRNSFR_ID,MAX([1STAPPRVR]) AS '1STAPPRVR', MAX([2NDAPPRVR]) AS '2NDAPPRVR', MAX([3RDAPPRVR]) AS '3RDAPPRVR' FROM #TEMP  AS C

GROUP BY C.TRNSFR_ID

The above query results as the below screen

This can be Joined with Transfer Table and taken employee wise approver numbers.

There are lot other ways to do the same. The following is just an alternative.

-- START

SELECT X.ID, X.TRNSFR_ID, X.APP_1, Y.APP_2 FROM (

SELECT ID, TRNSFR_ID, APPRVR as 'APP_1' FROM (

SELECT

* FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY TRNSFR_ID ORDER BY TRNSFR_ID) AS 'RNO', * FROM APPROVER_TBL

) A

WHERE A.RNO = 1) B

 )AS X LEFT JOIN (

SELECT ID, TRNSFR_ID, APPRVR as 'APP_2' FROM (

SELECT

* FROM (

SELECT ROW_NUMBER() OVER (PARTITION BY TRNSFR_ID ORDER BY TRNSFR_ID) AS 'RNO', * FROM APPROVER_TBL

) C

WHERE C.RNO = 2) D

) AS Y  ON X.TRNSFR_ID = Y.TRNSFR_ID

--END

I have added only 1st and 2nd approver here. This way the same query can be extended to display the 3rd approver also.

Rather than doing inner join with derived tables, it can be inserted in a temp tables with the same way to get it not complicated and do join.

Generating Excel Report with Microsoft.Office.Interop.Excel.dll and C#

I was developing a page which generated Excel report from code behind and the excel report to be password protected and to be maied to the user group to which the user who clicked on report generation belongs to. I had added a reference to Microsoft Excel 12.0 Object Library under the COM tab when I did the "Add Reference".

The server machine (production server) where I deployed the application/page did not have MS Office or Excel installed. The report generation was not happening. I was getting the following error:

Retrieving the COM class factory for component with CLSID {00020820-0000-0000-C000-000000000046} failed due to the follo
wing error: 80040154.

It took little time for me to identify that it is just because the excel is not installed on the server. I took the Interop.Excel.dll dll from the GAC of my development machine to the BIN folder of the prodiction server. But this also didnt help. I was stuck with the same error. Realised that there is no other go unless I install Excel or Office on the production server.

You need to have Microsoft Excel installed in order to have access to the Introp libraries that you need to add as references to your project in order to make it work.

To use the features of a Microsoft Office application, we need to use the primary interop assembly (PIA) for the application. The primary interop assembly enables managed code to interact with a Microsoft Office application's COM-based object model.
To apply PIA that can obtain the reference to interop with excel in your program, you have to install Excel.

Here is a nice option of generating excel file without having MS office/MS excel installed on the server, by Carlos Aguilar Mares
http://www.carlosag.net/Tools/ExcelXmlWriter/
BLOG:
http://blogs.msdn.com/b/carlosag/archive/2005/08/27/whyiwroteexcelxmlwriter.aspx

Securing an ASP.Net WebService

Implementing authentication for Webservice is quite simple. This is one of the several methods in implementing security for WebServices.

The WebService you created should have a custom SOAPHeader. We pass the credentials with  SOAPHeader to the server during the WebService calls and get it validated on server. The response of the WebMethod will be sent back only if the user is authenticated.

We will have to include the custom SOAPHeader that we want to include in the SOAP message.
For this, we will have to write a separate class(UserCredentials) which inherits from [System.Web.Services.Protocols.SoapHeader] with two properties. (UserName and Password)

My WebService to be hosted on a remote Server

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(Name = "KochiService", ConformsTo = WsiProfiles.BasicProfile1_1)]

public class KochiService : System.Web.Services.WebService {

public UserCredentials Credentials;
public KochiService()
{
//Uncomment the following line if using designed components
//InitializeComponent();
}

[WebMethod]
[SoapDocumentMethod(Binding = "KochiService")]
[SoapHeader("Credentials", Required= true)]
public string GetEmployeeSalary() {
if (AuthenticateUser())
{
   return "This user's Salary is 10,000";
}
else
{
    return "You are not authorised to consume this service";
}
}
private bool AuthenticateUser()
{
if ((Credentials.UserName == "Sabin") && (Credentials.Password == "1234"))
{
   return true;
}
else
{
   return false;
} 
} 
}

For Security, Custom SOAP Header Class; User Credentials

using System;
using System.Collections.Generic;
using System.Web;

public class UserCredentials : System.Web.Services.Protocols.SoapHeader
{
    public string UserName;
    public string Password;
}


HOSTING THE WEB SERVICE.
Build the solution and deploy the WebService files on remote server.
.ASMX on the root of the Website or Virtual Directory and the .CS file in the App_Code of the ASP.Net Website.

Also make sure that the UserCredential class got deployed on the server under App_Code.
After hosting you will be able to view the details of the webservice by accessing the .ASMX page as www.MyWebsite.com/KochiService.asmx




Now, your WebService is available online for all the users. But only those pass the exact Credentials will ONLY be able to get the response from the remote machine(WebService).



CONSUMING THE WEBSERVICE

Those who have the correct Credentials for the WebService can access the service from anywhere in  a .Net Website over the internet. For accessing the WebService, we need to add the service through the ServiceReferance by right clicking on the WebSite Project in Visual Studio. While adding, it creates a proxy to call the service from the solution. 


Calling WebService from .aspx Page remotely

using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class WebServiceCalls : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
MyServiceReference.KochiServiceClient oService = new MyServiceReference.KochiServiceClient();
MyServiceReference.UserCredentials oCredentials = new MyServiceReference.UserCredentials();

oCredentials.UserName = "Sabin";
oCredentials.Password = "1234";

Response.Write(oService.GetEmployeeSalary(oCredentials));

}
}

The service will return the response based on the authentication as below



Sending XML to Stored Procedure

The need of sending a series of strings to Stored Procedure will be there in all most all the projects. XML variables in SQL Server make it easy to deal with XML strings into relational databases. The new methods we should use are value() and nodes() which allow us to select values from XML documents.



DECLARE @Employees xml
SET @Employees = '1908210174'

SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @Employees.nodes('/Employees/id') as ParamValues(ID)



The above SQL statements returns three rows as below:
1908
2101
74

Now, let us see how this can be used to fetch the Employee information for a list of Employee Ids. Take a look at the Stored Procedure.

CREATE PROCEDURE GetEmployeesDetailsForThisList(@EmployeeIds xml) AS
DECLARE @Employees TABLE (ID int)

INSERT INTO @Employees (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @EmployeeIds.nodes('/Employees/id') as ParamValues(ID)

SELECT * FROM
    EmployeeTable
INNER JOIN 
    @EmployeeIds e
ON    EmployeeTable.ID = e.ID

This Stored Procedure can be called as

EXEC GetEmployeesDetailsForThisList '1908210174'





XML public static string BuildEmployeesXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();

    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("{0}", values[i]);
    }
    xmlString.AppendFormat("", xmlRootName);
    return xmlString.ToString();
}

This above ASP.NET method will return XML String, and this can be sent as the input parameter to Stored Procedure

Amazon's trouble raises cloud computing doubts

Just posting the content of EconomicTimes

The black out at Amazon's EC2 (Elastic Cloud Computing) data centre has cast a shadow over cloud computing, which has been hailed as a sturdy, reliable and inexpensive storage and network solution, especially for small and medium enterprises (SMEs) that cannot afford their own large servers.

On the early morning of April 21 (Pacific Day Time), Amazon's EC2 data centre in Virginia crashed, taking down with it several popular websites and small businesses that depend on it. These included favoured social networking destinations like Evite, Quora, Reddit and Foursquare, among others. Now, the question is being asked: if an Amazonian cloud giant can crash so badly, what about the rest? Is cloud computing as reliable as we thought?

"People will now realise that cloud isn't magic like they earlier thought it was," says Lydia Leong, research vice-president and cloud computing expert at technology research and advisory firm Gartner . "They will now realise that cloud is merely about viability and not about continuous availability."

But that's exactly the kind of marketing pitch that sold cloud computing to many small businesses, including the ever-increasing social networking bandwagon. SMEs are now graduating to the next level of cloud computing, using it not just for storage, but also for active computing purposes like communication, sustaining remote workforces and deploying cloud services like remote IT help, cloud operating systems , and so on. The impact of such an outage, therefore, is felt even more.

Online businesses affected by the EC2 outage lost that many hours of ad revenues, business opportunities and drops of the precious trust of many loyal followers, a primary pillar of social networking. The losses are hard to quantify.

"Since Amazon isn't giving a customer list, we can only guess. From what we know, it's probably in millions of dollars," says computer scientist David Alan Grier of the eminent Institute of Electrical and Electronics Engineers ( IEEE )) Society and author of When Computers were Human and Too Soon to Tell: Essays for the End of the Computer Revolution . "The biggest problem, though, could be the loss of confidence in cloud computing. We still don't know why the Virginia data centre failed."

The EC2 holds incredibly valuable data of Amazon's cloud client companies. And yet, Amazon's Virginia centre is, according to sources, remarkably open and vulnerable, located in an ordinary industrial building near Dulles Airport.

Although Amazon will probably recover quickly, the event has damaged its credibility. Time will tell how badly. "If Amazon can explain the problem and make a good case for why the damage may not be big, then it will be fine," says Grier. "If not, the work will go elsewhere. Amazon may be a big player, but there are other big players waiting to step into the game." These include the likes of Google , IBM , Cisco, RedHat and Microsoft (whose cloud ads are all over Silicon Valley), to name a few.

"Amazon's cloud competitors are likely to use this outage as a marketing tool. But it could have happened to anybody," says an ex-HP cloud veteran who currently works at one of Silicon Valley's most promising cloud start-ups, which recently got acquired.