Calculate Sql Server database size having only db_reader permissions on target database

There are several possible ways to calculate database size (the same size which is shown when you right click on the database in Sql Server Management Studio > Properties > Files):

In this example we have 23 Mb of database file and 11 Mb of transaction log, total 34 Mb.

One way we can try is to run the following query:

select db_name(database_id) as database_name, 
    type_desc, 
    name, 
    size/128.0 as CurrentSizeMB
from sys.master_files
WHERE DB_NAME(database_id) = 'MyDatabase'

If you have permissions to run this command result will look like this:

As you can see it shows the same numbers as properties window shown above. The problem is that according to documentation you should have quite high server-level permissions to access sys.master_files:

The minimum permissions that are required to see the corresponding row are CREATE DATABASE, ALTER ANY DATABASE, or VIEW ANY DEFINITION.

If you don’t have one of these permissions result will be empty.

Is it possible to calculate database size having less permissions? E.g. having only db_reader permissions on target database. The answer is yes it is possible. In order to do that we need to use system stored procedure sp_spaceused:

use MyDatabaseName
exec sp_spaceused

It works also with db_reader permissions on the target database. Result will look like this:

It returns 2 result sets and in 1st result set it returns overall database size which is sum of db file name and transaction log (34 Mb in our example).

Authenticate in Sharepoint on-prem FBA site via OTB /_vti_bin/Authentication.asmx web service

Sharepoint contains number of OTB web services which are located inside _vti_bin virtual directory. In this article we will check one of them: authentication.asmx. This is quite interesting web service which allows to authenticate your app in Sharepoint FBA site. I.e. allows to send username and password and get FedAuth authentication cookies if provided credentials are valid (the same cookies which are used by Sharepoint FBA site when you successfully logged in via it’s login page).

Note that you may use this web service in server-side apps or mobile apps but not in client side JavaScript-based solution because FedAuth cookies have HttpOnly flag and thus can’t be used in JavaScript.

In order to use this web service we need to send special SOAP body in HTTP POST request. In order to get example of this SOAP open http://example.com/_vti_bin/authentication.asmx in browser (where instead of http://example.com you should use url of your site). It will show list of available web methods:

• Login
• Mode

Click on Login and you will get needed SOAP examples (in this article we will use SOAP 1.1):

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <Login xmlns="http://schemas.microsoft.com/sharepoint/soap/">
      <username>string</username>
      <password>string</password>
    </Login>
  </soap:Body>
</soap:Envelope>

In this SOAP body we need to set actual username and password in appropriate xml tags.

Here is example which shows how to send this SOAP body to authentication.asmx web service and get authentication cookies:

var cookies = new CookieContainer();
var handler = new HttpClientHandler();
handler.CookieContainer = cookies;
using (var httpClient = new HttpClient(handler))
{
    string soapBody =
    "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" +
    "  <soap:Body>" +
    "    <Login xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" +
    "      <username>" + username + "</username>" +
    "      <password>" + password + "</password>" +
    "    </Login>" +
    "  </soap:Body>" +
    "</soap:Envelope>";
    var req = new HttpRequestMessage(HttpMethod.Post, "http://example.com/_vti_bin/authentication.asmx");
    req.Content = new StringContent(soapBody, Encoding.UTF8, "text/xml")
    var res = httpClient.SendAsync(req).GetAwaiter().GetResult();
    if (res.StatusCode == HttpStatusCode.OK)
    {
        var response = res.Content.ReadAsStringAsync().Result;
        var cookie = cookies.GetCookies(new Uri("http://example.com")).Cast<Cookie>().FirstOrDefault(c => c.Name == "FedAuth");
    }
}

In this example we first create HTTP POST request to authentication.asmx using SOAP body retrieved above and then if authentication was successful read FedAuth cookies returned from server. After that we may use these cookies for making authenticated calls to other web services. In one of the future articles I will show how to do that.

Get access token for calling AAD secured Azure functions via PowerShell

Some time ago I posted PowerShell example which lists all Azure AD groups via Rest API: List Azure AD groups via Rest Graph API in Powershell. However if you have custom Azure functions app which is secured via AAD (see Call Azure AD secured Azure functions from C#) you may use similar approach to get access token based on client id/secret (i.e. which will use app permissions) for calling your Azure functions from PowerShell or from 3rd party clients like Postman.

In order to get access token for calling AAD secured Azure functions you may use the following PowerShell script:

param
(
	[Parameter(Mandatory=$true)]
	[string]$Tenant,
	[Parameter(Mandatory=$true)]
	[string]$ClientId,
	[Parameter(Mandatory=$true)]
	[string]$ClientSecret
)

$currentDir = $PSScriptRoot
$dllCommonDir = resolve-path($currentDir + "\Common\")

[System.Reflection.Assembly]::LoadFile([System.IO.Path]::Combine($dllCommonDir, "Microsoft.Identity.Client.dll"))

function GetAccessToken($tenant, $clientId, $ClientSecret)
{
	$appCredentials = New-Object Microsoft.Identity.Client.ClientCredential -ArgumentList $ClientSecret
	$aadLoginUri = New-Object System.Uri -ArgumentList "https://login.microsoftonline.com/"
	$authorityUri = New-Object System.Uri -ArgumentList $aadLoginUri, $Tenant
	$authority = $authorityUri.AbsoluteUri
	$redirectUri = "urn:ietf:wg:oauth:2.0:oob"
	$clientApplication = New-Object Microsoft.Identity.Client.ConfidentialClientApplication($ClientId, $authority, $redirectUri, $appCredentials, $null, $null)
	[string[]]$defaultScope = @("https://graph.microsoft.com/.default")
	$authenticationResult = $clientApplication.AcquireTokenForClientAsync($defaultScope).Result
	return $authenticationResult.AccessToken
}

Write-Host "Acquiring access token..." -foregroundcolor green
$accessToken = GetAccessToken $Tenant $ClientId $ClientSecret
if ([System.String]::IsNullOrEmpty($accessToken))
{
	Write-Host "Can't acquire access token" -foregroundcolor red
	return
}
Write-Host "Access token successfully acquired" -foregroundcolor green
$filePath = [System.IO.Path]::Combine($currentDir, [System.String]::Format("_accesstoken_{0}.txt", [System.DateTime]::Now.ToString("yyyyMMddHHmmss")))
($accessToken) | Out-File $filePath -Append

It will fetch access token and will log it to text file. For calling this script you will need Microsoft.Identity.Client.dll assembly located in Common subfolder.

One way to fix Failed to run web job error on Azure App Service

If you have just updated web job (see the following related articles: Upload WebJob to Azure App service with predefined schedule and How to remove Azure web job via Azure RM PowerShell) you may face with the following issue: when you will try to run updated web job you will get the error “Failed to run web job”. The problem is that error notification won’t contain any description and it will be hard to find the actual reason of this problem. In this article I will describe one of the reasons.

When web job is started it creates special lock file triggeredJob.lock in /data/jobs/triggered/{webJobName} folder of SCM site. You may check it if will connect to the site via FTP:

In the same folder there will be sub folders with timestamps which correspond to webjob runs. In these sub folders you will find output logs of each web job run – the same logs which are shown in Azure portal > App service > Web jobs > Logs. The purpose of this triggeredJob.lock file is to prevent launch of second instance of web job when previous instance didn’t finish yet. And the problem is that if during update web job was running this lock file may not be successfully deleted. As result when you will try to run updated version of web job you will get “Failed to run web job” error.

Solution will be to delete this file manually. However it is also not that straightforward. If you will try to remove it from FTP client you will get error

“The process cannot access the file because it is being used by another process”:

In order to delete it we need to stop both App Service and SCM site. Note that it is mandatory to stop both sites – if you will only stop App Service from Azure portal triggeredJob.lock will be still used by the process. Stopping of SCM site is more tricky than App Service. The process is described here: Full stopping a Web App. You need to go to Resource Explorer (azure.com) and select your App service. After that in JSON view on the right side click Edit and do 2 things:

1. Change “state” from “Running” to “Stopped” – this is the same as if you would stop App service form Azure portal
2. Find “scmSiteAlsoStopped” property and set it from “false” to “true” – it will stop SCM file

After that click PUT button on the top. It will stop both sites and you will be able to delete triggeredJob.lock now. Then go to Azure portal and start App service – it will start both sites. After all these steps you should be able to run web job again.