search

dotnet / interactive

.NET Interactive combines the power of .NET with many other languages to create notebooks, REPLs, and embedded coding experiences. Share code, explore data, write, and learn across your apps in ways you couldn't before.
MIT License
2.86k stars 381 forks source link

Unable to connect to SQL database from Polyglot Notebook #3422

Open kbhaskar7 opened 8 months ago

kbhaskar7 commented 8 months ago

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:

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. ):

Screenshots

image

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

image

kbhaskar7 commented 8 months ago

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:

image

colombod commented 7 months ago

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

colombod commented 7 months ago

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 :

Image

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

PiotrWachulec commented 7 months ago

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: image

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 commented 7 months ago

@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 commented 6 months ago

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 commented 6 months ago

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.