Microsoft SQL Server Express – Create sa User Account without sa Account Credentials

I had some trouble coming up with a sensible title for this one, so sorry if it doesn’t make any sense.  Anywho, I had a awkward issue when I was migrating a database to a new server today…I didn’t have any administrative access over the SQL Server Express (2008) installation!  Windows Authentication would not give me administrative access even though it should have (yes the server was configured to accept Windows Authentication and SQL Authentication, and I was a Domain Admin/Local Admin), and the existing sa password on the server was a mystery that was impossible to solve.  So I took to the interwebs and came across this post on stackoverflow.  The comment by “40-Love” in particular held the solution for me, which I will detail below:

  • Close all SQL Server apps
  • Open services and stop all SQL services
  • Open the SQL Server service properties
  • Enter “-m” in the start parameters box
  • Open Command Prompt as administrator and do the following

  • Stop the SQL Server service
  • Remove the “-m” you added to the start parameters earlier
  • Start the SQL Server service

Wah-lah!  I now had an account on the SQL Server with sa access to do my business with.

WSUS Connection Error for Computer Groups – Unicode Character

When using the WSUS console, from time to time I will get the following error when selecting a particular Computer Group:

When copying the error out to notepad and reviewing, the key indicator for this issue is something along the lines of:

System.Xml.XmlException — ‘’, hexadecimal value 0x16, is an invalid character.

Upon seeing this I know that a unicode character in the database is at fault, so to the database I go.  If you have your database deployed using WID, see this post for connecting to it.  If you are working with a SQL database, make sure you are running the following queries against the “SUSDB” database.

Run the query found in the image below and copy/past the results to a notepad document.

Look for the unicode character(s) at fault.  It should look like what you see below:

Take note of the row number and run the following query where 3023 is the row with your unicode character:

Typically you will see the unicode character under the ComputerModel column and the BiosVersion Column will be Unknown.  Run the following query to clear the unicode character where 3023 is the row with your unicode character:

Next run the following query to get information about that computer object (hostname, IP, etc.) where 3023 is the row with your unicode character:

Go update the BIOS on that machine to the latest version to prevent the issue from reoccurring.

Configuring Barracuda Backup Agent to use Microsoft VDI to Perform Incremental SQL Backups

As I may have mentioned, my company is currently testing out a Barracuda Backup Appliance and I have been going through the ropes of getting everything operating the way it needs to be for our environment.  One issue I came across along the way was that my SQL machines were not having their transaction logs truncated when I ran a “Microsoft SQL” backup.  Come to find out that the Barracuda Backup Agent is by default configured to use Microsoft VSS, which has it make Full and Differential backups rather than Full and Incremental backups created when using Microsoft VDI.  For a good explanation of the difference between backups, read this MSDN explanation.  The following is a good definition of Microsoft VDI provided in the comments here:

“SQL Server provides an API called Virtual Backup Device Interface (VDI) that enables independent software vendors to integrate SQL Server into their products for providing support for backup and restore operations. These APIs are engineered to provide maximum reliability and performance, and support the full range of SQL Server backup and restore functionality, including the full range of hot and snapshot backup capabilities.”

Mind you the differential is perfectly fine if all of your databases are in Simple recovery mode.  If your databases are in Full recovery mode, incremental backups are the way to go from what I understand.  This is also Barracuda’s recommendation (found here).  So, to fix this issue we have to manually change the configuration file for the Barracuda Backup Agent on the SQL server itself (Barracuda KB Article).

Start by stopping the Barracuda Backup Agent service.  Then navigate to C:\Program Files\Barracuda\Barracuda Backup Agent\config and edit the config.ini file.  Under the configuration section add the following (add a configuration section if it is not present):

[configuration]

vssSqlVersion=20

Save and close the file and go back to services and start the Barracuda Backup Agent service.  Now go to your Cloud Control and navigate to your backup schedule.  Under “Items to Backup” expand your SQL server and you should now see Microsoft SQL Server and Microsoft SQL options.  Clear the Microsoft SQL option and select the Microsoft SQL Server and save your settings.  You should now be good to go!  The Microsoft SQL option will remove itself from the Items to Backup options moving forward as seen below.

Microsoft SQL: Query Last Time Transaction Log was Truncated

Use the following Query:

It will output the following (where the backup_finish_date column is the last time the logs have been truncated):

Source

Connect to WSUS Database

Had an instance the other day where I needed to connect to the database for WSUS on Server 2012R2, but I actually had no idea how to do it!  Luckily, we have the internet at our disposal.

First things first (although quite obvious) we are going to need to make sure that SQL Server Management Studio is installed on the WSUS box, available to download from Microsoft for free from here.  Once you have that installed, open it up as administrator and connect to the following using Windows Authentication:

np:\\.\pipe\MICROSOFT##WID\tsql\query

Select connect and wa-lah you should get connected assuming you are an administrator.  You will find a database named SUSDB, this is your WSUS database.