Fixing Office Web Apps 2013 Server Error

After a disaster at work, involving our SAN shitting itself and falling over, we invoked our DR procedures and got services back online. While testing the functionality of our SharePoint 2013 farm, which had a few little issues that were resolved in an hour, I noticed that Office Web Apps 2013 were not working, Giving an nondescript “Server Error”.

webapps-errorI logged onto the Web Apps Server box and ran the Get-OfficeWebAppsFarm powershell command an got the following.

webapps-powershell-error-1

 

Notice the WARNING: It does not appear that this machine is part of an Office Web Apps Server farm message at the top? The Server was also missing from the “Machines” property of the farm.

Since this was a single Office Web Apps server and not an actual farm, the easiest solution was to recreate the farm with the following command.

$farm = Get-OfficeWebAppsFarm
New-OfficeWebAppsFarm -InternalURL $farm.InternalURL -ExternalURL $farm.ExternalURL -CertificateName $farm.CertificateName -EditingEnabled

This command grabs all necessary properties from the original farm. It also assumes you have a volume license for office and wish to enable editing. If you don’t remove -EditingEnabled from the end or suffer Microsoft’s wrath.

After I executed this command the machine was listed in the farm properties and Web Apps were working once again.

 

webapps-powershell-fixed-1

 

Attach SQL Server database with missing ldf file

This morning I was faced with an interesting issue. Somehow, I’m not entirely sure how, all the rows from one table in a database for a bespoke application I developed were missing. After spending an hour trawling through the logs of both SQL Server and the application’s logs I couldn’t figure out why they were missing. Luckily the application isn’t mission critical!

So, onto out backup server to restore the database from last nights backup. I performed a file level restore of the .mdf file but wait a minute, where the f**k is the .ldf file? Missing from the backup 😦

To restore the database as a single file copy the mdf file to the desired location and use the following query.

USE master
GO
CREATE DATABASE [TestDatabase] ON
(FILENAME = N’C:TempFolderSP_C_Intranet-Restored.mdf’)
FOR ATTACH_REBUILD_LOG
GO

Oh dear. it looks like the transaction large wasn’t committed before the backup was taken.

The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘TestDatabase’. CREATE DATABASE is aborted.

There is a way around this issue but it may result in data loss so it is not recommended for a live environment. We can force SQL Server to rebuild the log using the following Query.

USE master
GO
CREATE DATABASE [TestDatabase] ON
(FILENAME = N’C:TempFolderSP_C_Intranet-Restored.mdf’)
FOR ATTACH_FORCE_REBUILD_LOG
GO

And that’s it. Now go and make sure your backups are running properly instead of pissing about.

1: The Hardware and Topology

I know it’s been a while since I initially announced this project, but unfortunately this is a “side project” and my day job needs to take priority.

First of all I thought i would show you the hardware that we will be using for this project, along with the topology that we intend to deploy in order to provide our tenants with a second to none service.

First of all lets talk about the topology. I have created a diagram to depict this, available here, but I will attempt to explain the theory behind each section.

First of all we get our Internet connection from our ISP’s data centre via two disparate gigabit private fibre circuits that are fed from two separate BT exchanges. These circuits terminate into two separate “presentation” switches on our site to create a resilient Internet connection.

It is from these switches that our corporate firewalls are fed, and also where we will be connecting our pfSense box. We currently have 32 public IP’s that can be bound to by any device plugged into these switches, providing it passes the security checks when it is plugged in of course.

On the tenant side of the pfSense box we will link to a switch (or two). To prevent each tenant from seeing each other , and to prevent to them from binding to each others external IP’s we intend to use the PPPoE Server feature of pfSense for authentication. This will also allow us to track how much bandwidth each tenant uses.

Here are a few pics of the server we will be running pfSense from.

IBM x3550

To the rear of the server you can see six gigabit LAN interfaces (the seventh is an on board management port and cannot be used for networking). Just to the right there are two power supplies.

For testing we are using a Cisco C2960PD-8TT-L switch which is an eight port 10/100 switch with one Gigabit uplink port. The switch is powered via POE on the uplink port and is completely silent.

We are still unsure of the configuration we will be using on our pfSense box at the moment but over the next few weeks we will be testing various setup, each of which will be documented by me on this blog.

If you have any suggestions on this project then please share them in the comments!

 

Recovering a “Suspect” Database in SQL Server 2008

Today I broke my SharePoint development environment by shutting down the SQL Server forcibly and corrupting my transaction logs, making the databases show in Management Studio show as “Suspect”. Thankfully this was a development environment and not our live environment! After a lot of googling and perseverance, I managed to cobble together a solution to get my databases back online and continue to work.

The best way to recover from this issue is to restore from a Backup. Stupidly I didn’t have a good backup available for the databases in my Dev environment.

I DO NOT recommend this method on a live system!!!

To recover the database, which involves rebuilding the transaction log I executed the following query in Management Studio. Obviously change the database name to reflect your own database name.

USE master
GO
ALTER DATABASE [DB_Name] SET EMERGENCY
GO
ALTER DATABASE [DB_Name] SET SINGLE_USER;
GO
DBCC CHECKDB ([DB_Name], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO
ALTER DATABASE [DB_Name] SET ONLINE;
GO
ALTER DATABASE [DB_Name] SET MULTI_USER;
GO

Again I stress, DO NOT sue this method on a live environment.

Hacking your Microsoft SQL Server

Today a friend was complaining about how he had forgotten the sa password for his SQL Server…. Oh Dear.

If you follow Microsoft’s best practices for SQL security, as my friend did, you will have also disabled the BUILT-INAdministrators login… Oh Dear Oh Dear.

Fear not, we will have you back in within minutes, just follow these instructions:

  1. Logon to Windows on the SQL server a system administrator.
  2. Open CMD and type the following command to stop the SQL service NET STOP MSSQL, Replacing MSSQL with your server instance name, for example: MSSQL$Instance.
  3. Type NET START MSSQL /m to start SQL up in Single User Mode.
  4. Open SQL Management Studio, and connect to your server using windows authentication.
  5. Open a New Query window and type the following into the query window:

    CREATE LOGIN <username>
    WITH PASSWORD='<password>';
    GO
    SP_ADDSRVROLEMEMBER '<username>', 'sysadmin'
    GO

    Example:

    CREATE LOGIN m0rph3us
    WITH PASSWORD='12345678';
    GO
    SP_ADDSRVROLEMEMBER 'm0rph3us', 'sysadmin'
    GO

  6. Click Execute. This will create the required username, with the desired password, and add that username to the sysadmin role, granting full control of the server.
  7. Close SQL Management Studio.
  8. Open CMD and type NET STOP MSSQL .
  9. Type NET START MSSQL .
  10. Open SQL Management Studio, and connect to the server using SQL Authentication, and input the username and password we created earlier.

You should now have regained full control of your SQL server, so go ahead and change the sa password.

I know these steps definately work in Microsoft SQL Server 2005, but I have not tried it on any other version of SQL. Please let me know in the comments if you try this method in another version of SQL, and the outcome and I will updater this post.