Mystery of MSSQLSERVER~ Windows Users in SQL Server 2016

The estimated reading time for this post is 2 minutes

I came across to install and configure SQL Server 2016 and found there are many MSSQLSERVER windows users are created in Windows Local Users. Therefore I start to do some Bing search but unfortunately found nothing related to this. I will explain what are those local users and what is the use of them.

SQL Server 2016 has In-Database Analytics (R Language) feature which performs data analytics by using R language against the relational data.

intelligence-db

As you know R engine is separated and it is not built-in into the database engine and needs to externally runs by other service called ‘SQL Server Launchpad’. SQL Server Launchpad is part of new SQL Server architecture called ‘Extensibility Framework’ as well, which SQL Server and R work together is by using a framework we call the extensibility architecture. Previously, CLR or extended stored procedures would enable you to run code outside the constructs of SQL Server, but in those cases, the code still runs inside the SQL Server process space. Having external code running inside the SQL Server process space can cause disruption  and it is also not possible to legally embed runtimes that are not owned by Microsoft.

Extensibility architecture that enables external code, so far R programs (in SQL Server 2017 R + Python programs), to run, not inside the SQL Server process, but as external processes that launch external runtimes.

If you install SQL Server with R Services, you will be able to see the new Launchpad service in SQL Server configuration manager.

Now, what is the relation between Launchpad and MSSQLSERVER~ windows local users? so far you understand what is the Launchpad’s responsibility, therefore every single external process needs to have different windows credentials to use. When you execute the stored procedure sp_execute_external_script, SQL Server database engine connects to the Launchpad service using a named pipe and send a message to that service telling it WHAT to run and HOW (what parameters). The number of individual windows local accounts are limited to maximum 100 users and it is configurable in Launchpad’s properties advance tab.

Once you change the value and restart the Launchpad service, the count of MSSQLSERVER windows local users will change as well and SQL Server Launchpad service will create additional folders in ExtensibilityData directory. In this case, I already change the value to 25.

Launchpad has a registration mechanism for launchers specific to a runtime/language. Based on the script type, it will invoke the corresponding launcher which handles the duties for invoking and managing the external runtime execution. This launcher creates a Satellite process to execute our R Scripts. The Satellite process has a special DLL (RLauncher.dll) that knows how to exchange data with SQL Server to retrieve input rows/parameters and send back results and output parameters. Multiple of these processes can be launched to isolate users from each other and achieve better scalability.

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

1 Comment on "Mystery of MSSQLSERVER~ Windows Users in SQL Server 2016"

Notify of
avatar
1000
Sort by:   newest | oldest | most voted
Amin Karimi
Guest

Thanks a lot. Useful tip 😉

wpDiscuz