Powershell: “Failed To Connect To Server SERVERNAME” Remote SQL Server 2005 using SMO

That title is a ripper isn’t it ?
It reminds me of this limerick

There once was a young man from Japan
Whose limericks never did scan.
When told this was so,
He said, “Yes, I know.
I think it’s because I try and get as many words into the last line as I possibly can.”

So I was magnificently running the following Powershell fragment:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)
$serverInstance = New-Object(‘Microsoft.SqlServer.Management.SMO.Server’) $MyRemoreDbServer
$MyDatabase = $serverInstance.Databases | where {$_.Name -eq $DbVariableName}

…when one day it doesn’t work, throwing up the luminous red error “Failure To Connect To Server MyDatabase”. I was shocked. Four hours later I was still shocked. It worked on a local connection but not on a remote connection.

Then, another freaking genius of a colleague (different to the last one) came in and said, “Check your Windows Firewall settings”, to which I said “How”, and he showed me.

Which is just as well because Windows Firewall was set to ON and when we set it to OFF my code fragment started working again. Huzzah!

This is the page which had the magic answer on it. I read it but only understood about a quarter of it. I’m sure you will do better. Its called “SQL Server 2005 Remote Connectivity Issue Troubleshoot” and its on a blog called SQL Protocols maintained by the Elders of Zion, Microsoft SQL Server protocols team. Fans of Aztec-Mayan Glyphs will find this page relatively easy to decipher. The repeated references to Windows Firewall didn’t mean anything to me until now.

What you specifically need to do is

1) Enable “Fire and Printer Sharing” in Firewall exception list in Windows Firewall on your remote database server.

2) Add TCP port or sqlservr.exe to Firewall exception list, either add “..\Binn\sqlsevr.exe” or add port.

OR Just turn the Windows Firewall off. If you dare.

I can haz MCSA ?

Advertisements

Tags: , , , ,

One Response to “Powershell: “Failed To Connect To Server SERVERNAME” Remote SQL Server 2005 using SMO”

  1. Coupon Says:

    dream…

    […]Powershell: “Failed To Connect To Server SERVERNAME” Remote SQL Server 2005 using SMO « Bite The Wax Tadpole[…]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: