September 26, 2019 at 1:05 pm
Hello All,
I'm trying to write some scripting to help automate database refreshes from Production to Test. As part of this process, we are required to Obfuscate/Mast sensitive data before making available in these lower environments. The application that does the masking requires .NET and the GUI works just fine, and calling the Command Line version of the application from a DOS/Command Prompt works just fine too ; however, when I try to call the application from SQL Server using xp_cmdshell, I keep receiving a .NET error.
Has anyone ever run into this error before on SQL Server and if so, is there a way to bypass/fix it? I even tried to create a batch file on the target machine and then call the application from within the batch file, but same error. I created a quick SSIS package using the 'Execute Script Task' and was able to launch it that way, but it seemed to stall out or taking an inordinate amount of time to execute. I installed the newest version of .NET Framework v4.8 in order to install the application, but it seems likes SQL Server might be using a different version of .NET or something. I've seen this error discussed when programming and scripting stuff, but I haven't run anything with SQL Server so far.
Error Below:
Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified.
September 26, 2019 at 1:34 pm
If executing this via xp_cmdshell then there shouldn't be any limitation. The first thing I would check is the environment. When you open a command prompt it is set up for however your account is configured. But SQL Server doesn't run as your account. It will either be its own account, or Local System, or a service account. I don't think Local System can have its own environment configured (I don't remember off hand as I haven't looked into this in years, but I do remember that there is one built-in account, either Local System or NETWORK that doesn't have its own profile) but most likely whatever SQL Server is running as isn't configured the same. In this case, I am guessing that at the very least the PATH environment variable is missing one or more entries, and possibly also there might be one or more missing environment variables (e.g. INCLUDE, LIB, LIBPATH, NETFXSDKDir, etc).
To check, open a command prompt, verify that your obfuscation app works, then run "SET" to see a list of all your environment variables. Then, in SQL Server, execute:
EXEC xp_cmdshell N'SET';
And compare the output of both. If there are paths and/or entire variables missing from the SQL Server-based list, then find the Logon Account and add those missing items to that account's environment setup.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 26, 2019 at 2:32 pm
Thanks for the advice. I grabbed the SET output from both and put them into Notepad++ using the Compare plugin to help highlight differences and the very first thing I saw was that the Servers were different. This is because the DB I'm masking is on a different server than where the masking software is run. Basically, I'm masking the Test instance and I have the software installed in the DEV instance, so I'm logged on the Dev server and connected via SSMS to the Test instance. As a test, I connected to the DEV instance via SSMS from the Dev server and then tried to run the software again and this time it worked!
So, I guess I have two avenues to explore. First, install the newest framework on the Test instance and see if that helps. If not, then maybe I have to install the software on the Test instance too. The SQL Server Service Account is a domain account and is the same on both instances.
September 26, 2019 at 3:16 pm
Thanks for the advice. ...
So, I guess I have two avenues to explore. First, install the newest framework on the Test instance and see if that helps. If not, then maybe I have to install the software on the Test instance too. The SQL Server Service Account is a domain account and is the same on both instances.
No problem. And yes, if both are installed on the Dev server, then you will need to install both on the Test server since xp_cmdshell executes locally to the server that the instance is running on. The files can exist on a share and need not be physically on that server, but the server (and the Logon Account) will need read access to that share. Still, the process itself will run locally as this is not an RPC call, so NetStandard will need to be installed on the Test server.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
September 27, 2019 at 2:22 am
As a follow up, I installed the same version of the .NET framework on the Server that hold the DB to be masked, and it worked! To recap, I'm connected to TEST via SSMS and using the xp_cmdshell to launch the software on the DEV server, which in turns masks the DB on the TEST instance.
Thanks again for the advice!
Cheers,
Chris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply