Security requirement for SharePoint backup shared folder

STSADM command is the only tool available when scheduling a SharePoint catastrophic backup. And this command requires a shared folder to store backup files. It's essential to setup appropriate permissions for this folder in order to have a successful backup run. From what I have done in our environment, the following accounts need modify (or change) permissions:

1. The account configured to run the Windows scheduled backup task
2. The moss farm account - this is the account used to run SharePoint jobs and Central Administration application pool.
3. The account for SQL Server services.
4. The Sql Server computer account. It's the Sql Server server name with a $ sign. For example, if your server name is sqlserver1, then the account name is sqlserver1$.

The same account permissions need to be configured for Sharing Permission, and Folder permission in Windows explorer.

Error when access a local SharePoint site using host headers

I run into a problem of accessing a SharePoint site which was setup using host headers instead of host name plus port number. When I tried to access the site locally on the server, I was asked to provide authentication information. Even the correct login name and password were given, the server failed to authenticate.

The same site can be accessed from another computer without any problem.

After some research, I found out this is a result of loopback security check that exists from Windows Server 2003 SP1 and later. Micsoft described this feature in the following articles:
http://support.microsoft.com/kb/926642
http://support.microsoft.com/kb/896861

Microsoft also give workaround to fix this issue. The following is a copy and paste from the two articles:

Method 1: Specify host names
Note We recommend that you use this method.

To specify the host names that are mapped to the loopback address and can connect to Web sites on your computer, follow these steps:

Click Start, click Run, type regedit, and then click OK.
In Registry Editor, locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
Right-click MSV1_0, point to New, and then click Multi-String Value.
Type BackConnectionHostNames, and then press ENTER.
Right-click BackConnectionHostNames, and then click Modify.
In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.
Quit Registry Editor, and then restart the IISAdmin service.


Method 2: Disable the loopback check
Follow these steps:
Click Start, click Run, type regedit, and then click OK.
In Registry Editor, locate and then click the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
Right-click Lsa, point to New, and then click DWORD Value.
Type DisableLoopbackCheck, and then press ENTER.
Right-click DisableLoopbackCheck, and then click Modify.
In the Value data box, type 1, and then click OK.
Quit Registry Editor, and then restart your computer.


We took the first approach - specifying host names in the BackConnectionHostNames entry and our issue was solved.

Pain with MOSS content import

Today I was trying to import a SharePoint website using Sharepoint Content Depolyment Wizard. I exported the source site to a folder and create the dest site with an empty site collection using stsadm command. When I started imporint I kept having the error "sharepoint import FatalError: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))".

This has driven me crazy. I did a few google search but it didn't help at all.

And finally I got the problem solved. When I created the site collection I specified a different login account as ownerlogin, other than the account I used to login to the server. This makes the given account as site collection administrator, but the current login account doesn't have access to the site collection.

Then I went to central admin, assign the current login as secondary administrator to the site collection. Then the import process was successful.

Unique CorellationToken for each Task activity in WSS workflow

I was strugling with a workflow that the CreateTask did not create a task as expected. After googling around and it turned out that Task-related activities need a unique CorellationToken value.

There are three groups of activities with each group has it's own CorellationToken, and activities with the same group should share the same correlation token:


Workflow token:

OnWorkflowActivated
OnWorkflowItemChanged
OhWorkflowItemDeleted
SetState
SendEmail
UpdateAllTasks


Task token:

CreateTask
CreateTaskWithContentType
UpdateTask
DeleteTask
CompleteTask
RollbackTask
OnTaskChanged
OnTaskDeleted
OnTaskCreated


Modification token:

EnableWorkflowModification
OnWorkflowModified

SharePoint Workflow failed to start

If a workflow is created through a WSPBuilder project, the workflow would have problem to start. To solve this problem, the .csproject file needs to be modified manually. Open the project file in notepad, add the following line

Import Project="$(MSBuildExtensionsPath)\Microsoft\Windows Workflow Foundation\v3.5\Workflow.Targets"

right after the line

Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets"


Then problem solved.

This is really a frustrating issue.

Deploy a wss solution from Visual Studio 2008 extension for WSS has error

It's anoying to have this error when I deploy a soltion:


The feature name XXX already exists in SharePoint. You need to rename the feature before solution deployment can succeed.

To solve this problem you can't just rename the feature as that just adds more mess. The correct approach is to go to SharePoint Central Administration webiste, in the operations web, choose Solution Management.

Find that solution that causes trouble, extract that solution. Then remove that solution from the list.

After the solution is removed, the Visual Studio 2008 can deploy it to SharePoint again.

ADO closes a recordset before any data is read from a stored procedure

Recently I was working on a legacy VB Script file which has following code:

dim conn
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = connTimeOut
conn.CommandTimeout = connCmdTimeOut
conn.Open connString

dim dbCmd
set dbCmd = CreateObject("ADODB.Command")
dbCmd.CommandTimeout = 300
dbCmd.CommandType = adCmdText
dbCmd.ActiveConnection = Conn

sqlText = "ReportScheduler '" & currentDate & "' "
dbCmd.CommandText = sqlText
dim rsScheduledReports
set GetScheduledReports = dbCmd.Execute

if not rsScheduledReports.EOF then

...


Each time when the code is executed to this line, an error is generated :

ADODB.Recordset: Operation is not allowed when the object is closed

This is strange as there are actually many records come from the database and this is proved by executing the sql in Management Studio mannually.

The VBScript code worked on SQL Server 2000, recently we upgraded to version 2005 then it breaks.

I spent a lot of time googling on the Internet and finally a trick fixed it. Just add this line to the beginning of the stored procedure: SET NOCOUNT ON.

This makes me thinking what's difference between SQL Server 2000 and 2005 version when adding set nocount on statement in a stored procedure. The behaviour must have changed for new version.

Use FMTONLY To Obtain an Query or stored procedure metadata

When executing a t-sql or stored procedure, if set FMTONLY value to ON, an zero-row resultset is produced with metadata information. The full query is not run. This feature is used for many report designers like Crystal Report and Reporting Service.

For example:

SET FMTONLY ON
select * from dbo.Items
SET FMTONLY OFF

The result is an empty row with column names and column data types.

Declare a local variable in T-Sql while loop

I have had some trouble to find a bug inside a sql server stored procedure.

I have a while loop in the stored procedure like this:

While Exists(…)
Begin
Declare @CategoryID int

// here I assign the value to @CategoryID with a CategoryID field by joining Category and Product tables, the join could return one record or no record with some where clause.

// then check value @CategoryID by printing it

// do something to make the while loop end
End

There is no problem with while loop itself, it ends as expected. The problem is the value of @CategoryID variable. I assume each time I declare the variable in the loop, a null value is assigned it by default. But the reality is not, if there is a value returned from the table join select, the value is assigned to the variable, this is how it should be. But if there is no value returned from the join, the value from the last loop is carried forward, instead of a null. This makes me thinking if the Declare command in a while loop only happen once and leave the variable available for the next loop.

Reporting Service security

I have a Reporting Service report which sits in a remote server. I need to display the report in a ReportViewer control in a dotnetnuke portal. The report was displayed fine when I test in a stand alone web page, but when I deploy the user control in the DNN portal, I got the following security message:

The permissions granted to user 'BIOLAB\AD_ALANZ$' are insufficient for performing this operation. (rsAccessDenied)

It turns out the account used in my local computer doesn't have enough permission to view the report.

I did a search through web and solved by the following steps:
Go to the report manager home page. Click on Properties tab.
Click on New Role Assignment button.
Add a new user BIOLAB\AD_ALANZ$, choose Browser role from the list and click Apply.

Then my report is showing in the ReportViewer successfully.

ASP.NET session state is not available in custom role provider

I have a custom role provider. In the method public override string[] GetRolesForUser(string username) I check the role name stored in a session variable. This didn't work, as when I put a break point in the code I found out the value of HttpContext.Current.Session is null.

I stumbled for a while and I came to this link http://forums.asp.net/p/967758/1214615.aspx. In the post ScottGu says "Unfortunately authentication and authorization happen before the session object is populated."

So I have to change to load user role from database by the user name.

How to split a string in t-sql

If a list of values are concatenated by a character like '1,2,3,4,5', we need to split the string to a temp table so each value can be used in sql query. There are many tricks of doing this but the easiest one I have seen is below:


DECLARE @Parameter VARCHAR(8000)
SET @Parameter = 'Element01,Element02,Element03,Element04,Element05'

-- Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(8000) --The string value of the element
)

--Decalre a variable to remember the position of the current delimiter
DECLARE @N INT
-- Add start and end commas to the Parameter so we can handle
-- single elements

SET @Parameter = ','+@Parameter +','

SET @N = 1

-- Loop through and find each comma, then insert the string value
-- found between the current comma and the next comma. @N is
-- the position of the current comma.
WHILE @N < LEN(@Parameter) --Don't include the last comma
BEGIN
INSERT INTO @Elements
VALUES (SUBSTRING(@Parameter, @N+1, CHARINDEX(',', @Parameter, @N+1) - @N-1))
-- Find the next comma
SELECT @N = CHARINDEX(',',@Parameter,@N+1)

END

SELECT * FROM @Elements

T-SQL alias play around

I noticed an interesting feature of T-SQL reagarding field alias in a sql statement. The field alias cannot be used in a WHERE clause but can be used in ORDER BY .

To test this I have created a table called Test with two columns: ID and TestDate. The ID is an identity column and TestDate is DateTime. I populated several ramdom records in the table.

First I run the following sql :
select ID, TestDate AS RealDate, Convert(varchar, TestDate, 103) AS DisplayDate from test
Where TestDate > '01/03/2006'
order by RealDate

5 2006-02-01 00:00:00.000 01/02/2006
3 2006-02-10 00:00:00.000 10/02/2006
1 2006-02-23 00:00:00.000 23/02/2006
2 2006-02-25 00:00:00.000 25/02/2006
4 2006-04-08 00:00:00.000 08/04/2006

The result shows the date time field is sorted properly and alias RealDate is used for sorting.

Then I run this:
select ID, TestDate AS RealDate, Convert(varchar, TestDate, 103) AS DisplayDate from test
Where TestDate > '01/03/2006'
order by DisplayDate

5 2006-02-01 00:00:00.000 01/02/2006
4 2006-04-08 00:00:00.000 08/04/2006
3 2006-02-10 00:00:00.000 10/02/2006
1 2006-02-23 00:00:00.000 23/02/2006
2 2006-02-25 00:00:00.000 25/02/2006

The result shows the sorting is by the varchar field and this is what I have expected.

Then I run this:
select ID, TestDate AS RealDate, Convert(varchar, TestDate, 103) AS DisplayDate from test
Where DisplayDate > '01/03/2006'
order by DisplayDate

Msg 207, Level 16, State 1, Line 1
Invalid column name 'DisplayDate'.

And this:
select ID, TestDate AS RealDate, Convert(varchar, TestDate, 103) AS DisplayDate from test
Where RealDate > '01/03/2006'
order by DisplayDate

Msg 207, Level 16, State 1, Line 1
Invalid column name 'RealDate'.

The two error messages above show we can't use alias in a where clause.

Schedule a SSIS package job by dtexec command

It has been a pain to schedule a Sql Agent Job for SSIS packages by using the new SSIS package step type. The security and permission problems are so complex that we can't figured out how to setup the proxy or account permissions. I have read the MSDN help and some step by step instructions but I have no luck by doing that.

Finally I decided to change the job step type to Operating System (CmdExec) and using the dtexec command to run the package directly. And this solved the permission problem immediately. The Job runs more than happy and we don't have the pain anymore.

Setup configuration file for a unit test project

It's a common scenario when we need to have a configuration file when doing unit testing, especially for testing a database access object. If the project tested is a Windows application, the configuration in the project is app.config, for a web project it's web.config. The configuration file must be copied to the testing bin folder to read configuration data, with the file name AssemblyFile.dll.config. We could copy the config file to the bin\debug or bin\release folder in the testing project, but then we are maintaining multiple copies manually.

To avoid this, we can take advantage of the Post-build event of Visual Studio. First we need to copy app.config file from testing Windows application project to the root folder of the testing project, or web.config file from the Web project and rename it to app.config. Secondly in the testing project property window, click on Build Events tab, we can then enter the following into the Post-build event command line box:

copy /Y "$(ProjectDir)App.config" "$(TargetDir)$(TargetFileName).config"

This command will copy and rename the configuration file to the target folder automaticallty.

Reg-less Com component deployment

I was developing a Windows application which uses an Activex control from a third party vendor. I registered the control, made a reference to the component in my project, and the application run very well. I made a ClickOnce deployment for my application and try to run the application from a web page in another computer which doesn't have the activex control registered, I didn't find any luck. The application failed to start.

I noticed after I register the activex control in that computer manually, the application run immediately. It turns out the deployment package doesn't register the activex control automatically. I don't want to register the activex control in every computer of our clients, because the maintenance will become a nightmare.

Then I came across this page when I did some gooling:

http://msdn.microsoft.com/msdnmag/issues/05/04/RegFreeCOM/default.aspx

It talks about how to deploy an activex control without registering it. This is page is just what I need.

I followed the procedure by changing the Isolated property of the COM reference to true. When I run the application locally, I saw the dll file containing the COM component was copied to the bin folder of my project.


After I deploy the application to my website again with the change, I run the application successfully even without the activex control registered.

Define a prerequisites download location for Windows ClickOnce deployment

In a .NET Windows application, we can publish the application in a Internet web site and ask users to install it from a web page. One of the prerequisites of my application is .NET Framework 2.0. When I publish the installation files, I initially chose to download prerequisites files from vendors web site, but it didn't work.

Then I chose to download from a specific location, and the download was successful. Is there anything wrong with Microsoft's supporting website? The Microsoft website may be working fine, but our firewall setting could prevent us from downloading from an external website.

This makes me upload the .net Framwork installation package into our own website and then I am sure the downloading is guaranteed.

Use Application.ThreadException event to catch unhandled exceptions in a Windows Forms application

Application.ThreadException event occurs when an unhandled exception is thrown, based on MSDN. To avoid your Windows application from crashing when an exception is not caught by your code, you should attach your own event handler to this event in program.cs file.

The following code shows how this works:

[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.ThreadException += new System.Threading.ThreadExceptionEventHandler(UnHandledException);

Application.Run(new MainForm());
}

static void UnHandledException(object sender, System.Threading.ThreadExceptionEventArgs e)
{
MessageBox.Show(e.Exception.Message);
}

Note the event handler should be attached before the Application.Run method.

Scroll a Windows Form DataGridView to a specific row

I have a Windows Form which contains a TextBox and a DataGridView. When a user enters anything I need to do a search and make the grid scroll to the row matched.

I was struggling for a while for the scrolling until I came across this property: FirstDisplayedScrollingRowIndex. It's simple, just assign the row index value to this property and the grid will scroll to the row and show the row as the first row in the visible area.

.NET Interview Questions (6)

Developers using XML

· What is the purpose of XML Namespaces?
XML Namespaces remove conflicts between different documents from different sources.

· When is the DOM appropriate for use? When is it not? Are there size limitations?
Dom is appropriate for small document and the document needs to be modified. It’s not appropriate for big document because of performance issue. There is no size limitation as long as there is enough physical memory.

· What is the WS-I Basic Profile and why is it important?

· Write a small XML document that uses a default namespace and a qualified (prefixed) namespace. Include elements from both namespace.

· What is the one fundamental difference between Elements and Attributes?
Element can contain another elements and attributes. Attributes can only reside inside an element.

· What is the difference between Well-Formed XML and Valid XML?
Well-Formed XML follows xml grammar. Valid XML is validated against xml schema or dtd. A valid xml is well-formed. A well-formed xml might not be valid.

· How would you validate XML using .NET?
Using XmlValidatingReader class.

· Why is this almost always a bad idea? When is it a good idea? myXmlDocument.SelectNodes("//mynode");
This statement returns all elements under mynode. This could return a large amount of data. It’s more efficient to use SelectSingleNode method and pass a full path, or use SelectNodes with a relatively deeper path.

· Describe the difference between pull-style parsers (XmlReader) and eventing-readers (Sax)
In XmlReader you control the parsing and define which element to go to. Sax parses the Xml internally, and raises events whenever it find something interesting.

· What is the difference between XPathDocument and XmlDocument? Describe situations where one should be used over the other.
XpathDocument is not editable. XmlDocument is editable. XpathDocument is used for fast readonly situation. XmlDocument is used when the document needs to be changed.

· What is the difference between an XML "Fragment" and an XML "Document."
XML Fragment is not a full xml document. It doesn’t have element.

· What does it meant to say “the canonical” form of XML?
The purpose of Canonical XML is to define a standard format for an XML document. Canonical XML is a very strict XML syntax, which lets documents in canonical XML be compared directly.
Using this strict syntax makes it easier to see whether two XML documents are the same. For example, a section of text in one document might read Black & White, whereas the same section of text might read Black & White in another document, and even in another. If you compare those three documents byte by byte, they’ll be different. But if you write them all in canonical XML, which specifies every aspect of the syntax you can use, these three documents would all have the same version of this text (which would be Black & White) and could be compared without problem. This Comparison is especially critical when xml documents are digitally signed. The digital signal may be interpreted in different way and the document may be rejected. Why is the XML InfoSet specification different from the Xml DOM? What does the InfoSet attempt to solve?

· Contrast DTDs versus XSDs. What are their similarities and differences? Which is preferred and why?
Both are used for defining the rules of an xml document. DTDs are written in regex, XSDs are written using xml.
Document Type Definition (DTD) describes a model or set of rules for an XML document. XML Schema Definition (XSD) also describes the structure of an XML document but XSDs are much more powerful. The disadvantage with the Document Type Definition is it doesn’t support data types beyond the basic 10 primitive types. It cannot properly define the type of data contained by the tag. An Xml Schema provides an Object Oriented approach to defining the format of an xml document. The Xml schema support most basic programming types like integer, byte, string, float etc., We can also define complex types of our own which can be used to define a xml document. Xml Schemas are always preferred over DTDs as a document can be more precisely defined using the XML Schemas because of its rich support for data representation.

· Does System.Xml support DTDs? How?
Yes, in addition to XDR and XSD schema validation, .NET continues to support the DTD to validate the XML documents. The System.Xml namespace contains a class named XmlValidatingReader that can be used to validate the XML documents.

· Can any XML Schema be represented as an object graph? Vice versa?
Yes.