Tuesday, May 7, 2019

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: -


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


Here is the solution to fix above mentioned error: -

1- Download a more recent update of Sql server Native client driver: - 
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!

Monday, October 17, 2016

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 

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!

Wednesday, September 21, 2016

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:-


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:- 

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

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

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)  

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. 

Wednesday, September 3, 2014

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);

Wednesday, August 6, 2014

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

SharePoint Foundation 2010
SharePoint Server 2010 Enterprise Trial
SharePoint Server 2010 Enterprise
SharePoint Server 2010 Standard Trial
SharePoint Server 2010 Standard

Monday, July 14, 2014

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.


- Go to IIS Application server manager
- Choose IIS -> Authentication
- Edit Anonymous Authentication
- Change Anonymous User Identitiy to Application Pool Identity

hopefully this will work! 

Thursday, April 24, 2014

System.Security.SecurityException: The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security

Recently, i encountered an error after deploying a web application over IIS 7. It was working fine with IIS v6.0 with Windows Server 2003 but on IIS 7 it started giving an error "System.Security.SecurityException" on the line: EventLog.SourceExists(sourceName). Here is what i did to solve this issue:-

- Go to Registry Editor on Windows Server 2008

- Choose HKLM-> SYSTEM->CurrentControlSet->services->eventlog
- Grant Read permission on "Security" node to IIS AppPool\DefaultAppPool User

It will solve the issue of searching through event sources to find whether this source already exists or not.