Category: How To | Page 25

How to Fix Access Denied (401) Error in Microsoft IIS

After migrating a web application to a new server, we encountered this server error:

401 - Unauthorized: Access is denied due to invalid credentials. You do not have permision to view this directory or page using the credentials that you supplied.

This happened despite the fact the user is already authenticated via Active Directory.

There can be many causes to Access Denied error, but if you think you’ve already configured everything correctly from your ASP.NET application, there might be a little detail that’s forgotten. Make sure you give the proper permission to Authenticated Users to access your web application directory.

Right-click on the directory where the web application is stored and select Properties and click on Security tab.

Click on Edit…, then Add… button. Type in Authenticated Users in the Enter the object names to select.

Click OK and you should see Authenticated Users as one of the user names. Give proper permissions on the Permissions for Authenticated Users box on the lower end if they’re not checked already.

Click OK twice to close the dialog box. It should take effect immediately, but if you want to be sure, you can restart IIS for your web application.

Refresh your browser and it should display the web page now.

Linked SQL Server Table Shows #Deleted in MS Access

When linking a SQL Server table (SQL Server 2008 R2) via ODBC, I encountered a major error where all cells in the table display #Deleted when opened from Microsoft Access 2010 (14.0.6123.5001).

The problem turns out to be that Microsoft Jet Database Engine maps bigint datatype to binary. One way to fix this is to clone the table and change the datatype from bigint to either int or (n)varchar. Changing the datatype in the original table just for this purpose is definitely not recommended.

If the table is used only as a reference then the solution is much simpler since it’s not necessary to edit or create new records. Instead of linking the table directly from MS Access, we can create a view that converts the datatype from bigint to a string (nvarchar) and link the view instead.

Create a plain view that selects all fields from the table.

CREATE VIEW dbo.vApplications
AS
SELECT CONVERT(NVARCHAR(25), Id) AS Id
      ,Employee
      ,EmpId
      ,CodeNumber
FROM dbo.Applications;

Then link the view from Access so we can see the data while the original table is untouched.

How to Get Month Number from Month Name and Year (T-SQL)

From a set of data in SQL Server database, we needed to find a way to quickly get a numeric representation (add a field MonthNo) of a month name in order to do a comparison. Given we only have a month name and year fields here’s a simple way to do it.

Original SQL:

SELECT [Id], [Month], [Year] 
FROM Table1

SQL Server: Query Result (Id, Month, Year)

Add a field called MonthNo:

MONTH(CONVERT(NVARCHAR, [Month]) 
	+ ' ' + CONVERT(NVARCHAR, [Year])
) AS MonthNo

Final SQL with the new column:

SELECT [Id]
	  , MONTH(CONVERT(NVARCHAR, [Month]) + ' ' 
	  + CONVERT(NVARCHAR, [Year])) AS [MonthNo]
	  , [Month], [Year] 	
FROM Table1

SQL Server: Query Result (Id, MonthNo, Month, Year)

Make Asset Manager Models Publishable in Service Manager

Even with Connect-It scenario between HP Asset Manager (AM) to Service Manager (SM) running, for quite some time we had an issue where certain models did not carry over to Service Manager.

It turns out, one little detail was overlooked. By design, not all models you have in Asset Manager need to be seen in Service Manager. AM as the single repository of all assets will always contain more models including some of the unknown and “catch all” models. And all these unreconciled models don’t need to be brought over to the HelpDesk. So when a model needs to show up in Service Manager, make sure that the Publishable in Service Manager check box (SQL field: bPubInSSC in amModel table) is checked. The check box can be found in the Models screen, in Certification category under General tab.

Models: Publishable in Service Manager check box

SSRS Doesn’t Render Reports in Chrome and Safari

Just earlier today I encountered an issue where SSRS reports that works fine in IE and FireFox were showing blank in Chrome and Safari and interestingly enough the common denominator for these two browsers are that they are based on WebKit.

Fortunately this is a known issue and someone already came up with a working solution by adding a javascript snippet in ReportingServices.js file under this directory (for default installation):

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js

You can open up the file and copy & paste the code below:

ReportingServices.js

function pageLoad() {
	var element = document.getElementById("ctl31_ctl10");
	if (element) {
		element.style.overflow = "visible";
	}
}

Double check the element id. It is not always ctl31_ctl10.

Save the ReportingServices.js and close the text editor.

Refresh the browser and the report should be displayed properly now.

How to Fix Access Denied (401) Error in Microsoft IIS

Further Reading

Linked SQL Server Table Shows #Deleted in MS Access

Further Reading

How to Get Month Number from Month Name and Year (T-SQL)

Further Reading

Make Asset Manager Models Publishable in Service Manager

SSRS Doesn’t Render Reports in Chrome and Safari

ReportingServices.js

Further Reading

Recent Posts

Further Reading

Further Reading

Further Reading

ReportingServices.js

Further Reading

Recent Posts

Tags