{"id":86441,"date":"2020-02-21T18:09:24","date_gmt":"2020-02-21T18:09:24","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86441"},"modified":"2024-08-30T14:12:43","modified_gmt":"2024-08-30T14:12:43","slug":"sql-server-machine-learning-2019-working-with-security-changes","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/data-science\/sql-server-machine-learning-2019-working-with-security-changes\/","title":{"rendered":"SQL Server Machine Learning 2019: Working with Security Changes"},"content":{"rendered":"

I have a confession to make. Why, in my last article about shortest_path<\/strong><\/a> in SQL Server 2019<\/em>, have I used Gephi<\/em> in order to illustrate the relationships, instead of using a script in R <\/em>for the same purpose and demonstrate Machine Learning Services as well?<\/p>\n

The initial plan was to use an R script; however, the R script which works perfectly in SQL Server 2017<\/em> doesn’t work in SQL Server 2019<\/em>.<\/p>\n

This is the original R script I had planned to use:<\/p>\n

EXEC\u00a0Sp_execute_external_script<\/strong>\u00a0\r\n\u00a0\u00a0@language\u00a0=\u00a0N'R',\u00a0\r\n\r\n\u00a0\u00a0@script\u00a0=\u00a0N' require(igraph) \r\ng\u00a0<-\u00a0graph.data.frame(graphdf) \r\nV(g)$label.cex\u00a0<-\u00a02\r\npng(filename\u00a0=\u00a0\"c:\\\\R\\\\plot1.png\",\u00a0height\u00a0=\u00a01200,\u00a0width\u00a0=\u00a01200,\u00a0res\u00a0=\u00a0100);\u00a0 \r\nplot(g,\u00a0vertex.label.family\u00a0=\u00a0\"sans\",\u00a0vertex.size\u00a0=\u00a040) \r\ndev.off()\u00a0',\u00a0\r\n\r\n\u00a0\u00a0@input_data_1\u00a0=\u00a0N'select\u00a0LikeMember.MemberName\u00a0as\u00a0LikeMember,\r\n LikedMember.MemberName\u00a0as\u00a0LikedMember \r\nfrom\u00a0dbo.ForumMembers\u00a0as\u00a0LikeMember,\u00a0\u00a0dbo.ForumMembers\u00a0as\u00a0LikedMember,\r\n\u00a0\u00a0Likes\r\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0where\u00a0Match(LikeMember-(Likes)->LikedMember)',\u00a0\r\n\r\n\u00a0\u00a0@input_data_1_name\u00a0=\u00a0N'graphdf'\u00a0\r\n\r\ngo\u00a0<\/pre>\n
The purpose of the script was only to create an image file with the relationships stored in graph tables, but in SQL Server 2019,<\/strong> it results in an access denied message.<\/p>\n
If you would like to reproduce this demonstration, you can use this script file<\/a> to create the database in a SQL Server 2017. You must also install the R packages needed by the script. There are many ways to do it. My favourite is this:<\/p>\n
    \n
  1. Open windows explorer<\/li>\n
  2. Browse to the SQL Server R<\/strong> folder, usually is this one: C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\R_SERVICES\\bin<\/em><\/li>\n
  3. Execute the R.EXE<\/em> application<\/li>\n
  4. Execute the instruction install.packages(“igraph”)<\/em> , where \u201cigraph\u201d is the name of the package to be installed<\/li>\n<\/ol>\n
    When you try and run the above R<\/strong> script. It will work well in SQL Server 2017<\/strong>, and fail in SQL Server 2019<\/strong> with the error message you can see in the image below:<\/p>\n
    \"A<\/p>\n
    During PASS Summit, I was able to ask questions to the Microsoft employees in the Data Clinic, and they explained the differences between the two versions. The explanation hasn\u2019t solved all the problems, but I was able to find a solution and create a workaround for this script.<\/p>\n
    While researching the workaround, I saw many examples of R scripts manipulating files and all these examples may fail in SQL Server 2019. <\/strong>Highlighting this problem, solution, and workaround in this article will be important to everyone using SQL Server Machine Learning<\/em> and planning to migrate to SQL Server 2019.<\/em><\/p>\n
    First, I\u2019ll review how Machine Learning in SQL Server works: it involves the execution of an external script, in R or Python, but has a flexible structure, you could create your own external language. The execution itself is started by a service called SQL Server Launchpad<\/em>, isolating the execution from SQL Server itself. The image below (from What is the Machine Learning Server<\/a>) illustrates how the execution architecture works, although this article will not go so deep into the architecture.<\/p>\n
    \"SQL<\/p>\n
    Launchpad in SQL Server 2017<\/h2>\n
    \nIn SQL Server 2017 the Launchpad starts processes for each execution. The processes need an identity, so SQL Server dynamically creates users inside the group SQLRUserGroup<\/em>. In the following image, you can see two groups, SQLRUserGroup<\/em>, for SQL Server 2019 and SQLRUserGroupSQL2017<\/em> for SQL Server 2017, since I have both in the same machine.<\/p>\n
    \"Groups<\/p>\n
    The difference in the name is due to the instance name; SQL Server 2019 is the default instance in my machine. Due to that, its user group name has only the core name, while SQL Server 2017 is in an instance called SQL2017<\/em>, so the instance name is attached to the core group name.<\/p>\n
    The processes have access to the machine resources, limited by the permissions given to the SQLRUserGroup<\/em>. Since SQL Server 2017 creates many users, you shouldn\u2019t grant permissions directly to the users, only to the group.<\/p>\n
    In order to be able to manipulate files in the file system, you only need to give the correct permissions to the group SQLRUserGroup<\/em>. The image below shows the group and users for SQL Server 2017<\/p>\n
    \"SQLRUserGroup<\/p>\n
    Launchpad in SQL Server 2019<\/h2>\n
    In SQL Server 2019, the Launchpad was improved. Instead of using processes for each execution, it uses app containers. The app containers need only a single identity, so they use the same identity as the Launchpad service.<\/p>\n
    While the 2017 version, the SQLRUserGroup, had many users; in the 2019 version, it has only one, the Launchpad service account. The image below shows the group for SQL Server 2019.<\/p>\n
    \"SQL<\/p>\n
    Nowadays, when talking about containers immediately, we think about Docker, but not in this case. The app containers I\u2019m mentioning here are a sandbox set of APIs introduced in Windows 8. These allow any application to create app containers and start an execution inside an app container.<\/p>\n
    The app containers work as isolated virtual machines (but much more lightweight, of course). This means that you can’t access the file system of the host (your SQL Server), neither for reading or to save files. You save any files inside the app container, and they are destroyed after the app container is released. In fact, the container is redirected to save files in a temporary folder inside the host hard drive. This increases the security, no doubt about that, but any script dealing with the file system may need changes to work in SQL Server 2019.<\/p>\n
    These app containers are created by Launchpad using the windows Sandbox API<\/em>. The Launchpad doesn\u2019t offer any way to customize the app containers creation, which could solve the problem.<\/p>\n
    You can learn more about app containers with these two links:<\/p>\n
    https:\/\/techcommunity.microsoft.com\/t5\/Windows-Kernel-Internals\/Windows-Sandbox\/ba-p\/301849<\/a> 
    \n    https:\/\/www.malwaretech.com\/2015\/09\/advanced-desktop-application-sandboxing.html<\/a><\/p>\n
    Proving this Concept<\/h2>\n
    In order to determine this concept, you can use a script in R to access the disk and list the files. You can execute the script below in both SQL Server versions, 2017 and 2019, and compare the results. In SQL Server 2017, the script will have access to the host disk. In SQL Server 2019, the script will have access only to a fake disk, which is, in fact, a temporary folder in the host disk and will be deleted after its use by the app container.<\/p>\n
    EXEC\u00a0Sp_execute_external_script<\/strong>\u00a0\r\n\u00a0\u00a0@language\u00a0=\u00a0N'R',\u00a0\r\n\u00a0\u00a0@script\u00a0=\u00a0N'data\u00a0<-\u00a0list.files() \r\n            data2\u00a0<-\u00a0data.frame(data)',\u00a0\r\n\u00a0\u00a0@output_data_1_name\u00a0=\u00a0N'data2'\u00a0\r\n\r\nEXEC\u00a0Sp_execute_external_script<\/strong>\u00a0\r\n\u00a0\u00a0@language\u00a0=\u00a0N'R',\u00a0\r\n\u00a0\u00a0@script\u00a0=\u00a0N'data\u00a0<-\u00a0list.dirs(\"..\\\\\") \r\n             data2\u00a0<-\u00a0data.frame(data)',\u00a0\r\n\u00a0\u00a0@output_data_1_name\u00a0=\u00a0N'data2'\u00a0\r\n\r\nEXEC\u00a0Sp_execute_external_script<\/strong>\u00a0\r\n\u00a0\u00a0@language\u00a0=\u00a0N'R',\u00a0\r\n\u00a0\u00a0@script\u00a0=\u00a0N'data\u00a0<-\u00a0list.dirs(\"..\\\\..\") \r\n            data2\u00a0<-\u00a0data.frame(data)',\u00a0\r\n\u00a0\u00a0@output_data_1_name\u00a0=\u00a0N'data2'\u00a0<\/pre>\n
    The image below the script shows the result in SQL Server 2019.<\/p>\n
    \"Script<\/p>\n
    Solutions<\/h2>\n
    The app containers are created during the SQL Server Machine Learning services setup. They are objects inside the Windows local directory, and such as all the objects in the local directory, they have a unique SID<\/em> to identify the app container.<\/p>\n
    You can define file system permissions directly for the SIDs. The problem is: How to identify the SID\u2019s of the app containers since they are not listed as user or groups.<\/p>\n
    Analyse two possible solutions:<\/p>\n
      \n
    1. Easy and tempting: You can give permission to the object called All Application Containers<\/em>. The R scripts will have the file system permission; however, any other app container eventually running on the same machine will have the file system permission as well.<\/li>\n
    2. Secure: You can identify the SIDs of the app containers installed by SQL Server and give permissions directly to them. In this way, only the app containers used by the SQL Server Machine Learning Services will receive these permissions<\/li>\n<\/ol>\n
      You can test both solutions using a straightforward script. First, create a folder called C:\\testFolder<\/em> and copy some files, any files, to the folder. Try to list the files in this folder using the script below.<\/p>\n
      EXEC Sp_execute_external_script \r\n  @language = N'R', \r\n  @script = N'data <- list.files(\"c:\\\\testFolder\") \r\n            data2 <- data.frame(data)', \r\n  @output_data_1_name = N'data2'<\/pre>\n
      All Application Containers<\/h3>\n
      The All Application Container object has a fixed SID in the local directory, which is S-1-15-2-1 . You can use the application icacls <\/em>to grant permission<\/p>\n
        \n
      1. Execute the R script above against the 2019 instance using SSMS. The script will return no result<\/li>\n
      2. Press Win+R<\/em> and type CMD<\/em> to open a command prompt as administrator<\/li>\n
      3. Type the following instruction and press ENTER<\/em><\/li>\n
        icacls c:\\testFolder \/grant *S-1-15-2-1:(OI)(CI)F \/t<\/pre>\n
      4. Using Windows Explorer, right-click the testFolder<\/em> and select Properties<\/em> in the context menu<\/li>\n
      5. In the Properties<\/em> window, click the Security<\/em> tab. You will be able to confirm the permission, like the image below.<\/li>\n
      6. \u00a0<\/li>\n
        \"TestFolder<\/p>\n
      7. Open SQL Server Configuration Manager<\/em><\/li>\n
      8. On the left side of SQL Server Configuration Manager<\/em>, select SQL Server Services<\/em><\/li>\n
      9. In the right-side of SQL Server Configuration Manager<\/em>, right-click the Launchpad<\/em> service and select Restart<\/em> item in the context menu<\/li>\n
      10. Execute the same R script again in SSMS. This time it will work and list the files in the folder.<\/li>\n
        \"Script<\/p>\n
      11. Delete the testFolder<\/em> folder so that you can continue with the other solution below<\/li>\n<\/ol>\n
        Using the App Containers SID<\/h3>\n
        The challenge you will face when giving permissions to the App Containers SID is to discover which are the App Containers SID. There is not a direct solution for that, although, after knowing where to look, it becomes easy. SQL Server installs firewall rules in Windows Firewall in order to forbid app containers from making external contact to the network. These firewall rules are created precisely to block the app containers SID from using the network, so you can identify these SIDs by analysing these firewall rules.<\/p>\n
        If you look at the firewall rules, you can see that the app containers are blocked.<\/p>\n
        \"Firewall\"<\/p>\n
        The firewall UI doesn\u2019t provide the SIDs, however. You need to dig deeper using PowerShell to retrieve the SIDs from the firewall rules.<\/p>\n
        The PowerShell command is a combination of the cmdlet Get-NetFirewallRule<\/code>, to retrieve the firewall rules and the cmdlet Get-NetFirewallApplicationFilter<\/code> in order to retrieve, from each rule, the app container filter information.<\/p>\n
        The complete PowerShell command line is this:<\/p>\n
        (Get-NetFirewallRule | Where-Object { ($_.Direction -eq \"Outbound\") -and ($_.DisplayName -like \"*appcontainer*\")}) | %{ ($_ | Get-NetFirewallApplicationFilter) | %{Write-Output $_.Package } } <\/pre>\n
        Analyse this command line in more detail:<\/p>\n
          \n
        1. Get-NetFirewallRule<\/code> will retrieve all the firewall rules in Windows Firewall<\/li>\n
        2. Where-Object<\/code> filters the firewall rules, retrieving only the outbound rules which contains \u2018appcontainer\u2019<\/em> in the name<\/li>\n
        3. The expression %{ }<\/code> is a shortcut to foreach-object<\/code> so that the instructions will be executed for each firewall rule<\/li>\n
        4. Get-NetFirewallApplicationFilter<\/code> is executed for each firewall rule, having the firewall rule ($_ )<\/code> as a parameter<\/li>\n
        5. The expression % { }<\/code> is used again to run one line for each application filter found<\/li>\n
        6. The Write-Output<\/code> shows the value of the Package<\/code> property on each application filter<\/li>\n<\/ol>\n
          \"SIDs\"<\/p>\n
          Having discovered the SIDs, you need to set the permission to the folder. The instruction will look like the one below for each SID.<\/p>\n
          ICACLS \"C:\\testFolder\" \/grant \"*S-1-15-2-1853514363-3573294594-1452771951-225645021-3819702349-824866239-3302992986\":(OI)(CI)F \/t<\/pre>\n
          You could copy and paste all the sids returned to create a batch file to set the permissions, but PowerShell can do all of this you as well. The following script prompts you for a file path, the one where you want Machine Learning to have access, and sets the permissions:<\/p>\n
          Param (\r\n    [Parameter(\r\n        Mandatory)]\r\n        [string]\r\n        $FolderName\r\n    )\r\nGet-NetFirewallRule |\r\n    Where-Object {\r\n        $_.Direction -eq \"Outbound\" -and\r\n        $_.DisplayName -like \"*appcontainer*\"\r\n        } |\r\n    ForEach-Object {\r\n        $_ |\r\n        Get-NetFirewallApplicationFilter | \r\n        ForEach-Object {\r\n            $Acl = Get-Acl $FolderName\r\n            $Sec= New-Object System.Security.Principal.SecurityIdentifier($_.Package)\r\n            $AccessRule = New-Object System.Security.AccessControl.FileSystemAccessRule(\r\n                $Sec,\r\n                \"FullControl\",\r\n                (1 -bor 2),\r\n                0,\r\n                \"Allow\"\r\n                )\r\n            $Acl.SetAccessRule($AccessRule)\r\n            $Acl |\r\n                Set-Acl $FolderName\r\n            } # end 2nd FE-O\r\n        } # end 1st FE-O <\/pre>\n
          The script is using COM objects System.Security.Principal.SecurityIdentifier<\/code> and System.Security.AccessControl.FileSystemAccessRule<\/code> in order to set the access rule on the folder.<\/p>\n
          After running the script, take a look at the security properties of the folder, and the R script should now work from SQL Server 2019.<\/p>\n
          \"TestFolder<\/p>\n
          Workaround with a FileTable<\/h2>\n
          Although I have identified two solutions, it\u2019s interesting to notice Microsoft is moving SQL Server Machine Learning towards a more secure environment, avoiding network and file system access. Considering this, you may like to use an alternative solution.<\/p>\n
          There are some options, all based on returning the image as an output parameter to SQL Server. Here is one of the options.<\/p>\n
          A filetable can map a disk folder as a table in SQL Server. This a very good solution when you need to deal with files. The script in R will see the table as a regular table. While reading or inserting into the table, the script will be reading and inserting from\/to the disk.<\/p>\n
          It’s a very good solution for a permanent environment, however, it has some problems:<\/p>\n