Several mandatory steps for every SQL Server installation

Recently I’ve installed MS Sql Server (don’t remember how many times I’ve done that already :) ) on the new virtual machine and realized that I always perform the following steps after each Sql Server installation:

1. Disable “sa” login

This step was added to mandatory tasks list after sad story: in hte times of beginning of my IT carrier one of my Sql Server instances ran with enabled “sa” login. Password was not very strong and after some time it was brute forced and I got malicious Sql Server job which tried to download and execute remote code on my PC. Fortunately I noticed that in time and made necessary actions. After that I always disable built-in “sa” login on Sql Server – this is the first things hackers will try to brute force on your instance.

2. Configure backups

This step is quite obvious. Remember that you should not only test that your backups work by specified schedule (e.g. if you configured nightly backups – check after few days that backups are really made during last nights) but also test restore scenario. Some time these simple steps will save you from a lot of problems. You may configure backups in Sql Server Management Studio > Management > Maintenance plans > New maintenance plan > Add Back Up Database Task from toolbox: After configuring backup set schedule in the same window.

Another important note is that you should not store backups on the same PC. Safest option is to move backups from local PC to the cloud storage using some cloud backup tool.

3. Limit Sql Server trace log size

If Sql Server runs long period of time it may flood hard drive with trace logs (don’t mix it with transaction logs – these are different). By default they are stored in MSSQL/Logs subfolders under your Sql Server instance folder. In order to configure their size go to Sql Server Management studio > Management > Right click on SQL Server Logs > Configure. Set some value in “Limit the number of error log files before they are recycled” and/or “Maximum size for error log file in KB”:

This simple action will allow to keep Sql Server logs size controlled. If you have similar mandatory steps in your practice please share them in comments.

Resolve “Everyone except external users” group on old tenants

Some time ago I wrote how to get login name for special group “Everyone except external users” in Sharepoint Online: Get login name of special group “Everyone except external users” programmatically in Sharepoint. To remind: it is constructed like this:

"c:0-.f|rolemanager|spo-grid-all-users/” + tenantId

Today we faced with scenario when such login name could not be resolved on one customer’s tenant. After research it was found that similar issue was also reported on OfficeDevPnP github project page (see here). So as it turned out on old tenants this way of getting “Everyone except external users” may not work. As workaround you may use the following solution from OfficeDevPnP:

public override string GetEveryoneExceptExternalUsersName(Web web)
{
 string userIdentity = "";
 try
 {
  // New tenant
  userIdentity = $"c:0-.f|rolemanager|spo-grid-all-users/{web.GetAuthenticationRealm()}";
  var spReader = web.EnsureUser(userIdentity);
  web.Context.Load(spReader);
  web.Context.ExecuteQueryRetry();
 }
 catch (ServerException)
 {
  // Old tenants
  string claimName = web.GetEveryoneExceptExternalUsersClaimName();
  var claim = Utility.ResolvePrincipal(web.Context, web, claimName, PrincipalType.SecurityGroup, PrincipalSource.RoleProvider, null, false);
  web.Context.ExecuteQueryRetry();
  userIdentity = claim.Value.LoginName;
 }

 return userIdentity;
}

I.e. at first we try to get login name using "c:0-.f|rolemanager|spo-grid-all-users/” + tenantId and try to resolve group with this name. If it fails we call GetEveryoneExceptExternalUsersClaimName() extension method which returns localized name of “Everyone except external users” group for current tenant (it has translations of group name for all supported languages) and tries to resolve this special group using this name. This code will work both on new and old tenants.

How to set Always On for Azure Function app via PowerShell

If you run Azure functions on dedicated App service plan (vs Consumption plan) of Basic pricing tier and above you may speed up warmup time of Azure functions by enabling Always On setting (Azure Function app > Configuration > General settings):

If you need to automate this process here is the PowerShell script which can be used in order to set Always On for Azure Function app:

$resourceGroupName = ...
$functionAppName = ...
$webAppPropertiesObject = @{"siteConfig" = @{"AlwaysOn" = $true}}
$webAppResource = Get-AzureRmResource -ResourceType "microsoft.web/sites" -ResourceGroupName $resourceGroupName -ResourceName $functionAppName
$webAppResource | Set-AzureRmResource -PropertyObject $webAppPropertiesObject -Force

After that you Function app will have Always On setting enabled.

Get Azure AD groups images from Graph API using delegated permissions

In order to get Azure AD group image you need to use the following Graph API endpoint:

GET /groups/{id}/photo/$value

If you checked my previous blog post Why you should be careful with /groups/{id}/photo and /users/{id}/photo endpoints in MS Graph or unintentional getting photos of big sizes in Graph then you probably know that it is better to specify group size in endpoint – otherwise you will get biggest available high resolution image for the group. E.g. this is how you may get image with 64x64 px size:

GET /groups/{id}/photos/64x64//$value

However there is another problem with fetching groups images from Graph API which you have to care about: it should be done using delegated permissions. I.e. it is not possible to retrieve AAD groups images from Graph API using application permissions (at least on the moment of writing this blog post).

If you use Graph client library for C# you first need to create GraphServiceClient object and provide instance of class which implements IAuthenticationProvider interface and contains logic for authenticating requests using delegated permissions (via username and password). Here is how it may look like:

public class AzureAuthenticationProviderDelegatedPermissions : IAuthenticationProvider
{
 public async Task AuthenticateRequestAsync(HttpRequestMessage request)
 {
  var delegatedAccessToken = await GetGraphAccessTokenForDelegatedPermissionsAsync();

  request.Headers.Add("Authorization", "Bearer " + delegatedAccessToken);
 }

 public async Task<string> GetGraphAccessTokenForDelegatedPermissionsAsync()
 {
  string clientId = ...
  string userName = ...
  string password = ...
  string tenant = ...
  
  var creds = new UserPasswordCredential(userName, password);
  var authContext = new AuthenticationContext(string.Format("https://login.microsoftonline.com/{0}", tenant));
  var authResult = await authContext.AcquireTokenAsync("https://graph.microsoft.com", clientId, creds);
  return authResult.AccessToken;
 }
}

var graphClientDelegated = new GraphServiceClient(new AzureAuthenticationProviderDelegatedPermissions());

After that we may fetch group image from Graph API like that:

                var stream = Task.Run(async () =>
                {
                    var photo = await graphClient.Groups[groupId].Photos["64x64"].Content.Request().GetAsync();
                    return photo;

                }).GetAwaiter().GetResult();

Actual user account which is used for fetching groups images doesn’t need any special permissions: it may be regular user account without any admin rights.

Problems with Teams creation via beta teams Graph endpoint with owner without O365 license

When you create Team by sending HTTP POST request to beta Graph endpoint /beta/teams (see Create team) you need to specify exactly 1 user as an owner of the new team:

POST https://graph.microsoft.com/beta/teams
Content-Type: application/json
{
  "displayName": "Test",
  "owners@odata.bind": [
    "https://graph.microsoft.com/beta/users('userId')"
  ]
}

where userId is login name of the user which will be owner of the group. However this request may fail with the following error:

Invoking endpoint 'https://graph.microsoft.com/beta/teams/' didn't succeed
Response status code 'Forbidden', reason phrase 'Forbidden'
Response content '
"code": "AccessDenied",
”message": "Failed to execute Templates backend request CreateTeamFromTemplateRequest

It may happen if user which is specified as owner of the team doesn’t have O365 license. In order to avoid this error use users with O365 license as team owners.

Several problems when use Set-AzureADApplication cmdlet with AzureAD app with allowPublicClient = true

In order to be able to use username/password credentials authentication flow with AzureAD app it should have allowPublicClient property set to true. As described in documentation this property won’t affect those OAuth flows which use reply urls:

Specifies the fallback application type. Azure AD infers the application type from the replyUrlsWithType by default. There are certain scenarios where Azure AD cannot determine the client app type (e.g. ROPC flow where HTTP request happens without a URL redirection). In those cases Azure AD will interpret the application type based on the value of this property. If this value is set to true the fallback application type is set as public client, such as an installed app running on a mobile device. The default value is false which means the fallback application type is confidential client such as web app.

However setting it true has several side effects. One of them is that you can’t set this property via Set-AzureADApplication cmdlet as there is no such property (Update 2021-04-22: it is possible to change it using another cmdlets - see Change "Allow public client flows" property of Azure AD apps via PowerShell). There is another property “publicClient” and based on this discussion on github when you set it to true/false it also set to true/false “allowPublicClient”. But if you will try to run Set-AzureADApplication and try to specify API permissions via RequiredResourceAccess

Set-AzureADApplication -ObjectId … –RequiredResourceAccess $permissions -PublicClient $true

you will get error:

Property requiredResourceAccess.resourceAccess is invalid.

It is possible to set allowPublicClient to true from UI in Azure portal > Azure active directory > App registrations > app > Authentication > Default client type > Treat application as a public client = Yes:

But after that it won’t be possible to change API permissions via Set-AzureADApplication cmdlet. The following code:

$tokensApp = Get-AzureADApplication | Where-Object { $_.AppId -eq "..." }
$requiredResourceAccess = $tokensApp.RequiredResourceAccess
$graphPermissions = $requiredResourceAccess | Where-Object { $_.ResourceAppId -eq "00000003-0000-0000-c000-000000000000" }
# add Sites.Read.All delegated permission
$perm = New-Object -TypeName "Microsoft.Open.AzureAD.Model.ResourceAccess"-ArgumentList "205e70e5-aba6-4c52-a976-6d2d46c48043","Scope"
$graphPermissions.ResourceAccess.Add($perm)

Set-AzureADApplication -ObjectId $tokensApp.ObjectId -RequiredResourceAccess $requiredResourceAccess

will thrown the same exception:

Set-AzureADApplication : Error occurred while executing SetApplication
Code: Request_BadRequest
Message: Property requiredResourceAccess.resourceAccess is invalid.
Details: PropertyName - requiredResourceAccess.resourceAccess, PropertyErrorCode - GenericError
HttpStatusCode: BadRequest
HttpStatusDescription: Bad Request
HttpResponseStatus: Completed

Note that the same code works properly if allowPublicClient property is set to false. For now I posted this problem in github here. If you know any workarounds for this problem please share it in comments.

Configurable schedule of timer triggered Azure functions

As you probably know it is possible to create Azure functions which will be triggered automatically by schedule specified by CRON expression. In order to do that create timer-triggered Azure function and specify CRON expression like this:

public static class MyFunc
{
 [FunctionName("MyFunc")]
 public static void Run([TimerTrigger("0 */15 * * * *")] TimerInfo myTimer, TraceWriter log)
 {
  ...
 }
} 

Here we defined function which will run every 15 minutes. But with this approach if you will want to change schedule on production env you will need to recompile the package and re-upload it to Azure. It is more convenient to create schedule configurable. In order to do that use the following syntax:

public static class MyFunc
{
 [FunctionName("MyFunc")]
 public static void Run([TimerTrigger("%MyFuncSchedule%")] TimerInfo myTimer, TraceWriter log)
 {
  ...
 }
}

At the same time in AF app settings you need to have MyFuncSchedule app setting:

{
  "IsEncrypted": false,
  "Values": {
    "MyFuncSchedule": "0 */15 * * * *",
  },
  "Host": {
    "CORS": "*"
  }
}

In this case it will be possible to change timer schedule for your timer-triggered Azure function without re-compiling and re-uploading the package.

Note 1. App setting with schedule should be added before to upload package with compiled Azure functions. Otherwise function won’t run.

Note 2. When you change Azure function app settings – currently running instances will be terminated. After that new instances will use updated app settings.