Using TLS 1.2 Windows Server 2008 R2 & 2012 R2, SQL and SharePoint

Everyone uses a certificate when requiring authentication on an internet facing site. However it’s surprising how many folks don’t take the time to understand SSL/TLS. Securing SSL/TLS protocols is a pretty common thing to do on any Windows Server running IIS and web applications that uses HTTPS, especially if they require some sort of compliance. It is a good idea to do this on all of your servers in your SharePoint farm, to ensure your secure connections really are secure. It’s also important to note that while I have several SharePoint 2016 environments where I have removed both TLS 1.0/1.1. However, I have not removed TLS 1.1 from the any of my SharePoint 2013 environments. However, all of my clients with SharePoint 2013 are using a HW Load Balancer like the F5 and have offloaded SSL and removed TLS 1.0/1.1 using the HW…

All Microsoft Windows devices using SSL/TLS protocols use SCHANNEL, where you have to install something like OpenSSL on Linux. You may also notice that while OpenSSL has more security vulnerabilities they tend to respond quickly to them. However, Microsoft has been disappointingly slow in updating the cryptography stack in its OS and Applications. Note: there may be flags when running SSL Lab scans against your servers that you may not be able to resolve at this time. This may also apply to the availability of the latest cipher suites as well.

All of the configuration changes to SCHANNEL are stored in the registry: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL

 

The first time I created a GPO to Configure SSL/TLS, and deploy to the farm. I spent a few days with Regedit and reading technet, I recommend using IISCrypto from Nartac to make the changes to ensure the process goes a smooth as possible on your first server then after reboot, exporting the SCHANNEL Key for use with a GPO to automate the deployment for all additional servers in your farm

You can use the following command to export up the SCHANNEL registry settings prior to making the changes and again after for use with the GPO, should you need to restore it: reg export HKLM\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\ SChannel-Export.reg

Known issues

There are a few gotchas when making modifications to SCHANNELL on Windows, please QA as necessary in the lab prior to deploying to production:

  1. SQL Server used to require TLS 1.0, when you disabled it your SharePoint Servers would not be able to communicate with the SQL Cluster. Please review the information about the SQL updates and additional known issues using the following link TLS 1.2 support for Microsoft SQL Server, then download and install the appropriate SQL Updates. All versions prior to SQL Server 2016 require the updates regardless of Service Pack or Cumulative Update
  2. Please make sure you download and installed KB3080079 if you are running a version of Windows Server prior to Windows Server 2012 or RDS/RDP will break when after disabling TLS 1.0 and rebooting. Note: If you are using IISCrypto you may see a pop like the following screenshot after reviewing TLS 1.0/1.1

     

  3. Older clients > Windows XP and earlier may not be able to connect if they do not support the newer SSL/TLS technologies and you disable the older ones. Out of the box Windows Server is configured to be relatively compatible with older clients, which in turn makes it less secure. You can find a complete browser compatibility list here: https://en.wikipedia.org/wiki/Template:TLS/SSL_support_history_of_web_browsers
  4. Qualys will ding you for supporting 1024 bit DHE groups, and will recommend DHE key exchanges be increased to 2048 bit or disabled, but 1024 is the limit on all versions of Windows prior to Windows 10 at this time.
  5. Be sure to thoroughly test your applications after making any changes, mainly looking for connection failures over HTTPS. The errors will be listed in the system event log with SCHANNEL as the source

The following configuration works with most modern software (Windows Vista and newer) while providing a relatively robust SSL/TLS configuration, and earning an A ranking on Qualys’s SSL Labs tester.

IISCRYPTO

  1. Download IISCrypto and apply the “Best Practices” Template
  2. Use The Best Practice Template; Click Templates, Use the drop Down choose Best Practice, then click Apply
  3. Disable TLS 1.0 Assuming SQL updates have been applied and KB3080079for RDS/RDP has been applied
  4. Disable MD5 under Hashes enabled
  5. Click Apply
  6. Reboot
  7. Test your site with Qualys’s SSL Labs tester

QUALYS SSLLabs Ranking

 


 

SQL Query: Set All Dbs AutoGrowth

image

Recommendations

The following are recommendations to proactively manage the growth of data and log files:

When possible, increase all data files and log files to their expected final size, or periodically increase these at set periods, for example, every month or every six months, or before rollout of a new storage-intensive site such as during file migrations.

Enable database autogrowth as a protective measure to make sure that you do not run out of space in data and log files. Consider the following:

ImportantImportant:

You must factor in the performance and operations issues associated with using autogrowth. For more information, see Considerations for the “autogrow” and “autoshrink” settings in SQL Server.

Default Settings

The default settings for a new database are to grow by 1 MB increments. Because this default setting for autogrowth results in an increases in the size of the database, do not rely on the default setting. Instead, use the guidance provided in Set SQL Server options.

 

Set autogrowth values to a fixed number of megabytes instead of to a percentage. The bigger the database, the bigger the growth increment should be.

NoteNote:  Use care when you set the autogrowth feature for SharePoint databases. If you set a database to autogrowth as a percentage, for example at a 10-percent (%) growth rate, a database that is 5 GB grows by 500MB every time that a data file has to be expanded. In this scenario, you could run out of disk space. or the db could be 100gb and growth would be 10gb every time the file needed space

Consider for example, a scenario where content is gradually increased, say at 100MB increments, and autogrowth is set at 10MB. Then suddenly a new document management site requires a very large amount of data storage, perhaps with initial size of 50 GB. For this large addition, growth at 500 MB increments is more appropriate than 10MB increments.

For a managed production system, consider autogrowth to be merely a contingency for unexpected growth. Do not use the autogrow option to manage your data and log growth on a day-to-day basis. Instead, set the autogrowth to allow for an approximate size in one year and then add a 20 percent margin for error. Also set an alert to notify you when the database runs low on space or approaches a maximum size.

Maintain a level of at least 25 percent available space across drives to accommodate growth and peak usage patterns. If you add drives to a RAID array or allocate more storage to manage, monitor capacity closely to avoid running out of space. Setting autogrowth to use MB verse %  along with changing the autogrowth MB size will create less fragmentation than using the defaults, this SQL Query makes it easier to modify a large number of Dbs.

-- Query to Set File AutoGrowth

SELECT
    DB_NAME(mf.database_id) database_name,
    mf.name logical_name,
    CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, size) / 128)) [file_size_MB],
    CASE mf.is_percent_growth
        WHEN 1 THEN 'Yes'
        ELSE 'No'
    END AS [is_percent_growth],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
        WHEN 0 THEN CONVERT(VARCHAR, mf.growth / 128) + ' MB'
    END AS [growth_in_increment_of],
    CASE mf.is_percent_growth
        WHEN 1 THEN CONVERT(DECIMAL(20, 2), (((CONVERT(DECIMAL, size) * growth) / 100) * 8) / 1024)
        WHEN 0 THEN CONVERT(DECIMAL(20, 2), (CONVERT(DECIMAL, growth) / 128))
    END AS [next_auto_growth_size_MB],
    CASE mf.max_size
        WHEN 0 THEN 'No growth is allowed'
        WHEN -1 THEN 'File will grow until the disk is full'
        ELSE CONVERT(VARCHAR, mf.max_size)

    END AS [max_size],
    physical_name
FROM sys.master_files mf
ORDER BY database_name

 

Download: Set_AutoGrowth4AllDbs.sql

 

Ivan

SQL Query: Move TempDb Files to separate LUNs

image

 

Tempdb Multiple Files

One of the important issues when hosting multiple Content Dbs with multiple terabytes data is to ensure that you have created a TempDB with additional files of the same size and once created move teach of the files to their own Volume.

Do NOT use ISCSI for SQL Dbs

In this case we are using a NetApp SAN with Fiber Chanel HBAs as iSCSI does NOT meet our SQL performance requirements. I Microsoft provides multiple whitepapers against using iSCSI for SQL Dbs. Also, I have seen performance degradation using iSCSI once the ContentDBs reach about 50GB in total size. Using Fiber Chanel I have been able to host multiple terabytes of ContentDbs without any degradation in performance.

Hardware

This server has 2 PROCS with 24 Cores, and 128GB of RAM with tempdb having 1 MDF, 2 NDF, and 1 LDF files on separate LUNS.. We used this script to move the original tempdb files to the SAN and then once provisioned to move the NDF files to separate LUNs.

NAME = Tempdb file Logical Name

FILENAME = the New path with existing filename where you want the file moved

 

 

 

Screenshot of OLD Locations

image

Screenshot NEW Locations

image
-- Query to Move tempDB

SELECT
    name,
    physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

USE master;
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf');
GO

ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'L:\MSSQL\Data\templog.ldf');
GO

SELECT
    name,
    physical_name AS CurrentLocation,
    state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

 

Note: you will need to restart the SQL Server Instance to have the move take affect

 

Download SET_TempDBLocation.ps1

 

Ivan

PowerShell: Run IISReset on All Servers in your farm at the same time

image

 

IIS-Reset.ps1

One of the many things scripts are good for in general  is making repetitive tasks easier and the results more consistent. PowerShell takes it to another level with its intuitive cmdlets . I find it much easier run a script from my laptop or log into a single server rather than using MSTSC to login to every server in the farm, I am working in a SharePoint environment of 25 servers, so it wouldn’t definitely be a drag..

 

 

 

PowerShell Script

<#    IIS-Reset.ps1
Run IISReset on Multiple Servers #>

#Specify servers in an array variable
[array]$servers = "Server1","Server2","Server3","Server4"

#Step through each server in the array and perform an IISRESET
foreach ($server in $servers)
{
    Write-Host "Restarting IIS on server $server..."
    IISRESET $server /noforce
    Write-Host "IIS status for server $server"
    IISRESET $server /status
}
Write-Host IIS has been restarted on all servers

Download http://1drv.ms/1ZjF889

 

Ivan

del.icio.us Tags: PowerShell,IISReset,SharePoint

PowerShell: Upgrade WAC – your Office Web Apps Farm

Like most folks who upgrade their SharePoint 2013 farms after they have applied the latest SharePoint 2013 CUs to the SharePoint side of the environment they will usually still have 2 WAC servers and at least 3 WFM servers left to apply and configure updates. This may depend on release of the updates as the Service Bus and Workflow manager updates do not coincide with the monthly delivery of SharePoint 2013 CUs.

image

 

Upgrade-WAC.ps1

The reason for this post is to make it easy for me (not to forget) updating the WAC Servers / Office Web App Farm. Updating the Office Web App farm is somewhat unique in that you remove the farm prior to the installing the CU then create a new farm after the cumulative update installed


 

 

 

 

PowerShell Script

# Update-WAC.ps1
# Add July 2015 CU

Import-Module -Name OfficeWebApps 

# Review the Current State of the Office Web App Environment
Get-OfficeWebAppsFarm 
Get-OfficeWebAppsHost 
Get-OfficeWebAppsMachine 
cmd /c pause 

# Remove OfficeWebAppMachine prior to installing Cumulative Update
Remove-OfficeWebAppsMachine
cmd /c pause

# Install the Cummulative Update while paused

# Configure Office WebApp Farm after installing the updates
# If using HTTP remove comment below
# New-OfficeWebAppsFarm -InternalURL "http://wac.contoso.com" -AllowHTTP -EditingEnabled

New-OfficeWebAppsFarm -InternalURL "https://wac.contoso.com" -ExternalURL "https://wac.contoso.com" -CertificateName "wac.contoso.com" -EditingEnabled
cmd /c pause

# Open IE to test and ensure the New OfficeWebApps Farm is configured
$ie = New-Object -ComObject InternetExplorer.Application
$ie.Navigate("https://wac.contoso.com/hosting/discovery.ashx")
$ie.Visible = $true
If successful your browser will open and will look the example below

SNAGHTML39a1ce2e

 

Download http://1drv.ms/1JCmcKm

 

Ivan

Metalogix Content Matrix 7.3.x – Bugs

 

SharePoint 2013 and Metalogix Content Matrix

SharePoint is mission-critical and users demand availability. Content Matrix allows you to migrate SharePoint by site collection, site, list, library, business unit, or department with zero downtime. Run old and new farms in parallel and test and re-arrange as often needed. Along with the Re-Organizer feature empowers your site collection owners and users, to manage SharePoint sites, content and metadata on an ongoing basis to keep in sync with business needs. All of my clients love this tool as it makes their environments more flexible and now with Re-Organizer we are enabling the site owners to more easily manage their site structure.

 

 

 

 

Error on Installation of Content Matrix 7.3.0002

During the installation of the Online edition of Content Matrix version 7.3.0002 while installing Metalogix Extensions Web Services you will see the error "MD5 check failed for the current file to be staged, the file may be corrupt" in the screenshot to the left. However, the installation will continue and complete. Also, if you go to the location in your AppData Folder and copy the "Metalogix SharePoint Extensions Web Service Setup.msi" it will run locally without any issues. The error appears to be related to the msi not matching the online manifest

 

 

 

 

Error Copying List Views Content Matrix 7.3.xxxx

Unfortunately, the installation error is not the only issue. The infamous "Object Reference not set to an instance of an object" error occurs when you attempt to copy list views. However, if you copy the complete list all views are included in the copy. In looking at the error details it appears to be caused by a dialog box, this makes sense as it never creates the job or writes to any log.

 

 

 

 

 

image[14]

 

Error Copying Sites Lists or Library’s  Content Matrix Organizer 7.3.xxxx

The last symptom was when attempting to copy / move anything  using the Reorganize from the Widget Drop Down or the Ribbon of the list…

 

We initially found and reported these errors on 12/18/2015..

At first we thought incorrectly the error only occurred after an upgrade from an earlier version of Content Matrix, after further testing we found the issues existed in all 7.3.x versions whether installed as part of an upgrade or installed in a pristine environment. As always Metalogix Content Matrix is our favorite tool for SharePoint Migrations whether on-prem or online and Metalogix support is very helpful.  The only resolution at this time is to revert to a previous version Metalogix Content Matrix version 7.2.0017

 

 

 

 

 

Ivan

SharePoint 2013 Unable to Open Documents from Ribbon when WOPI is Configured and Library Default open behavior for browser-enabled documents is Open in the client application

It appears to be an IE issue when clicking on the New Document Drop down from the ribbon using fiddler4 there are not any results returned, probably jscript or jquery … I am running IE11 and I have tested IE10 which exhibits the same behavior. I haven’t tested on earlier versions yet.

However, if you open with Mozilla the Microsoft Office is blocked until you Allow Mozilla to open Office Documents and then Default open behavior for browser-enabled documents works as expected. In other words when the library is set to open ion the browser (via WOPI) the documents open in the browser, when set to open in the client application, the docs open in the client as expected..

1. Click On Allow

Doc1_1-57-50

 

2. Then choose to Allow remember

Doc2_2-00-55

3. Then choose OK,

Doc3_2-02-20

 

4. Click on New Document one more time to open from the ribbon

Doc4_2-05-18

 

Note: this only occurs if you have WAC installed and configured WOPI with SharePoint. Also, you only have to go to library settings to set the Default open behavior for browser-enabled documents: to Open in the client application, you don’t have to set the site collection… I will resolve as time permits

 

Cheers,

Ivan

Embed a PowerPoint Presentation into a SharePoint 2013 Page with Multiple Farms

It seems like everyone NothingButSharePoint and Microsoft Office and Microsoft Bogs states that you can not embed unless your using Office 365 (this may have been due to using SharePoint 2010). There are many more post and articles that require you to use Onedrive.. There are probably many better ways to sharing Content using WAC with multiple farms and SharePoint On-Prem and I would enjoy hearing about how you have solved this issue

Embed a PowerPoint Presentation from a source SharePoint Farm and render the presentation in a target SharePoint Farm using CEWP

clip_image001

1. Go to http://focalpoint.cotoso.com and search for PPTX

2. Use the Drop Down, Choose embedded information, and Copy All

3. Go to http://learning.fabricam.com

5. Add Content Editor WebPart

clip_image002

6. Click Inside the WebPart Choose the Orange Insert, Click Embed Code

clip_image003

7. Insert (Paste CTRL+V) the Code you copied from http://focalpoint.contoso.com

SNAGHTML159a25c

8. Then Click the Insert button,

9. Next Edit the WebPart, and change the Width to 540px, then click OK

SNAGHTML15d48fe

10. Click Check-in, then Publish this Draft

SNAGHTML15f8f54

Note: The Site Collection Feature – Cross-Farm Site Permissions is activated on both Farms, the cross-farm site permissions feature to allow internal SharePoint applications to access websites across farms.

 

Ivan

SharePoint Saturday UTAH 2015

Register now!

Registration is still open. See our SPSEvents site for registration, session, and speaker information.

ShareSki

You can’t have SPS without #ShareSki, especially here in Utah! ShareSki is the day before SharePoint Saturday (Friday, Feb 27) on The Greatest Snow on Earth® at Snowbird! Meet at the Gadzoom lift between 9:00 and 9:45. We’ll be taking runs off this lift–returning to it–until 9:45, at which time we can’t guarantee a meetup. If you miss us at Gadzoom or loose us, we’ll have lunch in the Creekside Cafe and Grill at 12:00. Please arrive no later than 12:10 so we can get everyone through checkout in one shot. Go to snowbird.com to purchase your tickets online for $80 (save 16%).

SharePint

#SharePint is the après-event social of SharePoint Saturday. This gathering is primo for networking and socializing with fellow SharePoint connoisseurs. SharePint will be held at Red Rock Brewing Co., 254 South 200 West, Salt Lake City, starting approximately 6:30.

Sponsors

Special thanks to our sponsors for making this event possible!

Platinum

Gold

Silver

 

Cheers,

Ivan Sanders
SharePoint MVP / MCT

http://blogs.msmvps.com/ivansanders/

clip_image001 clip_image002 clip_image003 clip_image004 clip_image005

Technorati Tags: SharePoint Saturday UTAH,Business Intelligence,Event

RSA Web Agent 7.1.3 SharePoint WFEs Authentication with Multiple IP Addresses

image4

 

If you are unable to authenticate the after installation and configuration of the RSA WebAgent, you have downloaded the agent from EMC http://www.emc.com/security/rsa-securid/rsa-authentication-agents/iis-7-1.htm,  followed the installation instructions.

Configuration and Errors

  1. You have created and copied the sdconf.rec file to %windir%/system32 and to the C:\Program files\RSA Security\RSAWebAgent
  2. When attempting to authenticate you receive “100:Access denied. The RSA ACE/Server rejected the Passcode. Please try again.”
  3. The application Event viewer  there is an Event ID: 1012, Source: ACEClIENT “Multihomed host detected: Primary IP assumed xxx.xxx.xxx.xxx” and this is not the IP Address you are using for your Extended Web App.

The secureid file will not be created and you will not be able to authenticate

 

 

RSA Rulesimage

Whenever there are multiple IP Addresses (Multi homed Servers) on a Web Server and the Default Address  is not the primary Addresses used for RSA Authentication . The RSA Agent will NOT create the SecureID file on first authentication and the request will timeout…

  1. If the SharePoint server is configured as the WFE, and you are installing the RSA WebAgent to secure external access, ensure the Local System account has read/write access for the following registry key: HKLM\Software\SDTI\ACECLIENT. This ensures that SharePoint WFE is able to write the secret to the registry.
  2. If the SharePoint WFE  has multiple IP Addresses, you will need to explicitly configure the network adapter address through which SharePoint WFE connects to the RSA Authentication Manager for authentication or authentication will fail. To do this, create a new String Value PrimaryInterfaceIP in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\SDTI\AceClient\.
  3. Double Click on  HKEY_LOCAL_MACHINE\SOFTWARE\SDTI\AceClient\PrimaryInterfaceIP 
  4. Add the IP Address as the value data.  The value specified must match that set in the agent host record.

 

Don’t forget SharePoint Saturday in Utah next Saturday

clip_image011

SharePoint administrators, end users, architects, developers, and other professionals that work with Microsoft SharePoint Technologies will meet for:

SharePoint Saturday Utah on February 28th 2015 at the University of Utah David Eccles School of Business located at 1655 Campus Center Dr. Salt Lake City, UT 84112.

 

 

 

 

Cheers,

 

Ivan Sanders
SharePoint MVP / MCT

Blog: http://blogs.msmvps.com/ivansanders/

clip_image002 clip_image004 clip_image006 clip_image007 clip_image009 clip_image010


Next Page »