Monday, February 17, 2020

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.

Tuesday, February 11, 2020

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.

Friday, January 24, 2020

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.

Tuesday, January 21, 2020

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.

Friday, January 17, 2020

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.