Sladescross's Blog

Blogging about Sharepoint related stuff

SQL Server TRUSTWORTHY ON and CLR Assembly Safe and External June 25, 2013

In the end the decision is ultimately up to the DBA who allows CLR to run inside their database server. One thing I have learned over the years, is to generally speaking follow the published Microsoft recommendations for security on my servers. It wasn’t to long ago that web applications used dynamic SQL and sysadmin level rights to talk to SQL Servers, which is completely against the rules today. Ultimately the level of security that you have using CLR is based on whether or not you allow anything but SAFE Assemblies on your server. There are however many features that don’t exist unless you are willing to expand into at least the EXTERNAL_ACCESS area.

When creating an assembly into a SQL Server database, you can specify one of three different levels of security in which your code can run: SAFE, EXTERNAL_ACCESS, or UNSAFE. When the CREATE ASSEMBLY statement is run, certain checks are performed on the code assembly which may cause the assembly to fail to register on the server. For more information, see the Impersonation sample on CodePlex.

When code in an assembly runs under the SAFE permission set, it can only do computation and data access within the server through the in-process managed provider.

EXTERNAL_ACCESS addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code.

UNSAFE code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.

To create an EXTERNAL_ACCESS or UNSAFE assembly in SQL Server, one of the following two conditions must be met:
The assembly is strong name signed or Authenticode signed with a certificate. This strong name (or certificate) is created inside SQL Server as an asymmetric key (or certificate), and has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission (for external access assemblies) or UNSAFE ASSEMBLY permission (for unsafe assemblies).

The database owner (DBO) has EXTERNAL ACCESS ASSEMBLY (for EXTERNAL ACCESS assemblies) or UNSAFE ASSEMBLY (for UNSAFE assemblies) permission, and the database has the TRUSTWORTHY Database Property set to ON.

To enable CLR on a SQL Server instance you can execute the script below. Once you have enabled CLR you can go ahead and create the Integration Service catalog as discussed above.

–Script #1 – Enabling CLR on the SQL Server Instance
sp_configure ‘show advanced options’, 1;
sp_configure ‘clr enabled’, 1;

One noticeable difference in the SSIS project created in SQL Server 2012 is that by default the SSIS project will be created in Project Deployment mode, but if you need to you can change it by right clicking on the project in Solution Explorer and clicking on “Convert to Legacy Deployment Model”.

On the Project Parameters window, click on the New Parameter icon in the upper left and specify the name of the project parameter. In this case I want the parameter to hold the database name hence I have specified DatabaseName name for the parameter and the default value specified as “AdventureWorks2008R2Test”. This means if the package is executed in the designer, data will move to AdventureWorks2008R2Test database.

To make a connection manager to dynamically use the database name, you need to configure the Expression property of the TargetConnection connection manager and specify the value of InitialCatalog property to come from an expression (in this case project parameter which we created above) as shown below:

As I mentioned before, a parameter can have a server default value, the next screen is the place where you define the server default values for all the parameters of the project. Here you can either use the same design default value, specify a new value or choose the value to come from a variable as shown below:


SSIS Deployment and Execute

Object model and PowerShell to deploy.

Scenario #1 – Setting up Integration Services Server to execute a package

1.Establish a connection to the server.
2.Retrieve the Integration Services object.
3.Create all objects needed to deploy our project.
4.Deploy project and then execute our package.

Scenario #2 – Executing complex packages with parameters

Goal: To run a complex package that has parameters that need to filled in

1.Repeat everything from previous scenario up to deploying project
2.Fill in the values for parameters by either specifying constants or creating environments

Integration Services supports two deployment models, the project deployment model and the package deployment model. The project deployment model enables you to deploy your projects to the Integration Services server.

Package Deployment –> Packages (.dtsx extension) and configurations (.dtsConfig extension) are saved individually to the file system.

Project Deployment –> A project, containing packages and parameters, is built to a project deployment file (.ispac extension).

Project Deployment –> CLR integration is required on the database engine.

In the current release of Integration Services, you can deploy your projects to the Integration Services server. The Integration Services server enables you to manage packages, run packages, and configure runtime values for packages by using environments.

The Integration Services Deployment Wizard deploys projects to the SSISDB catalog on a SQL Server instance using the project deployment model.

To start the Integration Services Deployment Wizard from an open project in Visual Studio, select Deploy from the Project menu. To start the wizard in SQL Server Management Studio, expand the Integration Services Catalogs > SSISDB node in Object Explorer, right-click the Projects folder, and then click Deploy Project.

The wizard proceeds through the following four steps. Click Next to move to the next step, or Previous to return to the previous step.
Select Source – Select the Integration Services project that you want to deploy.

Select Destination – Select the project destination.

Review – Displays your selections.

Deploy/Results – Deploys the project and displays the results.

When you configure an Integration Services project to use the project deployment model, you can use stored procedures in the SSIS catalog to deploy the project and execute the packages. For information about the project deployment model, see Deployment of Projects and Packages.

You can also use SQL Server Management Studio or SQL Server Data Tools (SSDT) to deploy the project and execute the packages. For more information, see the topics in the See Also section.

You can deploy a project from server to server by using the catalog.get_project (SSISDB Database) and catalog.deploy_project (SSISDB Database) stored procedures.

You need to do the following before running the stored procedures.

Create a linked server object. For more information, see Create Linked Servers (SQL Server Database Engine).

On the Server Options page of the Linked Server Properties dialog box, set RPC and RPC Out to True. Also, set Enable Promotion of Distributed Transactions for RPC to False.

Enable dynamic parameters for the provider you selected for the linked server, by expanding the Providers node under Linked Servers in Object Explorer, right-clicking the provider, and then clicking Properties. Select Enable next to Dynamic parameter.

Confirm that the Distributed Transaction Coordinator (DTC) is started on both servers.

Integration Services supports two deployment models, the project deployment model and the package deployment model. The project deployment model enables you to deploy your projects to the Integration Services server.

For more information about deploying projects to the Integration Services server, see Deploy Projects to Integration Services Server.

For more information about the package deployment model, see Package Deployment (SSIS).


Tabular Data Model Deployment May 17, 2013

After a model has been deployed, there are additional considerations for securing model data access, backups, and processing operations that can be configured on the Analysis Services server by using SQL Server Management Studio. While these properties and configuration settings are outside the scope of this topic, they are, nonetheless, very important in assuring your deployed model data is secure, kept up to date, and provide a valuable data analysis resource for users in your organization.

Connect to a Tabular Model Database (SSAS)

Client libraries that provide access to Analysis Services databases can be used to connect to model databases that run on a tabular mode server. Libraries include the Analysis Services OLE DB provider, ADOMD.NET, and AMO.

Excel uses the OLE DB provider. If you have either MSOLAP.4 from SQL Server 2008 R2 (file name msolap100.dll, version 10.50.1600.1), or MSOLAP.5 (filename msolap110.dll) that is installed with the SQL Server 2012 version of PowerPivot for Excel, you have a version that will connect to tabular databases. Use the instructions provided in the following topic to check the version of the MSOLAP data provider on your computer: Upgrade PowerPivot for Excel and PowerPivot Data.

Choose from the following approaches to connect to model databases from Excel:

  • Create a data connection from within Excel, using the instructions provided in the next section.
  • Create a BI semantic model connection (.bism) file in SharePoint that provides redirection to a database running on an Analysis Services tabular mode server. A BI semantic model connection file provides a right-click command that launches Excel using the model database that you specified in the connection. It will also launch Power View if Reporting Services is installed. For more information about creating and using BI semantic model connection files, see Create a BI Semantic Model Connection to a Tabular Model Database.
  • Create a Reporting Services shared data source that references a tabular database as the data source. You can create the shared data source in SharePoint and use it to launch Power View. For more information, see Create a Shared Data Source for a Data Model (SSRS).

After you set permissions in Analysis Services, you can return to SharePoint and create a BI semantic model connection.

  1. In the library that will contain the BI semantic model connection, click Documents on the SharePoint ribbon.
  2. Click the down arrow on New Document, and select BI Semantic Model Connection File to open the New BI Semantic Model Connection page.
  3. Set both Server and Database properties. If you are unsure of the database name, use SQL Server Management Studio to view a list of the databases that are deployed on the server.Server name is either the network name of the server, the IP address, or the fully qualified domain name (for example, If the server is installed as a named instance, enter the server name in this format: computername\instancename.Database must be a tabular database that is currently available on the server. Do not specify another BI semantic model connection file, an Office Data Connection (.odc) file, an Analysis Services OLAP database, or a PowerPivot workbook. To get the database name, you can use Management Studio to connect to the server and view the list of available databases. Use the property page of the database to ensure you have the correct name.
  4. Click OK to save the page. At this point, the PowerPivot service application will verify the connection.Verification succeeds if the connection information is correct, and you have granted administrative permissions to the PowerPivot service application so that it can connect to Analysis Services as the current user.Verification fails if the connection information is wrong, or the service application lacks permissions. A validation message will appear on the page asking whether you want to save the file. If you know that the connection is valid, you should save the file anyway, because the error is the result of missing permissions rather than invalid connection information.You can verify the connection by using it in Excel or Power View to connect to tabular model database. If the data source connection succeeds, the connection is valid despite the verification warning.

Detailed OLAP connection strings.

Detailed instructions to create a Shared Data Source.

When you run a report from a SharePoint library, connection information can be defined inside the report or in an external file that is linked to the report. If the connection information is embedded within the report, it is called a custom data source. If the connection information is defined in an external file, it is called a shared data source. The external file can be a report server data source (.rsds) file or an Office Data Connection (.odc) file.

An .rsds file is similar to an .rds file, but it has a different schema. To create an .rsds file, you can publish an .rds from Report Designer or Model Designer to a SharePoint library (a new .rsds file is created from the original .rds file). Or, you can create a new file in a library on a SharePoint Site.

Create a shared data source (RSDS file)

You can create shared data sources on a SharePoint site with SQL Server 2012 Reporting Services (SSRS) SharePoint mode.

  1. In a SharePoint document library, click the Documents tab > New Document >Report Data Source.

 Note    If Report Data Source isn’t on the New Document menu, the report data source content type isn’t enabled. The SharePoint site administrator needs to Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).

  1. In Name, enter a descriptive name for the RSDS file.
  2. In Data Source Type, select Microsoft BI Semantic Model for Power View.
  3. In Connection String, specify a pointer to the data source and any other settings that are necessary for establishing a connection to the external data source. How you connect to a data model depends on how it is published or deployed. Here are examples for:
  • An Excel workbook in Shared Documents on a SharePoint site with the SQL Server 2012 SQL Server 2012 Reporting Services (SSRS) add-in—for example:
Data Source=”http://<SharePointSite>/Shared Documents/MyExcelWorkbook.xlsx”

 Note    In SharePoint 2013, if the report and workbook are in the same farm, then you don’t need the PowerPivot for SharePoint add-in. If they’re in different farms, then you still need the add-in in the farm hosting the workbook.

  • An Excel workbook in the PowerPivot Gallery on a SharePoint site with SQL Server 2012 PowerPivot for SharePoint and SQL Server 2012 Reporting Services (SSRS) add-ins—for example:
Data Source=”http://<SharePointSite>/PowerPivot Gallery/MyExcelWorkbook.xlsx”
  • A tabular model deployed to a SQL Server 2012 Analysis Services (SSAS) instance on a server—for example:
Data Source=ServerName\InstanceName; Initial Catalog=TabularModelName

 Note    For a default instance, you can leave out “\InstanceName”.

  1. In Credentials, specify how the report server obtains credentials to access the external data source. For Power View, credentials need to be stored or integrated. Read Credential and Connection Information for Report Data Sources.
  2. Click Test Connection to validate the data source configuration.
  3. Click OK to create the shared data source.

Create a BISM connection file

You can create Business Intelligence Semantic Model (BISM) connection files on a SharePoint site configured with the SQL Server 2012 PowerPivot for SharePoint add-in. BISM connection files can connect to Excel workbooks or SSAS tabular models.

Use a BI Semantic model to connect.

If you are connecting to a tabular model database that runs on an Analysis Services server, SharePoint permissions are not sufficient. You must also have database read permissions on the server. This step should have been performed when you created the BI semantic model connection. For more information, see Create a BI Semantic Model Connection to a Tabular Model Database.

For ad hoc report design in SharePoint, the user who creates the report must have SharePoint permissions on the BI semantic model connection (.bism) file and on the business intelligence semantic model database. The security context of the connection is the interactive user who is creating the report.

This section explains the connection behavior between various client applications, such as the Excel desktop application or the Power View reporting client on SharePoint, and a tabular model database inside or outside the SharePoint farm.

Connecting from Excel to tabular data on a network

When an Excel user specifies a BI semantic model connection as a data source, the connection information inside the .bism file is downloaded to the client application, which then issues its own direct request to the tabular model database on Analysis Services. To access the .bism connection, the Excel user must be a SharePoint user with read permissions on the .bism connection file. Once the connection information is downloaded, all subsequent connections bypass SharePoint, flowing directly from Excel to the backend tabular model database.

Connecting from Power View to tabular data on a network

When a SharePoint user clicks on a BI semantic connection in a document library, Power View (if it is installed), starts immediately and opens a connection to the tabular model database.

Connections between Power View and a tabular model database follow a double-hop authentication sequence where the user identity is flowed from the client to SharePoint, and then from SharePoint to a back-end Analysis Services tabular model database that runs outside of the farm. The ADOMD.NET client library that handles the connection request always tries Kerberos on the first attempt. If Kerberos is configured, the user identity is impersonated on the connection to the tabular model database, and the connection succeeds.

If Kerberos is not configured and the request fails, Reporting Services makes a second attempt. Under this scenario, the client library connects to Analysis Services using the Reporting Services service identity and NTLM authentication. The identity of the Power View user is passed on the connection string using the effectiveusername parameter.

Only a member of the system administrator role on the Analysis Services instance has permission to make a connection using the effectiveusername parameter and impersonate another user on the server instance. For this reason, the execution account of the Reporting Services shared service must have administrative rights on the Analysis Services instance.  Instructions for granting administrative permissions to the service account is provided in this topic, Create a BI Semantic Model Connection to a Tabular Model Database.


PowerPivot SQL Server 2008 March 18, 2013

Filed under: Checklist,Deployment,PowerPivot,SharePoint 2010 — sladescross @ 2:46 pm

Microsoft SQL Server 2008 R2.

Summary: Microsoft® SQL Server® PowerPivot technology redefines how IT departments deliver and succeed with BI. This technology gives users the power to drill into any aspect of their business and compress decision cycles to gain deeper insight, and it enables them to share their findings with others effortlessly and securely. At the same time, the IT department gains effective new management tools to increase operational efficiency in a Microsoft SharePoint® Server 2010 farm environment.Establishing a PowerPivot environment requires deploying the PowerPivot for Excel®2010 add-in on workstations running Microsoft Office 2010 and PowerPivot for SharePoint on SharePoint application servers. Those tasks are independent of each other, but planning and coordination are required to ensure the overall success of the deployment efforts.

Secure Store for BIS (SharePoint 2013).

Use the following checklist to install these BI features in the same SharePoint farm: PowerPivot for SharePoint, Report Builder, and Power View. Although this checklist recommends a specific installation order, in practice you can install these features in almost any order. This checklist assumes installation of the following products or features:

SharePoint Server 2010 with Service Pack 1 (SP1)

  • SQL Server 2012 Database Engine
  • SQL Server 2012 Reporting Services and Reporting Services Add-in
  • SQL Server 2012 PowerPivot for SharePoint


Step 1: Install PowerPivot for SharePoint

Step 2: Configure the Server

Step 3: Install Analysis Services OLE DB providers on Excel Services Application Servers

Step 4: Verify the installation

Additional installation steps are required if you run Excel Calculation Services and PowerPivot on separate application servers. On the application servers running Excel Calculation Services, install the appropriate version of the Analysis Services OLE DB (MSOLAP) provider.

There are two scenarios that call for manually installing the OLE DB provider on servers in the farm.

The most common scenario is when you have older and newer versions of PowerPivot workbooks that are saved in document libraries in the farm. If analysts in your organization are using the SQL Server 2008 R2 version of PowerPivot for Excel, and they save those workbooks to a SQL Server 2012 PowerPivot for SharePoint installation, the older workbook will not work. Its connection string will reference an older version of the provider, which won’t be on the server unless you install it. Installing both versions will enable data access for PowerPivot workbooks created in older and newer versions of PowerPivot for Excel. SQL Server 2012 Setup does not install the SQL Server 2008 R2 version of the provider, so you must install it manually if you are using workbooks from a previous version.

The second scenario is when you have a server in a SharePoint farm that runs Excel Services, but not PowerPivot for SharePoint. In this case, the application server that runs Excel Services must be manually updated to use both the newer version of the provider, as well as install an instance of the Microsoft.AnalysisServices.Xmla.dll file in the global assembly. These components are necessary for connecting to a PowerPivot for SharePoint instance. If Excel Services is using an older version of the provider, the connection request will fail.

Use the following instructions to add the OLE DB provider and other client connectivity components to SharePoint servers that do not already have them installed, such as application servers that run Excel Services without PowerPivot for SharePoint on the same hardware.

Use these instructions to install the current Analysis Services OLE DB provider and to add the Microsoft.AnalysisServices.Xmla.dll to the global assembly.

Verify a PowerPivot for SharePoint installation.

To verify PowerPivot data access on the server, do the following:

  1. Download the Picnic data sample that accompanies a Reporting Services tutorial. You will use the sample workbook in this download to verify PowerPivot data access. Extract the files.
  2. Upload the Excel workbook (.xlsx) to Shared Documents. The workbook contains embedded PowerPivot data.
  3. Click on the document to open it from the library.

Installing SharePoint 2013 Apps March 4, 2013

Filed under: Apps,Deployment,Installation,SharePoint 2013 — sladescross @ 8:07 am

There are a lot of similarities to working with solution packages but there are several differences to be aware of. The documentation on TechNet is pretty good, but putting it all together can be tricky.

Specific to PowerShell, there is some terminology to familiarize yourself with. You’ll see these for parameter names so you need to know what they are otherwise you’ll find yourself confused.
•App Package – physical file containing the app (.app file)
•App – an instance of an app installed on a particular subsite

Along with the App, you’ll find an Id property that refers to a GUID of that particular app instance. We’ll talk about that more when it comes to updates.


SharePoint 2013 App Manifest February 22, 2013

Filed under: App,Deployment,Sharepoint — sladescross @ 10:00 am

A app for SharePoint package is a file that has an “.app” extension and that complies with the Open Packaging Conventions (OPC). The package contains the following items:

Various items and the following.

SharePoint Solution Packages: Optionally, the app may include a SharePoint solution package (.wsp file) that contains the components of the app web. These components may include pages, list instances, views, documents, Web-scoped Features, and other SharePoint 2013 components. (For more information about what SharePoint components can be included in an app for SharePoint, see Types of SharePoint components that can be in an app for SharePoint.) The .wsp file may also contain apps for Office. The components in the .wsp file are deployed to the app web. For an example of an app package that includes a SharePoint solution package, see How to: Create a cloud-hosted app that includes a custom SharePoint list and content type.


SSIS Package Deployment February 1, 2012

Filed under: Deployment,SSIS,SSIS Deployment Package,SSIS Variable — sladescross @ 1:01 am

Package deployment for SQL Server 2008.

Configure extra folders in MSDB for package deployment.

Add a custom file system folder.

Tutorial deploying packages. Building the deployment bundle.

In this lesson, you will copy the deployment bundle to the destination computer and then run the Package Installation Wizard to install the packages, package dependencies, and ancillary files on that computer. The packages will be installed in the msdb SQL Server database and the other items will be installed in the file system. After you complete the package installation, you will test the deployment by running the packages from SQL Server Management Studio using the Execute Package Utility.

In this task, you will run the Package Installation Wizard to deploy the packages from the Deployment Tutorial project to an instance of SQL Server. Only packages can be installed in the sysssispackages table in the msdb SQL Server database, the supporting files that the deployment bundle includes will be deployed to the file system.

Integration Services provides several tools that you can use to run packages in the test and production environment: the command prompt utility dtexec and the Execute Package Utility. The Execute Package Utility is a graphical tool that is built on dtexec. Both of these tools execute the package immediately. In addition, SQL Server provides a subsystem of SQL Server Agent that is especially designed for scheduling package execution as a step in a SQL Server Agent job.

Head of the package deployment tutorial.

Run through of using a variable for a parameter to SQL statement,

Run through of passing SSIS variables to the SQL task.

Making the package portable.

Always reuse existing XML configuration files, if one exists for your server and database combination. This is the sole reason behind configuration files, as you will have to update connectivity information in only one place. If there isn’t a configuration file already created for your server and database combination, create a new configuration file using the below templates and follow the described naming convention.
For Windows authenticated connection to SQL Server:

 <DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String"> 
<ConfiguredValue>Data Source=[SRVNAME];Initial Catalog=[DBNAME];Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue> </Configuration> </DTSConfiguration> 

For SQL Server authenticated connection to SQL Server:

 <DTSConfiguration> <Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String"> 
<ConfiguredValue> Data Source=[SRVNAME];Initial Catalog=[DBNAME];User ID=[LOGIN];password=[PWD];Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False </ConfiguredValue> </Configuration> </DTSConfiguration> 

Note: In the above templates, square brackets [] are not required except around the name of the connection. Do not surround server name, database name, user name and password with square brackets



Deploying Package January 30, 2012

Filed under: Deployment,Package Deployment,SSIS — sladescross @ 10:54 pm

SSIS 2008 Deploying Package tutorial.



Publish and Deploy a BDC Model January 11, 2012

Filed under: BDC Publish,Deployment,Publish — sladescross @ 8:53 pm



Click Once and BCS December 28, 2011

Filed under: BCS,Click Once,Deployment — sladescross @ 7:49 pm

The mechanism for taking external lists offline makes use of an ActiveX control to check the prerequisites mentioned earlier. Because only Internet Explorer supports ActiveX controls, taking external lists offline is supported only in Internet Explorer. In other browsers, such as Firefox, the Connect to Outlook and Sync to SharePoint Workspace buttons are, disabled as shown in Figure 7.



Get every new post delivered to your Inbox.

Join 63 other followers