powershell

ODBC Driver Update

Posted by robd on April 23, 2020
powershell, SQL / No Comments

Hello,

As many of you may know the latest round updates have disabled TLS 1.0 and TLS 1.2

For us that meant either enabling TLS everywhere or using newer methods. Below is a URL to enable:

https://blogs.windows.com/msedgedev/2020/03/31/tls-1-0-tls-1-1-schedule-update-edge-ie11/

A much better option is to install Microsoft® ODBC Driver 13.1 for SQL Server on the clients:

https://www.microsoft.com/en-us/download/details.aspx?id=53339

Then update a whole bunch of ODBC system DSNs.

Thats great but some of our Citrix servers have a lot of system DSNs and I didnt much fancy doing them one by one so here’s some PowerShell to do it for you:

$DsnArray = Get-OdbcDsn -DriverName 'SQL Server'
ForEach ($Dsn in $DsnArray) {
     Remove-OdbcDsn -InputObject $Dsn 
     Add-OdbcDsn -Name $Dsn.Name -DsnType $Dsn.DsnType -Platform $Dsn.Platform -DriverName 'ODBC Driver 13 for SQL Server' -SetPropertyValue $Dsn.PropertyValue
}

1

2

3

4

5

$DsnArray = Get-OdbcDsn -DriverName 'SQL Server'

ForEach ($Dsn in $DsnArray) {

Remove-OdbcDsn -InputObject $Dsn

Add-OdbcDsn -Name $Dsn.Name -DsnType $Dsn.DsnType -Platform $Dsn.Platform -DriverName 'ODBC Driver 13 for SQL Server' -SetPropertyValue $Dsn.PropertyValue

}

Boom.

Tags: Citrix, Driver 13.1, ODBC, SQL

Check and change DNS on all the servers in the domain

Posted by robd on February 19, 2020
DNS, powershell / No Comments

Here’s a brilliant PowerShell scipt to check what the DNS servers are set as accross the domain then change it:

$allservers = @()
$domainpcs = Get-ADComputer -Filter * -Properties operatingsystem | where {$_.operatingsystem -like "*Server*"} | sort name
foreach ($pc in $domainpcs)
{
    if (Test-Connection $pc.DNSHostName -Quiet)
    {
        $thisserver = $null
        $DNSsettings = $null


        $DNSsettings = Get-DnsClientServerAddress -CimSession $pc.DNSHostName | where {($_.AddressFamily -eq 2) -and ($_.InterfaceAlias -notlike "Loopback*") -and ($_.InterfaceAlias -notlike "isatap*") -and ($_.ServerAddresses -ne $null)} | select @{n='DNSServers';e={$_ | select -ExpandProperty serveraddresses}},InterfaceIndex
        $thisserver =  New-Object psobject -Property @{
                       Servername = $pc.Name
                       interfaceindex = $DNSsettings.interfaceindex[0]
                       DNSsetting1 = $DNSsettings.dnsservers[0]
                       DNSsetting2 = $DNSsettings.dnsservers[1]
                       DNSsetting3 = $DNSsettings.dnsservers[2]
        }


        $allservers += $thisserver
        $thisserver
    }
    
}




foreach ($server in $allservers)

{

        $newdns1 = $null
        $newdns2 = $null
        $newdns3 = $null

        $needchange = $false

        write-host $server.Servername -ForegroundColor Green

       $newdns1 = $server.dnssetting1
        $newdns2 = $server.dnssetting2
        $newdns3 = $server.dnssetting3

       write-host $newdns1 -ForegroundColor Red
       write-host $newdns2 -ForegroundColor Red
       write-host $newdns3 -ForegroundColor Red


    

       Switch ($server.DNSsetting1)
       {
           "10.5.1.4" {$newdns1 = "8.8.8.8";$needchange =$true}
           "10.5.1.5" {$newdns1 = "8.8.4.4";$needchange =$true}
           "10.5.1.6" {$newdns1 = "1.1.1.1";$needchange =$true}
       }

       Switch ($server.dnssetting2)
       {
           "10.5.1.4" {$newdns2 = "8.8.8.8";$needchange =$true}
           "10.5.1.5" {$newdns2 = "8.8.4.4";$needchange =$true}
           "10.5.1.6" {$newdns2 = "1.1.1.1";$needchange =$true}
       }

       Switch ($server.dnssetting3)
       {
           "10.5.1.4" {$newdns3 = "8.8.8.8";$needchange =$true}
           "10.5.1.5" {$newdns3 = "8.8.4.4";$needchange =$true}
           "10.5.1.6" {$newdns3 = "1.1.1.1";$needchange =$true}
       }


       write-host $newdns1 -ForegroundColor Cyan
       write-host $newdns2 -ForegroundColor Cyan
       write-host $newdns3 -ForegroundColor Cyan

       $needchange
       if ($needchange)
       {      
           Set-DnsClientServerAddress -cimsession $server.servername -InterfaceIndex $server.interfaceindex -ServerAddresses ($newdns1,$newdns2,$newdns3)  -whatif
       }
}

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

$allservers = @()

$domainpcs = Get-ADComputer -Filter * -Properties operatingsystem | where {$_.operatingsystem -like "*Server*"} | sort name

foreach ($pc in $domainpcs)

{

if (Test-Connection $pc.DNSHostName -Quiet)

{

$thisserver = $null

$DNSsettings = $null

$DNSsettings = Get-DnsClientServerAddress -CimSession $pc.DNSHostName | where {($_.AddressFamily -eq 2) -and ($_.InterfaceAlias -notlike "Loopback*") -and ($_.InterfaceAlias -notlike "isatap*") -and ($_.ServerAddresses -ne $null)} | select @{n='DNSServers';e={$_ | select -ExpandProperty serveraddresses}},InterfaceIndex

$thisserver = New-Object psobject -Property @{

Servername = $pc.Name

interfaceindex = $DNSsettings.interfaceindex[0]

DNSsetting1 = $DNSsettings.dnsservers[0]

DNSsetting2 = $DNSsettings.dnsservers[1]

DNSsetting3 = $DNSsettings.dnsservers[2]

}

$allservers += $thisserver

$thisserver

}

foreach ($server in $allservers)

{

$newdns1 = $null

$newdns2 = $null

$newdns3 = $null

$needchange = $false

write-host $server.Servername -ForegroundColor Green

$newdns1 = $server.dnssetting1

$newdns2 = $server.dnssetting2

$newdns3 = $server.dnssetting3

write-host $newdns1 -ForegroundColor Red

write-host $newdns2 -ForegroundColor Red

write-host $newdns3 -ForegroundColor Red

Switch ($server.DNSsetting1)

{

"10.5.1.4" {$newdns1 = "8.8.8.8";$needchange =$true}

"10.5.1.5" {$newdns1 = "8.8.4.4";$needchange =$true}

"10.5.1.6" {$newdns1 = "1.1.1.1";$needchange =$true}

}

Switch ($server.dnssetting2)

{

"10.5.1.4" {$newdns2 = "8.8.8.8";$needchange =$true}

"10.5.1.5" {$newdns2 = "8.8.4.4";$needchange =$true}

"10.5.1.6" {$newdns2 = "1.1.1.1";$needchange =$true}

}

Switch ($server.dnssetting3)

{

"10.5.1.4" {$newdns3 = "8.8.8.8";$needchange =$true}

"10.5.1.5" {$newdns3 = "8.8.4.4";$needchange =$true}

"10.5.1.6" {$newdns3 = "1.1.1.1";$needchange =$true}

}

write-host $newdns1 -ForegroundColor Cyan

write-host $newdns2 -ForegroundColor Cyan

write-host $newdns3 -ForegroundColor Cyan

$needchange

if ($needchange)

{

Set-DnsClientServerAddress -cimsession $server.servername -InterfaceIndex $server.interfaceindex -ServerAddresses ($newdns1,$newdns2,$newdns3) -whatif

}

Tags: DNS, PowerShell

Check DNS accross all your Domain Controllers

Posted by robd on November 22, 2019
Active Directory, DNS, powershell / 1 Comment

Handy bit of PowerShell my bestest ever friend wrote to check DNS accross domain controllers:

#do dns servers agree for dns
$results = $null
$results = @()
$DNSServers = Get-ADDomainController -Filter * 
$hostname = Read-Host('enter dns record to check')
foreach ($DNSServer in $DNSServers)
{
    $dnsrecord = Resolve-DnsName -Name $hostname -Server $DNSServer.HostName -Type A
    $result = New-Object psobject -Property @{
    dnsserver = $DNSServer.Name
    hostname = $dnsrecord.name
    IPAddress = $dnsrecord.ipaddress
    }
    $results += $result
}

$results | select hostname,ipaddress,dnsserver | sort ipaddress

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

#do dns servers agree for dns

$results = $null

$results = @()

$DNSServers = Get-ADDomainController -Filter *

$hostname = Read-Host('enter dns record to check')

foreach ($DNSServer in $DNSServers)

{

$dnsrecord = Resolve-DnsName -Name $hostname -Server $DNSServer.HostName -Type A

$result = New-Object psobject -Property @{

dnsserver = $DNSServer.Name

hostname = $dnsrecord.name

IPAddress = $dnsrecord.ipaddress

}

$results += $result

}

$results | select hostname,ipaddress,dnsserver | sort ipaddress

Tags: DNS, PowerShell

Run PowerShell through a Proxy

Posted by robd on August 27, 2019
powershell, Proxy / No Comments

At work I’m behind a proxy which caused me havock when trying to install modules into PowerShell.

That was until I found this amazing script to tell PowerShell to use a proxy.

First open your PowerShell profile by either doing this in PowerShell:

notepad $PROFILE

1	notepad $PROFILE

Or open “Microsoft.PowerShell_profile.ps1” and “Microsoft.PowerShellISE_profile.ps1” in Explorer with notepad:

C:\Users\%Username%\My Documents\WindowsPowerShell

1	C:\Users\%Username%\My Documents\WindowsPowerShell

Once open, paste in the following, editing the proxy address and port.

[system.net.webrequest]::defaultwebproxy = new-object system.net.webproxy('http://ProxyName:ProxyPort')

[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials

[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true

1

2

3

4

5

[system.net.webrequest]::defaultwebproxy = new-object system.net.webproxy('http://ProxyName:ProxyPort')

[system.net.webrequest]::defaultwebproxy.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials

[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true

This will use your current credentials you’re logged in with to pass the commands to the proxy server.

Test with a

update-help

1	update-help

Tags: Power Shell, PowerShell, Proxy

Set-ACL – Se file and folder permissions

Posted by robd on May 12, 2019
powershell, Server / No Comments

I wanted to add an AD group to all the files and folders in a share, the problem is inheritance had been turned off on lots of the folders so I couldn’t just add the AD group to the top and let it filter down. So the solution was to use PowerShell.
First I mapped a drive to the Share (x: in this case). Why didn’t I run this on the server? UAC is a pain in the bum.

You’ll need to change the path and the domain and AD group.

$folders = Get-ChildItem -Directory -Path X:\ -Recurse
foreach ($folder in $folders){
$acl = Get-Acl -path $folder.FullName
$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule `
("DOMAIN\AD_GROUP", "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")
$acl.SetAccessRule($accessRule)
$acl | Set-Acl -path $folder.FullName
}

1

2

3

4

5

6

7

8

$folders = Get-ChildItem -Directory -Path X:\ -Recurse

foreach ($folder in $folders){

$acl = Get-Acl -path $folder.FullName

$accessRule = New-Object System.Security.AccessControl.FileSystemAccessRule `

("DOMAIN\AD_GROUP", "FullControl", "ContainerInherit,ObjectInherit", "None", "Allow")

$acl.SetAccessRule($accessRule)

$acl | Set-Acl -path $folder.FullName

}

Tags: permissions, PowerShell

Get-ACL – Report file and folder permissions

Posted by robd on May 11, 2019
powershell, Server / No Comments

If you need to report out file and folder permissions of a file share, see the below PowerShell.

First map the the share to a drive if it isnt already. In my case X: drive.

Why didn’t I do this on the server hosting the files? UAC gets in the way and is a pain the bum.

$FolderPath = dir -Directory -Path "x:\" -Recurse -Force
$Report = @()
Foreach ($Folder in $FolderPath) {
$Acl = Get-Acl -Path $Folder.FullName
foreach ($Access in $acl.Access)
{
$Properties = [ordered]@{'FolderName'=$Folder.FullName;'AD
Group or
User'=$Access.IdentityReference;'Permissions'=$Access.FileSystemRights;'Inherited'=$Access.IsInherited}$Report += New-Object -TypeName PSObject -Property $Properties}
}
$Report | Export-Csv -path "C:\temp\Folder_Permissions.csv"

1

2

3

4

5

6

7

8

9

10

11

$FolderPath = dir -Directory -Path "x:\" -Recurse -Force

$Report = @()

Foreach ($Folder in $FolderPath) {

$Acl = Get-Acl -Path $Folder.FullName

foreach ($Access in $acl.Access)

{

$Properties = [ordered]@{'FolderName'=$Folder.FullName;'AD

Group or

User'=$Access.IdentityReference;'Permissions'=$Access.FileSystemRights;'Inherited'=$Access.IsInherited}$Report += New-Object -TypeName PSObject -Property $Properties}

}

$Report | Export-Csv -path "C:\temp\Folder_Permissions.csv"

Tags: permissions, PowerShell

icalcs – Backing up Permissions

Posted by robd on May 10, 2019
powershell, Server / No Comments

I wanted to make some changes to some permissions on mass today but decided it would be prudent to backup the permissions first.

So I used icals, to do this I first ran CMD as admin, then mapped the share drive with “Net Use“.

Why didn’t I do this on the server hosting the files? UAC gets in the way and is a pain the bum.

To backup the permissions:

Swicthes:

/t – Performs the operation on all specified files in the current directory and its subdirectories.

/c – Continues the operation despite any file errors. Error messages will still be displayed.

icacls x:\ /save c:\temp\permissions.txt /t /c

1	icacls x:\ /save c:\temp\permissions.txt /t /c

Then to restore:

icacls y:\test /restore c:\temp\permissions.txt

1	icacls y:\test /restore c:\temp\permissions.txt

Tags: CMD, icalc, permissions

Edit VMs using PowerShell and PowerCLI

Posted by robd on January 28, 2019
powershell, vmware / No Comments

To resize VMs using PowerShell with PowerCLI from a csv list, first install the software:

https://my.vmware.com/web/vmware/details?downloadGroup=PCLI650R1&productId=614

Then create a list of servers to resize and save it as a CSV file in C:\temp\VMs.csv:

Server01

Server02

Server03

1

2

3

4

5

Server01

Server02

Server03

Save the below as Something.PS1 and run from PowerCLI

Note: Change VCENTRE to your vCentre, this script will TURN THE SERVER OFF then give each VM two CPUs, one socket and 5GBs of RAM.

$me = Get-Credential

connect-viserver "VCENTRE" -User $me

$vms = get-content C:\Temp\VMs.csv

ForEach ($vm in $vms){

$vms | Shutdown-VMGuest –Confirm:$False

Sleep 60

$vms | Set-VM –MemoryGB 8 –NumCpu 2 –Confirm:$False

$vms | Start-VM

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

$me = Get-Credential

connect-viserver "VCENTRE" -User $me

$vms = get-content C:\Temp\VMs.csv

ForEach ($vm in $vms){

$vms | Shutdown-VMGuest –Confirm:$False

Sleep 60

$vms | Set-VM –MemoryGB 8 –NumCpu 2 –Confirm:$False

$vms | Start-VM

}

Tags: PowerCLI, PowerShell, VMWARE

Install the Remote Server Administration Tools (RSAT) on Windows 10 1809 via PowerShell

Posted by robd on October 19, 2018
powershell / No Comments

Just a quick one, to install RSAT on Windows 10 1809 via PowerShell:

Get-WindowsCapability -Name RSAT* -Online | Add-WindowsCapability -Online

1	Get-WindowsCapability -Name RSAT* -Online \| Add-WindowsCapability -Online

then check:

Get-WindowsCapability -Name RSAT* -Online | Select-Object -Property DisplayName, State

1	Get-WindowsCapability -Name RSAT* -Online \| Select-Object -Property DisplayName, State

Tags: PowerShell, RSAT, Windows 10 1809

Use PowerShell to Remotely Enable Firewall Exceptions

Posted by robd on October 18, 2018
powershell / No Comments

Got this today while connecting to Event viewer on a remote windows 10 machine:

“Computer ‘DC01.MIKEFROBBINS.COM’ cannot be connected. Verify
that the network path is correct, the computer is available on the
network, and that the appropriate Windows Firewall rules are enabled
on the target computer.
To enable the appropriate Windows Firewall rules on the remote
computer, open the Windows Firewall with Advanced Security snap-in
and enable the following inbound rules:
COM+ Network Access (DCOM-In)
All rules in the Remote Event Log Management group
You can also enable these rules by using Group Policy settings for
Windows Firewall with Advanced Security. For servers that are running
the Server Core installation option, run the Netsh AdvFirewall
command, or the Windows PowerShell NetSecurity module.”

1

2

3

4

5

6

7

8

9

10

11

12

13

“Computer ‘DC01.MIKEFROBBINS.COM’ cannot be connected. Verify

that the network path is correct, the computer is available on the

network, and that the appropriate Windows Firewall rules are enabled

on the target computer.

To enable the appropriate Windows Firewall rules on the remote

computer, open the Windows Firewall with Advanced Security snap-in

and enable the following inbound rules:

COM+ Network Access (DCOM-In)

All rules in the Remote Event Log Management group

You can also enable these rules by using Group Policy settings for

Windows Firewall with Advanced Security. For servers that are running

the Server Core installation option, run the Netsh AdvFirewall

command, or the Windows PowerShell NetSecurity module.”

To Fix remotely:

Invoke-Command -ComputerName COMPUTERNAME {
Set-NetFirewallRule -DisplayGroup 'Remote Event Log Management' -Enabled True -PassThru |
select DisplayName, Enabled
} -Credential (Get-Credential)

1

2

3

4

Invoke-Command -ComputerName COMPUTERNAME {

Set-NetFirewallRule -DisplayGroup 'Remote Event Log Management' -Enabled True -PassThru |

select DisplayName, Enabled

} -Credential (Get-Credential)

Tags: PowerShell

Tech Blog

So much Tech and so little time

ODBC Driver Update

Check and change DNS on all the servers in the domain

Check DNS accross all your Domain Controllers

Run PowerShell through a Proxy

Set-ACL – Se file and folder permissions

Get-ACL – Report file and folder permissions

icalcs – Backing up Permissions

Edit VMs using PowerShell and PowerCLI

Install the Remote Server Administration Tools (RSAT) on Windows 10 1809 via PowerShell

Use PowerShell to Remotely Enable Firewall Exceptions

Subscribe to my posts

Dont like Adverts

Links

Recent Posts

Tag Cloud

Popular Posts

Map

Categories