Problems installing SQL Server 2005 with IIS 7

Overview

When I install SQL Server 2005 Enterprise on Windows Server 2008 with IIS 7, I was warned that "Microsoft Internet Information Services (IIS) is either not installed or is disabled". Actually this misleading warning means some features of IIS 7 are not installed, and SQL Server 2005 installation needs those features.

Reference

Problems installing SQL Server 2005 on Vista and IIS7

Deploy old SQL Reporting Services Reports to SharePoint Site

Overview

In my previous post, I discussed about how to setup SharePoint Integrated SQL Server Reporting Services (RS). Now that I have the Integrated RS ready, I can deploy the reports to the SharePoint site. But wait, how can I deploy a Pre-SP2 RS report to SharePoint site? Below shows the steps I used.

My RS reports consists of two files: a Report Definition file (.rdl) and a Data Source file (.rds). To deploy a Pre-SP2 RS report to a SharePoint site, I have to upload the Report Definition file, and create a new SP2 Data Source file (with extension changed to : .rsds).

Reference

1. Deploying Reports, Models, and Shared Data Sources to a SharePoint Site
2. How to: Create and Manage Shared Data Sources (SharePoint Integrated Mode)
3. How to: Add Report Server Content Types to a Library (SharePoint Integrated Mode)

Steps

Upload Report Definition file

1. Create a New Document Library (or use an existing one) in SharePoint site to store the Report Definition file and the Data Source file.
2. Upload the Report Definition file (.rdl) of your report to the Document Library created in Step 1.

Add "Report Data Source" Content Type to the Document Library

1. Before we can "New" a Data Source in SharePoint Document Library, we must first add the "Report Data Source" Content Type.
2. Navigate to the Document Library, select "Settings" -> "Document Library Settings"
3. Select "Advanced Settings"
4. Select "Yes" for "Allow management of content types", click "OK"
5. At "Document Library Settings", select "Add from existing site content types" under "Content Types" Category
6. Make sure that "All Groups" is selected for "Select site content types from:", then select "Report Data Source" Content Type and click "Add"
7. Click "OK"

Create a Data Source file in Document Library

1. Navigate to the Document Library, select "New" -> "Report Data Source"
2. Fill in the properties of the Data Source, I opened my old data source in visual studio and copy all the properties to the new one.
3. Click "OK"

Bind the Report Definition file to the new Data Source

1. Navigate to the Document Library, click to display the drop-down list of the Report Definition (.rdl) File
2. Select "Manage Data Sources" in the drop-down list
3. Click on the name of the Data Source to edit it.
4. Specify the URL of the newly created Data Source (.rsds) file in "Data Source Link" and click "OK"
5. Click "Close" to go back to Document Library, click on the Report Definition file to see the result. If a report is displayed successfully, then the deployment is done!

Guides on setting up SharePoint integrated SQL Server Reporting Services

Overview

This is going to be a long post, in this post I am trying to describe how I setup Integrated SQL Server Reporting Services on a Standalone SharePoint Server. According to my understanding, these steps can be applied to SharePoint Services too.

Software Configuration

• SQL Server 2005
• SharePoint Server 2007
• Microsoft .NET Framework 3.0 Download

Reference

Guides to install and configure

1. How to: Configure SharePoint Integration on a Stand-alone Server
2. Configuring Reporting Services for SharePoint 3.0 Integration
3. How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting Services Configuration)
4. How to: Configure the Report Server Integration Feature in SharePoint Central Administration

Information on MOSS integrated SQL Server Reporting Service

1. Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007
2. Installation and Configuration Guide for SQL Server Reporting Services - SharePoint Integration Mode (Word format)

Steps

Prepare the software package

1. SQL Server 2005 Service Pack 2 Download
2. SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies Download

Install software package

1. Install .NET Framework 3.0 (My server originally has this installed)
2. Install SQL Server 2005 SP 2
3. Install SQL Server 2005 RS Add-in for SharePoint

Create a Report Server DB for SharePoint Integrated Mode (Check Reference 3 for details)

1. Go to "Report Services Configuration" in SQL Server 2005 "Configuration Tools"
2. Connect to the Instance you want to create the Report Server (Mine is "MSSQLSERVER")
3. Click "Database Setup" on the left pane, then select the Server you want to connect and click "Connect"
4. Click "New" button next to the Database Name.
5. In "SQL Server Connection" dialog box, enter the name for the new Database, make sure to select the "Create the report server database in SharePoint Integrated mode" check box.
6. Click "OK"
7. On the Database Setup page, specify the Credential you want to connect to the report server, Click "Apply"

Configure Report Server Integration Feature in SharePoint Central Administration (Check Reference 4 for details)

1. Go to "SharePoint 3.0 Central Administration"
2. In Central Administration, select "Application Management", then click "Manage integration settings" under "Reporting Services"
3. Specify the "Report Server URL" and "Authentication Mode", normally the Report Server URL will be something like "http://server-name:8080/reportserver" where port number should follow your configuration but not always "8080". I use "Trusted Account" for "Authentication Mode"
4. Click "OK" to go back to "Application Management", then select "Grant database access".
5. Specify the "Server Name" and "Instance" of the Report Server (In standalone case the server name should be the same as the SharePoint Server).
6. In the pop up windows, specify the "User Name" and "Password" to connect to report server. Enter the administrator account here. Then click "OK" twice to go back to "Application Management".
7. Select "Set server defaults", if you enter this page successfully, then it means that SharePoint connect to report server successfully. Actually I didn't change any settings here, and the report service works fine in SharePoint Site.

Problems in deploying / debugging SQL Report Server Project: Permissions granted to user 'domain\username' are insufficient (rsAccessDenied)

Overview

I came across an issue that the project created from "Report Server Project Wizard" Template in Visual Studio did not deploy successfully and the "permissions granted to user 'domain\username' are insufficient for .... (rsAccessDenied)" error was shown.
Finally I was able to get it done by restoring the IIS 'ReportServer' Active Directory. Below shows the steps I follow to do the restore.

Reference

"The permissions granted to user '' are insufficient for performing this operation." when trying to view Report Site.

Steps

1. Click "Start" -> "All programs" -> "Microsoft SQL Server 2005" -> "Configuration Tools" -> "Reporting Services Configuration"
2. Enter the "Machine Name" & "Instance Name" and click "Connect"
3. Select "Report Server Virtual Directory" at the left pane and check "Apply default settings", and click "Apply"
4. Done!!!

Setup remote connection in SQL Server 2005

Overview:

SQL Server 2005 default 是不可以做 remote connection 的 (有點難以理解...), 而要設定SQL Server 為可以remote connection 也不是這麼容易的事. 經過一返Google以及試驗後, 終於成功以 SQL Server Authentication login 入 remote 既 DataBase.

Reference:

Enable remote connection to SQL Server 2005 Express

這個link講的是 2005 Express, 不過其實都是大同小異

Steps:

1. Enable SQL Server Authentication, 開一個 SQL Server Authentication Login Account (請參照上一個post)
2. 去 "All Programs" => "SQL Server 2005" => "Configuration Tools" => "SQL Server Surface Area Configuration"
3. 揀 "Surface Area Configuration for Services and Connections"
4. Expand "Server name" => "Database Engine" => "Remote Connections"
5. 揀 "Local and remote connections", 揀埋 "Using both TCP/IP and named pipes"
6. 0向左面 "SQL Server Browser", Expand 開揀 "Services", "Start type" 揀 Automatic, apply then start service.
7. 其實以上步驟已經configure好remote connection, 不過有可能SQL Server 無 enable到 named pipes 同 TCP/IP. 咁就要跟以下步驟黎enable 返
8. 去 "All Programs" => "SQL Server 2005" => "Configuration Tools" => "SQL Server Configuration Manager"
9. Expand "SQL Server 2005 Network Configuration", 揀 "Protocols for xxxxx", xxxxx 係你 Server Instance 個名
10. 0向右邊enable 返Named Pipes 同埋 TCP/IP, 跟住 restart service

如何新增 "SQL Authentication Login" in SQL Server 2005

Reference:
Windows Authentication vs. SQL Server Authentication

有沒有試過設定了新的SQL login account卻不能用它connect 到 SQL server的經驗呢？

原來SQL server 2005 有一個 "authentication mode" 的 setting, 而default是 "Windows Authentication mode", 即是只接受windows authentication, SQL authentication 是不行的.

要更改這個setting, 需要login 到 "SQL server Management Studio", 在 object explorer 中right click server 名字, 選 "properties" => "security", 就會見到可以選擇 "SQL Server and Windows Authentication mode" 啦~

如果要新增一個 SQL Authentication Login Account, 則在上面提到的 "properties" => "security" 再選 "Logins", 在右邉 "summary" 空白地方 right-click "New Login..." 就可以了, 記得選 "SQL Server Authentication"...

提多一點, 這裡create出來的account是沒有權限的, 連table也看不到, 要在各database處再添加permission給這個account才可以工作 (例如︰最簡單的觀看 table 內容就只要 "connect" 以及 "select" permission 就ok 了)