SharePoint 2013 introduces a new Active Directory Import mode for user profiles, but can the creation of the required synchronization connections be easily scripted? Heck no. Out of the box (OOTB), the PowerShell support for creating user profile AD “direct mode” import connections isn’t what it needs to be. In this post, I offer a scripting alternative that allows you to specify display names and LDAP filters.
I’ve been operating under the Bitstream Foundry banner for a few months now, and I have to admit that it’s been absolutely wonderful to return to hands-on work with SharePoint. It really didn’t take long for me to realize just how much I missed solving problems and making SharePoint do what customers/clients wanted it to do. Some of you may think I’m crazy for saying this, but it’s good to be back in the trenches.
Brief note: I lump the UPS, UPA, and related pieces under the “UPS” acronym for this post. It’s not technically accurate, but it keeps things simpler for readability.
The UPS was buggy, problematic, and easily broke if you looked at it the wrong way. I know that the bugs were ultimately ironed-out and that the UPS was “fixed” to a large extent, but I stopped playing with it before it got to that point. I never really needed it for anything I wanted to do, and so I did my best ostrich impersonation anytime anything related to the User Profile Service and its underlying plumbing came up.
It’s somewhat ironic, then, that I was only back into hands-on work for about a month before I found myself thrown into the UPS with SharePoint 2013. A client wanted me to put together a script that they could use to perform the post-setup configuration for the UPS in a farm once the UPS instance had been created (through AutoSPInstaller – which also happens to be my “go to” tool for SharePoint setup). The client wanted the script to configure built-in profile properties, create custom properties, create user sub-types, and most importantly: configure synchronization connections. End-to-end, the script needed to do everything that an admin might normally have to do by hand in order to get the UPS ready for production usage.
The Twist
UPS configuration via PowerShell isn’t new territory. There’s a pretty substantial base of online material to draw from for guidance and inspiration (starting with TechNet), but my task was novel in one very important regard: the synchronization connections I was setting up were using SharePoint 2013’s new Active Directory Import mode.
If you’re not familiar with Active Directory Import mode (“AD Direct Mode”), pause your reading here and head over to Spence Harbar’s blog for a thorough and extremely well-written overview. It is a top-notch post on Active Directory Import Mode and provides a foundation for the rest of this post.
If you’ve scripted-out the creation of UPS synchronization connections through PowerShell under SharePoint 2010, then you’re undoubtedly familiar with the Add-SPProfileSyncConnection cmdlet and its syntax (taken straight from TechNet):
As I started putting together my script and looking for usage examples covering the Add-SPProfileSyncConnection cmdlet, I naturally came to another of Spence Harbar’s posts. Spence’s post was the first sign of trouble for me. Even though Spence’s post was written in the SharePoint 2010 time frame, he noted a number of limitations with the Add-SPProfileSyncConnection cmdlet versus creating sync connections within Central Administration. For example, there was no way to specify a DisplayName parameter with Add-SPProfileSyncConnection; you were stuck with a name that was automatically assigned based on the connection’s associated domain name.
The Wall
The DisplayName limitation was annoying, but it wasn’t a show-stopper for me. With a little more investigation, though, the real roadblocks quickly made themselves known.
If you were to compare the fields and options that are available when adding a sync connection through SharePoint Central Administration (shown on the right) with those that are available when adding a connection with the Add-SPProfileSyncConnection, you’d see some significant gaps. The ones that stood between me and a configuration script that did what I needed it to do were the following:
The inability to use the “Filter out disabled users” switch
The inability to specify an LDAP filter to restrict imported users
The ability to filter disabled users was really a convenience; I could just as easily extend my LDAP filter to include the following:
(!userAccountControl:1.2.840.113556.1.4.803:=2)
… if I wanted to filter disabled users without using the checkbox that was available in Central Admin. Without the ability to specify an LDAP filter in the Add-SPProfileSyncConnection call, though, I was stuck. I needed some way to specify an LDAP filter to restrict imported users for the connections I was going to be creating.
I spent a lot of time hunting through forums and looking for information online, but the hunt proved fruitless. No matter how I approached the problem and tinkered with PowerShell, I came up empty. Falling back to the SharePoint Server Object Model, the ConnectionManager class (in the Microsoft.Office.Server.UserProfiles namespace), and its related types got me no further, either. Absolutely nothing in the APIs seemed to expose a way to add Active Directory Import sync connections in a way that exposed the properties I needed to specify.
I had a hard time believing it, but it seemed that the only way to create an Active Directory Import-based sync connection that allowed me to specify a DisplayName and LDAP filter was to use SharePoint Central Administration.
Huh?
The Source of the Problem
Since Central Administration supplied the mechanism to create sync connections with the desired parameters, I knew that SharePoint had the plumbing to support what I wanted to do somewhere in its assemblies. I just needed to figure out where that functionality was. So, I did what I typically do in these sorts of situations: I pulled out Reflector and went diving into SharePoint’s internals.
I took a look at both the EditDSServer.aspx page in the _layouts/15 folder (which is the Central Admin page for creating sync connections) and the Add-SPProfileSyncConnection cmdlet, did some backtracking, and eventually found myself back at the ConnectionManager class – no big surprise there. The somewhat surprising part, though, was what I found (shown to the left).
It appeared that the ConnectionManager class sported several methods to support the addition of sync connections: AddActiveDirectoryConnection, AddActiveDirectoryImportConnection, AddBusinessDataCatalogConnection, and AddLdapConnection. The one that I was interested in was the second one – AddActiveDirectoryImportConnection:
The big problem was that method was marked internal – meaning it wasn’t publicly exposed to outside callers.
Tracing things through, I found that the AddActiveDirectoryImportConnection method was ultimately accessible through two different public entry points: the Central Admin EditDSServer.aspx page and the Add-SPProfileSyncConnection PowerShell cmdlet. The EditDSServer.aspx page call was pretty direct, but the PowerShell cmdlet call ended up channeling through the UpdateCreateSyncConnection method on the Microsoft.Office.Server.Administration.UserProfileApplication class before making its way to the AddActiveDirectoryImportConnection method. And the UpdateCreateSyncConnection method was the source of the problem.
UpdateCreateSyncConnection is a method that coordinates the creation and modification of different sync connection types – not just the Active Directory Import connection type I was focused on. Most of the parameters that are needed to create a sync connection are available on the method signature, but not all of them.
Looking through the method to where it actually calls into the AddActiveDirectoryImportConnection internal method reveals how it handles passing-in an LDAP filter … or rather, how it doesn’t handle it:
See the string.Empty parameter in the method call? It’s in the same position where the AddActiveDirectoryImportConnection method expects an LDAP filter. The UpdateCreateSyncConnection method basically “drops the ball” and does absolutely nothing with an LDAP filter. If code had attitude, I envisioned this as the UpdateCreateSyncConnection method throwing up its virtual arms and saying “whatever.”
That crashing noise? My efforts thus far slamming into a wall.
Working Around the Limitation
Since I’d officially proven (to myself, anyway) that the Add-SPProfileSyncConnection cmdlet wasn’t going to work for my purposes, and I wasn’t going to be able to script out the desired action through Central Administration, I was looking at going against the AddActiveDirectoryImportConnection method myself directly from within my PowerShell script.
Insert a giant “ewwwwwwwww” sound here. Why? Because the method was marked internal. Since the method wasn’t publicly accessible, I’d need to use Reflection to call it.
I’ve used Reflection when I’ve had to in the past, and it certainly works, but it’s always had something of a “dark art” feel to me. And the times when I have used Reflection heavily have been within managed C# code – not PowerShell. Reflection is very persnickety regarding object types and method signatures, and C# (with its strict typing) makes it relatively painless to manage these issues. PowerShell, on the other hand, is pretty loose with typing and coercion – not ideal for working with Reflection-based parameter specification and method invocation.
Dark arts or not, I was bound and determined to provision my sync connections through PowerShell. So onward I marched.
The Result
I’m happy to say that this story does have a happy ending. The script that I’m sharing (Add-SPProfileDirectSyncConnection) to provision Active Directory Import connections appears below. It’s not the prettiest thing around, but hey – it works.
A word of caution before you pluck this out and start using it: the script goes directly against an internal method on the ConnectionManager class instead of a publicly exposed method, so I want to stress that this is a “use at your own risk” script. Anytime I have the option of using publicly accessible APIs, I do … but I had no other choice in this case.
[sourcecode language=”powershell” toolbar=”true” wraplines=”true”]
<#
.SYNOPSIS
Add-SPProfileDirectSyncConnection.ps1
.DESCRIPTION
Establishes a new Active Directory Direct user profile synchronization connection for SharePoint 2013.
.NOTES
Author: Sean McDonough
Last Revision: 27-June-2013
#>
param (
[parameter(mandatory=$true,position=1)][object]$userProfileApp,
[parameter(mandatory=$true)][string]$displayName,
[parameter(mandatory=$true)][string]$forestName,
[parameter(mandatory=$true)][string]$syncOU,
[parameter(mandatory=$false)][switch]$useSsl = $false,
[parameter(mandatory=$false)][switch]$useDisabledFilter = $false,
[parameter(mandatory=$false)][string]$ldapFilter = "",
[parameter(mandatory=$true)][string]$domain,
[parameter(mandatory=$true)][string]$username,
[parameter(mandatory=$true)][string]$password,
[parameter(mandatory=$false)][string]$claimProviderType = "Windows",
[parameter(mandatory=$false)][string]$claimProviderId = "Windows",
[parameter(mandatory=$false)][string]$claimIdMapAttribute = "samAccountName"
)
# Perform an LDAP lookup to get the object ID for the target domain.
$ldapLookupContext = "LDAP://" + $forestName
$ldapUsername = $domain + "\" + $username
$objDomain = New-Object System.DirectoryServices.DirectoryEntry($ldapLookupContext, $ldapUsername, $password)
if ($useSsl) {
$objDomain.AuthenticationType = [System.DirectoryServices.AuthenticationTypes]::SecureSocketsLayer
}
$ldapDomainDn = $objDomain.distinguishedName
$ldapDomainGuid = New-Object Guid($objDomain.objectGUID)
# Creation of the objects needed to properly specify the OU for the sync connection
$dnCtx = New-Object Microsoft.Office.Server.UserProfiles.DirectoryServiceNamingContext(
$ldapDomainDn, $forestName, $isDomain, $ldapDomainGuid, $includedOU, $excludedOU, $preferredDCs, $useOnlyPreferredDCs)
$namingContext = New-Object System.Collections.Generic.List[[Microsoft.Office.Server.UserProfiles.DirectoryServiceNamingContext]]
$namingContext.Add($dnCtx)
$ncParam = [System.Collections.Generic.List[Microsoft.Office.Server.UserProfiles.DirectoryServiceNamingContext]]$namingContext
# Since the method we’re about to invoke is internal, some hoops have to be jumped through
# to call it via PowerShell and Reflection
$paramTypes = @([Microsoft.Office.Server.UserProfiles.ConnectionType], [System.Guid], `
[System.String], [System.String], [System.Boolean], [System.Boolean], [System.String], `
[System.String], [System.String], [System.Security.SecureString], `
[System.Collections.Generic.List[Microsoft.Office.Server.UserProfiles.DirectoryServiceNamingContext]], `
[System.String], [System.String], [System.String])
$addConnMethodInfo = [Microsoft.Office.Server.UserProfiles.ConnectionManager].GetMethod("AddActiveDirectoryImportConnection", `
$INVOKE_ATTRIBUTES_NON_PUBLIC_MEMBERS, $null, $paramTypes, $null)
$methodParams = @($connType, $dcId, $displayName, $forestName, $isSslUsed, $isDisabledFilterUsed, $ldapFilter, `
$domain, $username, $securePassword, $ncParam, $claimProviderType, $claimProviderId, $claimIdMapAttribute)
$addConnMethodInfo.Invoke($upConfigMgr.ConnectionManager, $methodParams)
The script accepts a variety of parameters and has a couple of switches:
-userProfileApp. A reference to the User Profile Service Application instance for which the sync connection will be provisioned. This parameter is required.
-displayName. A string containing the “friendly name” that will appear for the sync connection within Central Administration. This parameter is required.
-forestName. A string that contains the fully qualified domain name (FQDN) for the domain that hosts the user profiles that will be imported by the sync connection. This parameter is required.
-syncOU. A string that contains the full distinguished specification of the Active Directory organizational unit (OU) where target user accounts (for import) reside. This parameter is required.
-useSsl. A switch that can be used to direct the script to use Secure Sockets Layer (SSL) transport encryption when connecting to Active Directory for synchronization operations and LDAP lookups. If this switch is not specified, connections are not made using SSL.
-useDisabledFilter. If this switch is included, the user profile selection process that takes place for the sync connection being established will filter out user accounts that are disabled. If this switch is not specified, both active and inactive user accounts will be imported during synchronization.
-ldapFilter. A string that contains an LDAP (Lightweight Directory Access Protocol) filter that will be applied to restrict the accounts which will be imported during synchronization. Creating this type of filter is made easier with the use of a tool like ADSI Edit. This parameter is optional.
-domain. A string containing the NetBIOS name of the domain where the profile synchronization account resides. This string is combined with the –username parameter to form a domain + username account specification. This parameter is required.
-username. A string containing the username of the profile synchronization account. This parameter should include the username only without a domain name qualifier. A domain name is specified separately through the –domain parameter. This parameter is required.
-password. A string containing the plaintext password of the profile synchronization account. This parameter is required.
-claimProviderType. A string identifying the type of authentication provider that is used to encode user profile account names. Common values for this parameter are Windows and Trusted. This parameter is optional; if unspecified, a value of Windows is used.
-claimProviderId. A string specifying the name or ID of the claims provider in-use. When a –claimProviderType of Windows is used, this parameter value is also typically Windows. If a –claimProviderType of Trusted is used, then this parameter value is used in conjunction with that value to identify the specific auth provider instance used. This parameter is optional and will default to Windows if unspecified.
-claimIdMapAttribute. A string that specifies the claims ID or field used by the claim provider. This parameter is specific to the claim provider, and in Windows authentication scenarios will usually be samAccountName. This parameter is optional.
The following is an example of how you might use the Add-SPProfileDirectSyncConnection script in a larger script that provisions user profile Active Directory Direct sync connections.
Some tips and information regarding the script and its usage:
Before executing this script, ensure that the account under which the script is being run has Full Control connection permission to the User Profile Service Application. This is set by highlighting the UPA instance in the “Manage Service Applications” page (ServiceApplications.aspx) in Central Admin, clicking the Permissions button on the toolbar, and granting Full Control permissions to the desired account as shown on the right. If this step is not performed, the script will error-out.
If a sync connection with the supplied –displayName already exists, the script will exit. It will not attempt to create another connection with the same name.
Ensure that the user profile synchronization account specified has the necessary replication permissions with the target user profile store. If the account lacks these permissions, the sync connection will be properly established but profiles will not be imported. Both TechNet and Spence Harbar covers this permission requirement in their various articles/posts, and it’s the same one that was needed in SharePoint 2010.
Final Word
My goal with this post was to (hopefully) save some of you the hassle I encountered while trying to automate a task that I thought would have been trivial … but wasn’t. Please let me know if this ends up helping you!
And although this script has been tested, I don’t have the facilities to test every combination of parameter and environment. If you find something that’s wrong or should be refactored, please let me know and I’ll perform an update.
After applying some recently-released patches for SharePoint 2013, my farm’s App infrastructure went belly-up. This post describes my troubleshooting and resolution.
I’ve been doing a lot of work with the new SharePoint 2013 App Model in the last few months. Specifically, I’ve been working on a free tool (for Idera) that will be going into the SharePoint App Marketplace sometime soon. The tool itself is not too terribly complicated – just a SharePoint-hosted app that will allow users to analyze library properties, compare library configuration settings, etc.
The development environment that I was using to put the new application together had been humming along just fine … until today. It seems that I tempted fate today by applying a handful of RTM patches to my environment.
What Happened?
I’d heard that some patches for SharePoint 2013 RTM had been released, so I pulled them down and applied them to my development environment. Those patches were:
After all binaries had been installed and a reboot was performed, I ran the SharePoint 2013 Products Configuration Wizard. The wizard ran and completed without issue, Central Administration popped-up afterwards, and life seemed to be going pretty well.
I went back to working on my SharePoint-hosted app, and that’s when things went south. When I tried to deploy the application to my development site collection from Visual Studio 2012, it failed with the following error message:
Error occurred in deployment step ‘Install app for SharePoint’: We’re sorry, we weren’t able to complete the operation, please try again in a few minutes. If you see this message repeatedly, contact your administrator.
Okay, I thought, that’s odd. Let’s give it a second.
Three failed redeploys later, I rebooted the VM to see if that might fix things. No luck.
Troubleshooting
My development wasn’t moving forward until I figured out what was going on, so I did a quick hunt online to see if anyone had encountered this problem. The few entries I found indicated that I should verify my App settings in Central Administration, so I tried that. Strangely, I couldn’t even get those settings to come up – just error pages.
All of this was puzzling. Remember: my farm was doing just fine with the entire app infrastructure just a day earlier, and all of a sudden things were dead in the water. Something had to have happened as a result of the patches that were applied.
Not finding any help on the Internet, I fired-up ULSViewer to see what was happening as I attempted to access the farm App settings from Central Administration. These were the errors I was seeing:
Insufficient SQL database permissions for user ‘Name: SPDC\svcSpServices SID: S-1-5-21-1522874658-601840234-4276112424-1115 ImpersonationLevel: None’ in database ‘SP2013_AppManagement’ on SQL Server instance ‘SpSqlAlias’. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object ‘proc_GetDataRange’, database ‘SP2013_AppManagement’, schema ‘dbo’.
Seeing that my service account (SPDC\svcSpServices) didn’t have the access it needed to run the proc_GetDataRange stored procedure left me scratching my head. I didn’t know what sort of permissions the service account actually required or how they were specifically granted. So, I hopped over to my SQL Server to see if anything struck me as odd or out-of-place.
Looking at the SP2013_AppManagement database, I saw that members in the SPDataAccess role had rights to execute the proc_GetDataRange stored procedure. SPDC\svcSPServices didn’t appear to be a direct member of that group (that I could tell), so I added it. Bazinga! Adding the account to the role permitted me to once again review the App settings in Central Administration.
Unfortunately, I still couldn’t deploy my Apps from Visual Studio. Going back to the ULS logs, I found the following:
Insufficient SQL database permissions for user ‘Name: NT AUTHORITY\IUSR SID: S-1-5-17 ImpersonationLevel: Impersonation’ in database ‘SP2013_AppManagement’ on SQL Server instance ‘SpSqlAlias’. Additional error information from SQL Server is included below. The EXECUTE permission was denied on the object ‘proc_AM_PutAppPrincipal’, database ‘SP2013_AppManagement’, schema ‘dbo’.
It was obvious to me that more than just a single account was out of whack since the proc_AM_PutAppPrincipal stored procedure was now in-play. Rather than try to manually correct all possible permission issues, I decided to try and get SharePoint to do the heavy lifting for me.
Resolution
Knowing that the problem was tied to the Application Management Service, I figured that one (possible) easy way to resolve the problem was to simply have SharePoint reprovision the Application Management Service service application. To do this, I carried out the following:
Deleted my App Management Service Application instance (which I happened to call “Application Management Service”) in Central Administration. I checked the box for Delete data associated with the Service Applications when it appeared to ensure that I got a new app management database.
Once the service application was deleted, I created a new App Management Service service application. I named it the same thing I had called it before (“Application Management Service”) and re-used the same database name I had been using (“SP2013_AppManagement”). I re-used the shared services application pool I had been using previously, too.
After completing these steps, I was able to successfully deploy my application to the development site collection through Visual Studio. I no longer saw the stored procedure access errors appearing in the ULS logs.
What Happened?
I don’t know what happened exactly, but what I observed seems to suggest that one of the patches I applied messed with the App Management service application database. Specifically, rights and permissions that one or more accounts possessed were somehow revoked by removing those accounts from the SPDataAccess role. Additional role and/or permission changes could have been made, as well – I just don’t know.
Once everything was running again, I went back into my SQL Server and had a look at the (new) SP2013_AppManagement database. Examining the role membership for SPDC\svcSpServices (which was one of the accounts that was blocked from accessing stored procedures earlier), I saw that the account had been put (back) into the SPDataAccess role. This seemed to confirm my observation that somehow things became “unwired” during the patching and/or configuration wizard run process.
My recent attempts to configure the Windows Azure Workflow service (Workflow 1.0 Beta) with a SQL Server alias didn’t go so well. If you’re playing with Workflow 1.0 Beta, stay away from aliases!
I’ve been doing a bit of build-out with the new SharePoint 2013 Preview in anticipation of some development work, and I’ve documented a few snags that I’ve hit along the way. Although I ran into some additional problems with the SharePoint 2013 Preview yesterday, this post isn’t about SharePoint specifically; it’s about the Windows Azure Workflow service – also known (at this point in time) simply as Workflow 1.0 Beta.
A Bit of Background
If you’re brand-new to the SharePoint 2013 scene, you may not yet have heard: the future for workflow lies outside of SharePoint, not within it. The Windows Azure Workflow service (yes, it even has “Azure” in the name if you’re running it on-premise and not in the cloud) is industrial-strength stuff, and it promises all sorts of improvements over workflow as we know it (and use it) right now.
To take advantage of Windows Azure Workflow at this point in the SharePoint 2013 release cycle requires the installation of the Workflow 1.0 Beta. The installation is not a particularly complicated process, but that’s probably because I’ve been using a solid resource.
Note: the “solid resource” I’m referring to is CriticalPath Training’s VM setup guide. I’ve been using it as a reference as I’ve been doing my SharePoint 2013 build-outs; the guide itself is fantastic and comes with some supporting PowerShell scripts to help things along. The guide and scripts are freely available here – you just need to create an account on the CriticalPath Training site to download them. I recommend them if you’re just getting started with the SharePoint 2013 Preview.
So, what’s my beef with the Workflow 1.0 Beta? To summarize it in a few works: Workflow 1.0 Beta doesn’t seem to work with SQL Server aliases. I certainly tried, but in the end I was forced to abandon using an alias.
How I Initially Configured It
If you read my previous “An unexpected error has occurred” post, then you know that there are four different VMs I’m configuring for a SharePoint 2013 environment. Two of those VMs are of interest in the discussion about Workflow 1.0 Beta configuration:
SP2013-SQL. A SQL Server 2013 Enterprise VM
SP2013-APPS. A utility server for running Workflow 1.0 Beta and other “off-box” services
As a general rule of thumb, anytime I need to establish a SQL Server connection, I try to create a SQL Server alias to avoid tightly coupling my SQL Server consumers/clients directly to a SQL Server instance. This buys me some flexibility in the unfortunate event that a server dies, I need to relocate databases, etc.
I was planning to install the Workflow 1.0 Beta on my SP2013-APPS virtual machine, and I knew that Workflow 1.0 Beta would need to connect to my SP2013-SQL SQL Server. So, I created both a 32-bit alias and a 64-bit alias called SpSqlAlias for the default SQL Server instance residing on SP2013-SQL (which happened to be at IP address 172.16.0.2) as shown on left.
Once the alias was created and all other prerequisites were addressed, I started the Workflow 1.0 Beta installation process. In the Workflow Configuration Wizard, I supplied my SQL Server alias in place of a server name, checked the connection, and was given a green check-mark. As the configuration process started, everything looked good. Even the Service Bus farm management and gateway databases were created without issue.
The problems started shortly thereafter, though, during the creation of a default container. Basically, I didn’t get any further. I literally stared at the screen on the right for a full ten (10) minutes without seeing any meaningful activity in the Details box. After 10 minutes had elapsed, the configuration process failed and I was treated to an exception message and stack trace. Omitting the inner exception detail, here’s what I was told:
[sourcecode language=”text”]
System.Management.Automation.CmdletInvocationException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) —> System.ComponentModel.Win32Exception: The system cannot find the file specified
[/sourcecode]
Validating the Alias
Of course, the first thing I double-checked was the SQL Server to ensure that it was responding. It was. I even backed through the configuration wizard a couple of steps and verified (with the “Test Connection” button) that I could reach the SQL Server. No issues there: my SQL Server alias was valid as far as the configuration wizard was concerned.
Looking more closely at the exception message left me suspicious. This part in particular made me raise my eyebrow:
(provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)
Named Pipes Provider? I had specified a TCP/IP alias, not Named Pipes. Changing the permitted 32-bit and 64-bit client protocols (again, via the SQL Server Configuration Manager) to make sure that TCP/IP was enabled and Named Pipes was disabled …
… made no difference, either – I’d still get an exception from the Named Pipes Provider. It looked as though one or more steps in the configuration process were “doing their own thing,” ignoring my alias and client protocols configuration, and (as a result) having trouble reaching the SQL Server.
Trying to Go with the Flow
The thought that entered my mind was, “Ok – don’t fight it if you don’t have to.” If the configuration wizard was going to fall back to using Named Pipes, then I’d go ahead and set up a Named Pipes alias. I wasn’t thrilled about the idea, but I’d rather have the SQL Server alias in-place than no alias at all.
So much for that thought.
I played with the actual Named Pipes alias format quite a bit, but in the end the result was always the same.
Attempts to use a TCP/IP alias always failed partway through configuration, and attempts to use a Named Pipes alias never even got started.
The Result
I gave it some more thought … and came up empty. So, I dumped any remaining aliases, ensured that all client protocols were back to their fully enabled state, and tried to do the configuration with just the SQL Server host name (to connect to the default instance).
The result?
Using just the host name, I had no issues performing the configuration.
The Conclusion
If you are setting up Workflow 1.0 Beta, stay away from SQL Server aliases. As best as I can tell, they aren’t (yet) supported. I’m hopeful that this is just a beta bug or limitation.
On the other hand, if you think I’ve gone off the deep end and can find some way to get the Workflow 1.0 Beta configuration to run with SQL Server aliases, please let me know – I’d love to hear about it!
After installing the current SharePoint 2013 preview build, I was greeted by “An unexpected error has occurred” message while trying to navigate to the Central Administration site. This post represents the steps I took to troubleshoot the problem and implement a least-privileges fix for it.
You’ve undoubtedly heard the news: SharePoint 2013 is coming. The preview is available right now, and you can download it from TechNet if you want to join in the fun. Just make sure you can meet the hardware and environmental prerequisites. They’re somewhat brutal.
As you might have guessed from the title of this post, I’ve been trying to get in on the SharePoint 2013 fun. There are a number of things I’m supposed to be working on for SharePoint 2013, so building out a SharePoint 2013 environment with the new preview build has been high on my list of things to do.
This post is about a very recent experience with a SharePoint 2013 installation and configuration … and yes, it’s one that had me looking long and hard for a happy pill.
As with many of my other blog posts, this post takes a winding, iterative approach towards analyzing problems and trying to find solutions. Please bear with me or jump to the “Implementing the Change” section near the end if you want to blindly apply a change (based on the blog post title) and hope for the best.
Hitting a Small Snag
This blog post would be something of a disappointment if all it said was “… SharePoint 2013 installed without issue, and my environment lived happily ever after.”
No such luck; just look at the screenshot on the left. Sometimes I feel like I’m a magnet for “bad technology karma” despite my attempts to keep a clean slate in that area. Of course, SharePoint 2013 is only in the preview stages of release, so hiccups are bound to occur. I accept that. Like many of you, I went through it with SharePoint 2010 and SharePoint 2007, as well.
Strangely, though, I built-out a SharePoint 2013 environment with an earlier build (prior to the release of the current preview) some time ago. That’s why I was really surprised to see the message shown in the screenshot immediately upon completing a run of the SharePoint 2013 Products Configuration Wizard:
An unexpected error has occurred.
That’s it. No additional information, no qualification – just a technological “whoops” accompanied by the equivalent of a shoulder shrug from my VM environment.
The Setup
Let me take a step back to describe the environment I had put into place before trying to install and configure the SharePoint 2013 binaries.
One major difference between my latest SharePoint 2013 setup attempt and the previous (successful) attempt was the make-up of the server environment. After learning of some of the install restrictions that are specific to SharePoint 2013 (for example, Office Web Apps require their own server), I decided to build out the following virtual servers on my laptop and assemble them into a domain:
SP2013-DC: a Windows 2008 R2 Enterprise domain controller (for my virtual spdc.com domain)
SP2013-SQL: a Windows 2008 R2 Enterprise server running SQL Server 2012 Enterprise
SP2013-WFE: a Windows 2008 R2 Enterprise all-in-one SharePoint 2013 Server
SP2013-APPS: a Windows 2008 R2 Enterprise “extra” server for roles/components that couldn’t be installed alongside SharePoint
Overkill? Perhaps, but I wanted to get a feel for how the different components might interact in a “real” production environment.
I also opted for a least privileges install so that I could start to understand where some of the security boundaries had shifted versus SharePoint 2010. Since I planned to use the farm for my development efforts, I didn’t want to make the common developer mistake of shoehorning everything onto one server with unrestricted privileges. Such an approach dodges security-related issues during development, but it also tends to yield code that falls apart (or at least generates security concerns) upon first contact with a “real” SharePoint environment.
Failed Troubleshooting
As stated earlier, my setup problems started after I installed the SharePoint 2013 bits and ran the SharePoint 2013 Products Configuration Wizard. The browser window that popped-up following the configuration wizard’s run was trying to take me to the Farm Configuration wizard that lives inside the Central Administration site. Clearly I hadn’t gotten very far in configuring my environment.
I started looking in some of the usual locations for additional troubleshooting hints. Strangely, I couldn’t quickly find any:
The Central Administration site application pool looked okay and was spun-up
My Application and System event logs were pretty doggone clean – exceptionally few errors and warnings, and none that appeared relevant to current problem
I didn’t see anything in the Security log to suggest problems
I tried an IISRESET. I rebooted the VM. I checked my SQL alias to make sure nothing was messed-up there. I checked my farm service account permissions in SQL Server to ensure that the account had the dbcreator and securityadmin role assignments as well as rights to the associated databases. Heck, I even deprovisioned the server and re-ran the SharePoint 2013 Products Configuration Wizard twice – once with a complete wipe of the databases. Nothing I did seemed to make a difference. Time after time, I kept getting “An unexpected error has occurred.”
Some Insight
Maybe it was my go ‘rounds with previous SharePoint beta releases, or maybe it was a combination of Eric Harlan’s and Todd Klindt’s spirits reaching out to me (the point of commonality between Todd and Eric: the two of them are fond of saying “it’s always permissions”). Whatever the source, I decided to start playing around with some account rights. Since I was setting up a least-privileges environment, it made sense that rights and permissions (or some lack of them) could be a factor.
The benefit of having gotten nearly nowhere on my farm configuration task was that there wasn’t much to really troubleshoot. Only a handful of application pools had been created (as shown on the right), and only one or two accounts were actually in-play. Since my Central Administration site was having trouble coming up, and knowing that the Central Administration site runs in the context of the farm service/timer service account, I focused my efforts there.
In my farm, I had assigned SPDC\svcSPFarm for use by the timer service. This account was a basic domain account at the start – nothing special, and no interesting rights to speak of. To see if I could make any progress on getting the Central Administration site to come up, I dropped the account into the Domain Admins group and tried to access the Central Administration site again.
I had no luck at first … but after an IISRESET and a re-launch of the site, Central Administration came up. I pulled the account out of the Domain Admins group and re-tried the site. It came up, but again – after an IISRESET, I was back to “An unexpected error has occurred.”
I repeated the process again, but the second time around I used the local (SP2013-WFE) Administrators group instead of the Domain Admins group. The results were the same: adding SPDC\svcSPFarm to the Administrators group allowed me to bring Central Administration up, and removing the account from the Admininstrators group brought things back down.
Hunch confirmed: it looked like I was dealing with some sort of rights or permissions issue.
Of course, knowing that there is a rights or permissions issue and knowing what the specific issue is are two very different things. The practical part of me screamed “just leave the account in the Administrators group and move on.”
Unfortunately, I don’t deal well with not knowing why something doesn’t work. It’s a personal hang-up that I have. So, I started with some low-impact/low-effort troubleshooting: I adjusted my VM’s Audit Policy settings (via the Local Security Policy MMC snap-in) to report on all failures that might pop-up.
Unfortunately, the only thing this change actually did for me was reveal that some sort of WinHttpAutoProxySvc service issue was popping-up when SPDC\svcSPFarm wasn’t an administrator. After a few minutes of researching the service, I decided that it probably wasn’t an immediate factor in the problem I was trying to troubleshoot.
So much for finding a quick answer.
Wading Into the Muck
I knew that I needed to dig deeper, and I knew where my troubleshooting was going to take me next. Honestly, I wasn’t too excited.
I dug into my SysInternals folder and dug out Process Monitor. For those of you who aren’t familiar with Process Monitor, I’ll sum it up this way: it’s the “nuclear option” when you need diagnostic information regarding what’s happening with the applications and services running on your system. Process Monitor collects file system activity, Registry reads/writes, network calls – pretty much everything that’s happening at a process level. It’s a phenomenal tool, but it generates a tremendous amount of information. And you need to wade through that information to find what you’re looking for.
I did an IISRESET, fired-up Process Monitor, and tried to bring up the Central Administration site once again. Since the SPDC\svcSPFarm account was no longer an administrator, I knew that the site would fail to come up. My hope was that Process Monitor would provide some insight into where things were getting stuck.
Over the course of the roughly 30 seconds it took the application pool to spin-up and then hand me a failure page, Process Monitor collected over 220,000 events.
Gulp.
I don’t know how you feel about it, but 220,000 events was downright intimidating to me. “Browsing” 220,000 events wasn’t going to be feasible. I’d worked with Process Monitor before, though, and I knew that the trick to making headway with the tool was in judicious use and application of its filtering capabilities.
Initially, I created filters to rule out a handful of processes that I knew wouldn’t be involved – things like Internet Explorer (iexplore.exe), Windows Explorer (Explorer.EXE), etc. Each filter that I added brought the number of events down, but I was still dealing with thousands upon thousands of events.
After a little thinking, I got a bit smarter with my filtering. First, I knew that I was dealing with an ASP.NET application pool; that was, after all, where Central Administration ran. That meant that the activity in which I was interested was probably taking place within an IIS worker process (w3wp.exe). I set a filter to show only those events that were tied to w3wp.exe activity.
Second, I knew that my farm service account (SPDC\svcSPFarm) was at the heart of my rights and permissions issue. So, I decided to filter out any activity that wasn’t tied to this account.
Applying those two filters got me down to roughly 50,000 events. Excluding SUCCESS results dropped me to 10,000 events. Some additional tinkering and exclusions brought the number down even lower. I was still wading through a large number of results, though, and I didn’t see anything that I could put my finger on.
Next, I decided to place SPDC\svcSPFarm back into the Administrators group and do another Process Monitor capture. As expected, I captured a few hundred thousand events. I went through the process of applying filters and whittling things down as I had done the first time. Then I spent a lot of time going back and forth between the successful and unsuccessful runs looking for differences that might explain what I was seeing.
Two Bit Comedy
After doing a number of comparisons, I began to focus on a series of entries that were tagged with a result message of BAD IMPERSONATION (as seen below). I was seeing 145 of these entries (out of 220,000+ events) when the Central Administration site was failing to come up. When SPDC\svcSPFarm was part of the local Administrators group, though, I wasn’t seeing any of the entries.
My gut told me that these BAD IMPERSONATION entries were probably a factor in my situation, so I started looking at them a bit more closely.
Many of the entries were seemingly non-specific attempts to access the Registry, but I did notice a handful of file and Registry accesses where an explicit impersonation attempt was being made with the current user’s account context. In the example on the right, for instance, an attempt was being made by the worker process to use my account context (SPDC\s0ladmin) for a CreateFile operation – and that attempt was failing.
This led to me formulate (what may seem like an obvious) hypothesis: seeing the BAD IMPERSONATION results, I suspected that the SPDC\svcSPFarm account was lacking something like the ability to replace a process-level token, log on interactively, or something like that. I’m certainly no expert when it comes to the specific boundaries and abilities associated with each rights assignment, but again – my gut was telling me that I should probably play around with some of the User Rights Assignments (via Local Security Policy) to see if I might get lucky.
A Fortunate Discovery
I popped open the Local Security Policy MMC snap-in on the SP2013-WFE VM once again, and I navigated down to User Rights Assignment node. At first glance, I feared that my gut feeling was off-the-mark. Looking through the rights assignments available, I saw that SPDC\svcSPFarm had already been granted the ability to Replace a process level token and Log on as a service – presumably by the SharePoint 2013 Products Configuration Wizard.
I continued looking at the various rights assignments, though, and I discovered one that looked promising: Impersonate a client after authentication. SPDC\svcSPFarm hadn’t been granted that right in my environment, and it seemed to me that such a right might be handy in getting rid of the BAD IMPERSONATION results I was seeing with Process Monitor. I took a leap, granted SPDC\svcSPFarm the ability to Impersonate a client after authentication (as shown on the left), performed an IISRESET, and tried to reach the Central Administration site.
And I’ll be darned if it didn’t actually work.
I don’t normally get lucky like that, but hey – I wasn’t going to argue with it. I browsed around the Central Administration site for a bit to see if the site would remain responsive, and I didn’t notice anything out of the ordinary. I also performed an IISRESET and brought the Central Administration site back up with Process Monitor running just to double-check things. Sure enough, the BAD IMPERSONATION results were gone.
The Fix?
I honestly have no idea whether this problem was specific to my environment or something that might be occurring in other SharePoint 2013 preview environments. I also don’t know if my solution is the “appropriate” solution to resolve the issue. It works for now, but I still have a lot of configuration and actual development work left to do to validate what I’ve implemented.
Since I’m trying to maintain a least-privileges install, though, I’m willing to try this out for a while instead of falling back to placing my farm service account (SPDC\svcSPFarm) in the Administrators group. Placing the account in that group is a last resort for me.
In case you were wondering: I did perform some level of verification on this change. Since the account I was running as (SPDC\s0ladmin) was itself a member of Domain Admins, I created a standard domain user account (SPDC\joe.nobody – he’s always my go-to guy in these situations) and added it to the Farm Administrators group in Central Administration. I then did an IISRESET and opened a browser to the Central Administration site from the domain controller (SP2013-DC) to see if SPDC\joe.nobody could indeed access the site. No troubles. The fact that the SPDC\joe.nobody account wasn’t a member of either Domain Admins or the local Administrators group (on SP2013-WFE) did not block the account from reaching Central Administration. No “An unexpected error has occurred” reared its head.
Implementing the Change
If you are of a similar mindset to me (i.e., you don’t like to elevate privileges unnecessarily) and find yourself unable to reach Central Administration with the same symptoms I’ve described, here is the quick run-through on how to grant your farm/timer service account the Impersonate a client after authentication right as I did:
On your SharePoint Server, go to Start > Administrative Tools > Local Security Policy to open the Local Security Policy MMC snap in.
When the snap-in opens, navigate (in the left Tree view) to the Security Settings > Local Policies > User Rights Assignment node.
Locate the Impersonate a client after authentication policy in the right-hand pane.
Right-click the policy and select the Properties item that appears in the pop-up menu.
A dialog box will appear. Click the Add User or Group … button on the dialog box.
In the Select Users, Computers, Service Accounts, or Groups dialog box that appears, add your farm service/timer service account.
Click the OK button on each of the two open dialog boxes to exit out of them.
Close the Local Security Policy MMC snap-in.
Perform an IISRESET and verify that the Central Administration site actually comes up instead of “An unexpected error has occurred”
Conclusion
If the change that I described in this post and implemented in my environment causes problems or requires further adjustment, I’ll update this post. My goal certainly isn’t to mislead – only to share and hopefully help those who may find themselves in the same situation as me.
If you’ve seen this problem in your SharePoint 2013 preview environment, please let me know. I’d love to hear about it, as well as how your worked through (or around) it!
UPDATE (9/4/2012)
I ran into the same issue with the account that was being used to serve up non-Central Admin site collections; i.e., the account that I was using as the identity for the application pools servicing the web applications I created. In my environment, this was SPDC\svcSpContentWebs as seen below (for the SharePoint – 80 application pool):
Attempts to bring up a site collection without the Impersonate a client after authentication privilege being assigned to the SPDC\svcSpContentWebs account would usually yield nothing more than a blank screen. As with the farm service account, there was very little to troubleshoot until I went in with Process Monitor to look for a bunch of BAD IMPERSONATION results:
At this point, I’m willing to bet that any other accounts that are assigned as application pool identities will need to be granted the Impersonate a client after authentication privilege, as well.
In addition to the Impersonate a client after authentication privilege, I also ended up having to grant the SPDC\svcSpContentWebs account the Log on as a batch job privilege from within the Local Security Policy MMC snap-in. Without the privilege to Log on as a batch job, I was receiving an HTTP 503 error every time I tried to bring up a site collection. Troubleshooting this problem wasn’t as difficult, though; examining the System event log helped with the following description for the WAS (Windows Process Activation Service) warning on an Event 5021 that was appearing:
The identity of application pool SharePoint – 80 is invalid. The user name or password that is specified for the identity may be incorrect, or the user may not have batch logon rights. If the identity is not corrected, the application pool will be disabled when the application pool receives its first request. If batch logon rights are causing the problem, the identity in the IIS configuration store must be changed after rights have been granted before Windows Process Activation Service (WAS) can retry the logon. If the identity remains invalid after the first request for the application pool is processed, the application pool will be disabled. The data field contains the error number.
In my case, my account credentials were correct, but for some reason the Log on as batch job right hadn’t been assigned to the SPDC\svcSpContentWebs account. Each time the application pool tried to spin up, it failed and was stopped; I’d then get two warnings from WAS (5021 and 5057) in my System event log, and that would be followed by a WAS5059 error.