Error: SQL Server Configuration Manager 2008 R2 (SSCM)

The Remote Procedure call failed. [0x800706be]

Environment: Windows 7 (x64), SQL Server 2008 R2, SQL Server 2012

I have installed SQL Server 2012(DENALI) alongside SQL Server 2008 R2 on my system.

While attempting to stop SQL Server 2008 R2 services using SQL Server Configuration Manager, I got the following error (refer screen shot)

Figure 1: Error in SQL Server Configuration Manager 2008 R2

To make it sure, please check the version of Configuration Manager (refer below screenshot)

Figure 2: Version of SQL Server Configuration Manager

  

However, SQL 2012 Configuration Manager lists all running services of 2008 R2 and 2012.

(Note the highlighted areas, you can find both versions of SQL Services listed)

Figure 3: SQL Server 2012 Configuration Manager lists all versions of SQL services, version of ConfigMgr

 (As a fact, Microsoft Office 2013 cannot be installed along side Office 2010, it upgrades 2010 to 2013). Likewise, IS this will be an initiation of depreciating use of older versions & tools? As many of them say  Yep, the answer is Nope!

Real Cause:

SQLs WMI provider DLLs (which are shared resources) likely gets corrupted after installation of newer version (SQL 2012).

Known solutions such as command line recompilation of MOF files and re-registration of DLL’s won’t work against this.

• mofcomp.exe “~\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof” 
• regsvr32 “~\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll”

Solution:

Installing service pack 2 for SQL Server 2008 R2 has resolved this error. (Download SP2)

However, it is always good ;-) to install the latest service pack i.e. SP3 is available (Download SP3)

EnJoY!!!!...
CoOoOlLlL.....

Upgrade: SQL 2005 on Windows server 2003 to SQL 2012

Environment: Windows 2003 R2 , SQL Server 2005, SSRS 2005

The fact is, SQL Server 2012 is not supported in Windows Server 2003. While we attempt to do so, we will always end up with an error message as in screenshots.

Test Server configuration

Run Setup

Error Message blocking further installation

How ever, we have many workarounds. One like,Upgrade your Windows server and then upgrade your SQL Server.... Oouch...! (is a very painful task thereafter). 

The cool way is migrate to a new server ;-)

EnJoY!!!!...
CoOoOlLlL.....

SQL Installation: Step by step Installation of SQL SERVER 2012 (DENALI)

1. The Installation Wizard runs the SQL Server Installation Center.

To create a new installation of SQL Server, click Installation in the left-hand navigation area.

Installation wizard

2. Click New SQL Server stand-alone installation or add features to an existing installation.

3. The System Configuration Checker runs a discovery operation on your computer; you can view the details on the screen by clicking Show Details.

4. On the Setup Support Files page, click Install to install the Setup support files.

5. On the Product Key page, select an option to indicate whether you are installing a free edition of SQL Server, or a production version of the product that has a PID key. 
In our case choose Evaluation edition under Specify a free edition option.
To continue, click Next.

6. On the License Terms page, review the license agreement and, if you agree, select the I accept the license terms check box, and then click Next.

7. On the Install Setup files page, Setup provides the progress of downloading, extracting, and installing the Setup files. If an update for SQL Server Setup is found, and is specified to be included, that update will also be installed

8. The System Configuration Checker verifies the system state of your computer before Setup continues.
To continue, click OK

9. The Setup Support Rules page runs a rule check operation on your computer; you can view the details on the screen by clicking Show Details.

10. On the Setup Support Rules page, click Next to install the Setup support rules.

11. On the Setup Role page, select SQL Server Feature Installation, and then click Next to continue to the Feature Selection page

 
12. On the Feature Selection page, select the components for your installation. 
Read the description for each component group appears in the Feature description pane after you select the feature name and select any combination of features which ever needed.

The prerequisites for the selected features are displayed in the Prerequisites for selected features pane. SQL Server Setup will install the prerequisite that are not already installed during the installation step described later in this procedure.
The default installation path is C:\Program Files\Microsoft SQL Server\110\. It can be changed if needed.
 
13. On the Installation Rules page, Setup verifies the system state of your computer before Setup continues.
If you have already installed Visual Studio 2010(without SP1), the following error appears.

On clicking view detailed Report link, you can find the relevant error message.

Download and install Visual Studio 2010 Service Pack1 from the following link: 
http://go.microsoft.com/fwlink/?LinkId=220649
Rerun the setup and follow the steps from 1 to 13, so that you will end up with status as Passed in Installation Rules window.

14. To Continue, click Next

 
15. On the Instance Configuration page, specify whether to install a default instance or a named instance. In our case, I have SQL 2008 R2 instance already running and going for Named instance.

Instance ID — by default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for your instance of SQL Server.

Instance root directory — by default, the instance root directory is C:\Program Files\Microsoft SQL Server\110\.

Installed instances — the grid shows instances of SQL Server that are on the computer where Setup is running (MSSQLSERVER – refers to already installed SQL server 2008 R2 default instance).
Only one default instance can be installed in a machine, despite of version

Click Next to continue.
 
16. The Disk Space Requirements page calculates the required disk space for the features that you specify. Then it compares the required space to the available disk space.

 
17. Server Configuration — Service Accounts page can be used to specify login accounts for SQL Server services. 
• The actual services that are configured on this page depend on the features that you selected to install.
• You can assign the same login account to all SQL Server services, or you can configure each service account individually. 
• You can also specify whether services start automatically, are started manually, or are disabled. Microsoft recommends that you configure service accounts individually to provide least privileges for each service, where SQL Server services are granted the minimum permissions they have to have to complete their tasks.

18. Server Configuration — Collation page is used to specify non-default collations for the Database Engine and Analysis Services. (No need to worry about this now, let’s proceed with default collation)
Click Next to continue

 
19. Database Engine Configuration – Server Configuration tab is used for Account Provisioning by specifying the following:
• Authentication Mode — Select Windows Authentication or Mixed Mode Authentication for your instance of SQL Server. If you select Mixed Mode Authentication, you must provide a strong password for the built-in SQL Server system administrator account.

• Specify SQL Server Administrators —specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove.
Click Next to continue.
 
20. Analysis Services Configuration — Server configuration tab is the Account Provisioning page to specify the server mode and the users or accounts that will have administrator permissions for Analysis Services.
• Server mode determines which memory and storage subsystems are used on the server. Different solution types run in different server modes.
If you plan to run multidimensional cube databases on the server, choose the default option, Multidimensional and Data Mining server mode

• Specify Administrative permissions — specify at least one system administrator for the Analysis Services. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove.
• Analysis Services Configuration — Data Directories page is used to specify non-default installation directories. 
To install to default directories, click Next to continue.
 
21. Reporting Services Configuration - page is used to specify the kind of Reporting Services installation to create.

 
22. Distributed Replay Controller - Configuration page is used to specify the users we want to grant administrative permissions to for the Distributed Replay controller service.

• Specify Administrative permissions — specify at least one system administrator for the Distributed Replay Controller Services. To add the account under which SQL Server Setup is running, click Add Current User. To add or remove accounts from the list of system administrators, click Add or Remove.
• To continue, click Next.
 
23. Distributed Replay Client Configuration page to specify the users you want to grant administrative permissions to for the Distributed Replay client service. Users that have administrative permissions will have unlimited access to the Distributed Replay client service.
• Controller Name is an optional parameter, and the default value is . Enter DENALI as the client controller name.

• To continue, click Next.
 
24. On the Error Reporting page, disable the option for error reporting and click Next.

 
25. The System Configuration Checker will run one more set of rules to validate your computer configuration with the SQL Server features that you have specified.

 
26. The Ready to Install page shows a tree view of installation options that were specified during Setup

To continue, click Install. 
 
27. During installation, the Installation Progress page provides status so that you can monitor installation progress as Setup continues…

 
28. After installation, the Complete page provides a link to the summary log file for the installation and other important notes.  

29. To complete the SQL Server installation process, click Close. 

Now we have successfully completed installing SQL Server 2012(DENALI). 
References: MSDN

EnJoY!!!!...
CoOoOlLlL.....

SSRS Report Viewer Error

Date Picker does not appear on report. (Or) Report viewer toolbar going out of sync (or) Report viewer toolbar not displayed properly 

Scenario:

• ·    When you have SSRS reports and your web application is hosted in IIS 6.0. and migrated to IIS 7.0
• ·         The report works fine in report manager or Report server.  
• ·         But, when reports were processed via the web application, the report viewer does not render the toolbar properly (and/or) the date picker icon is not displayed.

Nature of the error:

This means the error lies with the web application/IIS configuration and not with SSRS configuration.

Note: If the entire web application was developed in .net framework 4.0, the configuration will be added in default. This is only for the application, when migrated from IIS 6.0 to IIS 7.0

Work Around:

Kindly ensure that the following entries were found in the “web.config” file.

1.       In the assemblies section, 2 entries should be there for the report viewer as follows:

<assemblies>

<add assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

<add assembly="Microsoft.ReportViewer.Common, Version=9.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/>

assemblies>

2.       In the Http handlers section, an entry should be there for the report viewer as follows:

<httpHandlers>

      <add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>

httpHandlers>

Solution:

3.       To make sure IIS 7.0 to render the reports properly the following section has to be added.

<system.webServer>

 <handlers>

<add name="ReportViewerWebControl" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>

  handlers>

system.webServer>

EnJoY!!!!...

CoOoOlLlL.....

Source Control: Using TFS 2010 with BIDS 2008 (or) Adding your BIDS Solution to TFS 2010

Steps:

•     Install Visual Studio 2008 Team Explorer.
•     Install Visual Studio 2008 SP1. Download it from here.  (VS90sp1-KB945140-ENU.exe)

Screen Shots - Installing Visual Studio Service Pack 1: 

• 

  Extraction of files

Products dependent on this update

Accept Terms

Installation Progress

•       Install “Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010”.  Download it from here. (VS90SP1-KB974558-x86.exe)

VS 2008 Forward Compatibility update

Accept terms for Forward Compatibility

Installation of Forward compatibility update

Successful completion of forward compatibility update

EnJoY!!!!...
CoOoOlLlL.....

Data Driven Subscriptions and Row-level Security

Scenario: A customer wants to have report row-level security and data-driven subscriptions. If you have used SSRS for a while, you will now the cornerstone of report row-level security is User!UserID which returns the user identity. But here is the catch – SSRS will refuse creating a data driven subscription if a report has an expression with User!UserID.



Solution: How do we solve this horrible issue? A great hack is in order. We will disguise the User!UserID in an embedded function inside the report or in an external assembly so SSRS can't see it. (The reason why this function is named GetHashedUser although no hashing is in place will become obvious in a moment)


Public Function GetHashedUser() as String
   return Report.User!UserID.ToLower()
End Function

Believe it or not, this lets the data-driven subscription going. But there is another catch. A data-driven subscription runs in unattended mode and you need to pass the user identity as a parameter. So, when the report is executed live we want to pass the internal User!UserID to the report query or stored procedure. But when the report is subscribed to, we want to pass the user identity as a parameter. A simple expression for the query parameter will do the trick.

=iif(Parameters!UserID.Value ="", Code.GetHashedUser(), Parameters!UserID.Value)


If the parameter is empty (live delivery), we will call the GetHashedUser function, otherwise, we will pass the parameter value to the database. So far so good, but we now have a security hole. There is nothing stopping the end user from spoofing the report parameter and passing another user's identity to it to force the report take the data-driven subscription path. To fix this, we will need some level of encryption. It is suggested a solution that encrypts the user identity with SHA1 hash both in the report and the database. This should keep spoofy users at bay.

Note: The above solution assumes a report connection string with stored credentials which is what you would typically have for T-SQL (relational) reports. What about SSAS with Windows and dimension security? Not a good story and no changes in Denali.

Original Post by: Teo Lachev (Link)

EnJoY!!!!...
CoOoOlLlL.....