#!connect throws invalid operation exceptions for both MSSQL and KQL

[IntegerMan]

Describe the bug

Last night I encountered an issue with KQL that I believed was due to me using the tooling incorrectly, however I'm able to replicate it using SQL today.

I'm attempting to connect to an Azure SQL database from VS Code using .NET Interactive and the connect magic command as described at https://devblogs.microsoft.com/dotnet/net-interactive-with-sql-net-notebooks-in-visual-studio-code/ and as I've used before successfully.

My IP address is added to the allow list for my Azure SQL resource and connecting via connection string in Azure Data Studio works fine.

Attempting the #!connect call results in the following exception and call stack:

Error: System.InvalidOperationException: /home/matteland/.dotnet/tools/MicrosoftSqlToolsServiceLayer failed to start properly. Exit code: 150.
---> StreamJsonRpc.ConnectionLostException: The JSON-RPC connection with the remote party was lost before the request could complete.
---> System.OperationCanceledException: The operation was canceled.
at System.Threading.CancellationToken.ThrowOperationCanceledException()
at System.Threading.CancellationToken.ThrowIfCancellationRequested()
at StreamJsonRpc.MessageHandlerBase.WriteAsync(JsonRpcMessage content, CancellationToken cancellationToken)
at StreamJsonRpc.JsonRpc.SendAsync(JsonRpcMessage message, CancellationToken cancellationToken)
at StreamJsonRpc.JsonRpc.InvokeCoreAsync(JsonRpcRequest request, Type expectedResultType, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at StreamJsonRpc.JsonRpc.InvokeCoreAsync(JsonRpcRequest request, Type expectedResultType, CancellationToken cancellationToken)
at StreamJsonRpc.JsonRpc.InvokeCoreAsync[TResult](RequestId id, String targetName, IReadOnlyList`1 arguments, IReadOnlyList`1 positionalArgumentDeclaredTypes, IReadOnlyDictionary`2 namedArgumentDeclaredTypes, CancellationToken cancellationToken, Boolean isParameterObject)
at Microsoft.DotNet.Interactive.SqlServer.ToolsServiceClient.ConnectAsync(ConnectParams connectParams)
--- End of inner exception stack trace ---
at Microsoft.DotNet.Interactive.SqlServer.ToolsServiceClient.ConnectAsync(ConnectParams connectParams)
at Microsoft.DotNet.Interactive.SqlServer.ToolsServiceClientExtensions.ConnectAsync(ToolsServiceClient serviceClient, Uri ownerUri, String connectionStr)
at Microsoft.DotNet.Interactive.SqlServer.MsSqlKernel.ConnectAsync()
at Microsoft.DotNet.Interactive.SqlServer.MsSqlKernelConnector.CreateKernelAsync(String kernelName)
at Microsoft.DotNet.Interactive.SqlServer.ConnectMsSqlCommand.ConnectKernelsAsync(KernelInvocationContext context, InvocationContext commandLineContext)
at Microsoft.DotNet.Interactive.CompositeKernel.<>c__DisplayClass21_0.<<AddKernelConnector>b__0>d.MoveNext() in D:\a\_work\1\s\src\Microsoft.DotNet.Interactive\CompositeKernel.cs:line 271
--- End of stack trace from previous location ---
at System.CommandLine.NamingConventionBinder.CommandHandler.GetExitCodeAsync(Object returnValue, InvocationContext context)
at System.CommandLine.NamingConventionBinder.ModelBindingCommandHandler.InvokeAsync(InvocationContext context)
at System.CommandLine.Invocation.InvocationPipeline.<>c__DisplayClass4_0.<<BuildInvocationChain>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass15_0.<<AddMiddleware>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass12_0.<<UseHelp>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Builder.CommandLineBuilderExtensions.<>c__DisplayClass19_0.<<UseTypoCorrections>b__0>d.MoveNext()
--- End of stack trace from previous location ---
at System.CommandLine.Invocation.InvocationPipeline.<InvokeAsync>g__FullInvocationChainAsync|2_0(InvocationContext context)
at System.CommandLine.Parsing.ParseResultExtensions.InvokeAsync(ParseResult parseResult, IConsole console)
at Microsoft.DotNet.Interactive.Commands.DirectiveCommand.InvokeAsync(KernelInvocationContext context) in D:\a\_work\1\s\src\Microsoft.DotNet.Interactive\Commands\DirectiveCommand.cs:line 33
at Microsoft.DotNet.Interactive.Kernel.HandleAsync(KernelCommand command, KernelInvocationContext context) in D:\a\_work\1\s\src\Microsoft.DotNet.Interactive\Kernel.cs:line 330
at Microsoft.DotNet.Interactive.KernelCommandPipeline.<BuildPipeline>b__6_0(KernelCommand command, KernelInvocationContext context, KernelPipelineContinuation _) in D:\a\_work\1\s\src\Microsoft.DotNet.Interactive\KernelCommandPipeline.cs:line 60
at Microsoft.DotNet.Interactive.KernelCommandPipeline.SendAsync(KernelCommand command, KernelInvocationContext context) in D:\a\_work\1\s\src\Microsoft.DotNet.Interactive\KernelCommandPipeline.cs:line 41

I do not believe I'm using the commands inappropriately and know this type of connection functioned properly.

The KQL failure exhibits a nearly identical stack trace and can be found in my KQL question issue

Please complete the following:

.NET Interactive version 1.0.512901+07977b2f577c0c9a2bc47fc2ca1df2f59a14c825

Microsoft.DotNet.Interactive.SqlServer version 1.0.0-beta.24129.1

• OS
  • [ ] Windows 11
  • [ ] Windows 10
  • [ ] macOS
  • [x] Linux (Pop!_OS 22.04 (Ubuntu based))
  • [ ] iOS
  • [ ] Android
  • Browser
  • [ ] Chrome
  • [ ] Edge
  • [ ] Firefox
  • [ ] Safari
  • Frontend
  • [ ] Jupyter Notebook
  • [ ] Jupyter Lab
  • [ ] nteract
  • [x] Visual Studio Code
  • [ ] Visual Studio Code Insiders
  • [ ] Visual Studio
  • [ ] Other (please specify)

Screenshots

Relevant code for the NuGet Dependency:

#r "nuget:Microsoft.DotNet.Interactive.SqlServer,*-*"

Relevant code for the connect call:

#!connect mssql --kernel-name dad-jokes "Server=tcp:REDACTED.database.windows.net,1433;Initial Catalog=REDACTED;Persist Security Info=False;User ID=meland;Password=DiegoAndJonRock!;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

This bug is a blocker for me authoring some learning content on .NET Interactive.

[IntegerMan]

Also, @colombod and @jonsequitur I thought you would enjoy my temporary password for my short-lived demo database, showin in the connection string above.

[colombod]

@corivera can you help us out with this, is there an update to the tool we should migrate to?

[IntegerMan]

I've tried looking at a few random versions of this package on NuGet and specifying those versions during the NuGet package and none of them seem to resolve successfully.

Is there a workaround I could try temporarily at least to get this working enough to get back SQL / KQL results for a brief demonstration?

[colombod]

The issue seems to be coming from the sql tool package closing the connection. @corivera or @Charles-Gagnon might be able to help

[corivera]

@IntegerMan We start up a backend process that handles the server connections, which is what is failing to start here. If you start that process by itself, do you see any errors? You can run "/home/matteland/.dotnet/tools/MicrosoftSqlToolsServiceLayer" from a terminal window. If it starts successfully then there won't be any output, and you can cancel it with Ctrl+C.

Most likely the issue is that .NET 7 isn't installed, since that's what the backend process is built against. The KQL kernel uses a similar backend process, so the fix for that will probably be the same.

[colombod]

As .NET7 is not LTS when should we see the tool dependency upgraded to .NET8?

[corivera]

I'll look into doing a version bump for that.

[colombod]

I'll look into doing a version bump for that.

That would be great, thank you @corivera

[IntegerMan]

@corivera that's the confirmed issue. Running the service manually gave me the .NET 7 not installed message (as it should since I had a devil of a time getting .NET 8 installed awhile back and uninstalled everything else). Using the install script and installing .NET 7.0.407 resolved the issue both at running the service and in Polyglot Notebooks executing the #!connect command. Also, nice to meet you. I'm writing a book on these tools and this greatly helps me keep to my schedule.

@colombod, thanks for the assist as always. I do think this issue highlights some of my concerns with learning curve for people getting into the tooling, though. It's not the first time I've seen versions get incompatible around major releases in gaps between different groups and the like and the way these things fail is not always evident to the user in a way they can easily resolve. Additionally, the version of .NET listed as required for Polyglots is not centrally documented on the GitHub repo and the version listed on the marketplace extension is wrong (See #3462 from a few weeks ago). These things are barriers to folks getting started with the tooling and I'd love to see a more centralized and updated list of requirements or supported library versions for common requirements and more visible output for some of the failures like this in VS Code.

That being said, you all are amazing and these capabilities are fantastic and getting better; I just want to make sure they're accessible to everyone - particularly those trying things out for the first time - and that dependency issues are caught and flagged as new versions come out of major components.

I'm good if you leave this issue open until the version bump occurs, I'm also good if you want to close this issue now that we have a workaround (Install .NET 7 and .NET 8 for full support). It's your call, but thank you all again.

[chenxizhang]

same issue here. anyone?

[chenxizhang]

[colombod]

@corivera is working on the real fix. At the present the sql and kql tool is still targeting .NET 7.0 and that would require users using those extensions to have both .NET 7 and .NET 8 sdk installed. This should address your issues too

[chenxizhang]

Thanks, @colombod , it worked after I install .NET 7.0 SDK...

[jonsequitur]

The main fix is underway here: https://github.com/microsoft/sqltoolsservice/pull/2346

[colombod]

@corivera is a new package published?