Pandas 0.19 Read_Excel and UNC Addresses

Question

I have a SQL Server v2017 at work. When they installed machine learning it installed Python 3.5 with Pandas 0.19. I am trying to use read_excel on a file on a network drive. I can run the script on my local machine, but I have Python 3.9 and Pandas 1.35. The Script works fine locally but not when executed through the Server using EXECUTE sp_execute_external_script. I realize there could be a huge number of things that coul dbe causeing problems, but I need to rule out Pandas version first. The server is locke own adn it takes a lot of red tape to change something.

Can Pandas 0.19 read_excel access excel files on a UNC address. I know the newer version can, but this would help me rule out the Pandas library as a source for the issue.

Answer 1

(I work for MS and I support SQL ML Services)

The short answer to your question is -

You will have a hard time accessing a UNC path in ML Services. It is technically possible, but the complications make it a no-go for many. You didn't show your code or errors, but I can assure you that your problem isn't with pandas, and perhaps you got an error about not being able to 'connect' because we disable outbound network traffic from ML services by default... but if you got past that, then you probably got an authentication error.

The long answer to your question is -

SQL 2016 and 2017 - We use local 'worker' accounts. The default names (they are based on your instance name) are MSSQLSERVER01,02,03... 20. (There are 20 by default... there is also a MSSQLSERVER00, but we'll ignore that one).

The Launchpad service is ran by it's service account (default: NT Service\MSSQLLaunchpad), and it can be ran as a domain account. But, it is not launchpad that is actually executing your R/Python code. Launchpad kicks off the R process, and it does this under the MSSQLSERVERXX users. It is THAT user that is technically running your code, and therefore, it is that user that is trying to connect to your UNC path and not YOUR user that you are logged into SQL as. This user is a local user - which cannot authenticate across a UNC share. This issue comes down to a design limitation.

In Windows, there is no way to provide a username/password in your UNC path (whereas, in Linux, you can). Using a mapped drive will not work because those are local-to-your-user-and-login-session. Therefore, a mapped drive of one logged in user will not be accessible to other users (and therefore the MSSQLSERVERXX users).

In short, if you absolutely wanted to make it work, you would have to disable authentication entirely on your network share. In Windows, this is more than just adding "EVERYONE" permissions to the file. You would also have to allow GUEST (or in the *nix world, ANONYMOUS) access to file shares. This is disabled by default in all recent Windows versions and you would have to modify various gpos/registry settings/etc to even allow that. It would not be my recommendation.

If this were in an AD environment, you could also theoretically allow the COMPUTER account of your SQL host so that ALL connections from THAT "COMPUTER" would be allowed. Again, less than ideal.

In SQL 2019 - we got rid of the local user accounts, and use appcontainers instead. This removes the need for local user accounts (many customers in large organizations have restrictions on local user accounts), and offers additional security, but as always, with more security comes more complexity. In this situation, if you were to run the launchpad service as a domain user, your R/Python processes ARE executed as the LAUNCHPAD account (but in a very locked down appcontainer context). Theoretically, you could then grant THAT service account in AD access to your remote UNC share... BUT, appcontainers provide a far more granular control of specific 'permissions' (not file level permissions). For example, at least conceptually, when you are using an app on your phone, or perhaps a Windows store UWP app, and it asks 'do you want to allow this to access your camera?" - those layer of permissions are something that appcontainers can provide. We have to explicitly declare individual 'capabilities', and we do not currently declare the ability to access UNC shares due to several other security implications that we must first consider and address. This too is a design limitation currently.

The above possibilities for SQL 2016/2017 do not apply, and will not work, for SQL 2019.

However, for all of them, while it may not be ideal, my suggestion and your best option is:

• Reconsider which direction you are doing this. Instead of using your SPEES (sp_execute_external_scripts) code to access a network share, consider sharing out a directory from the SQL host itself... this way, you at least don't have to allow GUEST access, and can retain some level of permissions. Then you can drop whatever files you need into the share, but then access it via the local-to-that-host path (ex: C:\SQL_ML_SHARE\file.xel) in your SPEES code.