Connecting to a pre-SQL 2012 SSIS Instance with SQL 2012 SSMS

when I tried to connect to a SSIS SQL 2008 R2 instance with SQL 2012 SSMS I got the following error:

Connecting to the Integration Services service on the computer “MyServerName” failed with the following error: “Class not registered”.

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

I found a workaround proposed in this article by Phil Brammer

In short, you have to edit the C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml file on your SSIS 2012 instance server. Then add folder entries with new folder names that point to the Pre-2012 version of SSIS instance. After restarting the SSIS service on your SSIS 2012 instance, start SSMS 2012 and connect to the the SSIS 2012 instance (NOT to the SSIS 2008). In this instance you will see the new folders, and when you fold open the folders you will see the packages installed in the SSIS 2008 instance.

Add administrators to reports site after installation of Reporting Services

Add administrators to reports site after installation of Reporting Services

Problem

After installation and setup of the reporting services, when you browse to the reports site, you receive this error:

“User 'Domainname\Username' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”

You cannot even assign access because the Site settings link on the top right hand side is not available.

Solution

2 steps need to be performed:

• Add a windows user or group to the System Role Assignments and grant System Administrator rights.
• Add a role for this user or group at the root folder of the Report Server and grant

Walkthrough

• Log on locally to Windows Server running the Reporting Services with an account that is member of the local admin group.
• From the start button, run IE as administrator
• 
• In the UAC dialog, click Yes
• 
• In the IE address bar, browse to http://localhost\reports
• This there is no error message and the Site Settings link is available
• 
  Click on the Site Settings link, Click on the Security tab in the left hand pane
• 
• Click New Role Assignment in the toolbar above the list of groups and Users.
• In the New System Role Assignment page, add a Windows Group or username and assign a role, check System Administrator and System User. Click OK.
• 
• The new role is listed under Group or User.
• 
• Browse to the Reporting Services Home page, by clicking the Home link at the top of the page.
• Now you are in the Root folder of the Reports. In the toolbar, click Folder Settings.
• 
• In the Folder settings, there is only one tab, namely ‘Security’. Click on New Role Assignment.
• 
• In the New Role Assignment page, type a group or username, check Content Manager. Click OK to create.
• 
• The new role is assigned and listed in the list of Group or User.
• 

With these 2 actions we granted a windows group or user administrative rights on the report server. From now on you can browse to the reports site on any client PC, without the need to run as administrator.

To add users that can only browse reports

The following steps are needed:

• Grant access to the root folder of the reports site.
• Grant access to the Folder with the reports that the user needs to consult.

On SQL Server 2012 and 2014 Use ALTER ROLE ADD MEMBER instead of sp_addrolemember

While creating some scripts in a test environment to create logins and add members I noticed that ALTER ROLE has been upgrade with the ADD MEMBER option. this option replaces the stored procedure sp_addrolemember used in SQL 2008 R2 and previous versions.

The syntax for ALTER ROLE has now been upgraded with the ADD MEMBER option:

ALTER ROLE role_name

{

      [ ADD MEMBER database_principal ]

    | [ DROP MEMBER database_principal ]

    | WITH NAME = new_name

}

For instance on a SQL Server 2008 R2 granting logins to a Database would be like this:

USE [AdventureWorks]

GO

CREATE USER [Mydomain\MyAccount] FOR LOGIN [Mydomain\MyAccount]

GO

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'Mydomain\MyAccount'

GO

With SQL 2012 or 2014 you can now create logins with the following script:

USE [AdventureWorks]

GO

CREATE USER [MyDomain\MyAccount] FOR LOGIN [MyDomain\MyAccount]

GO

ALTER ROLE [db_datareader] ADD MEMBER [MyDomain\MyAccount]

GO

On the MSDN library site the SQL Server 2012 documentation for sp_addrolemember warns that this will be phased out in a future version