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.