forked from RamblingCookieMonster/PowerShell
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathNew-SQLConnection.ps1
127 lines (105 loc) · 4.61 KB
/
New-SQLConnection.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
function New-SqlConnection
{
<#
.SYNOPSIS
Creates a SQLConnection to a MS SQL Server instance
.DESCRIPTION
Creates a SQLConnection to a MS SQL Server instance
.PARAMETER ServerInstance
SQL Instance to connect to. For default instances, only specify the computer name: "MyComputer". For named instances, use the format "ComputerName\InstanceName".
.PARAMETER Database
A character string specifying the name of a database.
.PARAMETER Credential
Specifies A PSCredential for SQL Server Authentication connection to an instance of the Database Engine.
If -Credential is not specified, New-SQLConnection attempts a Windows Authentication connection using the Windows account running the PowerShell session.
SECURITY NOTE: If you use the -Debug switch, the connectionstring including plain text password will be sent to the debug stream.
.PARAMETER ConnectionTimeout
Specifies the number of seconds when New-SQLConnection times out if it cannot successfully connect to an instance of the Database Engine. The timeout value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.
.OUTPUTS
System.Data.SqlClient.SQLConnection
.EXAMPLE
$Connection = New-SqlConnection -ServerInstance c-is-hyperv-1
Invoke-SqlCmd2 -SQLConnection $Connection -query $Query
.LINK
https://github.com/RamblingCookieMonster/PowerShell
.FUNCTIONALITY
SQL
#>
[cmdletbinding()]
[OutputType([System.Data.SqlClient.SQLConnection])]
param(
[Parameter( Position=0,
Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false,
HelpMessage='SQL Server Instance required...' )]
[Alias( 'Instance', 'Instances', 'ComputerName', 'Server', 'Servers' )]
[ValidateNotNullOrEmpty()]
[string[]]
$ServerInstance,
[Parameter( Position=1,
Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[string]
$Database,
[Parameter( Position=2,
Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false )]
[System.Management.Automation.PSCredential]
$Credential,
[Parameter( Position=3,
Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false )]
[Int32]
$ConnectionTimeout=15,
[Parameter( Position=4,
Mandatory=$false,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false )]
[bool]
$Open = $True
)
Process
{
foreach($SQLInstance in $ServerInstance)
{
Write-Verbose "Querying ServerInstance '$SQLInstance'"
if ($Credential)
{
$ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $SQLInstance,$Database,$Credential.UserName,$Credential.GetNetworkCredential().Password,$ConnectionTimeout
}
else
{
$ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $SQLInstance,$Database,$ConnectionTimeout
}
$conn = New-Object System.Data.SqlClient.SQLConnection
$conn.ConnectionString = $ConnectionString
Write-Debug "ConnectionString $ConnectionString"
#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
if ($PSBoundParameters.Verbose)
{
$conn.FireInfoMessageEventOnUserErrors=$true
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] { Write-Verbose "$($_)" }
$conn.add_InfoMessage($handler)
}
if($Open)
{
Try
{
$conn.Open()
}
Catch
{
Write-Error $_
continue
}
}
write-Verbose "Created SQLConnection:`n$($Conn | Out-String)"
$Conn
}
}
}