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 ?

Powershell: Delete Registry Key On Remote Server

Friends Of The Bitten Tadpole:

There wasn’t much in the way of examples on the greater Google for this one. One forum entry even said it was too difficult and suggested using regedit.exe, but in fact it is very easy. Look at the following example:

# Delete the Registry Key given in $deleteKey
Function DeleteRegistryKey($deleteKey)
{
$type = [Microsoft.Win32.RegistryHive]::LocalMachine
$Hive = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $RemoteMachine)
$Key = $Hive.DeleteSubKey($deleteKey)
}

DeleteRegistryKey “Software\Microsoft\Whatever”

Too easy.

Powershell: Copy File on Remote Computer When Path Has Spaces In It

Dear Imaginary Audience,

This one depleted my life force by about 37 mins, so I am figuring it might help some of you others, even though it is simple to the point of trivial.

I have a remote computer called BRUCE:
There is a file called YouLittleRipper.txt contained in the following Path:
C:\Program Files\Microsoft\Mammoth App Folder

To copy YouLittleRipper.txt from another computer NARELLE in Powershell do the following:
- Start Powershell On Narelle
$RipperPath = "\\BRUCE" + "\" + "C$" + "\"Program Files\Microsoft\Mammoth App Folder" + "\"
$Source = $RipperPath + "YouLittleRipper.txt"
$Target = $RipperPath + "OnyaGranny.txt"
copy-item "$Source" "$Target"

The file “OnyaGranny.txt” will now exist on BRUCE in ‘Mammoth App Folder”.

The secret ingredient is to put the quotation marks around the whole path after it has been constructed in the variable $Source.

You owe me 37 mins. Give us it.

SQL Server 2005 Installation From Command Line: How To Avoid General Carnage

Tadpolers (or should that be ‘Biters’):

Testing on my current project require the installation of various products into Virtual Machines via Powershell scripts. Hence I am becoming more familiar with command-line execution of MSIs and SetUp.exes of various breeds.

Here’s a few issues I encountered before, during and after command-line installation of SQL Server 2005 and links to the solutions.

1. SQL Server 2005 Management Studio Not Installed
This comes about when you install Enterprise Edition on the same machine as SQL Express. Uninstall both instances (MSSQLSERVER and SQLEXPRESS plus any others you may have there) and reinstall Enterprise Edition. Management Console will be there afterwards. Promise.

Thanks to ssjaronyx4 on this thread at Proprofs

If you don’t have Managment Console present after a fresh install (i.e. SQLExpress was not there to begin with) then do a “Modify Installation’ and choose ONLY ‘Management Tools’ and ‘Business Intelligence Development Studio’ from Client Tools. Thanks to thef150 at Dev Newsgroups.

2. SQL Browser Service Not Running And Won’t Start
Quite possibly SQL Browser Service is disabled. Enable it via Surface Area Configuration Utility. Thanks to chuber on this thread at Blackthorne Discussion Groups

3. Autopeotomy During Install
My, you have had a bad time of it, haven’t you ? Unfortunately this is not fixable. Massive fail.

4. Passwords and Accounts
…such as SQLACCOUNT, SQLPASSWORD AGTACCOUNT, and AGTPASSWORD.
If possible, don’t specify them. Leave them blank and take the defaults. It’ll make your ife a bit easier…

…BUT If you are installing into a Virtual Machine, however, you must specify them.

I used Network Service i.e. account name “NT AUTHORITY\NETWORK SERVICE”, password “NT AUTHORITY\NETWORK SERVICE”.

Make sure you specify those account names in double quotes just like Haidong Ji or you will suffer like a dog.

Here’s the MSDN reference ‘How To Install SQL Server 2005 From The Command Prompt’

5. Installation Display Switches
Use /qb (Quiet with basic UI) instead of /qn (Quiet with no UI).
/qb will throw up the dialogs, but without requiring user input. This way you can see how the install is progressing and if any error messages come up you will see a nice Message Box instead of having to rummage through log files.

Access LDAP Server (Active Directory) from Powershell

It took me a long time to find a good page on how to access Active Directory from Powershell, but I finally found one.

Here it is: User Management, from Powershell.com.

What I was after was the bit on how to connect to Active Directory with different user credentials.

My Journey Of Pain in a bit more detail:

The Fully Qualified Domain Name of our LDAP Server is MyLDAPServer.place.room.net.

I found this out by downloading AD Explorer from the SysInternals Web Page
and doing:
File->Connect
Connect To: place.room.net
User: place\myaccount
Password: mypassword

When the connection comes up in AD Explorer you can see the name of the LDAP Server

Then I used the directions in the User Management web page above to connect via Powershell. Notice you need to pass the credentials to connect

$entry = new-object DirectoryServices.DirectoryEntry (“LDAP://MyLDAPServer/dc=place, dc=room, dc=net”, “place\myaccount”, “mypassword”)
$entry | get-member

A query like the one above returns exactly one AD entry.

Using the AD Explorer Search functionality you can find the DistinguishedName of any object in ActiveDirectory. You can also find the AD entry via Powershell using the DistinguishedName
e.g. For a particular SCP entry on my test machine:

$entry = new-object DirectoryServices.DirectoryEntry (“LDAP://MyLDAPServer/cn=MySCP, cn=MyTestMachine, CN=Computers, dc=place, dc=room, dc=net”, “place\myaccount”, “mypassword”)
$entry | get-member

There is also a DirectorySearcher object which can run AD searches and return collections of AD objects

The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer.

While running a MSI, the Installer tried to start a service and failed, thus aborting the install. It wrote the following message to the Windows Application Event Log.

The description for Event ID ( 0 ) in Source ( PCMConcentrator ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: The service could not determine the name of this machine. This is critical for service discovery to work, hence the service will now terminate..

The critical part of the error was in final two lines The service could not determine the name of this machine.

I chatted to Product Support for the publishers of the service and they told me that the problem was that the computer I was installing the Service on was not part of the correct Domain.

As it happens, I was installing the product on to a Virtual Machine created from a template and indeed had not specified any Domain. The VM was part of WORKGROUP. So I added the VM to the correct domain and the Service was able to start.

HTH.