Issues with invoke-sqlcmd, azure SQL, AAD Auth

[Xander-Rudolph]

This issue is a little convoluted so I'll try to be as clear as possible.

Requirements:

• Run a powershell script to add/remove user permissions from azure sql
• Users must be in the tenant that the Azure SQL Admin is in (cannot use SQL user profile)

When you use the standard invoke-sqlcmd functions, it wont work to add users login/roles because of the tenancy issue.

I have build my own version of invoke-sqlcmd to try to handle this:
Invoke-AzSqlCmd.txt

This works without issue on my windows 10 machine. When I kicked the build off that runs this same script, its running on a linux build box which has .net 3.1 core installed as well as powershell 6 (running as a snapin). When I run the powershell command on the linux VM, the "Authentication=Active Directory Password;" part throws the error:

New-Object: Exception calling ".ctor" with "1" argument(s): "Keyword not supported: 'authentication'."

This is because that isn't valid with the version of the framework. I tried to install the 2.0.0 SQL framework and it throws an error:

Install-Package Microsoft.Data.SqlClient
    Install-Package: Dependency loop detected for package 'Microsoft.Data.SqlClient'.

I tried to install some of the dependencies that are listed under the [Microsoft.Data.SqlClient] package, but I got the same dependency error for those as well.

I tried to install the .net 5.0 beta preview to see if that would do the trick, but it doesn't seem to be registered/recognized in powershell or dotnet.

sudo snap install dotnet-sdk --classic --channel=5.0/beta
dotnet --list-sdks
    3.1.301 [/usr/share/dotnet/sdk]

What do I need to do to get this working?

[karinazhou]

Hi @Xander-Rudolph

I just installed snapd on my Ubuntu 18.04 VM but somehow cannot get Install-Package working from Powershell. I doubt that my package sources are not set up properly. Could you provide some details about how to set up the environment?

And are you using Microsoft.Data.SqlClient in any project from PowerShell or just a single script file as you have attached?

[JRahnama]

@Xander-Rudolph can you confirm what version of Microsoft.Data.SqlClient you have installed on your VM? Active Directory support only added after 2.0.0. Look at here .

I also suggest update the Nuget on your powershell by running:

Find-PackageProvider -Name "Nuget" -AllVersions
Install-PackageProvider -Name "Nuget" -RequiredVersion "<SELECT any Version you want from above list>" -Force

The version is just a sample. Feel free to change it to any newer version.

[Xander-Rudolph]

@JRahnama when I try to use Find-PackageProvider on the linux machine I only have:

• PowerShellGet
• NanoServerPackage
• ContainerImage
• Chocolatier
• WinGet

Again, the VM is a linux ubuntu 16.04 running .net core 3.1.301. From the reasearch I have done, it looks like I need to install core 5.0 preview on my linux machine in order to be able to use the Microsoft.Data.SqlClient 2.0.0 provider.

Reproduction steps are quite simple. Install linux 16.04 on a VM, then run the following commands:

sudo apt update
sudo apt install snapd
sudo snap install powershell --classic
wget https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb -O packages-microsoft-prod.deb
sudo dpkg -i packages-microsoft-prod.deb
sudo apt-get update
sudo apt-get install apt-transport-https -y
sudo apt-get update
sudo apt-get install dotnet-sdk-3.1 -y

Then try to run the script using AAD auth.

[Xander-Rudolph]

@karinazhou I have just provided the provisioning info (sudo commands) to get the linux machine up. The above script is isolated and portable (trying to reduce dependencies). I have also not been able to get the install-package working via snap and when i try to snap in the .net 5.0 preview, I assumed the two would be able to communicate.

To be honest, I'm not exactly sure how to determine what versions of the Microsoft.Data.SqlClient I have locally. This script does work without the AAD auth on the linux machine so I assume its packaged with the .net core version I have.

[cheenamalhotra]

Hi @Xander-Rudolph

Yes, the "dependency loop error" is a known issue OneGet/oneget#475 with Powershell PackageManagement.
To get around it, you will have to install all packages (with dependencies) by specifying -SkipDependencies.

Also see discussion here: #161 (comment)
It'll have to wait for PowerShellGet v3.0 to be released to replace PackageManagement module.

[Xander-Rudolph]

@cheenamalhotra Thanks for letting me know. When I skip the dependencies, it does seem to take care of the install, however I'm still getting the keyword not supported... Even more strange, when I uninstall the snap version of powershell and use the 16.04 ubuntu install (https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell-core-on-linux?view=powershell-7) it lets me run install-package without skip dependencies but I still get the keyword not supported there as well.

[cheenamalhotra]

Active Directory Password authentication is supported for all versions of Microsoft.Data.SqlClient for .NET Core and .NET Standard, so shouldn't complain of 'authenticaiton' keyword if correct DLL is picked from "runtimes" folder. You'll have to thoroughly check all dependencies of installed NuGet package are also installed and that powershell is not loading M.D.S DLL from "lib" folder but from "runtimes" folder.

[cheenamalhotra]

Closing as inactive/not a bug.
Please make sure you're using Microsoft.Data.SqlClient and not System.Data.SqlClient and your application is loading the correct DLL from runtimes folder.