xSQLServerHelper: Function Connect-SQL returns successfull even when connection fails

[johlju]

Details of the scenario you try and problem that is occurring:

When running the following

$sql = New-Object Microsoft.SqlServer.Management.Smo.Server 'sqltest\Default'

An empty object is returned.

AuditLevel                  : 
BackupDirectory             : 
BrowserServiceAccount       : 
BrowserStartMode            : 
BuildClrVersionString       : 
BuildNumber                 : 
ClusterName                 : 
ClusterQuorumState          : 
ClusterQuorumType           : 
Collation                   : 
CollationID                 : 
ComparisonStyle             : 
ComputerNamePhysicalNetBIOS : 
DefaultFile                 : 
DefaultLog                  : 
Edition                     : 
ErrorLogPath                : 
FilestreamLevel             : 
FilestreamShareName         : 
HadrManagerStatus           : 
InstallDataDirectory        : 
InstallSharedDirectory      : 
InstanceName                : 
IsCaseSensitive             : 
IsClustered                 : 
IsFullTextInstalled         : 
IsHadrEnabled               : 
IsSingleUser                : 
Language                    : 
LoginMode                   : 
MailProfile                 : 
MasterDBLogPath             : 
MasterDBPath                : 
MaxPrecision                : 
NamedPipesEnabled           : 
NetName                     : 
NumberOfLogFiles            : 
OSVersion                   : 
PerfMonMode                 : 
PhysicalMemory              : 
PhysicalMemoryUsageInKB     : 
Platform                    : 
Processors                  : 
ProcessorUsage              : 
Product                     : 
ProductLevel                : 
ResourceLastUpdateDateTime  : 
ResourceVersionString       : 
RootDirectory               : 
ServerType                  : 
ServiceAccount              : 
ServiceInstanceId           : 
ServiceName                 : 
ServiceStartMode            : 
SqlCharSet                  : 
SqlCharSetName              : 
SqlDomainGroup              : 
SqlSortOrder                : 
SqlSortOrderName            : 
Status                      : 
TapeLoadWaitTime            : 
TcpEnabled                  : 
VersionMajor                : 
VersionMinor                : 
VersionString               : 
Name                        : sqltest\Default
Version                     : 
EngineEdition               : 
ResourceVersion             : 
BuildClrVersion             : 
DefaultTextMode             : True
Configuration               : Microsoft.SqlServer.Management.Smo.Configuration
AffinityInfo                : 
ProxyAccount                : 
Mail                        : 
Databases                   : 
Endpoints                   : 
Languages                   : 
SystemMessages              : 
UserDefinedMessages         : 
Credentials                 : 
CryptographicProviders      : 
Logins                      : 
Roles                       : 
LinkedServers               : 
SystemDataTypes             : 
JobServer                   : 
ResourceGovernor            : 
ServiceMasterKey            : 
Settings                    : Microsoft.SqlServer.Management.Smo.Settings
Information                 : Microsoft.SqlServer.Management.Smo.Information
UserOptions                 : Microsoft.SqlServer.Management.Smo.UserOptions
BackupDevices               : 
FullTextService             : 
ActiveDirectory             : 
Triggers                    : 
Audits                      : 
ServerAuditSpecifications   : 
AvailabilityGroups          : 
ConnectionContext           : Data Source=sqltest\Default;Integrated Security=True;MultipleActiveResultSets=False;Application Name="SQL Management"
Events                      : Microsoft.SqlServer.Management.Smo.ServerEvents
OleDbProviderSettings       : 
Urn                         : 
Properties                  : 
UserData                    : 
State                       : Existing
IsDesignMode                : False
DomainName                  : SMO
DomainInstanceName          : 

Instead of validating using the following, and throw an error is it is true.

if (!$sql)

Validation should use

if (-not $sql.Status -eq 'Online' )

The DSC configuration that is using the resource (as detailed as possible):
n/a

Version of the Operating System, SQL Server and PowerShell the DSC Target Node is running:
n/a

Version of the DSC module you're using, or 'dev' if you're using current dev branch:
Dev

[randomnote1]

Funny thing that I've seen with the status is the property doesn't update until you actually get another property. I normally query the version to make sure I'm connected.

[johlju]

If you just use $sql in this case, it returns 'sqltest\Default' (using ToString() or something) which is whatever you tried to connect with. So you are right, must use one of the properties. Only when you use one of the properties it actually connects (each time you use a property apparently).

I thought Status -eq 'Online' was better, so it throws if the SQL server is in any other state.

[randomnote1]

I'm working on this one.