Using MySQL database as a Datasource

How to connect to MySQL database from PowerPivot

One of the supported data sources that you can use to connect to your workbooks and create PowerPivot reports is MySQL database. But before creating such connection you need to make sure that the following requirements are fulfilled:

  • Make sure your MySQL server is binded on public IP address and accept traffic from PlexHosted`s SharePoint app server where your site is deployed, on default port 3306 (port 3306 used for demonstration purposes in this case).
  • You have a user that have read only permissions to required databases (root user is used for demonstration purposes in this case) GRANT ALL ON employees.* TO root@′ IDENTIFIED BY ‘verysecurepassword’; or GRANT ALL ON employees.* TO root@’%’ IDENTIFIED BY ‘verysecurepassword’; (if you have a firewall)
  • Your Desktop machines which are building excel reports must have permissions to connect to your MySQL server using public IP address.
  • The MySQL Connector/ODBC has been installed on the machine from which you are going to connect to MySQL. You can download and install it from [here].
Creating a connection to MySQL database
  1. Open the Excel workbook you would like to connect to the database and go to the PowerPivot tab. In the PowerPivot tab click Manage.
  2. When the PowerPivot for Excel window opened, click on From Other Sources button in the Get External Data section.
  3. Choose Others (OLEDB/ODBC) option.
  4. Type the name for your connection and enter the connection sting using the pattern below:
    Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=”DRIVER={MySQL ODBC 5.3 ANSI Driver};UID=readonlyuser;Server=;Database=employees;Password=verysecurepassword;Option=3DRIVER={MySQL ODBC 5.3 ANSI Driver}”;Server=;Database=employees;Option=3;”;
    UID=<user with permissions to connect to your database>
    Password=<your user password>
    Server=<your MySQL server public IP address>
    Database=<your database name.
    After entering the connection string click on Test Connection button.
  5. Click OK in the connection status window.
  6. Click Next.
  7. In the Table Import Wizard choose the option Write a query that will specify the data to import and click Next.
  8. Enter the query to import the required data from the database and click Validate. Once you query is validated click Finish.
  9. The Table Import Wizard will start importing data from your database to the PowerPivot model. Once the status of the wizard changes to Success click Close.
  10. You can see the data imported to the model and can start creating your PowerPivot reports by clicking on the PowerPivot table button.

“Site storage exceeded” error

You are 100% sure you should have free space on your SharePoint 2013 site, but still getting “Site storage exceeded” error, when trying to upload some new content. Is this situation familiar to you?Let’s analyze what might be the cause of such behavior.

Make sure you have considered two-stage SharePoint 2013 recycle bin origin, as soon as files moved to recycle bin occupy the storage of the site, as if they were present in an ordinary SP library. Since the Recycle Bin in SharePoint 2013 provides two-stage protection against accidental deletions, you should delete files not only from the site’s recycle bin, but site collection’s recycle bin also.
To completely remove a file or an item from SharePoint site, check site collection’s recycle bin under Site Settings -> Site Collection Administration -> Recycle bin and clear up both locations:
– “End user recycle bin items”
– “Deleted from end user Recycle Bin”.
Try to upload new content then.

Configuring a workflow

Using built-in SharePoint workflow templates you can configure a workflow using a browser and customize it in a way limited by workflow template structure to meet the exact needs. Workflow template defines a number of conditions to test and actions to be performed when these conditions are met.
Each workflow has the start and the end. The triggering event for a workflow is usually uploading a document or adding an item, but you can also configure it to be started manually. When a workflow event is triggered a new workflow instance is created. When a workflow completes all the stages, it is set to Completed status.
Additionally, workflows can use Task list and History list. It can add items to a Tasks list to help users to track what actions are required to complete workflow process and send emails to users when a task has been assigned to them. Workflow history provides the information about each workflow instance created, running or completed for an item or document. This list can be accessed only by adding /lists/workflow history/ to the sites URL, it is not available on the Site Contents page.
Let`s trace the process of configuring a workflow using built-in template. In our example we will use Three-State workflow. This workflow is perfect for tracking status of tasks or documents. We are going to create a workflow on project site that tracks documents and passes them through review and accepting process.

Adding a Choice column

If you want to associate List or Library with a Three-state workflow, you must add at least one Choice column that contains the state values that the workflow will track. To add this column:

  1. Chose the list or library you want to associate with a workflow and click on Library. Then click on Create Column.

  2. Enter the name of new column and choose Choice as its type.

  3. Enter the description of this new column, at least 3 choices for it and choose the necessary additional settings. You can enter more than 3 choices but three-state workflow can use only three options.

  4. Click OK.
Adding a Three-state workflow

Now you can add Three-state workflow to this library.

  1. Click on Library –> Workflow Settings and choose Add a Workflow from the drop-down menu.

  2. Choose Three-state workflow template and type the name of a new workflow.

  3. Specify the Task list and History list for this workflow, choose the start options and click Next.

  4. In the workflow states section you can choose the choice column and the values from it for each stage of the workflow.

  5. Specify the necessary settings for the Initiation stage of the workflow. The option to send email is enabled by default, you can disable it if required.

  6. Specify the necessary settings for Middle stage of the workflow.

  7. Click OK.

Now your Three-state workflow has been added to your library and you can check how it works.

Compliance in business: Laws and regulations IT departments should be aware of

In a business sense, compliance refers to a company’s efforts to obey all of the laws and regulations that govern how they can manage the business, their staff, and their treatment to their customers. The concept of compliance is to make sure that companies act responsibly and are held accountable for those actions.

This doesn’t just deal with ethical business practices or providing adequate customer service – these regulations are put in place to safeguard sensitive data, both of the business and the customer. So why is this so important? The most obvious and valuable outcome of compliance is that it decreases your risk of fines, penalties, work stoppages, lawsuits or a shutdown of your business.

It also helps to secure sensitive data, which is probably the most important aspect if your business. There are many laws and regulations IT departments need to be aware of in regards to compliance. What follows is a list of the most noteworthy to ensure your business follows:

  • The Sarbanes-Oxley Act of 2002 (SOX). Section 404 of SOX requires public companies annual reports to include the company’s own assessment of internal control over financial reporting, and its attestation by independent auditor. The above-mentioned assessment has been extended into the IT sphere by the opinion of the Public Company Accounting Oversight Board (PCAOB), corporation created by SOX to oversee the auditors of public companies
  • The Financial Services Modernization Act of 1999 (also known as Gramm-Leach-Bliley Act – GLBA) protects the privacy and security of individually identifiable financial information collected, stored, and processed by financial institutions. This set of recommendations for audit was produced by the Federal Financial Institutions Examination Council (FFIEC), an interagency group that includes five of the eight major financial regulatory agencies.
  • Health Insurance Portability and Accountability Act (HIPAA) focuses on the healthcare industry, but other companies can be impacted however, if they engage in related activities or if they provide services to companies that are directly affected by the regulation.
  • European Union Data Protection Directive (EUDPD) refers to the protection of data privacy for citizens throughout the European Union. It has a strong influence on international regulations, since it puts strict limitations on sending EU citizens’ personal information outside of the European Union to areas that are considered to have less than adequate standards for data security.
  • Bank Secrecy Act (BSA) requires banks and other financial institutions to report certain transactions to government agencies to help eliminate money laundering, tax evasion, or other criminal activities.
  • Payment Card Industry (PCI) Data Security Standard is used as a framework for the Cardholder Information Security Program (CISP), which is intended to protect cardholder data from exposure and compromise across the entire payment industry. Its goal is to ensure that members, merchants, and service providers maintain the highest information security standard.
  • California Senate Bill 1386 puts into practice robust disclosure requirements for businesses and government agencies that experience security breaches that might imperil the personal information of California residents. The bill was the first attempt to address the problem of identity theft on the state level.
  • International Convergence of Capital Measurement and Capital Standards—A Revised Framework (also known as Basel II) introduces recommendations by bank supervisors and central bankers from the assemblage of countries, which are the members of the Basel Committee on Banking Supervision for revising the international standards for measuring regulatory capital the adequacy of a bank’s capital.

There is also another set of rules arisen for the IT industry from the service providers’ perspective – the Defense Federal Acquisition Regulation Supplement (DFARS), used by Department of Defense (DoD). Special attention should be paid to the DFARS-252.204-7012 clause, issued by DoD in November 2013, which contains rules and requirements regarding utilizing and safeguarding Unclassified Controlled Technical Information (UCTI) that is vital to national security. According to its requirements the following specific areas must be protected:

  • The adequate safeguarding of UCTI on or transiting through contractor unclassified information systems
  • Reporting to the DoD and investigating any cyber incidents that affect UCTI

PlexHosted, as a respected SharePoint hosting provider, has successfully implemented DFARS-252.204-7012 clause to its infrastructure. A widely known fact is that the success of your business pretty much depends on its public image. And accordance to this set of required compliance standards, laws and regulations will help you to build positive reputation and also to improve consumer loyalty, since customers are more likely going to return to a product or service from a company they identify as trustworthy.

SharePoint 2013 services discount offer

Dear readers of PlexHosted blog,

We are pleased to present you our special offer with one month subscription discount for our Dedicated and Multi-tenant SharePoint solutions.

Multi-tenant SharePoint 2013 20% off discount

Dedicated SharePoint 2013 20% off discount

Hope this 20% discount offer will help you to make a favorable decision about subscription to our solutions. Please note, that this offer is valid for 2 month term only.

Search behavior configuration

SharePoint allows you to adapt search experience according to your specific needs. This can be done on the Search Settings page. By default all the settings from this page are configured on the site collection top level site and inherited by the subsites. All the settings configured on this page are used by search boxes and search results page.
Search Settings include the following options:

  • Enter a Search Center URL – you can specify the search center you would like to use to allow users search everything inside your site collection.
  • Which search results page should queries be sent to? – you can specify a custom page for search results.


Setting up Search Center URL

  1. Login to the SharePoint top level site as an administrator.
  2. Click the gear icon, go to Site Settings page. Search_behavior_configuration-2

  3. On the Site Settings page navigate to the Search Settings under the Site Collection AdministrationSearch_behavior_configuration-3

  4. On the Search Settings page in the “Enter a Search Center URL” section type the URL of the Search center you would like to use.  Search_behavior_configuration-4

  5. Click OK.

  6. Enter a search query to the Search box and press Enter. Search_behavior_configuration-7

  7. On the results page click on “expand your search” link.  Search_behavior_configuration-8

  8. Verify that you have been redirected to the specified Search Center.

Setting up custom results page

  1. Go to Site Settings page.  Search_behavior_configuration-2

  2. On the Site Settings page navigate to the Search Settings under the Site Collection Administration.  Search_behavior_configuration-3

  3. On the Search Settings page in the Which search results page should queries be sent to?” section remove the check from “Use the same results page settings as my parent” option.  Search_behavior_configuration-10

  4. Enter the URL of your custom results page. Search_behavior_configuration-11

  5. Click OK.

  6. Navigate to the sites home page and run a search query.  Search_behavior_configuration-13

  7. Check that you are taken to your custom results page.  Search_behavior_configuration-14


Cloud security myths

Our support engineer Anna Beletskaya says at her article about common security myths

The benefits of cloud are vast and significant. Cloud enables rapid deployment, provisioning, and scaling of IT resources and data.        It also shortens development time, reduces waste, and lowers costs. But despite increasing cloud adoption rates, some companies still do not rush to move its workloads and applications to the cloud due to some common myths and delusions that are more fluff than fact.

When organizations are considering moving to the cloud one of the first questions asked is, “Is it secure?” It’s a reasonable question, since there have been a lot of fears and doubts circulating across the industry around this point – particularly with some high profile breaches in 2014. While security is always a priority when it comes to business, several of these concerns are much more bark than bite. So, let’s demystify those concerns and give peace of mind for organizations looking to move forward with a transition to the cloud.

1. Cloud Environments Are Easier to Attack

Two identical servers have the same potential vulnerability risk level, no matter if they’re located in your company’s private data center or within a cloud provider’s data center. The data protection’s level relies less on the machines and more on the security state of each data center environment. At this point, many companies store their most valuable data in private clouds that they protect and maintain on their own. But if you are managing a few in-house IT resources of a small company, an external provider likely has more security expertise than you do.

So the clouds aren’t easier to attack, since the vulnerability depends on the security posture of the data center itself, which is provided by various security tools and methods.

2. Customers in the same cloud can attack each other.

Within a public cloud, multiple tenants share processing, storage, and other computing services within a cloud services provider’s data centers. It might make you imagine that it’s easy for one tenant to steal another tenant’s data due to the concept of shared resources. But in fact, virtualization provides strong partitions between tenants within the data center, so that they’re effectively isolated from each other. Unless your data center is owned by a competitor that would love to snitch your data, it’s very unlikely you’ll become an intended attack victim by a fellow customer.

3. You can’t control where your data resides in the cloud. 

Data residency is a key concern, and many countries’ regulations do not allow exporting of personal data or its storage to another country. Knowing where your data resides requires transparency from your provider. You should also know where your data travels and how it is protected either at rest or over transition. This myth can be easily addressed by choosing a global cloud service provider, which provides secure cloud hosting solutions and operates data centers in multiple countries.

Putting your cloud fears aside will allow you to see the benefits of cloud solutions – such as lower costs, smarter resources usage, and expanded capacity. You just need to rely on a trustworthy cloud solutions provider, such as PlexHosted LLC, which delivers the security and data protection level, that will satisfy your requirements, and can provide you with fully managed secure private cloud or a separate tenant within the public cloud.

How To – SharePoint Server 2016 Installation and Deployment

Today I would like to share some information regarding the upcoming SharePoint Server 2016. This article describes initial investments made in installation and deployment of SharePoint Server 2016 from Microsoft Ignite in Chicago.

1. Hardware Requirements
Installation with the minimum recommended services.


Test installation running all available services


2. Operating System Requirements
SharePoint Server 2016 is supported on Windows Server 2012 R2 and Windows Server Technical Preview.

3. Database requirements
SharePoint Server 2016 requires SQL Server 2014 for its databases, also it will support SQL Server 2016.
For additional information on SQL Server 2016 see also

4. .NET Framework requirements
According to Microsoft starting from January 13, 2016, .NET Framework 4.5.2 will be the minimum supported version of .NET Framework 4.x.
For SharePoint 16 based on Windows Server 2012 R2 you will need .NET Framework 4.5.2 version.
For SharePoint 16 based on Windows Server Technical Preview “Threshold” you will need .NET Framework 4.6 Preview version. This version comes with Windows Server Technical Preview “Threshold”.

5. Prerequisites
SharePoint Server 2016 prerequisites are the same as for SharePoint Server 2013. Prerequisites can be installed manually or with the Prerequisite Installer, similar as for SharePoint Server 2013.
Here is the full list of SharePoint Server 2016 prerequisites:
– Application Server Role, Web Server (IIS) Role
– Microsoft SQL Server 2012 Native Client
– Microsoft ODBC Driver 11 for SQL Server
– Microsoft Sync Framework Runtime v1.0 SP1 (x64)
– Windows Server AppFabric 1.1
– Cumulative Update Package 1 for Microsoft AppFabric 1.1 for Windows Server (KB2671763)
– Microsoft Identity Extensions
– Microsoft Information Protection and Control Client
– Microsoft WCF Data Services 5.0
– Microsoft WCF Data Services 5.6
– Microsoft .NET Framework 4.5.2
– Update for Microsoft .NET Framework to disable RC4 in Transport Layer Security (KB2898850)
– Visual C++ Redistributable Package for Visual Studio 2013

6. MinRole (v1) Overview
MinRole is one of the new infrastructure advancements in SharePoint Server 2016. MinRole is designed to transform guidance into code, simplifying deployment and scale with SharePoint ensuring a request is served end-to-end by the receiving server based on the origination of the request (I.e. end user or batch processing) and role of the destination server.
In previous versions of SharePoint, servers’ role was only defined by what services were provisioned and its placement in a SharePoint Farm topology.

MinRole simplifies on-premises server farm deployments, and reduces the unit of scale to simplify capacity planning while providing predictable performance characteristics through enabling selective provisioning of SharePoint server roles based on position and function within a topology.


MinRole enables an administrator to select the appropriate server role for a specific server when provisioning SharePoint Server 2016 based on predefined configurations:
Role №1 – Single Server Farm
Provisions all services on the server for a single server deployment. This role is provided for evaluation and development purposes.
Role №2 – Application
Services the backend jobs or the requests triggered by backend jobs, optimized for high throughput.
Role №3 – Web Front End
Services end user requests, optimized for low latency.
Role №4 – Search
Reserved for Search services.
Role №5 – Distributed Cache
Services distributed cache for the farm. Optionally, the server assigned to this role can load balance end user requests among the web front ends.
Role №6 – Special Load
Reserved for services to be isolated from other services, I.e. 3rd party, PerformancePoint, etc.

Also MinRole adds a new command line parameter, “localserverrole”, that can be used to specify the role that should be assigned to a specific server when configuring SharePoint Products and Technologies. Also MinRole can be instrumented through the PSConfig GUI.

7. Upgrade and Migration
SharePoint Server 2016 will support upgrade from SharePoint Server 2013. To upgrade from SharePoint Server 2013 to SharePoint Server 2016, you can use the database-attach method to upgrade.
As for site collections provisioned in backward compatible SharePoint 2010 (14) mode, they must be upgraded to 2013 prior to upgrading to SharePoint Server 2016.

Data import from Excel spreadsheet to SharePoint list

We would like to show you how to import your data within Excel spreadsheet into a SharePoint list.

It’s quite simple. Follow the instructions:

1. Login to your SharePoint site. Click Settings, click Site Content


2. Click “Add an app”


3. Type “excel” in the Find an app textbox. Click Import Spreadseet


4. Type the name for the new list, description. Click Browse button and select your Excel spreadsheet file. Click Import button


5. Select the Range type and select the range for the data. Click Import button


6. Now you can see your new list with the data that was imported from Excel spreadsheet



How to reset a user password in Sharepoint 2013

We would like to show you how to reset a user password in Sharepoint 2013

You need to perform the following steps:

1. To open Active Directory Users and Computers, click Start, click Control Panel, double-click Administrative Tools , and then double-click Active Directory Users and Computers.

2. In the console tree, click Users or, click the folder that contains the user account.

3. In the details pane, right-click the user whose password you want to reset, and then click Reset Password.

4. Type and confirm the password.





To start using it check our Sharepoint Enterprise Plan (Public Sites Included):