Unable to connect to SQL database from Polyglot Notebook

[kbhaskar7]

Describe the bug

I am attempting to use Ployglot notebooks extension for vs code and believe it will be very useful, but am stuck before I can even get started.

1. I have followed the provided instructions here: https://github.com/dotnet/interactive/blob/main/docs/working-with-data.md
2. I am unable to connect to my azure sql database.
3. I started with a fresh install of .net 8 and the polyglot extension.

I have gone through similar bugs, but can't find any solutions, what I have tried soo far with no success:

• Tried to use an older version of the 'Microsoft.DotNet.Interactive.SqlServer' package with no success. (was indicated to resolve the issue in another thread)
• The older package installs but fails with same error, attempted versions (1.0.0-beta.24063.1, 1.0.0-beta.23611.1 and 1.0.0-beta.22606.2)
• Tried to use .net 7 with no success since the extension only supports .net 8
• Tried running VS code in admin mode

This is the error I keep encountering:

Error: System.InvalidOperationException: C:\Users\------\.dotnet\tools\MicrosoftSqlToolsServiceLayer failed to start properly. Exit code: -2147450730. ---> 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 ---

Which version of .NET Interactive are you using? (In a notebook, run the #!about magic command. ):

• OS
  • [ ] Windows 11
  • [X] Windows 10
  • [ ] macOS
  • [ ] Linux (Please specify distro)
  • [ ] 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

I have changed the connection string in the screenshot to remove username here:

[kbhaskar7]

To be more specific and add more information, the #!connect mssql "...." --kernel-name devSQL command to add a new sql kernel fails.

Here is the my vs code info:

[colombod]

@Charles-Gagnon, @corivera is this something you can help us with?

[colombod]

I am able to successfully connect to the database server. The main difference I can se is that the latest version in nuget is 1.0.0-beta.24069.3

an this this the version I am running for the notebook :

Can you try an let us know if the issue still reproduces for you on latest versions?

[PiotrWachulec]

Hey, I also faced the same issue.

VS Code details:

Version: 1.85.2 (Universal)
Commit: 8b3775030ed1a69b13e4f4c628c612102e30a681
Date: 2024-01-18T06:40:32.531Z
Electron: 25.9.7
ElectronBuildId: 26354273
Chromium: 114.0.5735.289
Node.js: 18.15.0
V8: 11.4.183.29-electron.0
OS: Darwin arm64 23.2.0

.NET Interactive version:

Computer: MacBook Pro with M1 Pro, macOS Sonoma 14.2.1

Error code:

Error: System.InvalidOperationException: /Users/piotrwachulec/.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.
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

[corivera]

@kbhaskar7 The MSSQL notebook kernel starts another process in the background to handle the SQL queries. That JSON RPC error indicates there's likely a problem with that process not being able to start, since we communicate with it over RPC from the MSSQL kernel. You can start that process directly to see if it's throwing any obvious errors on startup. It's installed as a global dotnet tool when the SQL kernel is installed, and can be started by just running "MicrosoftSqlToolsServiceLayer" from a command line window. The process doesn't do anything by itself, so if it starts successfully then there should be no output after running the command. You can use Ctrl+C to close it after starting it.

If starting the process doesn't reveal any obvious errors, then you can run the kernel with a logging flag set to collect more info while the process is running. Before starting VS Code, you can create a system environment variable named DOTNET_SQLTOOLSSERVICE_LOGFILE whose value is a path to a file that will collect the traces. For example: "C:\Users\myuser\sqltoolslog.txt". Once that variable is defined you can start VS Code again and try using the SQL Kernel until the error is triggered. Afterwards, you can check the log file you specified to see if any errors are being thrown. Make sure to delete the log file environment variable once you're done.

[jasonmcboyd]

I ran into this same issue. Running MicrosoftSqlToolsServiceLayer revealed that I did not have the .Net 7 runtime installed:

You must install or update .NET to run this application.

App: C:\Users\jboyd.dotnet\tools\MicrosoftSqlToolsServiceLayer.exe Architecture: x64 Framework: 'Microsoft.NETCore.App', version '7.0.11' (x64) .NET location: C:\Program Files\dotnet\

The following frameworks were found: 8.0.2 at [C:\Program Files\dotnet\shared\Microsoft.NETCore.App]

Learn more: https://aka.ms/dotnet/app-launch-failed

To install missing framework, download: https://aka.ms/dotnet-core-applaunch?framework=Microsoft.NETCore.App&framework_version=7.0.11&arch=x64&rid=win-x64&os=win10

Install the .Net 7 runtime resolved the issue for me.

[IntegerMan]

I ran into this as well in #3484. Installing .NET 7 seems to be the solution, though a version bump for that dependency is perhaps coming soon that will resolve this for others.