Skip to content

Month: May 2014

Add Local Administrators as SQL Server Administrator using PowerShell

Ever faced a situation when you are stuck with a SQL Server when you don’t have access even though if you are the Local Administrator.

Luckily, there is a way to get yourself access in this situation and you can use the following script.

It uses the SQL Server Single-User Mode to start the SQL Server. Starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role. For more information, see Connect to SQL Server When System Administrators Are Locked Out.

Download Script


$ServiceName = "MSSQLSERVER" #Enter the service name for your SQL Server Instance (MSSQLSERVER by default)
$Server = "Manas" #Enter the name of SQL Server Instance

NET STOP $ServiceName 
NET START $ServiceName /mSQLCMD 

SQLCMD -S $Server -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 

NET STOP $ServiceName 
NET START $ServiceName

SQLCMD -S $Server -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'" 

An introduction to Action Learning Cycle

Conceptually, the action learning cycle (Figure: The action Learning Cycle) consists of four phases (Zuber-Skerritt, 2000).
051114_1205_Anintroduct1.png
The first phase of the cycle is to identify the problem which is relevant and is in the power of group to solve. It should be evident that the problem should be complex in nature and no opportunity for solution is readily available. Next, the group starts with the discussion with a intention to solve the problem. The questions rather than the individuals are given utmost importance, facilitating possible solution for the problem. Third, the group identifies the possible solution and implements it. In the final phase, the group looks back at the implemented solution and evaluates it.
(Marquardt, 1999) in his book ‘Action learning in action’, describes what he defines as the six key elements of action learning.

  1. A problem (project, challenge, opportunity, issue or task)
  2. An action learning group or team
  3. A process that emphasizes insightful questioning and reflective listening
  4. A resolution of taking action
  5. A commitment to learning
  6. A group facilitator

To fit the above elements in the action learning cycles, it could be considered that an action learning group/team tries to solve a problem (project, challenge) through a process of asking insightful questions and reflective listening. They identify an action and take a resolution to act upon it with a commitment to learning.
(Mumford, 1995) proposes the following essential elements for action learning:

  1. Learning should mean learning to take effective action
  2. Learning to take effective action involves actually taking action not just recommending action
  3. The action learning project must be significant to the learners themselves
  4. Learners learn best from one another

Running a 32 bit web application in IIS 8 with SharePoint 2013

By default, IIS running on a 64 bit computer does not support running a 32 bit application. However, you can change the setting in an Application Pool under which the application is running by setting the ‘Enable 32-bit Applications‘ to true. Or you can use the enable32BitAppOnWin64 to enable the 32 bit web applications on IIS running on a 64 bit computer.

IIS 32 bit application

Sounds simple enough. As per documentation, I created a new web-site in IIS and change the application pool to enable 32-bit application. Big deal!

Well, not so soon. I noticed that when I tried to visit the application it gives me HTTP Error 503. The service is unavailable. error.

IIS 32 bit application Error 503. The service is unavailable.

Considering that it might have been one time hick-up, I restarted the IIS and tried again. No luck and what I noticed that the application pool was stopped repeatedly as soon as I access the application. Checking the event logs showed the following error:

The Module DLL ‘C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\isapi\spnativerequestmodule.dll’ could not be loaded due to a configuration problem. The current configuration only supports loading images built for a x86 processor architecture. The data field contains the error number. To learn more about this issue, including how to troubleshooting this kind of processor architecture mismatch error, see http://go.microsoft.com/fwlink/?LinkId=29349.

Strange! This was a new application I created and had nothing to do with SharePoint. Except that the machine on which I was running this application also had SharePoint 2013 installed.

IIS 32 Bit SharePoint

Solution

In IIS 8, all the global (i.e. loaded into every AppPool) native ISAPI modules are loaded from the GlobalModules section. However this can be changed as IIS fully supports conditionally loading an ISAPI module depending on if its a 32 or 64 bit, managed or classic pipeline or even specific Web Application’s.

Run the following command using the Windows Command Prompt. This would basically direct the SPNativeRequestModule to only be loaded when the application pool is running in Integrated Mode and 64-bit application mode.


"%systemroot%\system32\inetsrv\AppCmd.exe" set config -section:system.webServer/globalModules /[name='SPNativeRequestModule'].preCondition:integratedMode,bitness64

Sorry, only tenant administrators can add or give access to this app

So, I just created my SharePoint Hosted App. Nothing fancy, just the default SharePoint Hosted App project template using Visual Studio and some minor plumbing to retrieve the user profile information using the JSOM.
After deploying the App, I was continuously getting error following error.

Access denied. You do not have permission to perform this action or access this resource.

I started debugging and found that this error occurs when I access the user profile store. Strange! Because, I had already specified the Read permissions for User Profiles in App using the AppMenifest.xml

UserProfileDemo_Permissions

And it turns out to be that after deploying the App, you need to say that you trust this App to access the resources (in this case, user profile store). I went ahead and guess what, another shocker in the form of the message below and for some reason I am not allowed to trust the App I just built.

Sorry, only tenant administrators can add or give access to this app

UserProfileDemo

But hey, this is my development environment. I have the Farm Administrator rights as well as the Local Administrator rights on the machine.

Resolution

After looking around on Google if there are other people having the same issue, I found this blog post from Chris Weldon. He suggested to add user explicitly to the Farm Administrator group. But it turned out that this was not enough to solve the issue I was facing.
Finally, what worked me was something similar. I added my user account explicitly to the Local Administrator group on my machine, reset the IIS and yes it works. 

What is Action Learning? An introduction!

I hear and I forget, I see and I remember, I do and I understand
(Confucius)

What we have to learn to do, we learn by doing
(Aristotle)

The concept of action learning was structurally applied in education by Reg Revans as early as in 1940s. The contemporary action learning guru, Revans has never provided a single and comprehensive statement of action learning and at different times emphasized one aspect or omitted another (Mumford, 1995). He preferred to describe action learning as what it is not.

(Pedler, 1991) came up with the definition for Action learning as an approach to the development of people in organizations which takes the task as the vehicle for learning. It is based on the premise that there is no learning without action and no sober and deliberate action without learning. The method has three main components—people, who accept the responsibility for taking action on a particular issue; problems, or the tasks that people set themselves; and a set of six or so colleagues who support and challenge each other to make progress on problems.

(Lex Dilworth, 1995) has called action learning “the DNA of a learning organization” since action learning both enables and forces organizations to continuously learn on an organization-wide basis and thereby be better able to adapt to the continuously changing environment.

(Marquardt, 1999) described, action learning as both a dynamic process and a powerful program. It involves a small group of people solving real problems, while at the same time focusing on what they are learning and how their learning can benefit each group member, the group itself, and the organization as a whole.

All the major contributors of action learning emphasised that it is the ‘action’ which is the core of this learning methodology. It is clear enough from various definitions that the action learning methodology is not only a methodology but also a process which helps the individuals, teams and organizations to make themselves self-dependent in solving the problems. It also creates an environment where they progressively grow while learning from their activities at the same time.

Add users to SharePoint Group using PowerShell

Yet another moment when you get frustrated trying to solve a simple issue and as a last resort search for ‘Why SharePoint Sucks?‘. And it turns out that we are just used to complain about SharePoint even when we are not understanding how it works. And most of the times, ignoring those hidden words and hints in MSDN documentation can be costly.

captain-america-says-sharepoint-does-not-suck
[Image credit: Scott Ellis]

But hey, who goes to MSDN when the first search result in StackOverflow gives you an answer. However, most of the time the questions and their answers in forums such as CodeProject, StackOverflow etc. are limited in scope and do not really cover the whole aspect. Having said that, MSDN should still be considered as the first source of documentation when it comes to Microsoft products.

Add bulk users to SharePoint Group using PowerShell

I wanted to add bulk (literally 100s) of users to a SharePoint group in a site collection. And of course, I was in no mood to put the list of users as an attachment to the manual and ask operations to add these users in various environments.

I wrote the following PowerShell script (yes, I love PowerShell) to read a file with list of users and add them in a loop to a SharePoint group using Set-SPUser. There isn’t much description or documentation available on MSDN for this cmdlet, but it basically adds a user to a SharePoint site within a certain group.


$loc = Get-Location		
$Users = Get-Content "$loc\Data\Users.txt"	

foreach ($User in $Users) { 	
	Set-SPUser -Identity $User -Web $siteCollUrl -Group $group 	 
} 	

But here comes the surprise. Using Set-SPUser, I was not able to add all the users to the group and it gave the following error on most of the users.

You must specify a valid user object or user identity

The nearest hint or solution was from Guna Kuppusamy where he had mentioned the same problem and the work around using stsadm.


stsadm -o adduser -url "<<Site URL>>" -userlogin "<<Domain\account>>" -useremail "<<Email address>>" -group "<<SP Group>>" -username "<<Display Name of the User>>"

To be honest, stsadm does not feel right when you are working with SharePoint 2010 or 2013. There has to be a better solution.

Solution 1: Use New-SPUser

It turns out to be that Set-SPUser can only set properties, permissions to a user account if it has already been added to SharePoint site. This means, you have to make sure that the user is already present in the SharePoint site. Otherwise, you can use the New-SPUSer cmdlet to add the user.

The New-SPUser cmdlet adds an existing user to a SharePoint Web site with the designated permissions. This user has the given permissions in all subsites that inherit permissions. The user account must already exist in the user directory.
If your environment is in Active Directory mode, the user must already exist in Active Directory Domain Services (AD DS) and only the UserAlias parameter is required; all other fields are pulled from AD DS. If only an alias is given and the farm is in Active Directory Account Create mode, the Email parameter is also required.

So, I added an extra step to check if the user exists by calling the Get-SPUser and when the user does not exist, call the New-SPUser just before using the Set-SPUser to set the permission.


$loc = Get-Location		
$Users = Get-Content "$loc\Data\Users.txt"	

foreach ($User in $Users) { 	
	$user = Get-SPUser -Identity $User -Web $siteCollUrl -ErrorAction SilentlyContinue
	if ($user -eq $null)
	{
		Get-SPWeb $siteCollUrl | New-SPUser –UserAlias $User
	}
	Set-SPUser -Identity $User -Web $siteCollUrl -Group $group 	  
} 		

Solution 2 (Recommended): Ensure User

It just gets better. And I should be asked to bake a cake for anyone who is reading this blog as it was only while I writing this post I realized that it was even simpler and straight forward by using the EnsureUser.

SPWeb.EnsureUser checks whether the specified logon name belongs to a valid user of the website, and if the logon name does not already exist, adds it to the website.

In fact, EnsureUser does the same thing which I implemented myself in Solution 1 by using Get and New-SPUser.


$loc = Get-Location		
$Users = Get-Content "$loc\Data\Users.txt"	

$web = Get-SPWeb -identity $siteCollUrl
foreach ($User in $Users) { 
    $web.EnsureUser($User)	
    Set-SPUser -Identity $User -Web $siteCollUrl -Group $group 	
} 

Thus, lesson learnt! The chances are that it’s not SharePoint but we … 🙂

Generating a sequence in SQL

I came through one of the many interesting SQL questions on one of the forums yesterday. The original poster of the question wanted to get a list of next 15 dates from today. And he wanted to do this by using cursors. While you can write a cursor to get the results in this case, but cursor are not meant to be used like this. A cursor is used to step through the results of a query that returns multiple results e.g. rows from a table.

One good old way of generating a sequence of next n dates would be to use the WHILE loop in SQL.


DECLARE @LoopCount INT
SET @LoopCount = 0
 
DECLARE @calcDate DateTime
SET @calcDate = GETDATE() 

DECLARE @outDate varchar(30) 

WHILE @LoopCount < 15
BEGIN
 
	 SET @outDate = CONVERT(NVARCHAR, @calcDate, 103) 
	 PRINT @outDate
	 SET @calcDate = DATEADD(dd, 1, @calcDate) 

	 SET @LoopCount = @LoopCount + 1
END

Generating a sequence of dates in SQL

Another better alternative would be to use the Common Table Expressions in SQL Server.

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.


WITH q AS
	(
	SELECT  GETDATE() AS datum
	UNION ALL
	SELECT  datum + 1
	FROM    q
	WHERE datum + 1 < GETDATE() + 15
	)
SELECT  CONVERT(NVARCHAR, DATEADD(dd, DATEDIFF(dd, 0, datum), 0), 103) AS MyDate
FROM    q

Generating a sequence of numbers in SQL

Similarly, you can generate the sequence of number in SQL using the Common Table Expressions


WITH q AS
	(
	SELECT  1 AS num
	UNION ALL
	SELECT  num + 1
	FROM    q
	WHERE num < 15
	)
SELECT  * FROM    q

Generating a sequence of alphabets in SQL

Or, you can even generate the sequence of alphabets in SQL using the Common Table Expressions


WITH q AS
	(
	SELECT  0 AS num
	UNION ALL
	SELECT  num + 1
	FROM    q
	WHERE num <= 26
	)
SELECT CHAR(num + 65) FROM    q

Add User Profiles in SharePoint 2013 using PowerShell Script

Setting up a SharePoint 2013 development environment is essentially a lengthy process. Apart from installation and configuring SharePoint 2013, you need to install SQL Server, Visual Studio etc. to get you started before developing your first SharePoint 2013 App.

MSDN has a detailed documentation available: Start: Set up the development environment for SharePoint 2013

However if you have Azure development subscription available, you can also use one of the Windows Azure developer image that comes with SharePoint 2013 and Visual Studio 2013 RC pre-installed.  The image also contains scripts to provision and configure SharePoint 2013 with or without an Active directory running on a different machine. This is surely a big helping hand as you don’t have to do all the pre-plumbing before you can get started.

Anyway, this is not this blog post about. It was just a background as I was using one of these pre-configured developer images. Once ready, for one of the Apps I was created I wanted to have my User Profile Service to have a considerable number of user profiles. As my development environment was not connected to any domain or active directory, the only users I had access to were the local computer accounts and there were not many. One way was to create the users manually and then import them in user profile, but that does not scale very well and requires repeated effort if I want to set up another similar environment later on. So, I decided to automate the process of creation of local users and then adding them to SharePoint User Profile.

Step 1: Generate the list of accounts to be created

Not really applicable in a real environment where you would have real accounts, but for my development environment I decided to create a Xml file using PowerShell with various elements for a User such as FirstName, LastName, EmailAddress etc.


<User>
	<Account>User0001</Account>
	<EmailAddress>User0001@WORKGROUP.com</EmailAddress>
	<FirstName>FirstName_User1</FirstName>
	<LastName>LastName_User1</LastName>
</User>

Here is the script which generate the specified number of user data in form of an xml file. It takes the number of accounts to generated as input and results into a Users.xml file.


$Path = (Get-Item -Path ".\" -Verbose).FullName + "\Users.xml"
 
$XmlWriter = New-Object System.XMl.XmlTextWriter($Path,$Null)
 
$xmlWriter.Formatting = 'Indented'
$xmlWriter.Indentation = 1
$XmlWriter.IndentChar = "`t"
 
$xmlWriter.WriteStartDocument()
 
# create root element "Users" and add some attributes to it
$XmlWriter.WriteComment('List of Users')
$xmlWriter.WriteStartElement('Users')

$nrOfUsers = Read-Host 'Please enter the numbers of users you want to create:'
 
# add a couple of random entries
for($x=1; $x -le  [int]$nrOfUsers; $x++)
{
    $User = 'User{0:0000}' -f $x
    $email = $User +  '@' +  (gwmi WIN32_ComputerSystem).Domain
 
    $guid = [System.GUID]::NewGuid().ToString()
 
    # each data set is called "User"
    $xmlWriter.WriteStartElement('User')
   
    # add three pieces of information:
    $xmlWriter.WriteElementString('Account',$User)
    $xmlWriter.WriteElementString('EmailAddress',$email)
    $xmlWriter.WriteElementString('FirstName','User')
	$xmlWriter.WriteElementString('LastName',$x)
 
    # close the "User" node:
    $xmlWriter.WriteEndElement()
}
 
# close the "Users" node:
$xmlWriter.WriteEndElement()
 
# finalize the document:
$xmlWriter.WriteEndDocument()
$xmlWriter.Flush()
$xmlWriter.Close()

Step 2: Create local account using PowerShell

PowerShell is very useful for automating Active Directory. It allows to quickly and relatively easy automate mundane actions or perform same operations with many objects.

PowerShell provides very broad set of methods to work with Active Directory. There is some of them:

  • Microsoft Active Directory Module for Windows PowerShell
  • ADSI adapter
  • .Net Classes
  • Non Microsoft free extensions, such as Quest Active Directory Cmdlets or AD provider from PowerShell Community Extensions.

Read more about these methods here on MSDN.


$HostName =(gwmi WIN32_ComputerSystem).Name
[ADSI]$server= "WinNT://$HostName" 

$localUsers = $server.Children | where {$_.SchemaClassName -eq 'user'}  |  % {$_.name[0].ToString()}

#Add a new local user after checking that it does not exist
if($localUsers -NotContains $user.Account){
	$Account = $server.Create("User", $user.Account) 
	$password = [System.Web.Security.Membership]::GeneratePassword(10,2)
	$Account.SetPassword($password)
	$Account.SetInfo() 
}
else{
	Write-Host "User Account " $user.Account " already exists"
}

Step 3: Add the User Profile to SharePoint 2013 using PowerShell

You also need to add the user account which is running the PowerShell script to have the Full Control on the User Profile Service application via the Central Administration. To add the permission:

  • Go to Central Administration
  • Go to Manage Service Applications
  • Select User Profile Service Application and click on the Permissions button in the ribbon.
  • Select the user who is running the script and give him ‘Full Control’ access.

User Profile Service Application Permission

You must create the UserProfileManager object before you access a UserProfile object. You can then retrieve the user profiles that the user can access. Full access to everyone’s user profile requires “Manage User Profiles” rights. Full access to your own profile requires “Use Personal Features” rights. Everyone has read access to all profiles.


#instantiate the user profile manager
$mysite = new-object microsoft.sharepoint.spsite($siteurl)
$servicecontext = get-spservicecontext($mysite)
$userprofilemanager = new-object microsoft.office.server.userprofiles.userprofilemanager($servicecontext)

Next, use the userprofilemanager object to check if the profile exists for this particular user. If it does not, the create a new user profile as shown below.

However, this is a crude implementation as I wanted to use it one time. To make it even better, you might also update the existing profile if any with the new data specified in the xml file.


#Add a new user profile for the user
if($userprofilemanager.UserExists($user.Account)){
	Write-Host "User Profile for " $user.Account " already exists"
}
else{
	$newprofile = $userprofilemanager.createuserprofile($user.Account)
	$newprofile.DisplayName = $user.Account
	$newprofile["FirstName"].add($user.FirstName)
	$newprofile["LastName"].add($user.LastName)
	$newprofile["WorkEmail"].add($user.EmailAddress)
	$newprofile.commit()
}    

If you run the CreateUserProfiles.ps1 from the script download, it asks you the following parameters to enter.

  • Number of users to be created. I entered 1000 in this case which seemed to be created without any issues.
  • Site Url of the Central Administration site. This is required to get the context of the service application.

Create User Profile

And the result is as below. Without any manual effort, we have 1000 user profiles ready in our development environment.

User Profiles

Download Scripts

The curious case of Scrum Master’s role

At the very core, both agile manifesto and scrum are frameworks and not a detailed method or process. This was intentional because the founders of scrum wanted to keep it as lightweight framework which can be used by the organizations to develop the software with value.

However, the reason often leads to different interpretations of scrum and sometimes leads to the result which is completely against the scrum framework.

There are three distinct roles which identified within the Scrum methodology:

  • The Scrum master, who ensures the process is followed, removes impediments, and protects the Development Team from disruption.
  • The Product Owner, who represents the stakeholders and the business.
  • The Development Team, a cross-functional, self-organizing team who do the actual analysis, design, implementation, testing, etc.

Scrum_Roles

And one of the most misunderstood role in scrum team is of Scrum Master. Before we go more into details of Scrum Master, if you are interested to read about the fundamentals of Scrum and the various roles defined in a Scrum team, refer to my previous post ‘A beginners guide to Scrum’

A whole lot of development organizations and teams are using scrum these days. Having said that, I have noted that most of these teams use scrum as a planning methodology focusing only on the daily scrum meetings and/or sprints. And just because it’s fashionable to say that the project follows scrum approach, someone from the team takes the responsibility of being a scrum master. In most of the cases, as the world ‘master‘ is involved, it is either the erstwhile project manager or the architect/lead developer of the project.

Scrum Master is not Project Manager

By definition, project managers have the responsibility of the planning, execution and closing of project. In addition, project manager is often a client representative and has to determine and implement the exact needs of the client, based on knowledge of the firm they are representing. A project manager is the bridging gap between the team and client.

On the other hand, a scrum master does not have any responsibility of planning and delivery of the project. The main focus of a scrum master is to make sure that he removes any impediment or blockage which comes in the way of development team.

Scrum Master is not Lead Developer

The Development Lead’s role is focused on providing more detail to the Solution Architect’s architecture. This would include creation of detailed technical design, program specifications etc. The Development Lead is also the first line of support for the developers who need help understanding a concept or working through a particularly issue. The Development Lead does not need to be hierarchically up in the organizational chart and is not responsible for the administrative work of the employees. Their primary focus is on helping the developers to deliver the quality software on time.

There has been a real debate and questions coming in if the Scrum Master should know the technical details of the software development.

It’s completely OK if the scrum master does not understand a single bit of architecture, technical design or other details of the software project.

The real focus of a scrum master is on the process and not the details of the work which development team is responsible. However, if the development team is stuck with a technical impediment which they can not solve themselves then they should contact scrum master. For e.g. it could a technical or architectural decision which they are not sure how to progress with. In such case, a scrum master can reach out to other parts of organizations or even outside organizations to make sure development team gets the answer and they can continue with.

So, Who exactly is Scrum Master?

Schwaber & Sutherland gave a precise definition of Scrum Master role in the scrum guide without detailing the day-to-day activities.

The Scrum Master is a servant-leader for the Scrum Team. The Scrum Master helps those outside the Scrum Team understand which of their interactions with the Scrum Team are helpful and which aren’t. The Scrum Master helps everyone change these interactions to maximize the value created by the Scrum Team.

Scrum master is for sure not a project manager and/or a development lead. He does not manage people and he is not someone to whom people should be reporting (in context of Scrum). Having said that, it should be noted that Scrum Master is a management position. Scrum Master should have organizational management powers and ability to reach other parts of the organization to make sure he is able remove any impediment faced by the team.

Scrum Master is a shield for the development team so that they focus on what they should be doing and continue the same. A Scrum Master makes sure that the product owner is aware of the scrum process and acknowledges the structure of the framework to formulate and prioritize the product/sprint backlog.

At the same time, I am a believer that scrum master is a role rather than a position.

Have you also observed similar situation where the Scrum Master role is mingled or is not properly defined in your organization?