Applications Server
 

Sharepoint 2013 : Building an Application with Access Services (part 2) - Configuring SQL Server 2012, Configuring the Windows Development Environment Firewall

11/29/2013 2:08:57 AM

1.3 Configuring SQL Server 2012

SQL Server must be correctly configured to support the demands of Access Services 2013. The first of the following required steps requires the installation media to be available.

Adding Required SQL Server Features

Run the setup (or rerun the setup to adjust an existing installation), and ensure that the following features are enabled in the development environment:

  • Database Engine Services
  • Full-Test and Semantic Extractions for Search
  • SQL Management Tools (Basic and Advanced)
  • Client Tools Connectivity

This step requires the original installation media and may require the reinstallation of service packs. Follow proper protocols when updating production machines!

Enabling Contained Databases

SQL Server 2012 helps to isolate databases from other databases via the concept of contained databases. The containment of databases in SQL Server 2012 is required by Access Services to not only protect each database but also the SQL Server instance. SQL Server implements database isolation from the instance in four ways:

  • Meta data describing the database is maintained in the database rather than in the master database.
  • All meta data is stored with the same collation.
  • User authentication is performed by the database rather than by SQL Server.
  • SQL Server support reporting on containment information.

Activating containment is as simple as setting the Enable Contained Databases flag to True in the SQL Server’s Advanced Properties dialog. Open SQL Server Management Studio, right-click the server, and choose Properties. On the Advanced page is the option to Enable Contained Databases. Set this to True.

Enabling the Mixed Authentication Security Mode

Access Services 2013 supports both Windows and SQL Server authentication, so SQL Server must be configured to support both as well. In SQL Server Management Studio, right-click the server and choose Properties. On the Security page is the option to allow SQL Server and Windows Authentication Mode.

Ensuring Appropriate Service Account Permissions

SharePoint 2013 continues the long tradition of service accounts available previously in SharePoint 2010 and 2007. When an Access Services 2013 service application is created, a service account must be either assigned or created. This service account must be a pre-existing Active Directory account and must have been granted the dbcreator, public, and securityadmin roles for the server. Open SQL Server Management Studio and locate the service account in the Security ⇒ Logins section. Right-click the account, and select the Server Roles page to ensure the required server roles have been granted.

Enabling Required Networking Protocols

Open the SQL Server Configuration Manager tool, and expand the SQL Server Network Configuration group to select Protocols for MSSQLSERVER. By default, only Shared Memory is enabled. Right-click Named Pipes and select Enabled. Right-click TCP/IP and select Enabled. At this point, all available network protocols should be enabled.

1.4 Configuring the Windows Development Environment Firewall

Now that SQL Server has been properly configured, the Windows environment hosting SQL Server must be prepared to receive HTTP traffic on ports 1433 and 1434 for TCP and UDP.

1. Open the Windows Firewall with the Advanced Security utility.
2. Right-click the Inbound Rules option and select New Rule.
3. The New Inbound Rule Wizard pops up and asks which type of rule should be created.
4. Select Port and click Next.
5. The wizard advances to the Protocols and Ports screen. Select TCP and type 1433, 1434 into the Specific Ports field; then click Next.
6. The wizard advances to the Action screen. Select Allow the Connection and click Next.
7. The wizard advances to the Profile screen. Leave Domain and Private, clearing the checkmark from Public, and click Next.
8. The wizard advances to the Name screen. Type SQL TCP into the Name field, and click Finish.

Repeat the previous steps with the following changes for the UDP incoming rule:

  • On the Protocols and Ports screen, select UDP and type 1433, 1434 into the Specific Ports field; then click Next.
  • On the Name screen, type SQL UDP into the Name field, and click Finish.

1.5 Configuring SharePoint 2013

At this point, SQL Server is fully configured, the firewall is ready to accept inbound traffic on the SQL Server environment, and an isolated app domain has been created. The final prerequisite step is to create and configure the Access Services 2013 service application.

Starting Required Services

In Central Administration, go to Manage Services on Server to ensure the following services are started. Start them if they are not already running:

  • Access Services 2013
  • Access Services (Access Database Service 2010 for viewing and modifying existing 2010 Access web databases)
  • App Management Service
  • Microsoft SharePoint Foundation Subscription Settings Service
  • Secure Store Service
Creating a Secure Store Service Application

Open Central Administration and go to Manage Services on Server. In the Service Applications Ribbon tab, click the New drop down, and select to create a new Secure Store Service Application. The default settings are all valid, so click OK to create the new service application. After the new application has been created, run an IISRESET to ensure enough memory is available for the next step. Click the Generate New Key button on the Ribbon to fully activate the Secure Store Service. This last step takes a while; don’t be alarmed if nothing happens for a few minutes.

Creating the Access Services 2013 Service Application

The final step is to create the actual Access Services 2013 service application. Open Central Administration, and go to Manage Services on Server. On the Service Applications Ribbon tab, click the New drop-down, and select the Access Services option (not the Access Services 2010 option, which is used to support legacy Access Services 2010 instances).

The Create New Access Services Application dialog opens and asks for the Application Database Server. This server is used to create new application databases and is expected not to be the same SQL Server instance that SharePoint is using; although that configuration is supported. If a new SQL Server is brought online for Access Services databases in the future, opening the Access Services Application configuration offers an opportunity to change the database server.

 
Others
 
- Sharepoint 2013 : Building an Application with Access Services (part 1) - Configuring an On-premise Development Environment
- Microsoft Lync Server 2013 : Lync Online and Hybrid Deployments - Configuring Directory Synchronization (part 1)
- Microsoft Lync Server 2013 : Lync Online and Hybrid Deployments - Configuring Directory Synchronization (part 1)
- Microsoft Lync Server 2013 : Lync Online and Hybrid Deployments - AD FS Deployment for SSO (part 3) - Adding or Converting a Domain for SSO
- Microsoft Lync Server 2013 : Lync Online and Hybrid Deployments - AD FS Deployment for SSO (part 2)
- Microsoft Lync Server 2013 : Lync Online and Hybrid Deployments - AD FS Deployment for SSO (part 1) - Configuring the First Federation Server in the Farm
- Sharepoint 2013 : Managing and Configuring Communities
- Sharepoint 2013 : Managing and Configuring My Sites (part 4) - SkyDrive Pro
- Sharepoint 2013 : Managing and Configuring My Sites (part 3) - Configuring My Sites - Managing Social Tags and Notes, Manage Following
- Sharepoint 2013 : Managing and Configuring My Sites (part 2) - Configuring My Sites - Enabling the Activity Feed Job
- Sharepoint 2013 : Managing and Configuring My Sites (part 1) - Configuring My Sites - My Site Host Site Collection, Setting Up My Sites
- Exchange Server 2013 : Building an Available Messaging System (part 3) - INTERSITE REPLICATION BANDWIDTH
- Exchange Server 2013 : Building an Available Messaging System (part 2) - Exchange Hybrid Deployment, Database Availability Group Planning
- Exchange Server 2013 : Building an Available Messaging System (part 1) - Transport, Namespace Planning
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 9) - Audiences - Audience Targeting Rules and Logic, Targeting Content to Audiences
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 8) - Audiences - Configuring Audiences
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 7) - SharePoint Profile Synchronization - Managing User Profiles, Managing Policies
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 6) - SharePoint Profile Synchronization - Managing User Properties
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 5) - SharePoint Profile Synchronization - Editing Connection Filters
- Sharepoint 2013 : Managing and Configuring Profile Synchronization (part 4) - Configuring the Synchronization Connection
 
 
Most View
 
- Using the Windows 8 Interface : Navigating the Start Screen (part 1) - Navigating the Start Screen with a Mouse
- Windows 8 : Creating a Windows Network - Installing Network Wiring (part 1) - General Cabling Tips
- Windows Server 2008 : Launching the Group Policy Management Console, Understanding Group Policy Order of Precedence
- Windows Server 2012 : Deploying Dynamic Access Control (part 1) - Preparing Claims
- Overview of Oauth in Sharepoint 2013 : Introduction to OAuth
- Windows 7 : The WMC Functions (part 2) - Pictures, Videos, Music
- Adobe Illustrator CS5 : Working with Paths (part 2) - Understanding control handles and control handle lines
- Windows Server 2008 : Using netdom (part 2) - Verifying Trust Relationships
- : Windows Server 2008 and Windows Vista : Administrative (.adm) Templates (part 4) - Managing .adm Templates
- Windows Server 2012 : Hyper-V - Creating and Configuring Virtual Machines (part 2) - Creating Virtual Machines
 
 
Top 10
 
- Microsoft Excel 2010 : Filtering Options (part 3) - Using the Search Function for Grouped Dates, Using Text, Number and Date Special Filters
- Microsoft Excel 2010 : Filtering Options (part 2) - Searching Functions for Listed Items
- Microsoft Excel 2010 : Filtering Options (part 1) - Filter Listing for Listed Items, Grouped Dates Filter Listing
- Microsoft Excel 2010 : Filtering and Consolidating Data - Preparing Data, Applying a Filter to a Dataset
- Microsoft PowerPoint 2010 : Inserting Charts and Related Material - Formatting a SmartArt Graphic
- Microsoft PowerPoint 2010 : Inserting Charts and Related Material - Resizing a SmartArt Graphic
- Microsoft PowerPoint 2010 : Inserting Charts and Related Material - Modifying a SmartArt Graphic
- Microsoft PowerPoint 2010 : Inserting Charts and Related Material - Using the Text Pane with SmartArt Graphics
- Microsoft PowerPoint 2010 : Inserting Charts and Related Material - Creating SmartArt Graphics
- Microsoft Lync Server 2013 : SQL Server Dependencies - Creating SQL Server Maintenance Plans