500th Twitter follower spotlight: Joshua Luedeman

It is hard sometimes to take the time to acknowledge our Twitter followers and spend time getting to know them. I decided to change that a little bit and go beyond the 140 character limit and do a video conference spotlight on whomever became follower number 500th.

Recently, I reached my 500th follower on Twitter. The lucky tweep was Joshua Luedeman aka @BigDadyLueda, a DBA/Business Intelligence Developer from Tallahassee, FL. I asked Joshua to do a recorded video conference via Skype and answer a couple of questions to get to know him better and introduce him to the rest of the community.

It turned out Joshua is a very smart guy from upstate New York who recently moved to Florida with his family. Joshua and I share several things in common:

• Proud father of two girls
• Husband in love with his beautiful wife
• Passionate about SQL Server and Business Intelligence
• Recently moved to Florida
• Recently re-focused his career in Business Intelligence
• Looks up to @SQLChicken (Jorge Segarra) and his SQL University initiative
• Reads SQL Server Books on Line and related blog posts
• Desires to become more active in the SQL Server, Business Intelligence, SQL PASS  community by blogging and speaking

It was truly a pleasure and honor to get to know him in more than 140 characters. Here is the recording of our video conference:

 

SSRS errors: (rsReportServerDatabaseUnavailable) The report server cannot open a connection to the report server database.

Sometimes you may get this error when trying to launch your report server webpage:

The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

While the error is descriptive enough to let you know that Reporting Services could not find the report server database, it may be a challenge to some who have no idea where this database is and what is it used for. To your surprise, there is more than one database involved.

Reporting Services requires two databases to run:

• ReportServer
• ReportServerTempDB

In short, the Report Server database is needed to store report definitions, report history and snapshots, report models, shared data sources, resources, scheduling and delivery information, metadata and several other objects. ReportServerTempDB is where Reporting Services stores session and execution data, cached reports, and work tables that are generated by the report server. For more information about Reporting Services database visit: http://msdn.microsoft.com/en-us/library/ms156016.aspx.

To solve the error mentioned above you need the following information:

1. What is the SQL Server Reporting Services instance you are trying to reach?
2. Where are the SQL Server Reporting Services databases hosted?

Assuming you have the right permissions, you will need to run the Reporting Services Configuration Manager and point to the Reporting Services instance as seen on the image below. You would normally find  Reporting Services Configuration Manager under Start->Programs->Microsoft SQL Server 2008 R2->Configuration Tools

Once you connect to your Reporting Services instance through Reporting Services Configuration Manager, go to the  Database tab located on the left pane to view the name of the SQL Server instance where the ReportServer database is being hosted and the name of the ReportServer database as seen in the image below.

Keep in mind that in some environments the ReportServer database may be hosted in a separate SQL Server database instance and the ReportServer database may have been renamed to other than the default “ReportServer” database name. You will usually find this type of configuration in a scale-out architecture. For more information on scale-out architecture visit: http://sqlcat.com/technicalnotes/archive/2008/06/05/reporting-services-scale-out-architecture.aspx.

Once you located the SQL Server database instance where the ReportServer and ReportServerTempDB database are hosted you need to follow this checklist:

1. Is the SQL Server database instance running? Is the SQL Server service started?
2. Can you verify connectivity to the SQL Server database instance?
3. Are the ReportServer and ReportServerTempDB databases attached and online?
4. Does the Reporting Services service account have read/write access to the ReportServer and ReportServerTempDB databases?

Once all of this is verified to be true and issues have been fixed, than the last step would be to restart the Reporting Services instance. If everything is right you should be able to access your reports. It is highly recommended to frequently backup both the ReportServer and ReportServerTempDB databases. If you lose you ReportServer database you may re-deploy reports, data sources and datasets, but you will lose all subscriptions, schedules and report parts that users may have created.

Also, keep in mind that the ReportServerTempDB database does not behave like the SQL Server TempDB. Per MSDN Books on Line:

“Reporting Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables. Although the temporary database does not contain persistent data, you should back up a copy of the database anyway so that you can avoid having to re-create it as part of a failure recovery operation.” For more details visit: http://msdn.microsoft.com/en-us/library/ms156016.aspx

 

Phoenix User Group Presentation: 0 to SSIS

Thanks to the Phoenix User Group for hosting me tonight! The presentation went smooth and had good questions at the end. I will present anytime you need me and hopefully make it to Phoenix sometime soon.

Cheers my fellow Phoenix friends!

Phoenix User Group 2/9/2011 meeting attendees

SSAS errors: Logon failure / Cube process failure

Often I encounter this error myself when trying to process an SSAS cube at a client’s site or get asked by customers and friends how to solve this error. Although it may seem really obvious,  not everyone may understand “what” is rejecting their credentials or “what” are they authenticating against.

Simple. The data source.

So, what credentials are being passed through to your data source?

At this point you may answer: “my Windows credentials” or “credentials of the current user.” This is not always the case.

Impersonation
Your Windows credentials are used to create the connection strings to your data source at design-time. During processing and run-time, Analysis Services needs an account to be able to reach the data source. Keep in mind that you will not always process Analysis Services cubes manually within BIDS or SSMS. Once the Analysis Services cube is initially deployed, in most environments you will have scheduled SSIS packages that will re-process the cubes every night. In order for Analysis Services to be able to read the data from your data source, it needs some form of credentials to pass through. This is also known as impersonation.

In Analysis Services 2008-R2, there are several impersonation options:

• Use a specific Windows username and password
• Use the service account
• Use the credentials of the current user
• Inherit (or default)

To learn more about each of these options you can read MSDN Books Online at: http://msdn.microsoft.com/en-us/library/ms187597.aspx

In a nutshell, the impersonation account specified needs to have read access to your data source. For example, if your data source resides in SQL Server, the account specified as your impersonation account needs to  be mapped to your database with db_datareader role membership. It is best practice to specify a dedicated account that has limited read-only access to the database. Avoid using an account with elevated privileges such as sysadmin or db_owner.

The recommended impersonation setting is to specify a Windows username and password. If your server is joined to a domain, you will have to specify DOMAIN\Username in the User name textbox and the associated password in the Password textbox as seen below:

If you specify “Use the service account”,  it will use the account used to start the Analysis Services instance to authenticate against your data source. In this case, the service account will need read access to the data source. Keep in mind that the system service accounts Network Service, Local System and Local Service will not have access to your data source if the data source is on a different server. Typically, you will use a Windows domain account that has Log on as Service privilege in Active Directory Group Policy as your service account to start Analysis Services.

Inherit (SQL Server 2008/2008-R2, Default in SQL Server 2005) uses the impersonation mode  and credentials set in the Data Source Impersonation Info database property. To view or modify this database property, open SSMS and connect to the Analysis Services instance, expand the databases folder and right click on the database you are interested and select properties. You will now see the database properties window and the impersonation mode selected. To change the impersonation mode, click on the elipsis inside of the Data Source Impersonation Info textbox highlighted below:

Per MSDN Books Online, by default the Data Source Impersonation Info database property is set to Use the service account. This means that if the Inherit impersonation option is selected in your BIDS solution, it will use whatever is specified in the Data Source Impersonation Info database property. If this database property was set to Default as shown on the image above, then it will use the Default impersonation mode in which it will use the impersonation method that is most appropriate for the context in which impersonation is used. For more details read http://technet.microsoft.com/en-us/library/ms126693.aspx.

In summary, if you are getting a logon failure when processing an SSAS cube, check the impersonation settings of your data source. As a best practice, choose “Use specific username and password” as Impersonation option and use a dedicated Windows domain account.

The dedicated domain account should be configured at a minimmum as follows:
– Regular domain user or part of a restricted domain user group
– No password expiration policy should be applied. If not, password may expire and processing will fail at some point.
– Account should be mapped to a SQL Server database login with db_datareader role membership.

If the dedicated domain account will be used as the service account, make sure it is part of the “Logon as a service” AD Group Policy.