internal/Connect-SqlServer.ps1
|
Function Connect-SqlServer { <# .SYNOPSIS Internal function that creates SMO server object. Input can be text or SMO.Server. #> [CmdletBinding()] param ( [Parameter(Mandatory = $true)] [object[]]$SqlServer, [System.Management.Automation.PSCredential]$SqlCredential, [switch]$ParameterConnection, [switch]$RegularUser, [string]$ApplicationName = "dbatools PowerShell module - dbatools.io" ) $SqlServer = $SqlServer[0] if ($SqlServer.GetType() -eq [Microsoft.SqlServer.Management.Smo.Server]) { if ($ParameterConnection) { $paramserver = New-Object Microsoft.SqlServer.Management.Smo.Server $paramserver.ConnectionContext.ApplicationName = $ApplicationName $paramserver.ConnectionContext.ConnectionString = $SqlServer.ConnectionContext.ConnectionString if ($SqlCredential.username -ne $null) { $username = ($SqlCredential.username).TrimStart("\") if ($username -like "*\*") { $username = $username.Split("\")[1] $authtype = "Windows Authentication with Credential" $paramserver.ConnectionContext.LoginSecure = $true $paramserver.ConnectionContext.ConnectAsUser = $true $paramserver.ConnectionContext.ConnectAsUserName = $username $paramserver.ConnectionContext.ConnectAsUserPassword = ($SqlCredential).GetNetworkCredential().Password } else { $authtype = "SQL Authentication" $paramserver.ConnectionContext.LoginSecure = $false $paramserver.ConnectionContext.set_Login($username) $paramserver.ConnectionContext.set_SecurePassword($SqlCredential.Password) } } $paramserver.ConnectionContext.Connect() return $paramserver } if ($SqlServer.ConnectionContext.IsOpen -eq $false) { $SqlServer.ConnectionContext.Connect() } return $SqlServer } # This seems a little complex but is required because some connections do TCP,sqlserver [regex]$portdetection = ":\d{1,5}$" if ($sqlserver.ToString().LastIndexOf(":") -ne -1) { $portnumber = $sqlserver.ToString().substring($sqlserver.ToString().LastIndexOf(":")) if ($portnumber -match $portdetection) { $replacedportseparator = $portnumber -replace ":", "," $sqlserver = $sqlserver -replace $portnumber, $replacedportseparator } } if ($null -ne $SqlServer.Name) { $SqlServer = $SqlServer.Name } $server = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer $server.ConnectionContext.ApplicationName = $ApplicationName <# Just realized this will not work because it's SMO ;) We will return to if this is still needed and how to handle it in 1.0. if ($server.Configuration.SmoAndDmoXPsEnabled.RunValue -eq 0) { Write-Error "Accessing this server via SQL Management Objects (SMO) or Distributed Management Objects (DMO) is currently not permitted. Enable the option 'SMO and DMO XPs' on your instance using sp_configure to continue. Note that this will require 'Show Advanced Options' to be enabled using sp_configure as well." break } #> try { if ($SqlCredential.username -ne $null) { $username = ($SqlCredential.username).TrimStart("\") if ($username -like "*\*") { $username = $username.Split("\")[1] $authtype = "Windows Authentication with Credential" $server.ConnectionContext.LoginSecure = $true $server.ConnectionContext.ConnectAsUser = $true $server.ConnectionContext.ConnectAsUserName = $username $server.ConnectionContext.ConnectAsUserPassword = ($SqlCredential).GetNetworkCredential().Password } else { $authtype = "SQL Authentication" $server.ConnectionContext.LoginSecure = $false $server.ConnectionContext.set_Login($username) $server.ConnectionContext.set_SecurePassword($SqlCredential.Password) } } } catch { } try { if ($ParameterConnection) { $server.ConnectionContext.ConnectTimeout = 7 } $server.ConnectionContext.Connect() } catch { $message = $_.Exception.InnerException.InnerException $message = $message.ToString() $message = ($message -Split '-->')[0] $message = ($message -Split 'at System.Data.SqlClient')[0] $message = ($message -Split 'at System.Data.ProviderBase')[0] throw "Can't connect to $sqlserver`: $message " } if ($RegularUser -eq $false) { if ($server.ConnectionContext.FixedServerRoles -notmatch "SysAdmin") { throw "Not a sysadmin on $SqlServer. Quitting." } } if ($ParameterConnection -eq $false) { $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Trigger], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Rule], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Schema], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.SqlAssembly], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Table], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.View], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.StoredProcedure], 'IsSystemObject') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.UserDefinedFunction], 'IsSystemObject') if ($server.VersionMajor -eq 8) { # 2000 $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], 'ReplicationOptions', 'Collation', 'CompatibilityLevel', 'CreateDate', 'ID', 'IsAccessible', 'IsFullTextEnabled', 'IsUpdateable', 'LastBackupDate', 'LastDifferentialBackupDate', 'LastLogBackupDate', 'Name', 'Owner', 'PrimaryFilePath', 'ReadOnly', 'RecoveryModel', 'Status', 'Version') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], 'CreateDate', 'DateLastModified', 'DefaultDatabase', 'DenyWindowsLogin', 'IsSystemObject', 'Language', 'LanguageAlias', 'LoginType', 'Name', 'Sid', 'WindowsLoginAccessType') } elseif ($server.VersionMajor -eq 9 -or $server.VersionMajor -eq 10) { # 2005 and 2008 $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], 'ReplicationOptions', 'BrokerEnabled', 'Collation', 'CompatibilityLevel', 'CreateDate', 'ID', 'IsAccessible', 'IsFullTextEnabled', 'IsMirroringEnabled', 'IsUpdateable', 'LastBackupDate', 'LastDifferentialBackupDate', 'LastLogBackupDate', 'Name', 'Owner', 'PrimaryFilePath', 'ReadOnly', 'RecoveryModel', 'Status', 'Trustworthy', 'Version') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], 'AsymmetricKey', 'Certificate', 'CreateDate', 'Credential', 'DateLastModified', 'DefaultDatabase', 'DenyWindowsLogin', 'ID', 'IsDisabled', 'IsLocked', 'IsPasswordExpired', 'IsSystemObject', 'Language', 'LanguageAlias', 'LoginType', 'MustChangePassword', 'Name', 'PasswordExpirationEnabled', 'PasswordPolicyEnforced', 'Sid', 'WindowsLoginAccessType') } else { # 2012 and above $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], 'ReplicationOptions', 'ActiveConnections', 'AvailabilityDatabaseSynchronizationState', 'AvailabilityGroupName', 'BrokerEnabled', 'Collation', 'CompatibilityLevel', 'ContainmentType', 'CreateDate', 'ID', 'IsAccessible', 'IsFullTextEnabled', 'IsMirroringEnabled', 'IsUpdateable', 'LastBackupDate', 'LastDifferentialBackupDate', 'LastLogBackupDate', 'Name', 'Owner', 'PrimaryFilePath', 'ReadOnly', 'RecoveryModel', 'Status', 'Trustworthy', 'Version') $server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Login], 'AsymmetricKey', 'Certificate', 'CreateDate', 'Credential', 'DateLastModified', 'DefaultDatabase', 'DenyWindowsLogin', 'ID', 'IsDisabled', 'IsLocked', 'IsPasswordExpired', 'IsSystemObject', 'Language', 'LanguageAlias', 'LoginType', 'MustChangePassword', 'Name', 'PasswordExpirationEnabled', 'PasswordHashAlgorithm', 'PasswordPolicyEnforced', 'Sid', 'WindowsLoginAccessType') } } return $server } |