SQL Server Exception!! This time with a Java application though

As a systems integration specialist, i have been working with various enterprise scale application and sometimes we do end up integrating to legacy systems to either pull data that can be ingested later into the cloud based system (SAAS application).

In todays' article i am going to explain the problem i faced while integrating to a legacy Sql server database restored over a more recent version of Sql server resulting in database connection failure and how we overcome that particular connectivity issue without rolling back to an old version of Sql Server. 

To start with, here is the error message encountered when we tried to connect to Sql Server 2022 using one of the inhouse JAVA application: -

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host <hostname excluded>, port 1433 has failed.

Further stack trace revealed this: -

Error: "Connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

First thing that comes to mind here is to cross check if there are any firewalls blocking the connectivity and that thought quickly vanishes as i was able to connect via SSMS (Sql Server management studio)

Second thought was to cross check Sql Server Native Client configuration since the JAVA application was relying on native client libraries to connect to the instance of Sql Server.

so we opened Sql Server 2022 configuration manager and navigated to SQL Native Client 11.0 configuration which was empty considering the fact that Microsoft has removed this from Sql Server 2022: -

To resolve this, you may manually install the latest servicing release of native client from here: -

https://www.microsoft.com/en-us/download/details.aspx?id=50402

Once installed, you should be able to see the client protocols available under configuration manager: -

SSL Security error [DBNETLIB]ConnectionOpen.... No expired/invalid certificates. what else?

Hello folks,
Recently, i got into an issue where i was tasked to connect to Sql server 2014 via 64-bit ODBC connectivity. 
so i simply dialed into the windows server and tried to create a DSN using Sql server native client components as the below screenshot depicts: -

....and the connection test resulted in the following error: -

Error

Connection failed:

SQLState : '01000'

SQL Server Error: 771

[Microsoft][ODBC SQL Server Driver][TCP/IP  Sockets]ConnectionOpen(SECDoClientHandshake()).

Connection failed:

SQLState : '08001'

SQL Server Error: 18

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SSL Security error

Solution

Here is the solution to fix above mentioned error: -

1- Download a more recent update of Sql server Native client driver: - 

https://www.microsoft.com/en-us/download/details.aspx?id=50402

2 - Install the driver and update the existing version

It seems Sql Server used by the customer has TLS 1.2 forced on connection requests and that resulted in the SSL error

- Sql Server 2014 CU 2 build version used by the customer: 12.0.4422.0

- Sql Server Native Client version that fixed the issue:  11.0.7001.0

hope it helps!

A connection cannot be made. Ensure that the server is running. Sql Server Analysis Services. ADOMD client

Error again on an application that worked before!!! Aaah! this is what we face every other day. in the tech world. 

Without going into details of those errors, let's focus on an error message as follows:- 

"A connection cannot be made. Ensure that the server is running." 

and then the inner exception message 

INNER EXCEPTION MESSAGE

===========================

Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host.

so yes i am talking about a .Net application trying to connect to SSAS (Sql server Analysis services) in order to process a cube.

so the cube was already there, and it gets processed every night smoothly but how come if i try to process the cube manually via the application, it results in an error! 

well, looking at the error it gives you a feel that the following might have happened 

•  Analysis services aren't running
• OR it has something to do with the Sql browser service 
• OR some network restriction

hmmm ... Not any of above! then you can follow the remaining post :) 

The issue was that the user we used to launch the application doesn't have rights on SSAS. You need to launch your .Net application with a user account having Administrator rights on SSAS ( Shift - Right click-> Run as User)

here is how to do assign rights in SSAS: - 

• Start SSMS
• Connect Analysis services
• Right click on the server name and click properties
• As shown below, add the desired user

hope that helps!

As a data integration specialist, i have been writing sql scripts to fetch data from one database and load those records in another database; ofcourse after some manipulation. 
Ah! when it involves datetime stored in a string format in the source table then it can be problematic for you to manipulate it correctly.
I am showing a sample (ofcourse just the datetime stuff) here to understand better:-

Sample

create table SourceData( offered_date nvarchar(30) )

insert into SourceData values('2016-09-20 15:59:29')
insert into SourceData values('2016-09-20 15:59:40')
insert into SourceData values('2016-09-20 15:59:30')

select *from SourceData
Here is how the output look like:- 

offered_date
2016-09-20 15:59:29
2016-09-20 15:59:40
2016-09-20 15:59:30

Scenario
My task was to get this data aggregated on an hourly basis. So i simply converted the string value in a datetime and got the hour value.
precisely as follows:-

select datepart(hour, convert(smalldatetime,offered_date)) Offered_Hour,*
from SourceData

Offered_Hour    offered_date
15    2016-09-20 15:59:29
16    2016-09-20 15:59:40
16    2016-09-20 15:59:30

Issue
Here is the twist! first record is correct but the next two records are wrong! 
so the conversion function to smalldatetime has rounded the seconds value to the minute and this has caused 15:59:30 to appear as 16:00:00
SmallDatetime gives precision to a minute level only (rounding off seconds value)  

Solution
so i ended up using datetime2 datatype instead of smalldatetime that gives you the exact value after conversion. 

Here is how it looks like now:-
select datepart(hour, convert(datetime2,offered_date)) Offered_Hour,*
from SourceData

Offered_Hour    offered_date
15    2016-09-20 15:59:29
15    2016-09-20 15:59:40
15    2016-09-20 15:59:30

Yes Yes now it gives the correct hour value!! 
Starting with Sql server 2008 database developers are getting familiar with the datetime2.
Clearly, datetime2 is an upgrade of smalldatetime when it comes to precision. 

Hello folks!

I am sharing two pieces of code that i have recently used while using DevExpress GridView control for Microsoft Visual Studio. Let me appreciate DevExpress in general for making developers life easier by providing very powerful controls for Visual Studio.

Let me quickly come back to the scenario

We have a Gridview control and we need to perform some operation in the code behind and once we are done with code behind stuff then call some Javascript thing.

To carryout this, do the following steps:-

Step 1: Create a custom button under ASPxGridView Command column

Step 2: Generate an event on custom button call back as follows:-

//Code behind on custom button call back
protected void grd_Memos_CustomButtonCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomButtonCallbackEventArgs e)
 {
DevExpress.Web.ASPxGridView.ASPxGridView gridView = (DevExpress.Web.ASPxGridView.ASPxGridView)sender;
var Mem_Id = grd_Memos.GetRowValues(e.VisibleIndex, "MEM_ID");
gridView.JSProperties["WindowUrl"] = string.Format("ViewMemoReceipts.aspx?MEM_ID={0}",Mem_Id);
 }

Step 3: Go to ASPxGridView Tasks and choose Client-Side Events and add the code in the EventHandler body of Endcallback as follows:-
function(s, e) {
if (s.WindowUrl != null) window.open(s.WindowUrl);
}

How to find Sharepoint edition installed on server?

A quick tip to get sharepoint version using Power Shell

• Login to server with a user having farm administration rights
• Run powershell with admin rights
• Type the following:- asnp Microsoft.Sharepoint.Powershell and hit Enter
• Type the following:- (Get-SPFarm).products 
• OR
• Type the following:- Get-SPFarm| Select products
• And you will get a list of GUIDs

I am copying list of GUIDs for reference.In my case it is Microsoft Sharpoint Enterprise. Sharepoint foundation is always shown with Enterprise

BEED1F75-C398-4447-AEF1-E66E1F0DF91E	SharePoint Foundation 2010
88BED06D-8C6B-4E62-AB01-546D6005FE97	SharePoint Server 2010 Enterprise Trial
D5595F62-449B-4061-B0B2-0CBAD410BB51	SharePoint Server 2010 Enterprise
B2C0B444-3914-4ACB-A0B8-7CF50A8F7AA0	SharePoint Server 2010 Standard Trial
3FDFBCC8-B3E4-4482-91FA-122C6432805C	SharePoint Server 2010 Standard

HTTP Error 401.3 - Unauthorized You do not have permission to view this directory or page because of the access control list (ACL) configuration or encryption settings for this resource on the Web server

Recently i encountered this issue while deploying an asp.net solution on Windows Server 2008 R2. 

Looking at the error, i was quite sure it has something to do with website permissions. 

By default IIS treats anonymous authentication credentials to Specific User and if that specific user doesn't have permission on wwwroot folder then IIS will throw HTTP 401.3.

Solution

- Go to IIS Application server manager

- Choose IIS -> Authentication

- Edit Anonymous Authentication

- Change Anonymous User Identitiy to Application Pool Identity

hopefully this will work!