Month: March 2022

Get status of SQL server services on one or more servers remotely

'SQLSERVER01','SQLSERVER02','SQLSERVER03'| get-dbaservice -Type Engine, Agent | Out-GridView

This will provide output as shown below:

The great thing about this approach is that, I can swap out the command get-dbaservice with restart-dbaservice. This will restart the SQL services remotely without having to RDP into a machine.

'SQLSERVER01','SQLSERVER02','SQLSERVER03'| Restart-dbaservice -Type Engine, Agent | Out-GridView

If you don’t have a monitoring tool, this is a great way to check if the services are running or not on a specific SQL server when you are troubleshooting a connection issue.

Update SQL service account on multiple SQL servers using dbatools

Typically, I use the following statement whenever i want to update a SQL service account

Get-DbaService sql1 -Type Engine -Instance MSSQLSERVER | Update-DbaServiceAccount -Username 'MyDomain\sqluser1'

Configures SQL Server engine service on the machine sql1 to run under MyDomain\sqluser1. Will request user to input the account password.

Here is a simple one liner to update SQL service account on multiple servers:

'SQLSERVER01','SQLSERVER02','SQLSERVER03'| Get-DbaService -Type Engine,Agent -Instance MSSQLSERVER | Update-DbaServiceAccount -Username 'MyDomain\sqluser1'

The type is inputs for both Engine and Agent. Therefore, both services will be set to run as that domain user.

Use Powershell to enable TLS 1.2 on a window server

 $path1 = 'HKLM:\SOFTWARE\Microsoft\.NETFramework\v2.0.50727'
 $path2 = 'HKLM:\SOFTWARE\Microsoft\.NETFramework\v4.0.30319'

#New-item -Path $path1 #Uncomment this line if you need to add the folder
 New-ItemProperty -Path $path1 -Name 'SystemDefaultTlsVersions' -Value 1 -PropertyType DWord
 New-ItemProperty -Path $path1 -Name 'SchUseStrongCrypto' -Value 1 -PropertyType DWord

#New-item -Path $path2 #Uncomment this line if you need to add the folder
 New-ItemProperty -Path $path2 -Name 'SystemDefaultTlsVersions' -Value 1 -PropertyType DWord
 New-ItemProperty -Path $path2 -Name 'SchUseStrongCrypto' -Value 1 -PropertyType DWord

Enforcing Strong Encryption: Enabling TLS 1.2 on SQL server

SQL Server 2019 RTM is shipped with TLS 1.2 support and no additional update/fix should be required to enable TLS 1.2 support. The server where SQL is installed must support TLS 1.2. Microsoft SQL 2019 /2016 is already enabled for TLS 1.2.

To ensure server can operate over TLS. Follow the below instructions to configure SQL server 2016.

  • Enable TLS 1.2 to the registry if needed
    1. Start the registry editor by clicking on Start and Run. Type in “regedit” into the Run – Note: You will be editing the registry.
    2. Browse to the following registry key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols.
    3. Right click on the Protocols folder and select New then Key from the drop-down menu. This will create new folder. Rename this folder to TLS 1.2.
    4. Right click on the TLS 1.2 key and add two new keys underneath it.
    5. Rename the two new keys as:
      • Client
      • Server
    6. Right click on the Client key and select New and then DWORD (32-bit) Value from the drop-down list.
    7. Rename the DWORD to DisabledByDefault.
    8. Right-click the name DisabledByDefault and select Modify from the drop-down menu.
    9. Ensure that the Value data field is set to 0 and the Base is Hexadecimal. Click on OK.
    10. Create another DWORD for the Client key as you did in Step 6.
    11. Rename this second DWORD to Enabled.
    12. Right-click the name Enabled and select .. from the drop-down menu.
    13. Ensure that the Value data field is set to 1 and the Base is Hexadecimal. Click on OK.
    14. Repeat steps 6 to 13 for the Server key (by creating two DWORDs, DisabledByDefault and Enabled, and their values underneath the Server key).
  • Registry values that are added or changed to enable TLS 1.2

  • Configure .NET to use strong cryptography to the registry if needed. By configuring .NET to use strong cryptography this enables TLS 1.2 for .NET applications. Even if .NET 4.7 is installed it must be configured to enable TLS 1.2.
    1. Start the registry editor by clicking on Start and Run. Type in “regedit” into the Run – Note: You will be editing the registry.
    2. Browse to the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319
    3. Right click on the 0.30319 key and select New and then DWORD (32-bit) Value from the drop-down list.
    4. Rename the DWORD to SchUseStrongCrypto.
    5. Right-click the name SchUseStrongCrypto and select Modify from the drop-down menu.
    6. Ensure that the Value data field is set to 1 and the Base is Hexadecimal. Click on OK.
    7. Browse to the following registry key: HKLM:\SOFTWARE\Wow6432Node\Microsoft\.NetFramework\v4.0.30319
    8. Right click on the 4.0.30319 key and select New and then DWORD (32-bit) Value from the drop-down list.
    9. Rename the DWORD to SchUseStrongCrypto.
    10. Right-click the name SchUseStrongCrypto and select Modify from the drop-down menu.
    11. Ensure that the Value data field is set to 1 and the Base is Hexadecimal. Click on OK.
  • Registry values that are added or changed to enable .NET to use strong crypto

Reboot the SQL Server: Once your SQL server has been rebooted, TLS 1.2 will be enabled.

%d bloggers like this: