[Feature Request]: Get connection credentials by file uri

[MikhailProfile]

Feature Description

Since Azure data studio is retired soon, I'm trying to recreate SearchEverywhere extension for VS Code (https://github.com/MikhailProfile/SearchEverywhere)

To provide a seamless solution to users of a dependent extension I need to know current connection for any active sql document (files could have different connections in 2 different opened files).

My Pull Request #19341 was closed in reference to discussion #19411.

In a discussion there is a suggest to user getConnectionString exposed by the extension:
https://github.com/microsoft/vscode-mssql/blob/main/src/extension.ts#L113

It takes a connectionUri Or connectionDetails as input parameters. I'm bit confused because at this point I don't have any connection details or Uri on my end. Where I could get this data to get active connection for opened file?

@aasimkhan30 could you please provide details how to use it? Or consider to merge the pull request. It's a tiny change but it allows to get connection info by file uri.

@rayzinnz could you please provide your case of using getConnectionString? You mentioned that you satisfied with getConnectionString.

Thanks!

Problem and Motivation

Currently It's impossible to get active connection for opened sql file.

Related Area

• Connection dialog
• Query editor
• IntelliSense/auto-completion
• Query results panel
• Object Explorer
• Table Designer
• Schema Compare
• Schema Designer
• Query Plan Visualizer
• Other (please describe below)

If you selected "Other", please describe the area

Extends main extension API. New method.

Confirmation

• I have searched existing feature requests and couldn't find a match
• I want to help implement this feature

[rayzinnz]

@MikhailProfile My plan is to used the connection string to open a new connection to the database, and use that new connection. I am still looking into how to achieve this.

[rayzinnz]

@MikhailProfile My plan is to used the connection string to open a new connection to the database, and use that new connection. I am still looking into how to achieve this.

Actually this won't work. The exposed "getConnectionString() requires a "connectionUriOrDetails: string | ConnectionDetails" input parameter, which aren't known, so back to square one.

vscode-mssql/src/extension.ts

Line 113 in 9c2ced0

getConnectionString: (

[aasimkhan30]

Hey @MikhailProfile and @rayzinnz — correct me if I’m wrong, but sounds like you both need the connection details for the currently active editor?

You can use the existing getConnectionString() API with the URI from activeTextEditor. Something like this:

const document = vscode.window.activeTextEditor;
const connectionString = getConnectionString(document.uri.toString(), true); // true to include the password

[rayzinnz]

Hey @MikhailProfile and @rayzinnz — correct me if I’m wrong, but sounds like you both need the connection details for the currently active editor?

You can use the existing getConnectionString() API with the URI from activeTextEditor. Something like this:

const document = vscode.window.activeTextEditor;
const connectionString = getConnectionString(document.uri.toString(), true); // true to include the password

@aasimkhan30 I try your code, the uri is a filepath, e.g. "file:///c%3A/DUMP/SQLQuery_1.sql", and passing this to the getConnectionString gives this error: "Object reference not set to an instance of an object." Note there is a connection on this active text editor when I run it.

[MikhailProfile]

Good day, @aasimkhan30,

Thanks for your help! I was able to get connection string by next code:

Now to work with other API methods I need to transform it to IConnectionInfo.
@aasimkhan30 could you please do not close the issue. I will back with the solution.

PS Code by text: async getActualConnection() { let uri = vscode.window.activeTextEditor?.document?.uri.toString(true); if (uri) { const connectionString = await this.mssqlApi.getConnectionString(uri, true);

[MikhailProfile]

Little details about my extension. It gives opportunity to list all objects from database and provide opportunity to scripts them as CREATE, ALTER and so on.

I could create IConnectionInfo from connection string in some way.
Connect to API with this details since I need an Uri for ScriptingService

Pass it to API in ScriptingService:

Scripting Service does a SendRequest

But as far as I understand since I do no have groupId, id, profileName in connection details(we lack it in connection string), ScriptingService creates extra connections in Server menu

Extra connections:

@aasimkhan30 would be appreciate for any ideas how to solve it.

If I do the same using my Pull Request getCredentialsByFileUri function. It works like a charm.

[rayzinnz]

Good day, @aasimkhan30,

Thanks for your help! I was able to get connection string by next code:

Now to work with other API methods I need to transform it to IConnectionInfo. [@aasimkhan30](https://github.com/aasimkhan30) could you please do not close the issue. I will back with the solution.

PS Code by text: async getActualConnection() { let uri = vscode.window.activeTextEditor?.document?.uri.toString(true); if (uri) { const connectionString = await this.mssqlApi.getConnectionString(uri, true);

Thank you, yes the trick I was missing is passing the skipEncoding=true here: toString(true)

It now works!

[rayzinnz]

I ended up using this to open a new connection to the server using the retrieved connection string:

npm install mssql

import * as sql from 'mssql';
pool = sql.connect(connectionString);

If not exposing the connection object of the window, it would be great to get the connection treenodes exposed publicly so we can get connection UUIDs etc...

[MikhailProfile]

Good day, @aasimkhan30

Any updates on the above please?

[aasimkhan30]

Hi @MikhailProfile, we're still having internal discussions on the best way to support connectivity through external extensions. In the meantime, you can access the mssql.connections value from the global settings to get all existing connections. From there, you can select the one that matches the connection string for the editor.

This approach should help you avoid duplicating connections in the Object Explorer.

Here’s a snippet to get you started:

const configs = vscode.workspace.getConfiguration("mssql");
const connections = configs.inspect("connections")?.globalValue as any[];

This will give you an array of connection profiles with their IDs.
It’s not ideal, but it should get you unblocked for now. Thanks!

[MikhailProfile]

Hi @aasimkhan30,

Thanks a lot for your help! Will try and revert back.