Category Archives: Computer

MSSQL Backup History Query

To see MSSQL history Query, run this SQL query. It will show all backups between the 1 january 2021 and 1 february 2021, order by Database and then Last backup time.

It will also show if the backup is a CopyOnly job.

;WITH CTE_Backup AS
(
SELECT  database_name,is_copy_only,backup_start_date,type,physical_device_name
       ,Row_Number() OVER(PARTITION BY database_name,BS.type
        ORDER BY backup_start_date DESC) AS RowNum
FROM    msdb..backupset BS
JOIN    msdb.dbo.backupmediafamily BMF
ON      BS.media_set_id=BMF.media_set_id
AND		BS.backup_start_date >= '01/01/2021'
AND		BS.backup_start_date <= '02/02/2021'
)
SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name,CTE.is_copy_only
FROM        sys.databases D
LEFT JOIN   CTE_Backup CTE
ON          D.name = CTE.database_name

ORDER BY    D.name,last_backup_time

Problems with running WMI Request from domain member to workgroup machine

Today, I have the problem that I could not run WMI request from a Domain join member server, to a standalone workgroup server.

It keeps telling me Access Denied.

It turn out that:
In a workgroup, the account connecting to the remote computer is a local user on that computer. Even if the account is in the Administrators group, UAC filtering means that a script runs as a standard user.

That is a good link to refer to. It is about Remote UAC and WMI. The solution is to go to the following registry key – HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System – and create a new name called LocalAccountTokenFilterPolicy (of type DWORD_32) with a value of 1. (Check the Serverfault post for one more solution if you are using a Server 2012).

VMWare – Certificate Status Trigger alarm Alert

Back in December i replaced my Certificate on my VMWare Appliance with a our new Wildcard certificate.
But here in January, i got an alarm saying “Certificate Alarm” and it trigger it as an Alert.

Searching around for a solution, on what certificate that expire soon, i found this command:

for i in $(/usr/lib/vmware-vmafd/bin/vecs-cli store list); do echo STORE $i; /usr/lib/vmware-vmafd/bin/vecs-cli entry list --store $i --text | egrep "Alias|Not After"; done

It showing me that a backup mackine certificate is expiring

STORE MACHINE_SSL_CERT
Alias : __MACHINE_CERT
            Not After : Jan 15 14:20:36 2022 GMT
STORE TRUSTED_ROOTS
Alias : b4a1550d09628bfe628d2685fe24f2b67afb9855
            Not After : Jan 31 10:55:48 2025 GMT
Alias : 7a1f6edf1e16498c4eff9190fa5ef6742bbdde0e
            Not After : Dec  4 14:03:36 2026 GMT
Alias : 909c4328a37dcff3d72de88c51df6f63600fc3f3
            Not After : Dec  4 14:03:36 2026 GMT
Alias : 98c6a8dc887963ba3cf9c2731cbdd3f7de05ac2d
            Not After : Nov  6 12:23:33 2027 GMT
Alias : 91c489ecb94b64e2eeffe438a0ad3fc1465959a2
            Not After : Jan 15 14:20:36 2022 GMT
Alias : 4c27431717565a3a07f3e6d0032c4258949cf9ec
            Not After : Feb 20 10:00:00 2024 GMT
Alias : b1bc968bd4f49d622aa89a81f2150152a41d829c
            Not After : Jan 28 12:00:00 2028 GMT
STORE TRUSTED_ROOT_CRLS
Alias : bcd1699f713eabf1dbd8a21618ea6b0487adbdc8
STORE STS_INTERNAL_SSL_CERT
Alias : __MACHINE_CERT
            Not After : Dec  4 14:03:36 2026 GMT
STORE machine
Alias : machine
            Not After : Jan 31 10:55:48 2025 GMT
STORE vsphere-webclient
Alias : vsphere-webclient
            Not After : Jan 31 10:55:48 2025 GMT
STORE vpxd
Alias : vpxd
            Not After : Jan 31 10:55:48 2025 GMT
STORE vpxd-extension
Alias : vpxd-extension
            Not After : Jan 31 10:55:48 2025 GMT
STORE SMS
Alias : sms_self_signed
            Not After : Feb  3 11:51:48 2025 GMT
STORE BACKUP_STORE
Alias : bkp___MACHINE_CERT
            Not After : Jan 20 12:00:00 2021 GMT
Alias : bkp_machine
            Not After : Jan 31 10:55:48 2025 GMT
Alias : bkp_vsphere-webclient
            Not After : Jan 31 10:55:48 2025 GMT
Alias : bkp_vpxd
            Not After : Jan 31 10:55:48 2025 GMT
Alias : bkp_vpxd-extension
            Not After : Jan 31 10:55:48 2025 GMT

So i exported this Machine certificate to a CRT file, and then deleted it with these commands

mkdir /certificates

/usr/lib/vmware-vmafd/bin/vecs-cli entry getcert --store BACKUP_STORE --alias bkp___MACHINE_CERT --output /certificates/old_machine.crt

/usr/lib/vmware-vmafd/bin/vecs-cli entry delete --store BACKUP_STORE --alias bkp___MACHINE_CERT -y

It then tell me that the certificate have been deleted

Deleted entry with alias [bkp___MACHINE_CERT] in store [BACKUP_STORE] successfully

Also running this command again

for i in $(/usr/lib/vmware-vmafd/bin/vecs-cli store list); do echo STORE $i; /usr/lib/vmware-vmafd/bin/vecs-cli entry list --store $i --text | egrep "Alias|Not After"; done

Show that the certificate have now gone.

Intune Exchange Connector version is not supported

Microsoft have public this article: https://support.microsoft.com/da-dk/help/4464186/microsoft-intune-exchange-connector-version-not-supported, that tell you to just download a new version from Microsoft Intune Admin portal.

But this warning messages could also happen if you allready have a connector installed on another server, even if microsoft says that the new connector will just replace the old one.
So if you get this warning, just after you have downloaded the software, and you have an old connector, delete the old one first.
Remember to check notice of what User Notification and Unmanaged device access where set to before deleting the old one

Restart Explorer as Adminstrator

If you have problems with accessing a drive, with the error message: f:\ is not accessible. Access is denied

It can happen, if the drive doesn’t have this permission:
Everyone (This folder Only):

  • Read & Execute
  • List folder contents
  • Read

To access the drive, you need restart Explorer as administrator.

Find Explorer.exe as the user you are login as. In this example the username is admin_sofi, and kill it by right click on it and choice “End Task”

After start CMD as administrator, and write this command:
c:\windows\explorer.exe /NOACCHECK

After this you can see that explorer.exe is now running in “Elevated”

MSSQL database roles get all securables – Full script out the Database Roles

/********************************************************************
 *                                                                  *
 * Author: John Eisbrener                                           *
 * Script Purpose: Script out Database Role Definition              *
 * Notes: Please report any bugs to http://www.dbaeyes.com/         *
 *                                                                  *
 * Update: 2014-03-03 - Adjusted output to accommodate Role         *
 *                      definitions that are longer than 8000 chars *
 * Update: 2013-09-03 - Added user output per Joe Spivey's comment  *
 *                    - Modified formatting for oddly named objects *
 *                    - Included support for Grants on DMVs         *
 ********************************************************************/
DECLARE @roleName VARCHAR(255)
SET @roleName = 'DatabaseRoleName'

-- Script out the Role
DECLARE @roleDesc VARCHAR(MAX), @crlf VARCHAR(2)
SET @crlf = CHAR(13) + CHAR(10)
SET @roleDesc = 'CREATE ROLE [' + @roleName + ']' + @crlf + 'GO' + @crlf + @crlf

SELECT    @roleDesc = @roleDesc +
        CASE dp.state
            WHEN 'D' THEN 'DENY '
            WHEN 'G' THEN 'GRANT '
            WHEN 'R' THEN 'REVOKE '
            WHEN 'W' THEN 'GRANT '
        END + 
        dp.permission_name + ' ' +
        CASE dp.class
            WHEN 0 THEN ''
            WHEN 1 THEN --table or column subset on the table
                CASE WHEN dp.major_id < 0 THEN
                    + 'ON [sys].[' + OBJECT_NAME(dp.major_id) + '] '
                ELSE
                    + 'ON [' +
                    (SELECT SCHEMA_NAME(schema_id) + '].[' + name FROM sys.objects WHERE object_id = dp.major_id)
                        + -- optionally concatenate column names
                    CASE WHEN MAX(dp.minor_id) > 0 
                         THEN '] ([' + REPLACE(
                                        (SELECT name + '], [' 
                                         FROM sys.columns 
                                         WHERE object_id = dp.major_id 
                                            AND column_id IN (SELECT minor_id 
                                                              FROM sys.database_permissions 
                                                              WHERE major_id = dp.major_id
                                                                AND USER_NAME(grantee_principal_id) IN (@roleName)
                                                             )
                                         FOR XML PATH('')
                                        ) --replace final square bracket pair
                                    + '])', ', []', '')
                         ELSE ']'
                    END + ' '
                END
            WHEN 3 THEN 'ON SCHEMA::[' + SCHEMA_NAME(dp.major_id) + '] '
            WHEN 4 THEN 'ON ' + (SELECT RIGHT(type_desc, 4) + '::[' + name FROM sys.database_principals WHERE principal_id = dp.major_id) + '] '
            WHEN 5 THEN 'ON ASSEMBLY::[' + (SELECT name FROM sys.assemblies WHERE assembly_id = dp.major_id) + '] '
            WHEN 6 THEN 'ON TYPE::[' + (SELECT name FROM sys.types WHERE user_type_id = dp.major_id) + '] '
            WHEN 10 THEN 'ON XML SCHEMA COLLECTION::[' + (SELECT SCHEMA_NAME(schema_id) + '.' + name FROM sys.xml_schema_collections WHERE xml_collection_id = dp.major_id) + '] '
            WHEN 15 THEN 'ON MESSAGE TYPE::[' + (SELECT name FROM sys.service_message_types WHERE message_type_id = dp.major_id) + '] '
            WHEN 16 THEN 'ON CONTRACT::[' + (SELECT name FROM sys.service_contracts WHERE service_contract_id = dp.major_id) + '] '
            WHEN 17 THEN 'ON SERVICE::[' + (SELECT name FROM sys.services WHERE service_id = dp.major_id) + '] '
            WHEN 18 THEN 'ON REMOTE SERVICE BINDING::[' + (SELECT name FROM sys.remote_service_bindings WHERE remote_service_binding_id = dp.major_id) + '] '
            WHEN 19 THEN 'ON ROUTE::[' + (SELECT name FROM sys.routes WHERE route_id = dp.major_id) + '] '
            WHEN 23 THEN 'ON FULLTEXT CATALOG::[' + (SELECT name FROM sys.fulltext_catalogs WHERE fulltext_catalog_id = dp.major_id) + '] '
            WHEN 24 THEN 'ON SYMMETRIC KEY::[' + (SELECT name FROM sys.symmetric_keys WHERE symmetric_key_id = dp.major_id) + '] '
            WHEN 25 THEN 'ON CERTIFICATE::[' + (SELECT name FROM sys.certificates WHERE certificate_id = dp.major_id) + '] '
            WHEN 26 THEN 'ON ASYMMETRIC KEY::[' + (SELECT name FROM sys.asymmetric_keys WHERE asymmetric_key_id = dp.major_id) + '] '
         END COLLATE SQL_Latin1_General_CP1_CI_AS
         + 'TO [' + @roleName + ']' + 
         CASE dp.state WHEN 'W' THEN ' WITH GRANT OPTION' ELSE '' END + @crlf
FROM    sys.database_permissions dp
WHERE    USER_NAME(dp.grantee_principal_id) IN (@roleName)
GROUP BY dp.state, dp.major_id, dp.permission_name, dp.class

SELECT @roleDesc = @roleDesc + 'GO' + @crlf + @crlf

-- Display users within Role.  Code stubbed by Joe Spivey
SELECT	@roleDesc = @roleDesc + 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' + @crlf
FROM	sys.database_principals users
		INNER JOIN sys.database_role_members link 
			ON link.member_principal_id = users.principal_id
		INNER JOIN sys.database_principals roles 
			ON roles.principal_id = link.role_principal_id
WHERE	roles.name = @roleName

-- PRINT out in blocks of up to 8000 based on last \r\n
DECLARE @printCur INT
SET @printCur = 8000

WHILE LEN(@roleDesc) > 8000
BEGIN
    -- Reverse first 8000 characters and look for first lf cr (reversed crlf) as delimiter
    SET @printCur = 8000 - CHARINDEX(CHAR(10) + CHAR(13), REVERSE(SUBSTRING(@roleDesc, 0, 8000)))

    PRINT LEFT(@roleDesc, @printCur)
    SELECT @roleDesc = RIGHT(@roleDesc, LEN(@roleDesc) - @printCur)
END

PRINT @roleDesc + 'GO'

Thanks for the script to John Eisbrener – Check for new version here: https://dbaeyes.wordpress.com/2013/04/19/fully-script-out-a-mssql-database-role/

Windows 7/2008/2008 R2 Won’t boot after installation of KB4512506 / KB4512486 / KB4512476

After installation of these update, you can have problem booting you server. with this warning:

The digital signature for this file couldn’t be verified.
file:\windows\system32\winload.efi status 0xc0000428

You can fix it by booting you windows operation on the installation disc, and go to a Recovery command prompt and type these commands:

c:\> bcdedit

This screenshot is taken from a running Windows Server, in a recovery boot the identifier under the second Windows Boot Loader, will be named {default} instead of {current}

c:\> bcdedit -set {default} -nointegritychecks 1

Replace {default} with the one from the bcdedit output.

Normal this have been resolved by installation and updated version of KB4474419, but there have not been an updated version to Windows server 2008 yet.
But to Windows 7 and Windows server 2008 R2, you can download an updated version here:
http://www.catalog.update.microsoft.com/search.aspx?q=kb4474419