Subscribe to Blog via Email
Part of this is going to be the SQL Server DBA in me ranting, so be patient and know as it says in the lovely site disclaimer, this is MY OPIONION and yes, I stick by this opinion 100%.
I have very strong feelings on who and how a SQL Server, especially one with SSRS is installed. Microsoft’s continued mis-marketing, (albeit successful for sales) and ease of installation has created database environments that are misconfigured, poor-performing and poorly designed- BY DEFAULT.
Few experts in .Net or SSRS, when asked, know what transaction logs, filegroups, lock escalation or sp/dbcc procedures are. If you don’t know what each and every one of these are for, you shouldn’t be installing SQL Server. If you don’t know why the tempdb and transaction logs should be on separate spindles or why it’s not a good idea to have one, monstrous, huge drive, (see the first part of this sentence for a clue…) then you shouldn’t be installing SQL Server.
What are the rules of thumb a DBA follows when we are installing?
So, what started the rant? One of my poor NT Admin’s went through a challenging process that renamed a service account. After he corrected all this procedure broke, we arrived a couple days later to find that no one could run any of the reports through the web from this main reporting server.
It turns out, when this SQL Server with SSRS was installed and configured for our company by a third party vendor it was performed with the service account that the NT Admin was forced to rename. Due to this, it was the “db owner” of the Reporting Server, including all the encryption internally and cached authentication had “run out”.
I found the error immediately, as there were also SQL Server Agent jobs attempting to authenticate with the service account, so the following message was reported in SQL Server’s error logs:
 SQLServer Error: 15404, Could not obtain information about Windows NT group/user ‘DOMAIN\SERVICE_ACCT’, error code 0xffff0002. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
It wasn’t as simple as renaming the database owner for the ReportServer database, as there are encryption keys that are created at the time of installation. I will say, they have simplified the process for 2008 vs. earlier versions though! 🙂
• Change the ownership of the ReportServer and ReportServerTempdb databases to the DOMAIN\NEW_SRVC_ACCT database, (as it should have originally been performed as.) using the stored proc sp_changedbowner.
exec sp_changedbowner ‘DOMAIN\NEW_SRVC_ACCT’
• Start the Reporting Services Configuration Manager and connect to the database repository for the SSRS.
Many issues, like the one above, can be avoided if a DBA works with the NT Admin to ensure the server is build correctly to support SQL Server and if a DBA performs the installation and configuration of the database server.
**Added 10/07/10 after an email or two with a couple other SQL Server DBA’s-
IF you don’t know any of the topics that I listed for requirements to install SQL Server, it should also be a requirement to know these before you are aliased as DBO or given admin privileges on a SQL Server box- nuff’ said!!
~done rant!~ 🙂