Archive: 2009/11

2009

MicrosoftSQL Server

(re)Gain sysadmin access to SQL2005 or SQL2008

In SQL Server 2005 and 2008 the local Administrators account is not sysadmin by default. This makes it even more important that the one setting up the Database also remembers to add a SQL Server admins group to the sysadmin role. If this step is forgotten, the user installing the database server is the only one that will ever be sysadmin. In some extreme cases I’ve seen situations where no one except some dude on vacation is sysadmin and I need to install or upgrade a bunch of applications and services. In these cases I have also been assigned Local Administrator rights on the server, but since the local Administrators group isn’t sysadmin either I still cannot login to the SQL server. What to do!? Thanks to Raul Carcia’s blog post it’s not that a big deal. The instructions are written for SQL Server 2005 but works equally fine on SQL Server 2008 and the only pre-requirement is that you are a local server administrator. Here’s what to do: Open the SQL Server Configuration Manager. In SQL Server Services, open the properties for the SQL Server instance you need access to. In the Advanced tab, find Startup Parameters. Add “;-m” to the end of that line. Press OK and restart the SQL Server into “Maintenance Mode” or “Single User Mode” if you like. (check that a restart is OK first 😉) Open a command prompt (right-click, “Run as Administrator” in Windows 2008) and go to C:\Program Files\Microsoft SQL Server\100\Tools\Binn (C:\Program Files\Microsoft SQL Server_90_\Tools\Binn for SQL2005) Execute sqlcmd /A /E /S (use . for local default instance and .INSTANCE for local named instance) In the CLI, execute: EXEC sp_addsrvrolemember 'DOMAIN\yourusername', 'sysadmin';GO Return to the SQL Server Configuration Manager and restore the Startup Parameters to it’s previous settings. Restart the SQL Server instance to allow users to get access to it again. Now, you should be able to login to the SQL server with sysadmin rights using your current user. This would also be a good time to actually set up a SQL Server Admins group (local or domain) to add to the sysadmin role to avoid having others to the above steps when you, yourself, are on vacation. 😉 As Raul Carcia point out in his original post, this is really a disaster recovery procedure and there’s definitely nothing sneaky about it since it leaves a lot of trails in the event logs. All in all, a Great article by Raul and all credit should go his way.

MicrosoftOpsMgr 2007

Updated: MP for System Center Configurations Manager 2007 SP2 on x64

Microsoft has released an updated MP for SCCM SP2 (v6.0.6000.2, released on 10/28/2009) for OpsMgr R2. The update basically contains support for x64 that was missing in the previous release. The Configuration Manager 2007 SP2 Management Pack adds support for monitoring Configuration Manager 2007 SP2 in a 64-bit environment with Operations Manager 2007 R2 or Operations Manager 2007 SP1 with hotfix (KB971541) installed. This enables the Configuration Manager 2007 SP2 Management Pack to work with either the 32-bit or the 64-bit Operations Manager 2007 agent. Except for the 64-bit support, the other features and guidance for Configuration Manager 2007 Management Packs remain intact. Emphasis is mine. Read more and download here: http://www.microsoft.com/downloads/details.aspx?FamilyID=a8443173-46c2-4581-b3b8-ce67160f627b

MicrosoftSQL Server

Installing SQL Reporting Services 2005 on Windows 2008 x64

Let’s say you have followed this guide: http://support.microsoft.com/kb/938245/ Still not working? The one thing I forgot, or rather did not find in any of the guides, was to change the website application pool to “Classic .NET AppPool”. It is actually noted in KB938245 but only after the installation, during the configuration. For some reason I have not been able to install Reporting Services 2005 on Windows 2008 without changing this prior to the installation. Maybe I am doing it wrong but this seems to be working all right for me.