Category Archives: Computer

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.

Enable Full Desktop in RemoteApp Collection

If you have a RemoteAPP collection, and you will like to have allow you user, to access a full Desktop.

This can be changed by logging into yours Remote Desktop Broker, and go to the Regedit, and browse to this page:

HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Terminal Server\CentralPublishedResources\PublishedFarms\<collection>\RemoteDesktops\<collection>

And set the value of ShowInPortal to 1

After this you will have the desktop in RDWeb

RemoteFX not working anymore

Today I have this problem, that no matter what I do. I could get RemoteFX to work.
The strange things is it where working yesterday, and no one will admit that the have changed anything.
And no windows update have been installed.
I was going into debugging, and for some reason I so this registry was set to 0
HKLM -> Software -> Policies -> Microsoft -> Windows NT -> Terminal Services -> fDisableCam


Normally it is not configure on a RDS Session Host server.
So I deleted it, rebooted the server and RemoteFX is now working again.

See if a database is using Enterprise features of an Microsoft SQL server.

Run this command to see if any database on the SQL Server is using enterprise features

IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
  DROP TABLE ##enterprise_features

CREATE TABLE ##enterprise_features
  (
     dbname       SYSNAME,
     feature_name VARCHAR(100),
     feature_id   INT
  )

EXEC sp_msforeachdb
N' USE [?] 
IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0 
BEGIN 
   INSERT INTO ##enterprise_features 
    SELECT dbname=DB_NAME(),feature_name,feature_id 
    FROM sys.dm_db_persisted_sku_features 
END '
SELECT *
FROM   ##enterprise_features