Login Failed connecting to SharePoint WID using SQL Server Express when login is perfectly valid, on Windows Server 2008 r2 (damn you r2!)

Ok this one is kinda lame… but alas, the fix is simple.

My ultimate goal was to perform a backup of a SharePoint WSS database on a Windows Server 2008 R2 server, with the intent of performing a database restore, to a new SharePoint 2010 Foundation Farm, so that I could perform a Database Attach Upgrade.

However… after installing SQL Server 2005 Management Studio Express on the WSS Server (this went okay), I could not login to the local SQL Server WID (Windows Internal Database) instance despite using the correct account/credentials. I used the correct method to connect to the WID of course (which, for your reading benefit/reminder is: \.pipemssql$microsoft##sseesqlquery). However, no matter what I did, I would get the following returned to me:

Figure 1 : Dude, you should totally let me in… Login Failed … really?

Alas, as you may have already become accustomed to, R2 and its security (Features?) are at play here. Despite the account HAVING access, I didn’t RUN SQL Server Studio Express “as Administrator”.

Alas, just do that, and your account will let you through just fine… Badabing!