Getting the Permissions Wired-Up Properly When Attaching a Content Database to a SharePoint Farm

by Oct 19, 2012

Categories

Tags

Administration agent-based monitoring Agentless Monitoring alert responses alert thresholds alerting Alerts Amazon Aurora Amazon EC2 Amazon RDS Amazon RDS / Aurora Amazon RDS for SQL Server Amazon Redshift Amazon S3 Amazon Web Services (AWS) Analytics application monitoring Aqua Data Studio automation availability Azure Azure SQL Database azure sql managed instance Azure VM backup Backup and recovery backup and restore backup compression backup status Backup Strategy backups big data Blocking bug fixes business architecture business data objects business intelligence business process modeling business process models capacity planning change management cloud cloud database cloud database monitoring cloud infrastructure cloud migration cloud providers Cloud Readiness Cloud Services cloud storage cloud virtual machine cloud VM clusters code completion collaboration compliance compliance audit compliance audits compliance manager compliance reporting conference configuration connect to database cpu Cross Platform custom counters Custom Views customer survey customer testimonials Dark Theme dashboards data analysis Data Analytics data architect data architecture data breaches Data Collector data governance data lakes data lineage data management data model data modeler data modeling data models data privacy data protection data security data security measures data sources data visualization data warehouse database database administration database administrator database automation database backup database backups database capacity database changes database community database connection database design database developer database developers database development database diversity Database Engine Tuning Advisor database fragmentation database GUI database IDE database indexes database inventory management database locks database management database migration database monitoring database navigation database optimization database performance Database Permissions database platforms database profiling database queries database recovery database replication database restore database schema database security database support database synchronization database tools database transactions database tuning database-as-a-service databases DB Change Manager DB Optimizer DB PowerStudio DB2 DBA DBaaS DBArtisan dBase DBMS DDL Debugging defragmentation Demo diagnostic manager diagnostics dimensional modeling disaster recovery Download drills embedded database Encryption End-user Experience entity-relationship model ER/Studio ER/Studio Data Architect ER/Studio Enterprise Team Edition events execution plans free tools galera cluster GDPR Getting Started Git GitHub Google Cloud Hadoop Healthcare high availability HIPAA Hive hybrid clouds Hyper-V IDERA IDERA ACE Index Analyzer index optimization infrastructure as a service (IaaS) infrastructure monitoring installation Integrated Development Environment interbase Inventory Manager IT infrastructure Java JD Edwards JSON licensing load test load testing logical data model macOS macros managed cloud database managed cloud databases MariaDB memory memorystorage memoryusage metadata metric baselines metric thresholds Microsoft Azure Microsoft Azure SQL Database Microsoft PowerShell Microsoft SQL Server Microsoft Windows MongoDB monitoring Monitoring Tools Monyog multiple platforms MySQL news newsletter NoSQL Notifications odbc optimization Oracle PeopleSoft performance Performance Dashboards performance metrics performance monitoring performance schema performance tuning personally identifiable information physical data model Platform platform as a service (PaaS) PostgreSQL Precise Precise for Databases Precise for Oracle Precise for SQL Server Precise Management Database (PMDB) product updates Project Migration public clouds Query Analyzer query builder query monitor query optimization query performance Query Store query tool query tuning query-level waits Rapid SQL rdbms real time monitoring Real User Monitoring recovery regulations relational databases Releases Reporting Reports repository Restore reverse engineering Roadmap sample SAP Scalability Security Policy Security Practices server monitoring Server performance server-level waits Service Level Agreement SkySQL slow query SNMP snowflake source control SQL SQL Admin Toolset SQL CM SQL code SQL coding SQL Compliance Manager SQL Defrag Manager sql development SQL Diagnostic Manager SQL Diagnostic Manager for MySQL SQL Diagnostic Manager for SQL Server SQL Diagnostic Manager Pro SQL DM SQL Doctor SQL Enterprise Job Manager SQl IM SQL Inventory Manager SQL Management Suite SQL Monitoring SQL Performance SQL Quality SQL query SQL Query Tuner SQL Safe Backup SQL script SQL Secure SQL Security Suite SQL Server sql server alert SQL Server Migration SQL Server Performance SQL Server Recommendations SQL Server Security SQL statement history SQL tuning SQL Virtual Database sqlmemory sqlserver SQLyog Storage Storage Performance structured data Subversion Support tempdb tempdb data temporal data Tips and Tricks troubleshooting universal data models universal mapping unstructured data Uptime Infrastructure Monitor user experience user permissions Virtual Machine (VM) web services webinar What-if analysis WindowsPowerShell

You’ve probably heard the latest news in the SharePoint world: SharePoint 2013 has been released to manufacturing (RTM)! We’ve been told that SharePoint 2013 should start to appear “in the wild” starting sometime in November, and I expect that the amount of tinkering by enthusiasts with both the public preview release and the soon-to-be-available production release is only going to increase in the weeks and months ahead.

New releases of SharePoint usually entail moving databases between production farms and test environments constructed with the newer version of SharePoint. Since SharePoint 2013 only supports database-attach upgrades, it seemed like a good time to cover some of the concerns associated with moving content databases between SharePoint environments. This may help some of you who are new to SharePoint; for the rest of us, it is (hopefully) good refresher material.

Doing the Database Shuffle

Moving content databases between SharePoint farms happens more often than you might think. Sure, it happens in the aforementioned SharePoint version upgrade scenario, but it probably happens even more often in cases where content in a development, testing, or staging environment needs to be refreshed with (current) production content.

The sequence of steps that are employed to copy content databases from a source farm to a destination farm are generally well-documented and understood. I’m going to focus on one specific scenario for the purposes of this post, though: copying databases to an environment where those databases don’t yet exist.

In this type of “first-time copy” scenario, the following set of steps is pretty common:

 1. A content database is identified for copy purposes. For new administrators, identification of a target content database is typically achieved by going into the SharePoint Central Administration site. The Manage Content Databases page (as shown on the right) makes it easy to determine the identify of a content database that is associated with a target Web application.

2. A backup (.BAK file) of the content database is created in the source environment using SQL Server’s built-in backup mechanism. Alternatively, a database detach – followed by a copy of any .MDF and .LDF files associated with the database – may be performed.

3. The backup (.BAK) or detached database file set (.MDF and .LDF) is copied from the source environment to the destination environment.

4. In the destination environment, a restore is performed (in the case of a .BAK file) or the database files are attached (in the case of .MDF and .LDF files) to SQL Server.

5. The SharePoint content database, which is now available in SQL Server, is attached to a Web application that has been created in the destination environment/farm.

What Could Possibly Go Wrong?

Step #5 in the previous sequence is where the frustration starts for many new and accidental SharePoint administrators. Why is that the case? Well, seasoned SharePoint administrators know that the “right” (or at least “easiest”) way to mount a content database is to drop down into the SharePoint Management Console (PowerShell) and issue a Mount-SPContentDatabase command, such as

Mount-SPContentDatabase –Name SP2010_Content_SPMcDonoughWeb80 –WebApplication http://spdev:18580/

 New and accidental SharePoint administrators tend to gravitate towards the SharePoint Central Administration site, though, rather than using a PowerShell prompt. Heck, I’ve been working with SharePoint for years, and I still prefer to do things through a web user-interface (UI) if the option is available to me. The problem in this case, however, is that attempting to attach the database to a Web application doesn’t work properly via the Manage Content Databases page in a properly configured, least privileges environment. With SharePoint 2010, an exception (like the one on the left) is thrown. The generic form of this exception:

Cannot open database “xxx” requested by the login. The login failed. Login Failed for user ‘yyy’.

If you hadn’t seen this type of exception before trying to add the content database, you might spend an awful lot of time trying to figure out where to go next. In SharePoint 2010, no real “next step” help is provided. As it turns out, the solution to this problem is simple: use the Mount-SPContentDatabase cmdlet instead of Central Administration’s Manage Content Databases page.

Why Does This Happen?

When in doubt, blame it on permissions. That usually turns out to be a safe bet with SharePoint. In this case, it’s also accurate.

When a content database is joined to a farm and in normal, everyday use, there are always at least two accounts that have some form of access (typically as a SQL Server db_owner) to it:

  • Application Pool Account. The account context that is assigned to the IIS application pool within which the associated Web application runs. Normal user read/write operations take place through this security context.
  • Farm Service Account. The account context that SharePoint’s Timer Service run under; also the account context that is assigned to actions originating from SharePoint’s Central Administration site. Non-user activity (typically for configuration and/or administrative purposes) commonly occurs through this security context.

When a content database is either restored (in the case of a backup) or attached (if it had been detached) from another farm, these two accounts in the destination farm normally don’t have the rights they require to access the database. Because of this, SharePoint can’t (initially) do anything with the databases.

The Mount-SPContentDatabase cmdlet takes care of granting the permissions SharePoint needs in order to add a content database to a Web application in the destination environment. Adding the database through the Manage Content Databases page in Central Administration may look like an operation that is equivalent to Mount-SPContentDatabase, but it’s not. Microsoft actually does spell this out, but you need to dig for the information in both the database-attach TechNet reference I supplied earlier (for SharePoint 2013) or in the equivalent reference for SharePoint 2010:

You can use either the Mount-SPContentDatabase cmdlet in Windows PowerShell or the addcontentdb Stsadm command to attach a content database to a Web application. Using the SharePoint Central Administration pages to attach a content database is not supported for upgrading.

And now for some good news: Microsoft threw new (and accidental) administrators a bone in SharePoint 2013. Attempts to add a content database that doesn’t meet the permissions requirements through Central Administration results in this sort of dialog.

The content database requires upgrade and could not be attached in this operation. Use either the [stsadm –o addcontentdb] command or [Mount-SPContentDatabase] cmdlet instead.

Woo-hoo – actual guidance to resolve the issue!

Mounted-Up but Still Locked-Out.

 After the database is successfully mounted in a Web application, a common next step that administrators (naturally) take is to browse to a site collection that exists within content database that was just mounted. Unfortunately, the curse of insufficient permissions typically rears its head in this situation to block access to any of the site collections that exist within the content database.

The dialog on the right is a common sight in SharePoint 2010 environments when an account lacks the permissions to access a site collection. The message displayed really doesn’t leave much to go on.

Error: Access Denied

“But wait,” you might be saying, “I’m a member of the SharePoint Farm Administrators group. I should be able to access site collections in the content database I just added.” This is a common misconception, particularly amongst newer administrators. The reality is this: unless you have rights to the content itself, Farm Administrator status doesn’t get you there.

In truth, though, being a Farm Administrator does afford you the means to grant yourself access to the content. The key to entry lies with the creation of a Web application User Policy. For content databases that have been freshly added to a “new” farm, a Web application policy is a common first step to administrative access for configuration and additional rights assignment.

How do you “let yourself in” through a Web application policy? TechNet provides you with a walk-through and some additional information, but here’s the quick-and-dirty run-down:

 1. Open the Central Administration site

2. Select the Manage Web Applications link in the Quick Launch menu and navigate to the Manage web applications link that appears under the Web Applications section.

3. Select the target Web application from the list that appears, and then click the User Policy item in the ribbon.

4. A modal dialog like the one above and to the right will appear. Click the Add Users button in the modal dialog.

5. The dialog will change to permit a Web application zone selection. Unless you have a specific need to grant access through only one Web application zone, stick with the default setting of (All zones) and press the Next button.

6. In the dialog that appears next, add your name/identity to the Choose Users list. The Permissions settings are relatively self-explanatory, so choose Full Control or Full Read as appropriate. Finally, check the Account operates as System checkbox if you wish for your activities (within the Web application’s site collections) to be identified as having been carried out by the “System” account rather than through your (domain) account.

7. Click the Finish button and verify that the changes you made appear in the Web application policy list.

8. Click the OK button to close the Policy for Web Application dialog.

After creating a Web application User Policy, you should have the permissions needed to access site collections within the Web application.

If you are new to Web application User Policies, here’s an important point to note: Web application User Policies actually trump permission settings that are specified within the site collections themselves. This doesn’t come up too often with Full Control and Full Read permission policies, but it is a source of confusion (particularly for site collection administrators) when Deny Write and Deny All permission policies are in effect. If a user who is a Site Owner also happens to be identified in a Deny Write Web application User Policy, that user will be to access the site that they own … but they won’t be able to make changes (i.e., perform write activities)! Due to the potential for confusion, Web application Deny policies should be used sparingly.

To Sum It Up

 Are these the only hurdles you need to clear in order to mount and access the site collections contained in a “foreign” content database? No, but in my experience these are the ones that show up most often. If you can get a database mounted into a Web application and get access granted to an administrative account, you’re most of the way home – at least from a permissions perspective.

I deliberately avoided talking about the other major source of trouble: the upgrade process itself. Whether you’re going from SharePoint 2010 to SharePoint 2013, SharePoint 2007 to SharePoint 2010, or simply between farm versions in SharePoint 2010, there are a whole host of additional problems you may face.

Database-attach upgrades are an art, not a science. Permissions, on the other hand, are pretty well understood for content database mounting and access. Although there isn’t much prescriptive guidance I can give on the former, I hope that after reading this you feel decently equipped (or refreshed, if you are a veteran administrator) to tackle the latter.

References and Resources

  1. Microsoft SharePoint Team Blog: SharePoint Reaches RTM!
  2. TechNet: Download Microsoft SharePoint Server 2013 Preview
  3. TechNet: Attach databases and upgrade to SharePoint 2013
  4. TechNet: Attach databases and upgrade to SharePoint 2010
  5. TechNet: Manage permission policies for a Web application (SharePoint Server 2010)