I have decided to share some of the daily challenges in my software engineering career. In fact, I have worked on this problem the day I published this article.

Quick Reminder: Don’t forget to subscribe to **my YouTube channel** where you can reach to many up-to-date tutorials on programming languages, frameworks, and my career planing advices.

Background

We have been seeing an issue with the following SQL Command that is executed as part of one of our MSI installers:

CREATE ASSEMBLY RTFTextParser FROM 'C:\TempLocation\..\..\SomeFile.dll'

This piece is actually a part of a hundreds of lines of SQL script that we run. And it is not the only .dll we import to SQL Server. If you have not seen Create Assembly command before, it is to run CLR code from within T-SQL code. And yes… MS SQL Server is pretty powerful from that perspective. Anyways… When we were running this script with Windows (Integrated) Authentication, it was working perfectly. However, it was failing when executed with the SQL username and password authentication. The error we had been seeing, though, was pretty obvious:

CREATE ASSEMBLY failed because it could not open the physical file ‘C:\TempLocation....\SomeFile.dll: 5 — Access Denied

After reading some material on MSDN, it became obvious that the problem was with the service account MS SQL is using. If you look at the SQL Server Service in Windows Services and under Log On tab, you can see what account/role the SQL is using to access your computer resources. Mine was NT Service/MSSQLSERVER.

The fix

In the very simplest terms, the fix required two steps:

1.Giving your SQL login user sysadmin rights, as required by SQL Server for this job.

2.Giving your files/folders READ access rights for the service account as shown below:

These two fixed the problem, at least with manual intervention. But, you cannot expect your clients to be doing this. As engineers, most of the time, our job requires us to develop applications that infer as much information as possible with the minimum amount of given information. This is good for two things:

1. Fewer inputs means fewer checks and fewer errors. That leads us to a more reliable software.

2. If customers enter less data, they will be more satisfied with your product. At the end, the reason why they are paying for your product is to hand over manual tasks to your automated software.

With all these benefits in mind, I developed a class that actually takes care of the following steps for us:

1.I needed a method that gives Read permission for a given serviceAccountName to folders and files I need. The following code did it for me:

private static void GiveReadPermission(string serviceAccount)
{
    var tempPath = Path.Combine(Path.GetTempPath(), "CompanyName", "Framework");
    var di = new DirectoryInfo(tempPath);
    var security = di.GetAccessControl();
    var access = new FileSystemAccessRule(serviceAccount, FileSystemRights.Read, InheritanceFlags.ObjectInherit, PropagationFlags.InheritOnly, AccessControlType.Allow);
    security.AddAccessRule(access);
    di.SetAccessControl(security);
}

By using GetAccessControl() method under DirectoryInfo object, I am accessing the current rights of the temp folder. Later, I am setting its access control with SetAccessControl(). It accepts an object of type FileSystemAccessRule and it is the only that tells what permissions I am giving to what user. The user is defined with serviceAccount parameter. InheritanceFlags.ObjectInherit means, pass the same permissions down to the files, as well. However, you first need to indicate that you want to pass down the permission to the child object by saying PropagationFlags.InheritOnly. After all these explanations, the code must be very straightforward.

Good! But now, I need to get serviceAccount value from somewhere. Remember, the serviceAccount , in my case, is NT Service/MSSQLSERVER , but you cannot hard-code these things. You need it to be flexible enough to work with any service account, right?!

2.Now, let’s get the serviceAccount from the following method. But, remember, the SQL Server Instance can be different, and this method accepts it as a parameter:

private static string GetSqlServiceAccountName(string sqlServerInstanceName)
{
    ManagementObject management = new ManagementObject($"Win32_Service.Name='{sqlServerInstanceName}'");
    management.Get();
    return management["StartName"].ToString();
}

ManagementObject does wonders. It allows you to lots of information about a given Windows Service by passing the name of the information you want to know its value using [] indexer syntax. In my case, management["StartName"] was the Log On As information, meaning the name of the service account, SQL Server service was using to interact with Windows.

3.Like before, this time, sqlServerInstanceName has to come from somewhere. Again, this product needs to be able to work in different client machines, and hard-coding such information into your code is bad! Now, we are getting it from somewhere with the information we have:

private static string GetServiceInstanceName(SqlConnection connection)
{
    using (var command = connection.CreateCommand())
    {
      command.CommandText = "SELECT COALESCE(\'MSSQL$\'+cast(SERVERPROPERTY(\'InstanceName\') as sysname), \'MSSQLSERVER\');";
       var result = command.ExecuteScalar();
       return result.ToString();
    }
}

When you know the instance name of your SQL Server instance, you can get its Windows Service information. By default, SQL Server instances are installed with the name MSSQLSERVER. If you give them a different name, they are called named instances, and you can get its name from the SQL Serer itself by querying select SERVERPROPERTY('InstanceName').If it returns NULL, that means, a custom name was not given, therefore, the name of your instance is the default MSSQLSERVER. The code above does it for me. So I don't need to check if the value comes null to return the default name in my C# code. Again, this way, I didn’t need to get this value from the customer in a text field. The customers usually don’t know how much attention you pay to their needs unless they work with a bad engineer and company. If they do, they will miss you a lot.

4.Now, let’s put them all together. We now depend only on a SqlConnection object. But when you read this code, it looks easier to eyes and exposes its intentions clearly to the next developer.

private static void PrepareDirectoryAccessRightsToInstallerArtifacts(SqlConnection connection)
{
    var sqlServerInstanceName = GetServiceInstanceName(connection);
    var serviceAccountName =
               GetSqlServiceAccountName(sqlServerInstanceName);
    GiveReadPermission(serviceAccountName);
}

All these fixed my issue and helped me start my weekend with a satisfied self.

Conclusion

1. When you see such an error, follow what I did.

2. Make sure that your customers are not exposed to technical areas of your product. Infer as much as you can with the given information by your customers. They will be really grateful.