Repository navigation
Expand file tree
/
Copy path00_RunAllScriptsSecurityOnly.ps1
More file actions
127 lines (96 loc) · 3.19 KB
/
Copy path00_RunAllScriptsSecurityOnly.ps1
File metadata and controls
127 lines (96 loc) · 3.19 KB
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
<#
.SYNOPSIS
Runs Security-based ps1 scripts for the target server
.DESCRIPTION
Runs Security-based ps1 scripts for the target server
.EXAMPLE
00_RunAllScriptsSecurityOnly.ps1 localhost
.EXAMPLE
00_RunAllScriptsSecurityOnly.ps1 server01 sa password
.Inputs
ServerName, [SQLUser], [SQLPassword]
.Outputs
.NOTES
.LINK
http://github.com/gwalkey
#>
Param(
[string]$SQLInstance="localhost",
[string]$myuser,
[string]$mypass
)
cls
[string]$BaseFolder = (Get-Item -Path ".\" -Verbose).FullName
# Load SMO Assemblies
Import-Module ".\LoadSQLSmo.psm1"
LoadSQLSMO
# Server connection check
try
{
$old_ErrorActionPreference = $ErrorActionPreference
$ErrorActionPreference = 'SilentlyContinue'
if ($mypass.Length -ge 1 -and $myuser.Length -ge 1)
{
Write-Output "Testing SQL Auth"
# .NET Method
# Open connection and Execute sql against server
$DataSet = New-Object System.Data.DataSet
$SQLConnectionString = "Data Source=$SQLInstance;User ID=$myuser;Password=$mypass;"
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $SQLConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select serverproperty('productversion')"
$SqlCmd.Connection = $Connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
# Insert results into Dataset table
$SqlAdapter.Fill($DataSet) | out-null
# Close connection to sql server
$Connection.Close()
$results = $DataSet.Tables[0].Rows[0]
$serverauth="sql"
}
else
{
Write-Output "Testing Windows Auth"
# .NET Method
# Open connection and Execute sql against server using Windows Auth
$DataSet = New-Object System.Data.DataSet
$SQLConnectionString = "Data Source=$SQLInstance;Integrated Security=SSPI;"
$Connection = New-Object System.Data.SqlClient.SqlConnection
$Connection.ConnectionString = $SQLConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select serverproperty('productversion')"
$SqlCmd.Connection = $Connection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
# Insert results into Dataset table
$SqlAdapter.Fill($DataSet) | out-null
# Close connection to sql server
$Connection.Close()
$results = $DataSet.Tables[0].Rows[0]
$serverauth = "win"
}
if($results -ne $null)
{
Write-Output ("SQL Version: {0}" -f $results.Column1)
}
# Reset default PS error handler
$ErrorActionPreference = $old_ErrorActionPreference
}
catch
{
Write-Host -f red "$SQLInstance appears offline - Try Windows Authorization."
Set-Location $BaseFolder
exit
}
set-location "$BaseFolder"
& .\01_Server_Logins.ps1 $SQLInstance $myuser $mypass
& .\01_Server_Credentials.ps1 $SQLInstance $myuser $mypass
& .\01_Server_Roles.ps1 $SQLInstance $myuser $mypass
& .\02_Linked_Servers.ps1 $SQLInstance $myuser $mypass
& .\07_Service_Creds.ps1 $SQLInstance $myuser $mypass
& .\12_Security_Audit.ps1 $SQLInstance $myuser $mypass
& .\13_PKI.ps1 $SQLInstance $myuser $mypass
set-location "$BaseFolder"
exit