Provide an authentication and secrets mechanism for database access

[jonsequitur]

In order to prevent people from having to paste connection strings into notebooks, we should have proper authentication support. This could include:

• Aliases for connection strings that can be referenced from with the #!connect magic command.
• AAD auth flow.

[jcmrva]

It would be great if we could use the dotnet user-secrets tool with notebooks.

[brettfo]

Thinking a little more broadly than databases, could we possibly get auth tokens via RequestInput and a specific value for inputTypeHint? VS Code extensions can provide authenticators, so if inputTypeHint was "token:github", the VS Code extension could get the GitHub auth provider (if installed) and try to get a token directly from that. We even have a good fall-back story, where if the requested auth provider isn't installed and we can't automatically get a token, we can simply prompt the user for a value. In the Jupyter Lab case we could simply fall back to the Jupyter method of prompting for input, and in the console/automation case, we could pull the value from a command line argument.

[jonsequitur]

The input type hint system was intended to be used this way, so now it's hopefully just a matter of building the providers.

[jonsequitur]

This work depends on #3567.

[KristofferBerge]

It would be great if we could use the dotnet user-secrets tool with notebooks.

This is fairly simple to do if you provide the connection string in the connect command as a variable. I would appreciate if best practices are documented in the examples, and not just hard coded connection string. It takes me so much time to figure out how to do this properly

#r "nuget:Microsoft.DotNet.Interactive.SqlServer,*-*"
#r "nuget:Microsoft.Extensions.Configuration, 7.0.0"
#r "nuget:Microsoft.Extensions.Configuration.UserSecrets, 7.0.0"

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.UserSecrets;

// In memory config
var inMemoryConfigSettings = new Dictionary<string, string> {
    {"Some:Setting", "asdf"},
};

// Go to %APPDATA%/Microsoft/UserSecrets and create a folder with this name containing a secrets.json file
var userSecretsId = "e8ce9a26-4485-4002-b685-4fdedbb1e521";

IConfiguration configuration = new ConfigurationBuilder()
    .AddInMemoryCollection(inMemoryConfigSettings)
    .AddUserSecrets(userSecretsId)
    .Build();

var connectionString = $"Data Source={configuration["Sql:Server"]}.database.windows.net;Initial Catalog={configuration["Sql:Database"]};user id={configuration["Sql:Username"]};password={configuration["Sql:Password"]}";

#!connect mssql --kernel-name mykernel @csharp:connectionString

[jonsequitur]

@KristofferBerge I'm working on the documentation for this now but secrets management is a new feature of Polyglot Notebooks that you can try out in VS Code Insiders now.

[CobusKruger]

I'm replacing ADS with VS Code, I've already set up the MSSQL extension to have all my connections nicely aliased and have those passwords stored securely. Isn't there a way for Polyglot Notebooks to reuse those?

In a perfect world, I would right-click the connection and select "New Notebook" and have it be already connected. But as a consolation prize, I would happily use the magic command with one of the MSSQL aliases. Is this doable?

[jonsequitur]

Isn't there a way for Polyglot Notebooks to reuse those?

There isn't, but Polyglot Notebooks does have its own secret storage mechanism, which you can read about here: https://github.com/dotnet/interactive/blob/main/docs/input-prompts.md#saving-a-value

We've recently added a menu to make it easier to establish data connections and give you easier access to previously-used connections. You can see these menu options using the command palette command Polyglot Notebooks: Connect to new subkernel (which is being renamed to Polyglot Notebooks: Connect to new cell kernel in an upcoming release.) The items under this menu will insert cells into your notebook which you can then run. This flow allows the notebook to be rerun without having to repeat the UI gestures or to be run in Jupyter Lab, etc. where no such custiom UI is available.

If you have any feedback on these features we'd love to hear it.