Repro steps:

1. Create a table with a clustered index
2. Try to encrypt a column in this table using UseOnlineApproach
3. The encryption fails with the error similar to "Set-SqlColumnEncryption: Cannot create more than one clustered index on table 'HR.tmp_ms_xx_xxxxxxxxxxxxx'. Drop the existing clustered index 'tmp_ms_xx_index_CIX_EmpDetails_EmpId1' before creating another."

PR - https://msdata.visualstudio.com/SQLToolsAndLibraries/_git/AlwaysEncrypted/pullrequest/1054777

Prerequisites

• Write a descriptive title.
• Make sure you are able to repro it on the latest released version
• Search the existing issues.
• Refer to the FAQ.
• Refer to Differences between Windows PowerShell 5.1 and PowerShell.

Steps to reproduce

I am trying to run a local development environment setup script for my solution that includes the use of the SqlServer module to make changes. It uses the following command to connect to the default SQL instance:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT

This worked on a previous laptop, but does not work on my newly issued laptop. I don't have access to the old laptop anymore to isolate differences.

Expected behavior

The `Set-Location` command should connect successfully to the database to allow the subsequent actions to complete.

Actual behavior

The following exception is thrown in response to `Set-Location SQLSERVER:\SQL\localhost\DEFAULT`:


Set-Location: C:\src\repos\MYPATH\setup-database.ps1:7
Line |
   7 |  Set-Location SQLSERVER:\SQL\localhost\DEFAULT
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | SQL Server PowerShell provider error: Could not connect to 'localhost\DEFAULT'. [Could not load type
     | 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral,
     | PublicKeyToken=b77a5c561934e089'.]

While troubleshooting I also found that using Set-Location SQLSERVER:\SQL followed by Get-ChildItem as described in the Microsoft docs (https://learn.microsoft.com/en-us/sql/powershell/navigate-sql-server-powershell-paths?source=recommendations&view=sql-server-ver16) also produces an error:

PS SQLSERVER:\SQL> set-location SQLSERVER:\SQL
PS SQLSERVER:\SQL> Get-ChildItem
WARNING: Could not obtain SQL Server Service information. An attempt to connect to 'INT-P16-TJP' failed with the following error: Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.

### Error details

```console
PowerShell 7.3.3
PS C:\Users\thomas.parikkaADM> Import-Module SqlServer
PS C:\Users\thomas.parikkaADM> Set-Location SQLSERVER:\SQL
PS SQLSERVER:\SQL> Get-ChildItem
WARNING: Could not obtain SQL Server Service information. An attempt to connect to 'INT-P16-TJP' failed with the following error: Could not load type 'Microsoft.SqlServer.Server.SqlContext' from assembly 'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
PS SQLSERVER:\SQL> Get-Error
PS SQLSERVER:\SQL>

### Environment data

```powershell
PS C:\> $PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.3.3
PSEdition                      Core
GitCommitId                    7.3.3
OS                             Microsoft Windows 10.0.19045
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Visuals

No response

Current version of the module has a rather fragile logic around "isolation" and "binding redirection".

A cmmon problem I faced in v22 is the interaction of the SqlServer module with some of the Az.* modules, where the order of the Import-Module makes a great difference and may cause unpredictable results are runtime (note: results and failures are also PS5 and PS7 specific, typically... meaning that some things may work in PS5 and fail in PS7, and vice versa)

There is ample literature on the subject, for example:

• https://learn.microsoft.com/en-us/powershell/scripting/dev-cross-plat/resolving-dependency-conflicts
• https://github.com/rjmholt/ModuleDependencyIsolationExample/blob/master/new/JsonModule.Cmdlets/DependencyAssemblyLoadContext.cs
• https://github.com/daxian-dbw/PowerShell-ALC-Samples/blob/main/Resolving-Event-with-ALC/src/SampleModule/Commands/CustomALC.cs

The cost and the intricacies to get is properly implemented look like a monumental effort that would require a pretty serious redesign/refactoring of the whole module (and, quite honestly, all the other existing modules that SQLServer module would happen to interoperate with).

Furthermore, I suspect that for PS5 things may be a lot harder to get implemente cleanly and properly, due to the fact that all the new stuff in .Net are only available in .Net Core. So, perhaps, this tasks would have to be deferred to the day when the SQLServer module will be PS7-only.

This is something that was introduced in Microsft.Data.SqlClient 5.x, so perhaps it would be good to add it to Invoke-Sqlcmd (if applicable).

Similar to the StrictEncryption option, in a way.

We need to move forward and also support existing cmdlet (Read-SqlXEvent) which moved already to MDS4.x

Long term, we want to move to MDS 5.x - and bite the bullet around the new defaults for TrustServerCertificate and the new Encrypt options (Strict/Optional/Mandatory) to support TDS8.0 and such.

It would be great to have it by the time v22 gets out of Previews.

1. On PowerShell7, import v22.0.59
2. Run the following script

    Convert-UrnToPath 'RegisteredServersStore/ServerGroup[@Name=''DatabaseEngineServerGroup'']/ServerGroup[@Name=''G1'']'

3. Result

   Operation is not supported on this platform.

Either I'm publishing the module incorrectly or the PSGallery is doing something it should not.

Contrast the output of the following cmdlets:

PS C:\Users\matteot> Get-Module SQLServer -ListAvailable | Select Author,CompanyName

Author                CompanyName
------                -----------
Microsoft Corporation Microsoft Corporation

PS C:\Users\matteot> Find-Module sqlserver -AllowPrerelease  | Select Author,CompanyName

Author                CompanyName
------                -----------
Microsoft Corporation matteot_msft

This issue was reported by https://twitter.com/pitoach/status/1525917723724857345.

Would be awesome to have Enable-SqlAlwaysOn and Disable-SqlAlwaysOn in v22

The SQL Powershell path provider times out when attempting to remotely access instances running on ports other than 1433.

Measure-Command { Get-Item 'SQLSERVER:\SQL\PrimaryServer\DEFAULT' } | Select-Object -ExpandProperty TotalSeconds
# Get-Item : Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to 'PrimaryServer\DEFAULT'. [Failed to connect to server PrimaryServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --> Access is denied]
# 117.6668215

Measure-Command { Get-Item 'SQLSERVER:\SQL\PrimaryServer\DEFAULT\Databases' } | Select-Object -ExpandProperty TotalSeconds
# Get-Item : Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to 'PrimaryServer\DEFAULT'. [Failed to connect to server PrimaryServer. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) --> Access is denied]
# 205.5115042

Accessing the root path is fast and Invoke-SqlCmd connects to the instance without issue

Measure-Command { Get-Item 'SQLSERVER:\SQL\PrimaryServer' } | Select-Object -ExpandProperty TotalSeconds
# 0.0078557

Measure-Command { Invoke-SqlCmd -ServerInstance 'PrimaryServer,1455' -Query 'select @@version' } | Select-Object -ExpandProperty TotalSeconds
# 0.060131

There's no issue when accessing the instance from the host itself

Measure-Command { Get-Item 'SQLSERVER:\SQL\localhost' } | Select-Object -ExpandProperty TotalSeconds
# 0.0086672

Measure-Command { Get-Item 'SQLSERVER:\SQL\localhost\DEFAULT' } | Select-Object -ExpandProperty TotalSeconds
# 0.0191936

Measure-Command { Get-Item 'SQLSERVER:\SQL\localhost\DEFAULT\Databases' } | Select-Object -ExpandProperty TotalSeconds
# 0.0171693

Remotely accessing both default and named instances running on port 1433 works without issue.

Is there something I can reconfigure to work around this issue?

It would be beneficial to allow passing an entire and not just the Token.

Not that the Invoke-Sqlcmd uses the metadata that comes with the whole AccessToken (like ExpireOn, UserID, etc...), but it would save the trouble of having to .Token on it + make it consistent with the other cmdlets that (in v22) are happy to take a full AccessToken.

Is it possible to use an AAD managed identity with Invoke-Sqlcmd? I tried with a connection string ($connectionString = "Server=${sqlServer};Authentication=Active Directory Managed Identity;Database=${database};MultipleActiveResultSets=True") on a deployment script that have the SQL Server Admin identity assigned to it. That throw System.ArgumentException: Keyword not supported: 'authentication'. I also tried using a token that I retrieved this way:

$credentials = Get-AzAccessToken -ResourceUrl https://database.windows.net
$token = $credentials. Token

I can use that access token to login but when I try to assign another identity to a database (CREATE USER [${user}] FROM EXTERNAL PROVIDER) I instead get an error that the user couldn't be retrived and I need to assign Directory Readers to the identity, which I already have. This is the full error code:

Principal '<user>' could not be resolved. Error message: 'Server identity is not configured. Please follow the steps in "Assign an Azure AD identity to your server and add Directory Reader permission to your identity" (https://aka.ms/sqlaadsetup)'

Is this module using a version of System.Data.SqlClient/Microsoft.Data.SqlClient where this simply isn't yet supported perhaps?

Hi,

I've installed version 22.0.59 of this module, and I can't seem to use the Start-SqlInstance or Stop-SqlInstance commands. They are in the module manifest, but when I go to use them, PowerShell says they aren't recognized. Get-SqlInstance and many other commands in the module seem to work fine.

Here's a screenshot.

I am trying to execute "Invoke-SqlCMD" PSCmdlet (v21.1.18256) via a C# project (targeting .NET 7.0), however it is throwing an error:

C#:

using System.Management.Automation;

using var ps = PowerShell.Create(RunspaceMode.NewRunspace);
ps.AddScript(
"""
Import-Module SQLServer

$query = "select 123";
$CONNECTION_STRING = "Server={SERVER};Database={DB};User Id={USER};Password={PWD};";

$params = @{
    OutputSqlErrors = $true
    IncludeSqlUserErrors = $true
    Verbose = $true
    Variable = $vars
    ConnectionString = $CONNECTION_STRING
    Query = $query
}

Invoke-SqlCMD @params
""");

var output = ps.Invoke();

Console.WriteLine(output.Count());
Console.WriteLine(ps.Streams.Error.FirstOrDefault()?.Exception);

Output:

0
System.PlatformNotSupportedException: System.Data.SqlClient is not supported on this platform.
   at System.Data.SqlClient.SqlConnection..ctor(String connectionString)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.CreateSqlConnection()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor..ctor(GetScriptCommand sqlCmdCmdLet)
   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()

If I navigate to the folder that the "SqlServer" PS module was installed in %userprofile%\Documents\PowerShell\Modules\SqlServer\21.1.18256\, and I inspect coreclr\System.Data.SqlClient.dll I see that there are PlatformNotSupportedException's hardcoded in the SqlConnection constructor. Looks like the assembly version of this DLL is 4.6.27618.01.

The strange thing is if I execute the same PS script from within a PS core (v7.3.3) terminal directly (on the same machine), then it executes w/o issue. So I'm assuming there are some binding redirects going on behind the scenes, but the same binding redirects are not happening when using System.Management.Automation.PowerShell from my .NET project.

I did end up trying to download the 4.6.1 System.Data.SqlClient package from Nuget, which had the same assembly version as the one mentioned above, and then I override the one in the coreclr folder with the one from the nuget package in the runtimes\win\lib\netstandard2.0 directory. This got me quite a bit further, but then ran across a different error:

System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.
 ---> System.TypeInitializationException: The type initializer for 'System.Data.SqlClient.SNILoadHandle' threw an exception.
 ---> System.DllNotFoundException: Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)
   at System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize(IntPtr pmo)
   at System.Data.SqlClient.SNINativeMethodWrapper.SNIInitialize()
   at System.Data.SqlClient.SNILoadHandle..ctor()
   at System.Data.SqlClient.SNILoadHandle..cctor()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.TdsParserStateObjectFactory.get_EncryptionOptions()
   at System.Data.SqlClient.TdsParser..cctor()
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.TdsParser..ctor(Boolean MARS, Boolean fAsynchronous)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteBatch(String batch)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ProcessBatch(String str, Int32 num)
Error: : Microsoft.SqlTools.ServiceLayer.BatchParser.BatchParserException: Incorrect syntax was encountered while parsing ''.
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.RaiseError(ErrorCode errorCode, Token token, String message)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.RaiseError(ErrorCode errorCode, String message)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ExecuteBatch(Int32 repeatCount)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ParseLines()
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.Parse()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(String sqlCommand)

So as you can see, it got a lot further, but still having some issues. I ended up adding a project reference to System.Data.SqlClient v4.6.1, and then it started working w/o any exceptions. Out of curiosity, I reverted the DLL I changed in the coreclr folder back to the old one, and ran the project again, and it worked which probably means the binding redirects in place now (just a guess).

I guess my main point here is why does the PS Module ship with a System.Data.SqlClient DLL that throws the PlatformNotSupportedException, when it should just be loading the correct .NET Standard 2.0 lib? I know my use case is probably a little special, but I really had to dig and trail-and-error to figure out what needed to be done. Overall, I'd expect that this same powershell script that I used in PS Core also works from C# PS Automation, especially since they're both executing on the same machine.

We could consider improving the area that's covered with SQL Server Configuration Manager, (as in #4), e.g., add/remove startup parameters (like trace flags etc) and so on.

Alternatively, it could be useful to have a TF-specific cmdlet that executes TRACEON/TRACEOFF queries, and optionally apply the change for startup TF params

https://www.powershellgallery.com/packages/SqlServer/22.0.59 has a changelog that links here but there's no recent commits. Can anyone provide more detail? We had some issues using it and wanted to check we were holding it right.

In it's most basic form, if I try running the following in Windows Terminal (latest Powershell 7.3.5):

Invoke-SqlCmd -Query "select 1" -TrustServerCertificate

It can cause either of the following errors:

Invoke-Sqlcmd: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: HTTP Provider, error: 0 - )
Invoke-Sqlcmd: Incorrect syntax was encountered while parsing ''.

or

Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Repeat 2 times:
--------------------------------
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO ByRef, IntPtr ByRef)
...

Windows: 11 (22H2)
Powershell: 7.3.5
SqlServer powershell module: 22.1.1
SQL Develop Edition: 2022 (RTM-GDR) (KB5021522) - 16.0.1050.5 (X64)
.NET SDKs installed:

• 6.0.313
• 6.0.410
• 7.0.107
• 7.0.203
• 7.0.304

Shared Memory and TCP/IP are both enabled in SQL Server Configuration Manager and the local SQL instance is configured to allow remote connections and I can easily connect to it In SSMS and Azure Data Studio without any issues using "localhost", "(local)" or using ip "127.0.0.1,1433".

Have tried downgrading to older versions of the SqlServer powershell module - last time I had it all working a few weeks ago I was on v 21.1.18256 but even this now fails.

Anyone got any ideas what's going on here?

Run the following cmdlet:

invoke-sqlcmd -ServerInstance SQLTOOLS2022-3  -DisableVariables -Query "SELECT N'  ''`$(ESCAPE_SQUOTE(SRVR))'' '"

Result:

Invoke-Sqlcmd:
Line |
   1 |  invoke-sqlcmd -ServerInstance SQLTOOLS2022-3  -DisableVariables -Quer …
     |  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     | Incorrect syntax was encountered while parsing '$(ESCAPE_SQUOTE('.

Expected:

Column1
-------
  '$(ESCAPE_SQUOTE(SRVR))'

This is work item to track the updated of SQL Assessment API engine to v1.1.9 to includes the following improvements:

• Added new and updated checks
• Added new checks for Azure SQL instances
• Azure related checks can use Azure Instanse Metadata Service (MDS) instead of Az.Graph PowerShell module; Az.Graph is still supported
• Azure SQL related checks from the default ruleset switched from Az.Graph to IMDS
• Severity now has 4 allowed values: Information, Low, Medium, High, instead of 3
• Improved performance for long running checks

It's a reminder for me to add them to the v22 relNotes.

Please consider open sourcing SqlServer module. It would be a great way to build a strong community around it and accelerate its growth and development.

Benefits (but not limited to):

• A wider pool of potential contributors and users, which can lead to more bug reports, feature requests, and contributions.

• The ability to leverage the expertise of the open-source community to improve and maintain the solution.

• The opportunity to give back to the community and contribute to the advancement of technology.

Let's discuss the potential benefits and any concerns you may have to evaluate this further.

I need to update the .md to document the recent enhancement around this parameter.

Now that Contained AG is available (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/contained-availability-groups-overview), it would be nice to have support for that in the SQL Server module cmdlets.

It is desirable to pick up the latest versions of the AS packages before v22 GA.

The following packages should be bumped to version 19.57.2.4

• Microsoft.AnalysisServices.retail.amd64
• Microsoft.AnalysisServices.NetCore.retail.amd64

Invoke-SqlCmd returns incorrect types for columns of BINARY/VARBINARY types. Specifically, it will return MaxBinaryLength bytes (default 1024) even if the column contains fewer. Since no length information is made available elsewhere, it's not possible for the consumer to compensate for this unless they already happen to know the length some other way.

Invoke-SqlCmd is certainly not my preferred tool of choice for executing queries, as opposed to scripts (its lack of support for parameterized queries alone damns it), but it is worth noting that sqlcmd (the thing it's trying to copy, for better and worse) does not exhibit this behavior; binary values that don't exceed the display width are formatted as appropriately-sized hexstrings.

Expected behavior

PS> (Invoke-SqlCmd -Server '(localdb)\mssqllocaldb' -Query 'SELECT 0x00 AS A').A.Length
1

Actual behavior

PS> (Invoke-SqlCmd -Server '(localdb)\mssqllocaldb' -Query 'SELECT 0x00 AS A').A.Length
1024

The current way to pass -Variable (for the variable substitution) is not very user-friendly.

Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1, '`$(V2)' AS C2" -TrustServerCertificate -Variable @("V1=1", "V2=2")

In addition to that, I would like to be able to say:

Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1, '`$(V2)' AS C2" -TrustServerCertificate -Variable @{ V1=1;  V2=2 }

which feels a lot more natural and does not present all the possible issues related to having to parser strings to figure out values.

A reminder for myself to look into this cmdlet. It does not seem to do anything (when I use it with -Path it works).

Also, I need to add tests for it.

In v21 we used -Variable in Invoke-Sqlcmd as follows

Invoke-Sqlcmd ... -Variable "MyPath = D:\temp\drop\sql\"

This stopped working in v22.

We've mitigated removing the spaces around '='

Invoke-Sqlcmd ... -Variable "MyPath=D:\temp\drop\sql\"

Any chance to have this fixed and released soon?

The problem is that we use the original format in hundreds of Azure DevOps Pipelines which means it takes days of effort to change them all across all customers we manage.

The issue started a couple of days ago when a new version (v20230407.1) of the Agent Image of Azure DevOps has been published which updated this module to v22.

The issue is that pinning the older Image or fixing this takes the same amount of time as require changes to all pipelines.

Fresh, default install of SQL server 2019 developer edition.

Updated to latest release version of SqlServer .

Executed

Invoke-SqlCmd -serverInstance localhost -query "SELECT 'Hello, world'"

fails with error

Invoke-SqlCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL
Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
At line:1 char:1
+ Invoke-SqlCmd -serverInstance localhost -query "SELECT 'Hello, world' ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Powershell version 5.1.19041.2673
SqlServer module 22.0.59

This is confusing for the end-user because the default install of the SqlServer PS module is now incompatible with the default install of SqlServer, and the message does not clearly explain what needs to be done to correct the issue. Notifying the user that they must either install a trusted certificate into the server, add the self-signed cert into the client certificate store, or use the trusting mode for Invoke-SqlCmd would help.

It would appear that PowerShell 7.3 is now on net7, so it seems logical to move forward.

If I execute

import-module SqlServer -requiredversion 21.1.18256
remove-module SqlServer
import-module SqlServer -requiredversion 22.1.1

I get

Update-TypeData : Error in TypeData "Microsoft.SqlServer.Management.Smo.Server": The member UpdateLevel is already present.
At C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.1.1\SqlServer.psm1:17 char:1
+ Update-TypeData -PrependPath $typeFile
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Update-TypeData], RuntimeException
    + FullyQualifiedErrorId : TypesXmlUpdateException,Microsoft.PowerShell.Commands.UpdateTypeDataCommand

and the same occurs if I reverse the order:

import-module SqlServer -requiredversion 22.1.1
remove-module SqlServer
import-module SqlServer -requiredversion 21.1.18256

yields

Update-TypeData : Error in TypeData "Microsoft.SqlServer.Management.Smo.Server": The member UpdateLevel is already present.
At C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18256\SqlServer.psm1:17 char:1
+ Update-TypeData -PrependPath $typeFile
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Update-TypeData], RuntimeException
    + FullyQualifiedErrorId : TypesXmlUpdateException,Microsoft.PowerShell.Commands.UpdateTypeDataCommand

Since 22.1.1 and 21.1.18256 have different APIs because of the -TrustServerCertificate switch, I need to be able to switch back and forth between them for executing new vs old scripts. Is there something I'm missing?

Haven't started on reproducing outside of Azure Pipelines yet, but:

Copied from the issue in actions/runner-images#7446:

Description

SqlServer 22.0.59 / Windows PowerShell 5.1 / windows-2022 / Azure DevOps

This occurs on Microsoft-hosted agents.

It may have happened after this update: actions/runner-images@0f4a356

When attempting to connect to a Synapse On Demand pool with password authentication, this occurs:

with the following connection string characteristics:
;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False

##[debug]Invoke-SqlCmd : Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.
##[debug]At D:\a\1\s\deploy\Common.ps1:31 char:27
##[debug]+ ...   $result = Invoke-SqlCmd -ConnectionString $connectionString -query  ...
##[debug]+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
##[debug]    + CategoryInfo          : InvalidResult: (:) [Invoke-Sqlcmd], MissingMethodException
##[debug]    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
##[debug] 
##[debug]Script stack trace:
##[debug]at Invoke-AzSqlQuery, D:\a\1\s\file.ps1: line 31
##[debug]at <ScriptBlock>, D:\a\1\s\file2.ps1: line 59
##[debug]at <ScriptBlock>, D:\a\_temp\04a7747b-46d3-4ec0-8b74-6a37641f9b6b.ps1: line 38
##[debug]at <ScriptBlock>, <No file>: line 1
##[debug]Exception:
##[debug]System.MissingMethodException: Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.
##[debug]   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.SqlAKVProviderWithDisposableCache..ctor(TokenCredential tokenCredential)
##[debug]   at Microsoft.SqlServer.Management.AlwaysEncrypted.Management.AlwaysEncryptedManagement.SetCustomProvidersWithDisposableCache(Boolean useGlobalCredential)
##[debug]   at Microsoft.SqlServer.Management.PowerShell.Shared.AzureKeyVaultUtilities.RegisterCustomKeyStoreProviders(String keyVaultAccessToken, String managedHsmAccessToken, SqlConnection conn)
##[debug]   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()
##[error]Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.

Platforms affected

• Azure DevOps
• GitHub Actions - Standard Runners
• GitHub Actions - Larger Runners

Runner images affected

• Ubuntu 20.04
• Ubuntu 22.04
• macOS 11
• macOS 12
• Windows Server 2019
• Windows Server 2022

Image version and build link

actions/runner-images@0f4a356

Is it regression?

Yes

2023-04-06T16:21:28.8314157Z Image: windows-2022
2023-04-06T16:21:28.8314374Z Version: 20230402.1
2023-04-06T16:21:28.8314693Z Included Software: https://github.com/actions/runner-images/blob/win22/20230402.1/images/win/Windows2022-Readme.md
2023-04-06T16:21:28.8315281Z Image Release: https://github.com/actions/runner-images/releases/tag/win22%2F20230402.1

Expected behavior

Invoke-SqlCmd connects to the server and executes the command

Actual behavior

##[debug]System.MissingMethodException: Method not found: 'Void Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider.SqlColumnEncryptionAzureKeyVaultProvider..ctor(Azure.Core.TokenCredential)'.

Repro steps

Connect to a Synapse On Demand pool with password authentication, with the following connection string characteristics:
;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False

Having Get-SqlDatabase support the -AccessToken argument seems a very useful thing to do, particularly until all the cmdlets can be updated to support -AccessToken (most if not of them support -InputObject, so that once the object is acquired using Get-SqlDatabase they would benefit from this).

In v22.0.57, I may have missed a few (e.g. Enable-SqlAlwaysOn and Disable-SqlAlwaysOn).

It would be nice to complete the list in some v22 update.

Steps to reproduce the issue:

SqlServer 22.0.59
Windows PowerShell 5.1
Windows Server 2022 Standard (21H2)

Windows PowerShell
PS > Import-Module SqlServer
PS> Invoke-sqlcmd -Query "SELECT 1" -ServerInstance "servername.domain.com" -TrustServerCertificate -MultiSubnetFailover -Encrypt Mandatory

Column1
-------
      1

SqlServer 22.0.59
PowerShell 7.3.3
Windows Server 2022 Standard (21H2)

PowerShell 7.3.3
PS > Import-Module SqlServer
PS> Invoke-sqlcmd -Query "SELECT 1" -ServerInstance "servername.domain.com" -TrustServerCertificate -MultiSubnetFailover -Encrypt Mandatory
Fatal error. System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
Repeat 2 times:
--------------------------------
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO ByRef, IntPtr ByRef)
--------------------------------
   at Microsoft.Data.SqlClient.SNINativeMethodWrapper.SNIOpenSyncEx(ConsumerInfo, System.String, IntPtr ByRef, Byte[], Byte[], Boolean, Boolean, Int32, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, Microsoft.Data.SqlClient.SQLDNSInfo, System.String)
   at Microsoft.Data.SqlClient.SNIHandle..ctor(ConsumerInfo, System.String, Byte[], Boolean, Int32, Byte[] ByRef, Boolean, Boolean, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, Microsoft.Data.SqlClient.SQLDNSInfo, Boolean, System.String)
   at Microsoft.Data.SqlClient.TdsParserStateObjectNative.CreatePhysicalSNIHandle(System.String, Boolean, Int64, Byte[] ByRef, Byte[][] ByRef, Boolean, Boolean, Boolean, Microsoft.Data.SqlClient.SqlConnectionIPAddressPreference, System.String, Microsoft.Data.SqlClient.SQLDNSInfo ByRef, System.String, Boolean, Boolean, System.String)
   at Microsoft.Data.SqlClient.TdsParser.Connect(Microsoft.Data.SqlClient.ServerInfo, Microsoft.Data.SqlClient.SqlInternalConnectionTds, Boolean, Int64, Microsoft.Data.SqlClient.SqlConnectionString, Boolean)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(Microsoft.Data.SqlClient.ServerInfo, System.String, System.Security.SecureString, Boolean, Microsoft.Data.ProviderBase.TimeoutTimer, Boolean)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(Microsoft.Data.SqlClient.ServerInfo, System.String, System.Security.SecureString, Boolean, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, Microsoft.Data.ProviderBase.TimeoutTimer)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(Microsoft.Data.ProviderBase.TimeoutTimer, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, System.String, System.Security.SecureString, Boolean)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(Microsoft.Data.ProviderBase.DbConnectionPoolIdentity, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SqlCredential, System.Object, System.String, System.Security.SecureString, Boolean, Microsoft.Data.SqlClient.SqlConnectionString, Microsoft.Data.SqlClient.SessionData, Boolean, System.String, Microsoft.Data.ProviderBase.DbConnectionPool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, System.Object, Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(Microsoft.Data.ProviderBase.DbConnectionPool, System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.Common.DbConnectionPoolKey, Microsoft.Data.Common.DbConnectionOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(System.Data.Common.DbConnection, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection, UInt32, Boolean, Boolean, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal ByRef)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(System.Data.Common.DbConnection, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal ByRef)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(System.Data.Common.DbConnection, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions, Microsoft.Data.ProviderBase.DbConnectionInternal, Microsoft.Data.ProviderBase.DbConnectionInternal ByRef)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection, Microsoft.Data.ProviderBase.DbConnectionFactory, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(System.Data.Common.DbConnection, Microsoft.Data.ProviderBase.DbConnectionFactory, System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.Common.DbConnectionOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(System.Threading.Tasks.TaskCompletionSource`1<Microsoft.Data.ProviderBase.DbConnectionInternal>, Microsoft.Data.SqlClient.SqlConnectionOverrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(Microsoft.Data.SqlClient.SqlConnectionOverrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteBatch(System.String)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ProcessBatch(System.String, Int32)
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.Go(Microsoft.SqlTools.ServiceLayer.BatchParser.TextBlock, Int32, Microsoft.SqlTools.ServiceLayer.BatchParser.SqlCmdCommand)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ExecuteBatch(Int32)
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.ParseLines()
   at Microsoft.SqlTools.ServiceLayer.BatchParser.Parser.Parse()
   at Microsoft.SqlServer.Management.PowerShell.ExecutionProcessor.ExecuteTSql(System.String)
   at Microsoft.SqlServer.Management.PowerShell.GetScriptCommand.ProcessRecord()
   at System.Management.Automation.CommandProcessor.ProcessRecord()
   at System.Management.Automation.CommandProcessorBase.DoExecute()
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(System.Object)
   at System.Management.Automation.PipelineOps.InvokePipeline(System.Object, Boolean, System.Management.Automation.CommandParameterInternal[][], System.Management.Automation.Language.CommandBaseAst[], System.Management.Automation.CommandRedirection[][], System.Management.Automation.Language.FunctionContext)
   at System.Management.Automation.Interpreter.ActionCallInstruction`6[[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Boolean, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]].Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.Interpreter.Run(System.Management.Automation.Interpreter.InterpretedFrame)
   at System.Management.Automation.Interpreter.LightLambda.RunVoid1[[System.__Canon, System.Private.CoreLib, Version=7.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]](System.__Canon)
   at System.Management.Automation.DlrScriptCommandProcessor.RunClause(System.Action`1<System.Management.Automation.Language.FunctionContext>, System.Object, System.Object)
   at System.Management.Automation.DlrScriptCommandProcessor.Complete()
   at System.Management.Automation.CommandProcessorBase.DoComplete()
   at System.Management.Automation.Internal.PipelineProcessor.DoCompleteCore(System.Management.Automation.CommandProcessorBase)
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(System.Object)
   at System.Management.Automation.Runspaces.LocalPipeline.InvokeHelper()
   at System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc()
   at System.Management.Automation.Runspaces.PipelineThread.WorkerProc()
   at System.Threading.ExecutionContext.RunInternal(System.Threading.ExecutionContext, System.Threading.ContextCallback, System.Object)

**Screenshot for reference: **

This issue does not occur if using SqlServer module 21.1.18256

SqlServer 21.1.18256
PowerShell 7.3.3
Windows Server 2022 Standard (21H2)

PowerShell 7.3.3
PS > get-Module sqlserver -ListAvailable

    Directory: C:\Program Files\WindowsPowerShell\Modules

ModuleType Version    PreRelease Name                                PSEdition ExportedCommands
---------- -------    ---------- ----                                --------- ----------------
Script     21.1.18256            sqlserver                           Desk      {Add-RoleMember, Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupListenerStaticIp, Add-SqlAzureAuthenticationContext…}

PS > import-Module SqlServer
PS > invoke-sqlcmd -Query "SELECT 1" -ConnectionString "Data Source=server.domain.com;Initial Catalog=ServerInfo;Integrated Security=True;MultiSubnetFailover=True;Connection Timeout=120;Encrypt=True;TrustServerCertificate=True"

Column1
-------
      1

After the upgrade to v22.0.59 our scripts using Invoke-SQLCmd fails with:
"SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted".

I assume this is related to this point from the release notes:

• Started transition to 'secure by-default': enforced in Invoke-Sqlcmd, optional for other cmdlets

Is there a way to disable this new default setting?

We are experiencing issues regarding connecting to db in v 22+.

We have Azure devOps pipeline that creates docker container with MsSQL server version 2019 as a pipeline resource. We then connect to server and try to create integration tests database. With version 22+ we have issue that pre-handshake failed.

Our Azure pipeline snippet :

Error that we get when running pipeline:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)

Current workaround is not to use v 22+. So we had to request -RequiredVersion 21.1.18256 version in order for our pipeline to work as before.

Fresh, default install of SQL server 2019 developer edition.

Updated to latest release version of SqlServer .

Executed

Invoke-SqlCmd -serverInstance localhost -query "SELECT 'Hello, world'"

fails with error

Invoke-SqlCmd : A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL
Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
At line:1 char:1
+ Invoke-SqlCmd -serverInstance localhost -query "SELECT 'Hello, world' ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Powershell version 5.1.19041.2673
SqlServer module 22.0.59

I know you've launched new encryption features, but this has broken basic development workflow. The error message is not informative because it made us assume the server was returning an untrusted self-signed certificate somehow.

We ran into this onboarding a new student developer. He and one of our developers spent the whole day trying to figure out how his development server was configured wrong, assuming there was a problem in their SQL server, not realizing that we'd simply installed the latest version of the module onto this new machine.

We need to move forward and also support existing cmdlet (Read-SqlXEvent) which moved already to MDS4.x

Long term, we want to move to MDS 5.x - and bite the bullet around the new defaults for TrustServerCertificate and the new Encrypt options (Strict/Optional/Mandatory) to support TDS8.0 and such.

It would be great to have it by the time v22 gets out of Previews.

To reproduce:

Launch Windows Powershell (x86) on a windows 10 machine.

Then execute

Invoke-SqlCmd 'Hello, WOrld'

Yields

Invoke-SqlCmd : Could not load file or assembly 'Microsoft.SqlServer.BatchParser, Version=15.100.0.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
At line:1 char:1
+ Invoke-SqlCmd 'Hello, WOrld'
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Invoke-Sqlcmd], FileNotFoundException
    + FullyQualifiedErrorId : System.IO.FileNotFoundException,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Now, it's perfectly reasonable that this module may not support running on 32-bit machines or there may be extra steps involved in making that happen, but a better error-message for this case would be appreciated. With the "BatchParser" error, the user does not know what caused the problem or what the resolution is.

We are using the new -AccessToken-parameter to connect to a database within Azure-Managed-Instance with a user-assigned managed-identity. For some cmdlets this is working as expected, but for others (e.g. Read-SqlTableData/Write-SqlTableData) the invokation leads to an error.

Using Invoke-Sqlcmd with -AccessToken returns the expected result:

Invoke-Sqlcmd -ServerInstance "$env:SQLInstance" -Database "$env:SQLDatabase" -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token -Query "SELECT TOP 1 * FROM sys.tables"

Instead using Read-SqlTableData with -AccessToken leads to an error:

Read-SqlTableData -ServerInstance "$env:SQLInstance" -Database "$env:SQLDatabase" -AccessToken (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token -SchemaName "sys" -TableName "tables" -TopN 1

Read-SqlTableData: Failed to connect to server <>.database.windows.net,1433.

Same error when using Write-SqlTableData

When passing the token from my personal azuread-user instead of using the managed-identity the invocation succeeds..

As both Invoke-Sqlcmd and using a personal access-token are working properly, we can not see any cause for Read-SqlTableData/Write-SqlTableData not working with -AccessToken and the managed-identity..

$PSVersionTable

Name                           Value
----                           -----
PSVersion                      7.3.3
PSEdition                      Core
GitCommitId                    7.3.3
OS                             Linux 5.4.0-1104-azure #110~18.04.1-Ubuntu SMP Sat Feb 11 17:41:21 UTC 2023
Platform                       Unix
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

(Get-Module SQLServer).Version

Major  Minor  Build  Revision
-----  -----  -----  --------
22     0      59     -1

Instance Name                        Version    ProductLevel UpdateLevel  HostPlatform HostDistribution
-------------                        -------    ------------ -----------  ------------ ----------------
<redacted>.database.windows.net,1433 16.0.175   RTM          CU2          Windows      Windows Server 2019 Datacenter

Cannot insert new data onto table when a computed column exists, returned below error:

The column "A" cannot be modified because it is either a computed column or is the result of a UNION operator.

I also got tripped up with the new certificate errors when connecting to a standard SQL server.

I used get-help invoke-sqlcmd in Powershell 7 to read through new parameters and under TrustServerCertificate I saw the note

This parameter is new in v22 of the module. For more details, see Strict Connection Encyption under Related Links (#related-links).

In the terminal of Powershell 7 I could see Related Links had something under it named "Strict Connection Encryption" but it wasn't a link I could click. I tried getting the online version of help but it didn't work

get-help invoke-sqlcmd -Online
Get-Help: The online version of this Help topic cannot be displayed because the Internet address (URI) of the Help topic is not specified in the command code or in the help file for 
the command.

How are we supposed to access this related link to get more details?

Currently, one cannot use an Azure context to read tables from an Azure SQL database. Using Microsoft.Data.SqlClient.SqlConnection doesn't work with SMO.

$sqlcc = new-object ('Microsoft.Data.SqlClient.SqlConnection') "Server=tcp:<ServerName>.database.windows.net,1433;Initial Catalog=<DbName>;Persist Security Info=False;User ID=<User>@<TenantName>.onmicrosoft.com;Password=<>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False"
$sc = new-object ('Microsoft.SqlServer.Management.Common.ServerConnection') $sqlcc
$srv = new-object ('Microsoft.SqlServer.Management.Smo.Server') $sc
$srv.ConnectionContext.Authentication = [Microsoft.SqlServer.Management.Common.SqlConnectionInfo+AuthenticationMethod]::ActiveDirectoryPassword
Read-SqlTableData -InputObject $srv.Databases["<DbName>"].Tables["<TableName>"] -TopN 5|ft

If Read-SQLTableData was AzureAD access token aware, one could read tables from AzSQLdb natively using PowerShell.

The following cmdlet does not work and I cannot think of a way to make it work.

Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1, '`$(V2)' AS C2" -TrustServerCertificate -ServerInstance localhost -Variable @('V1=1', 'V2=')

Result:

Invoke-Sqlcmd : The format used to define the new variable for Invoke-Sqlcmd cmdlet is invalid. Please use the
'var=value' format for defining a new variable.
At line:1 char:1

Similarly, trying to pass a variable whose value happens to start (or end) with spaces is not possible:

# Note the leading space in the value of the V1 variable
# The desired output is 1 instead of 2!
(Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1" -TrustServerCertificate -ServerInstance SQLTOOLS2014-3 -Variable @('V1= A')).C1.Length

Similarly, trying to pass a variable whose value happens to be (or contain) an '=' character (which is common in tokens, base64 blobs, etc...)

# Note the leading space in the value of the V1 variable
# The desired output is "A=A", instead we get an error!
(Invoke-Sqlcmd -Query "SELECT '`$(V1)' AS C1" -TrustServerCertificate -ServerInstance SQLTOOLS2014-3 -Variable @('V1=A=A')).C1

Since the -encrypt parameter was just added, all of our existing scripts cannot use the new 22.0 version of the module since it defaults to "Mandatory", but it appears we need "Optional" in order to make a connection. We would have to update dozens of scripts (probably closer to over 100) to add this parameter, as "optional", to make them work with the new module - but then adding that parameter also breaks the script if the user has not updated to the new module yet (since the old module is unaware of that new parameter). So we are stuck trying to coordinate all the scripts to be updated and all the users getting the new module at the same time.

Would it be possible for you to set the default value to "Optional" instead, so that if a user has the new module but is using a script that has not yet been updated to include that parameter, it will still be able connect to the DB? I am not a DBA...I just utilize this module to pull data from tables that we use in our scripts - so I don't fully understand the ramifications of setting it one way or the other. I just know that the scripts now fail to connect to our databases if the default value of "Mandatory" is used and setting it to optional allows it to work.

Thanks
NJK

The Invoke-Sqlcmd cmdlet has and extremely useful parameter called -AccessToken which allows to pass in an Access Token you retrieve with the Get-AzAccessToken cmdlet.

The Write-SqlTableData cmdlet accepts a -Credential object, which is great when I'm using an AppID. However, when I'm using a system-Managed Identity I need to use an Access Token, and the command doesn't have that parameter option.

Without the -AccessToken parameter on the Write-SqlTableData cmdlet I am forced to use a foreach loop to RBAR (Row by Agonizing Row) over the rows I have in PowerShell and INSERT them one at a time in using the Invoke-Sqlcmd cmdlet. This approach usually gets the job done, but obviously doesn't perform well for medium - large amounts of data to insert (noticeable slowdowns can be observed with as little as 300 rows).

What makes the situation worse is that the Invoke-Sqlcmd cmdlet can only get the job done when the data is very straight-forward. When the data contains characters like ' and " additional logic has to be added to every column in the VALUES section of the INSERT statement. Whereas the Write-SqlTableData cmdlet just handles this for you without having to add any additional logic.

It would appear that Test-SqlAvailabilityGroup, Test-SqlAvailabilityReplica, and Test-SqlDatabaseReplicaState all have a dependency on Microsoft.SqlServer.Management.HadrDmf.dll which is not available on net6.0...

If we want those 3 cmdlets to light up, we need to either make the DLL available.

Note: these 3 cmdlets were NOT available in v21.x of the PS6/PS7 of the module, so this is nothing new really.

The latest version of the SqlServer Powershell module seems to be missing support for SSIS. The documentation here:

https://learn.microsoft.com/en-us/sql/powershell/sql-server-powershell-provider?view=sql-server-ver16

Still lists SQLSERVER:\SSIS as one of the ways to navigate SSISDB in a SQL Server instance. However, the support is seemingly gone from the module itself. Any explanation here?

Thanks.

Run this:

    invoke-sqlcmd -ServerInstance SOMESERVER -query 'select cast(null as rowversion) rv'

Result:
v21 - System.Data.SqlClient

rv
--
{}

v22 - Microsoft.Data.SqlClient

rv
--

Both cases are wrong: we should show NULL, really. There are probably other issues related to "displaying" NULL, but this one around a NULL rowversion/timestamp type originates from the breaking changes in Microsoft.Data.SqlClient, where such value is returned as DBNull instead of an empty byte[]

Since .Net compiles to WebAssembly now, it should be feasible to create a WebAssembly package for 'Read-SQLTableData' so one could import and run it directly in the browser so someday there could be a PowerShell client supported for AzStaticWebapp too.

Python example: Getting started with PyScript — PyScript documentation
CodePen demo : Pyscript fetching data| Works (codepen.io)

Submitting as discussed with @Matteo-T.

I need to pick up
Microsoft.SsqlServer.XEvent.XeLite 2022.11.10.6+