Azure Active Directory Password for sql server

Question

Hi Team,

Please help me with the powershell syntax for connecting sql server using Azure Active Directory Password for sql server ..Invoke-cmd doesnt seems to support this.

Regards,
ACDBA

Answer 1

Hi @ACDBA , welcome to Microsoft Q&A forum.

Through Invoke-sqlcmd if we try to connect with Azure Sql Server using 'Azure Active Directory Password' authentication, it would throw the error as 'Invoke-Sqlcmd: Keyword not supported: 'authentication'.' as authentication is not supported in PowerShell version (please refer to article). Below is the connection string I used:

$ConnectionString = "Server=yourserver.database.windows.net;Authentication=Active Directory Password;Initial Catalog=yourdatabase;UID=xxxxxx;PWD=xxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=60;"

However, Microsoft introduced a package Microsoft.Data.SqlClient which can be used through PowerShell and you can connect using 'Azure Active Directory Password'. (refer to article). Below is the code used to connect and it worked fine:

Install-Package Microsoft.Data.SqlClient  

$dllPath = "C:\Program Files\PackageManagement\NuGet\Packages\Microsoft.Data.SqlClient.2.0.0\runtimes\win\lib\netcoreapp2.1\Microsoft.Data.SqlClient.dll"  
Add-Type -Path $dllPath  
$con = New-Object Microsoft.Data.SqlClient.SqlConnection  
 $con.ConnectionString =  "Server=yourserver.database.windows.net;Authentication=Active Directory Password;Initial Catalog=yourdatabase;UID=xxxxxx;PWD=xxxx;Trusted_Connection=False;Encrypt=True;Connection Timeout=60;"  
$con.Open()  
$query = "select * from test"  
Add-Type -AssemblyName System.Data  
$cmd = New-object Microsoft.Data.SqlClient.SqlCommand($query,$con)  
$ds = New-Object system.Data.DataSet (New-Object Microsoft.Data.SqlClient.SqlDataAdapter($cmd)).fill($ds) | out-null  
$ds.Tables[0]  
$con.Close()  

We need to add few dependencies as well for Microsoft.Data.SqlClient.dll which you can refer from below:

1. Microsoft.Data.SqlClient.SNI.dll(https://www.nuget.org/packages/Microsoft.Data.SqlClient.SNI.runtime/)
2. Microsoft.Identity.Client.dll(https://www.nuget.org/packages/Microsoft.Identity.Client/)

Just copy paste these dll in the same folder. Also if there is version issue, download that specific version.

Below articles are used for reference:

1. https://github.com/dotnet/SqlClient/issues/623
2. https://gist.github.com/MartinHBA/86c6014175758a07b09fa7bb76ba8e27

Please let me know if you still face any issues and we can discuss further.

----------

if answer helps, please select 'Accept Answer' as this could help other community members looking for similar queries.