Category Archives: Windows

Monitoring a Automatic Seeding in msSQL Always on Availability Groups

This command, can be run on both the primary and secondary node.

SELECT local_database_name
 ,role_desc
 ,internal_state_desc
 ,transfer_rate_bytes_per_second
 ,transferred_size_bytes
 ,database_size_bytes
 ,start_time_utc
 ,end_time_utc
 ,estimate_time_complete_utc
 ,total_disk_io_wait_time_ms
 ,total_network_wait_time_ms
 ,is_compression_enabled
FROM sys.dm_hadr_physical_seeding_stats

ADFS – Bad Request 400 in Internet Explorer

Today i have a customer that got an Bad Request Error 400, when he try to logon to ADFS from inside their own network, with domain connected computers.
The error is only showing up in Internet Explorer, witch lead me to it might be a kerberos thing.

The service account that run the ADFS services, is “ita_service_admin”.

So looking at that account with setspn i can see witch SPN is register to ita_service_admin account.

setspn -l XXX\ita_service_admin

The where no SPN for the account. The ADFS url is: https://fs.YYY.dk so i added the following SPN to ita_service_Admin account:

setspn -S HOST/FS.YYY.DK XXX\ITA_SERVICE_ADMIN
setspn -S HTTP/FS.YYY.DK XXX\ITA_SERVICE_ADMIN

But I got an error saying that HOST/FS.YYY.DK already exist. So i look for the SPN in the domain

This Code, will show all SPN in use in you Active Directory

cls
$search = New-Object DirectoryServices.DirectorySearcher([ADSI]“”)
$search.filter = “(servicePrincipalName=*)”
$results = $search.Findall()

#list results
foreach($result in $results)
{
$userEntry = $result.GetDirectoryEntry()
Write-host “Object Name = “ $userEntry.name -backgroundcolor “yellow” -foregroundcolor “black”
Write-host “DN      =      “  $userEntry.distinguishedName
Write-host “Object Cat. = “  $userEntry.objectCategory
Write-host “servicePrincipalNames”        $i=1

foreach($SPN in $userEntry.servicePrincipalName)
{
  Write-host “SPN(“ $i “)   =      “ $SPN       $i+=1
  }
Write-host “”

}

Find the SPN, and delete it from the account, i find the account under Administrator, so it looks like someone have change the service account for ADFS service from Administrator to ITA_SERVICE_ADMIN account in the past, without moving the SPN.

So delete the SPN from administrator account

setspn -D HOST/FS.YYY.DK XXX\administrator

Then try to add it again to ITA_Service_Admin account.

setspn -S HOST/FS.YYY.DK XXX\ITA_SERVICE_ADMIN

Restart the ADFS service, and the ADFS is working again also from Internet Explorer.

MSSQL How long will data be in cache/buffer

How fast is data leving the cache, when it not access again.

With this query, you can see how fast data will get expire, and overwritten by new data in the cache.

SELECT  @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
          THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
          FROM   master.sys.sysprocesses
          WHERE  cmd='LAZY WRITER')
      ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM  sys.dm_os_performance_counters
WHERE   [object_name] LIKE '%Manager%'
          AND[counter_name] = 'Page life expectancy'

Witch the query you can see how long time data will be in the cache/buffer.

MSSQL – See buffer hit ratio

Whit this code, you can see how much of query in percent at this moment is reading from the cache/buffer.

DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)

declare @Cachehitratio real

set @Cachehitratio = null

--Cache hit ratio
SELECT @value = cntr_value FROM master..sysperfinfo (nolock) 
WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM master..sysperfinfo (nolock) 
WHERE counter_name = 'Buffer cache hit ratio base'
set @Cachehitratio= (@value / @basevalue) *100

select @Cachehitratio

CAPI2 Error in event viewer when taking backup

When taking backup of a SQL server i got this error in my event viewer, every times the backup is running:
Event Viewer Error:

Cryptographic Services failed while processing the OnIdentity() call in the System Writer Object.

Details:
AddLegacyDriverFiles: Unable to back up image of binary Microsoft Link-Layer Discovery Protocol.

System Error:
Access is denied.
.

Solution:

During backup a VSS process running under NETWORK_SERVICE account calls cryptcatsvc!CSystemWriter::AddLegacyDriverFiles(), which enumerates all the drivers records in Service Control Manager database and tries opening each one of them. , The function fails on MSLLDP record with “Access Denied” error.

Turned out it fails because MSLLDP driver’s security permissions do not allow NETWORK_SERVICE to access the driver record.

The binary security descriptor for the record is located here:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MsLldp\Security

It should be modified, I used SC.EXE and Sysinternals’ ACCESSCHK.EXE to fix it.

The original security descriptor looked like below:

>accesschk.exe -c mslldp

mslldp
  RW NT AUTHORITY\SYSTEM
  RW BUILTIN\Administrators
  RW S-1-5-32-549       <- these are server operators
  R  NT SERVICE\NlaSvc

No service account is allowed to access MSLLDP driver

The security descriptor for the drivers that were processed successfully looked this way:

>accesschk.exe -c mup

mup
  RW NT AUTHORITY\SYSTEM
  RW BUILTIN\Administrators
  R  NT AUTHORITY\INTERACTIVE
  R  NT AUTHORITY\SERVICE  <- this gives access to services

How to add access rights for NT AUTHORITY\SERVICE to MSLLDP service:

1. Run: SC sdshow MSLLDP

You’ll get something like below (SDDL language is documented on MSDN):

D:(D;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BG)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)(A;;CCDCLCSWRPDTLOCRSDRCWDWO;;;BA)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SO)(A;;LCRPWP;;;S-1-5-80-3141615172-2057878085-1754447212-2405740020-3916490453)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

2. Run: SC sdshow MUP

You’ll get:

D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

3. Take NT AUTHORITY\ SERVICE entry, which is (A;;CCLCSWLOCRRC;;;SU) and add it to the original MSLLDP security descriptor properly, right before the last S:(AU… group.

4. Apply the new security descriptor to MSLLDP service :

sc sdset MSLLDP D:(D;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BG)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)(A;;CCDCLCSWRPDTLOCRSDRCWDWO;;;BA)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SO)(A;;LCRPWP;;;S-1-5-80-3141615172-2057878085-1754447212-2405740020-3916490453)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)


5. Check the result:

>accesschk.exe -c mslldp

mslldp
  RW NT AUTHORITY\SYSTEM
  RW BUILTIN\Administrators
  RW S-1-5-32-549
  R  NT SERVICE\NlaSvc
  R  NT AUTHORITY\SERVICE

6. Run you backup app, the error is gone for my Home Server backup.

!!! Do not forget to use your security descriptor for MSLLDP driver since I guess there can be some rare cases when its different for your machine. Do not copy my SDDL descriptions, just in case. And backup the old descriptor just in case !!!

Thanks to szz743
https://answers.microsoft.com/en-us/windows/forum/windows8_1-hardware/cryptographic-services-failed-while-processing-the/c4274af3-79fb-4412-8ca5-cee721bda112

Exchange and TLS problems

Today i have a problem that an exchange will not send mails though TLS, no matter what i do.

I find out that, somehow this exchange servers where creating new send connector, with forcehelo = True.

Witch mean that the Exchange server is using the OLD HELO instead of the EHLO, when talking to other SMTP servers. Setting this to false help. Now the Exchange server send with TLS.

So FORCEHELO=$TRUE breaks TLS.

To see if this is the problem, you can type:

get-sendconnector | select id, forcehelo

To set it to false, for the send connector “Default_OUT” you can run this powershell command:

get-sendconnector -id Default_OUT | set-sendconnector -forcehelo $false

Also you might need to set the TLS certificate to the right certificate. Remember that the certificate need to have the hostname in the certificate. Se more on this link:

https://practical365.com/exchange-server/configuring-the-tls-certificate-name-for-exchange-server-receive-connectors/

Enabled RemoteFX locally on a non domain joined computer

For some reason RemoteFX USB Redirection is not enabled standard in Windows.

So what to do with a non domain joined computer, you can change GPO from the Active Directory.

That is not a problem, you can run GPEDIT from the client.

Right click on start, and select Run

And click OK

Now browse to Computer Configuration -> Administrative Templates -> Windows Components -> Remote Desktop Services -> Remote Desktop Connection Client -> RemoteFX USB Device Redirection

Dobble click on “Allow RDP redirection of other supported RemoteFX USB desvices from this computer”

And change the settings to Enabled, and choice Adminstrators and Users.
Click ok

Now restart you computer, and you should now have enabled RemoteFX.

Change RDWEB to not redirect sound to client

If you have Installed Skype for Business and have enabled RemoteFX.

You might still find that Skype for Business is saying that it using Remote Audio, even if you have redirected you USB headset.

This can be changed, by running this command on in a Administrator Powershell.

Set-RDSessionCollectionConfiguration -CollectionName "YourCollectionName" -CustomRdpProperty "use re
direction server name:i:1 `n usbdevicestoredirect:s:* `n audiocapturemode:i:1 `n audiomode:i:1"

After this go to yours RDWeb page, and download a new RDP fil, and connect with you new RDP file.

After that Skype should say that is using you USB Headset.

Remember to select that you will redirect you USB Headset.