Last modified by sbrickey on 5/27/2016 12:18 PM
This article is long overdue, given how often I end up repeating myself.
SharePoint is a web application - however the users interact with it, in the end they're almost always interacting with code that is being run as a web application within IIS on the WFE's. (the tiny exception being PowerShell code and scheduled tasks, which may actually run on app servers, and interact directly with the database server - this is the 1%).
But as a computer is concerned, the web is a scary, dangerous, and sometimes harmful place to go and venture. As such, a wide array of technologies help protect you, when you open a web page. From the web browser itself, to the firewalls and antivirus/antimalware systems, to the operating system itself.
A Primary on Security
The most secure system, is the one that is never used. But the reality is, we don't build things to ignore them. We need to be able to go on the web, to access the services that it provides.
Security is therefore implemented on a scale. On the one hand, the most secure system is completely unusable. On the other hand, the most usable system is completely insecure. For people in the real world, computers need to know how to behave on that scale.
Security Within the Browser
For almost all people, working with SharePoint begins when the user opens a web browser (preferably Internet Explorer) to browse to their SharePoint site. In most cases, the browser doesn't know a SharePoint site from any other website, so it treats it as hostile. This means that it won't automatically tell the website who you are, or let the website open programs on your computer. In this scenario, your browser is placing its behavior on the secure side of the security scale, rather than the usability side. Only when it's told otherwise, will it change its behavior.
Additionally, to make SharePoint easier to use, people often want SharePoint to open programs on their computer. This is again a potentially VERY dangerous action to take! But, it also lets you synchronize your SharePoint calendar to Outlook for offline access or easier scheduling. Again, a scale between security and usability. Clearly, the browser needs to know which sites can be trusted for this most sacred of tasks.
Security Beyond the Browser
As much as SharePoint is a web application, it's also designed to be accessible/usable from other applications.
This presents two challenges:
- First, does the program trust that it's being launched (and told to open a file) under legitimate circumstances?
- Second, how will the application know which sites are trustworthy for things like automatic logon?
Most times, the application will simply trust the browser. Occasionally though, applications will keep their own set of settings in their own location.
Security Policy 1 : Internet Explorer : Intranet and Trusted Zones
Having already covered this in previous posts (IE Security - Trusted Sites and Intranet Zones), this is mostly an honerable mention.
This setting will basically put the browser into a very USABLE mode, with much lower security settings. Automatic login, launch programs, the works.
For those with ON-PREM SharePoint, using AD INTEGRATED authentication (NOT the same as ADFS), put your SharePoint site URLs in your INTRANET zone.
For those with ON-PREM SharePoint, using ADFS authentication, put your ADFS URLs in your INTRANET zone.
For those with CLOUD SharePoint, NOT using ADFS authentication, put your SharePoint site URLs in your TRUSTED zone.
For those with CLOUD SharePoint, using ADFS authentication, put your ADFS URLs in your INTRANET zone.
- The reason you use ADFS addresses is because that's when the browser actually tells the server which domain account you're logged in as, which is then sent back to SharePoint, behind your back so to speak.
- The reason that CLOUD uses TRUSTED sites (not ADFS) is because there's no way for the servers to match your computer login, so it just keeps and sends the name and password you provide.
Security Policy 2 : WebClient : Trusted Servers
When the user clicks Open With Explorer, the browser is actually telling Windows to open Explorer to the SharePoint URL. Windows then in turn will identify that web based folders are accessed using the WEBDAV protocol, which is provided by the Windows service called WebClient. When the WebClient attempts to open the URL, SharePoint wants to know the logon. The WebClient does NOT share the settings from Internet Explorer, so AD Integrated Authentication requires its own policy.
Reg Key : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters\AuthForwardServerList (MultiString Value)
Value : URL pattern per line
Documentation : https://support.microsoft.com/en-us/kb/943280
Usability Policy : WebClient : Max File Size
Within SharePoint, the default max file size is 250mb. Unfortunately, the WebClient has its own default max size of 50mb. Often worth bumping up.
Reg Key : HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\WebClient\Parameters\FileSizeLimitInBytes (DWORD)
Value : 50000000 is the default 50mb
Documentation : https://support.microsoft.com/en-us/kb/2668751
Optional Policy : Office : Open Office document directly in Office application
This policy setting allows you to choose whether Office documents located on web servers open directly in the registered application or through the web browser.
If you enable this policy setting, files will open directly in the associated Office application, bypassing the web browser.
If you disable this policy setting files will open through the web browser.
Reg Key : HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\15.0\common\internet\opendirectlyinapp
Reg Key : HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\16.0\common\internet\opendirectlyinappReg Key : HKEY_LOCAL_MACHINE\software\policies\microsoft\Office\15.0\common\fileio\opendirectlyinapp
Reg Key : HKEY_LOCAL_MACHINE\software\policies\microsoft\Office\16.0\common\fileio\opendirectlyinapp
Here are a few policies that you can set to help brand SharePoint to the users. These can help indicate to users that SharePoint is a trusted place to put their documents, or use it to indicate when SharePoint should NOT be used to store a file.
Branding Policy 1 : Office : Name your SharePoint site
When opening or saving documents within Office, you can override the default term 'SharePoint'.
Reg Key : HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\15.0\common\sharepointintegration\productname
Reg Key : HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\16.0\common\sharepointintegration\productname
Branding Policy 2 : Office : Name your SharePoint Sites heading
The folder name used to store network folder shortcuts published from SharePoint Server. "My SharePoints" (localized) by default.
Reg Key : HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\15.0\common\portal\linkpublishingfoldername
Reg Key : HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\16.0\common\portal\linkpublishingfoldername
Last modified by sbrickey on 5/23/2016 4:04 PM
People like to find reasons not to like SharePoint. I get that, though I often take a moment to listen to the reasons, because most of the time the reasons are bunk, and I can correct them. Not to say that SP doesn't have issues, but they aren't the big issues.
Last modified by sbrickey on 5/18/2016 2:38 PM
The other day, a client wanted their portal to provide a notification to the user at each logon, reminding them to be aware of the documents they are uploading, and the users who can access them. Basically, don't upload sensitive info to a very public library.
I decided that the easiest answer was to use SharePoint's Status Notification Bar. A quick and gentle reminder. They can dismiss the reminder on the page, but never permanently (or even for any period of time).
In the Site Assets library, I added a file with the following:
it's about as simple as can be... add the status... then grab the hyperlink, set the onclick code (since it needs to know the ID, which is only known after the status has been added). Then tell SP to load SP.JS and run the function.
Then I added a content editor web part, and linked it to the file.
Last modified by sbrickey on 3/16/2016 1:26 PM
Here are some links...
SharePoint Server 2016 : https://www.microsoft.com/en-us/download/details.aspx?id=51493
Language Packs : https://www.microsoft.com/en-us/download/details.aspx?id=51492
Project Server 2016 : Part of SP2016!
Last modified by sbrickey on 9/21/2015 9:23 AM
A while ago I was working with workflows programmatically, and I needed to see the XML data that SharePoint stored for each instance of a workflow (each time a workflow is run for a specific file/item).
For reasons that I don't recall offhand, I wanted to access it directly from the database (NOTE: I was doing this on a NON-PRODUCTION box, using a BACKUP copy of the content database - DO NOT DO THIS IN A PRODUCTION ENVIRONMENT)
Since the XML data is actually stored in as an encrypted blob in the content database, I also needed to decompress the data.
Here's the script...
Last modified by sbrickey on 8/28/2015 11:08 AM
The other day I was investigating a rogue GUID that appeared in SharePoint. The specifics aren't really relevant, except for two facts: First, SharePoint farms consist of several databases (10 minimum for Foundation edition, 18 for Standard edition, and 19 for Enteprrise edition). And second, SharePoint loves to use GUIDs; just about every primary key is a GUID.
Anyway. I was seeing a GUID, and I wanted to know where it came from.
Now, for those who aren't familiar with the SharePoint content database schemas (which you shouldn't worry about - fiddling with the databases is generally bad, and leaves you unsupported if anything goes wrong), a single TABLE is used to store ALL of the metadata across ALL of the site collections in the database. To accomplish this, the metadata table is MASSIVE (and is extremely tuned by MS). Since MS and SP have no way of knowing how much data you'll store in your metadata, they simply provide a large capacity of each type.
The last part is important, because I'm looking for a GUID, and the metadata table contains 6 core GUID fields (ID, SiteID, ListID, WorkflowID, ParentID, and DocID) as well as 350 fields for user defined metadata.
That's just ONE table (albeit the most tedious).
So I needed a better way. I needed something automatic (or at least really close).
To the TSQL drawing board, aka SQL Management Studio!
My plan was simple: have SQL build me the query.
First I needed to know about the columns. A quick search revealed the INFORMATION_SCHEMA.COLUMNS dmv, which includes the names of the database, schema, table, and column, as well as the column's datatype and row position/ordinal.
Next I needed to combine the rows of columns into a single string. For things like that, I prefer CTE's.
Since there's no guarantee that the GUID rows will be sequential, I can't just join the previous column to the next based on RowOrdinal+1... and doing a simple greater-than (and then filtering on max number of columns) turned out to be MASSIVELY slow, since the number of results expands very quickly when you've got every combination of 300 sequentially incrementing columns... so I needed a RowCounter that was partitioned to the table, to give each column its own incrementing number, which I could use to JOIN the current column to the next as I built my string of columns.
Finally, having the concatenated rows, I throw one more RowCount on the CTE results, to ignore all the records with incomplete strings.
Bump the recursion limit from 100, make sure the queries use NOLOCK (since we have no idea about the column indexes), adjust the string manipulation so that it's a bit more reusable (since it's being posted here), and it's done.
The Results (sample)
Obvious this doesn't include all of the tables... but the query is pretty flexible... the variables at the top define what type of data to look for, how to compare it, and the compared value... or feel free to modify it for your own needs.
I was originally thinking of actually executing the queries in a loop... decided that I can just as easily copy/paste the entire column of queries into a new window, and it's faster than the time it'd take to write the loop.
Last modified by sbrickey on 4/7/2015 2:51 PM
Introduced back in SharePoint 2007 (MOSS), was a Workflow web service that was designed to allow external applications to integrate with workflows hosted within SharePoint. Unfortunately, the web service is poorly documented, which leads to articles that provide a wide range of results, which are often specific to how the workflow is used.
So the first thing worth noting, is that dfs:myFields indicates that InfoPath Forms Services is being used to update the task data.
this is actually pretty straight forward... it looks into the XML, assumes that the first element is used to define the values... then for each of its child elements, assigns the hash table's key based on the name... the value is determined based on whether the child element also contains child elements (nested XML), in which case the entire subset of XML is used for the value.
I've adjusted the formatting, but all said and done, it's actually not too much... it's basically just checking the type of field, validating users, formatting dates, etc.
This method is deceptive...
Here, we start to see what matters...
Last modified by sbrickey on 2/10/2015 10:24 PM
Far too often, someone asks how they can best implement item level security. Generally, this occurs when a form is being designed, with the next step being to design a workflow that will restrict access to the form to the user who created it.
Last modified by sbrickey on 2/3/2015 2:56 PM
This question is fairly common, so here we go...
Permissions are always tricky, and many systems implement permissions differently. Some systems are overly simplistic, while others are overly complex. Here are some historical examples of permission systems:
- UNIX / POSIX style (Linux, BSD, etc)
Read | Write | Execute -- User | Group | World
The default ("World") can be granted permissions to Read, Write, and/or Execute (such as batch scripts)
Members of the Group to which the file belongs, has overriding permission to Read, Write, and/or Execute
Finally, the User to which the file belongs, has overriding permission to Read, Write, and/or Execute
if you are not given permission through one of these three options, you have no permission to the file (or directory)
Inherit from parent (yes/no)
Read | Write | List Folder Contents (for directories) -- Local User / Local Group / Domain User / Domain Group -- Allow / Deny
When inheriting from parent, unique permissions are additive
Whether permissions are applied from a local group or domain group makes no difference
Priorities for conflicting permissions are: User Deny > User Allow > Group Deny > Group Allow
(user deny takes priority over user allow, which takes priority over group deny, which takes priority over group allow)
basically, deny takes priority over allow, more specific (user) over less specific (group)
There are also some less common options as well (domain computers, builtin\EVERYONE, CREATOR, etc)
- Windows Shared Folders
similar to NTFS (read/write, local user/local group/domain user/domain group, allow/deny)
These permissions do NOT overwrite NTFS permissions (when the contents being shared are stored on an NTFS volume).
When using NTFS, user's access must be validated BOTH by the SHARE as well as NTFS
Within the Windows world, permissions have been a pain point for administrators for a LONG while. One of the goals of SharePoint was to SIMPLIFY the permission model, while still providing the necessary flexibility, and as much reusability as possible.
SharePoint's model is:
- Many specific "base" permissions (View, Add, Edit, Delete, Approve)
- Permission Levels create groups of the permissions ("Contributor" = View + Add + Edit + Delete)
- Permissions are applied to a "SharePoint Principal"... which is just a fancy way of saying that it's one of the following:
- External Group - since authentication can be handled by a number of different methods (Windows, MembershipProvider, Claims), this may be any of:
- Windows local or domain group
- Group as defined by the Membership Provider (ex: SQL Membership Provider can have custom groups, LDAP membership provider would use LDAP groups)
- Group as defined by a claim rule using a Claims provider (ex: user with location "US", user with group includes "Human Resources")
- SharePoint Group
- can include Users and External Groups
- Permissions use the GRANT model ONLY. There is no deny*
The model provides flexibility in two specific areas of its implementation
By allowing base permissions to be grouped, the permission levels tend to be implemented as roles for the user to perform.
By default, SharePoint will create the following permission levels:
- Read provides all of the permissions necessary to read/open/access the contents within SharePoint.
- Contribute provides all of the permissions from Read, as well as those necessary to add, edit, or delete contents.
- Approve provides all of the permissions from Contribute, as well as permission to approve or reject content.
- Design provides all of the permissions from Approve, as well as permission to adjust the look of the site (colors, themes, etc)
- Manage Hierarchy provides all of the permissions from Contribute, as well as permission to permission to add and delete lists, libraries, and subsites
- Full Control provides everything.
Further, you can create your OWN permission levels. Examples that I've seen:
- Submit Only (add, but not edit or delete)
- Contribute Without Delete (basically Contribute, but excluding permission to delete items)
If you ask a systems administrator how permissions should be applied, I would bet that 99% of the people will say "Active Directory Group". This answer is especially common in certain heavily regulated industries (healthcare, financial, government), often because a report of groups and their users are readily available for security teams and auditors.
While that answer has a certain amount of validity, it also places a burdon on the IT staff.
And unfortunately, there are a LOT of organizations that don't have the IT staff or budget neccessary to manage such an approach.
Sample Permission Analysis
So, from the File to the Permissions, SharePoint has to determine the following:
File ~ Each permission rule ~ SharePoint Group (optional) ~ External Group ~ User ~ Permission Level ~ Base Permission
each separate area can (and often will) multiply the rows of data. For example:
Example.PDF ~ ACL #1 ~ Site Visitors ~ DOMAIN\All Domain Users ~ Jane Doe, John Smith ~ Read ~ ViewListItems, OpenItems, Open, ViewPages
Example.PDF ~ ACL #2 ~ Site Contributors ~ DOMAIN\HR ~ Peggy, Dan, Erin ~ Contribute ~ ViewListItems, AddListItems, EditListItems, ...
Example.PDF ~ ACL #3 ~ Site Owners ~ Peggy, Dan ~ Full Control ~ all 36 permissions
so just this one file, having 3 sets of permissions (visitors, contributors, owners), can generate around 110 individual permissions (8 + 30 + 72) that SharePoint needs to evaluate.
Results vs Goals : Conclusion
By removing the ability to DENY permissions, the SharePoint permission model has successfully simplified much of the headaches that are present in NTFS.
By controlling access to the content through its APIs exclusively, the headaches of permission combinations with NTFS and Shared Folders was avoided entirely; further, by supporting other protocols (WebDAV), SharePoint provides a similar usability experience as Shared Folders with its unified security model.
By providing Permission Levels, the security model allows reuse of permissions and focuses on Roles, rather than individual permissions.
By providing SharePoint Groups, the use of groups can be implemented by smaller organizations (too small to manage permissions as AD groups), or by isolated teams where reuse is not necessary.
By adding the flexibility, the process of determining a users' permissions can require more lookups (SharePoint 2010 added the "Effective Permissions" button to aid in this process), and the additional relationships between Permission Levels and groups can be confusing, as well as the relationship between SharePoint Groups and Domain Groups.
Unfortunately, it's about as good as it can get. The simple truth is that in some ways, we live in a complex world, and developing a model that applies to such a large scope of users is not possible without some complexity. All in all, the permission model is only complex until you take the effort to understand it.
* there is ONE place that DENY can be used, and I won't say where because it SHOULDN'T be used.
- for those curious, SharePoint only asks the authentication provider what groups you're in... it's up to the provider to flatten any recursive group memberships... Windows does this automatically, though some providers like the SqlMembershipProvider don't support nested groups
- Technically, SharePoint doesn't need to evaluate each of the base permission levels, as they are implemented as bit flags that can be combined using rather trivial calculations. The net result is that instead of analyzing all 110 combinations, SharePoint would actually read 7 records (2 + 3 + 2), then flatten their permissions with the bitwise OR operation, and compare it to the operation being considered/attempted.