MS SQL driver: I can get connection to a DB and run SQL but I cannot see list of tables

[stephanemoulec]

I can connect to a SQL DB (Azure SQL instance), run code, but when I try to expand the connection to see the list of tables, I get the error "Request connection/GetChildrenForTreeItemRequest failed unexpectedly without providing any details."

I saw issues 640 and 642 but they apply to PostgreSQL. Could not use the solution.

My connection string:
` "sqltools.connections": [

    {
        "mssqlOptions": {
            "appName": "SQLTools",
            "useUTC": true,
            "encrypt": true
        },
        "previewLimit": 50,
        "server": "<my db server>",
        "port": 1433,
        "driver": "MSSQL",
        "name": "<my db name>",
        "database": "<my db name>",
        "username": "<user>",
        "password": "<password>"
    }
],`

[stephanemoulec]

Problem still exists. In most recent version (both VS Code and extension), I get message "Running the contributed command: 'sqltools.getChildrenForTreeItem' failed." when opening the tree

[anil-github-dev]

I've the same issue - using latest version 1.10.1 with the below properties. When expanding the connection getting the following error "Request connection/GetChildrenForTreeItemRequest failed unexpectedly without providing any details". I'm able to execute the SQLs without any issues after double clicking the connection.

{
"mssqlOptions": {
"appName": "SQLTools",
"useUTC": true,
"encrypt": true
},
"previewLimit": 50,
"server": "",
"port": 1433,
"driver": "MSSQL",
"name": "",
"database": "",
"username": "",
"password": ""
}

[Code4OSINT]

Running into the same issue/error, using PostgreSQL.

Database is visibly connected, running simple sql (create table) works fine. But cannot expand the connected db, error message keeps popping up:

Running the contributed command: 'sqltools.getChildrenForTreeItem' failed.

[magiruuvelvet]

I had the same issue today with a MariaDB database. Turned out the password was wrong, once I used the correct password the table tree was showing up in the sidebar. Really weird. I logged in as root user to the development database in case that matters.

The extension doesn't seem to handle invalid connections or wrong passwords correctly. When using some random IP address were no database is running I get all kinds of weird errors, instead of a useful one.

EDIT: The database showed up as successfully connected even though I used the wrong password.

[SADANANDKUMAR]

guys mine also have same problem

Running the contributed command: 'sqltools.getChildrenForTreeItem' failed.

[DustinWin]

guys mine also have same problem

Running the contributed command: 'sqltools.getChildrenForTreeItem' failed.

Same problem!

[MarcosBaungartner]

You need to click the plug icon (which is beside the name of the connection) to connect to the database, then it will ask for password.

[rangitoto72]

Same issue here:

Environment
VSCode: February 2021 (version 1.54)
SQL Tools: V 0.2.0
OS: OS11.2.3 ( Apple silicon)

Error:
Running the contributed command: 'sqltools.getChildrenForTreeItem' failed.

Here is the connection, it is fine, as it has the green dot for connected as well as the option to disconnect.

Here is my select to return data (So it is connecting fine)

[paintmeyellow]

Same issue

Extension version: 0.23.0
VS Code version: Code 1.54.3
OS version: Linux x64 5.8.0-45-generic

[sajadshafi]

I also had the same issue as I switched to root user it worked fine. But logging in as another user doesn't work.

[filasik]

same problem, OS Windows 10

[Kaloyanes]

I have the same problem. OS: Windows 10

[nihirisuto]

Tested my connection credentials in dbeaver and this, and I'm getting the sqltools.getChildrenForTreeItem error for all but one connection. Was able to expand tables for one of them but the other 3 connections I'm trying aren't working.

[Pedram-Mirelmi]

same issue!
i'm using MySQL server 8.0.23 on vscode 1.55.2
linux ubuntu 20.04 LTS
it gives me an error with the message: "running the contributed command: 'sqltools.getChildrenForTreeItem' failed"

[LucasMarmo]

https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server/50131831#50131831

Execute the following query in MYSQL Workbench

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

Where root as your user localhost as your URL and password as your password

Then run this query to refresh privileges:

flush privileges;

Try connecting using node after you do so.

If that doesn't work, try it without @'localhost' part.

[rangitoto72]

https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server/50131831#50131831

Execute the following query in MYSQL Workbench

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

Where root as your user localhost as your URL and password as your password

Then run this query to refresh privileges:

flush privileges;

Try connecting using node after you do so.

If that doesn't work, try it without @'localhost' part.

Might work but as you can see, the initial query, as with my issue is for TSQL ( Microsoft SQL Server. NOT MySql ), so using root@localhost has nothing to do with it.

[psinnathurai]

Same Issue here.

[ecesar88]

Same here

[NathanSFon]

I was having the same problem.
I decided to change the user to 'root' and in the password I left with ask when connecting, from that moment there was no error

[mdavinicius]

Same issue for me too :(

[MGMehdi]

Me too I'm running MySQL on docker with WSL2 (Ubuntu)
Latest version of VSCode and SQLTools

My docker-compose

version: '3'

services: 
    db:
        container_name: mysql
        image: mysql
        restart: always
        environment: 
            MYSQL_ROOT_PASSWORD: test123*

    
    phpmyadmin:
        image: phpmyadmin
        restart: always
        ports:
            - 8080:80
        environment:
            PMA_HOST: db
            PMA_USER: root
            PMA_PASSWORD: test123*

networks:
    default:
        external: true
        name: web

SQLTools connection

"sqltools.connections": [
    
        {
            "mysqlOptions": {
                "authProtocol": "default"
            },
            "previewLimit": 50,
            "server": "localhost",
            "port": 3306,
            "askForPassword": true,
            "driver": "MySQL",
            "name": "Inventaire",
            "database": "inventaire",
            "username": "root"
        }
    ],

[shinzoke]

Same thing here... would be Nice to have a decent fix or someone to point me into the right way...
Using docker on wsl2

[RoneyThomas]

https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server/50131831#50131831

Execute the following query in MYSQL Workbench

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'

Where root as your user localhost as your URL and password as your password

Then run this query to refresh privileges:

flush privileges;

Try connecting using node after you do so.

If that doesn't work, try it without @'localhost' part.

If anyone is having trouble with mysql 8.0 and above, use this and it will work.

[iwogar]

Hey, I'm not sure how MySQL got into this but the original issue was raised for MS SQL. Is this fix supposed to work for MS SQL too?

[RoneyThomas]

Hey, I'm not sure how MySQL got into this but the original issue was raised for MS SQL. Is this fix supposed to work for MS SQL too?

Yup

[psmatsinhe]

Hi, any explanation for this error beside having to downgrade!!!?

[huangjj27]

Hey, I'm not sure how MySQL got into this but the original issue was raised for MS SQL. Is this fix supposed to work for MS SQL too?

refer to Aidin's answer below that stackoverflow post.

[huangjj27]

Hi, any explanation for this error beside having to downgrade!!!?

refer to Aidin's answer below that stackoverflow post.

[huangjj27]

I think this issue can be fixed by adding support of authorization for caching_sha2_password, but as is mentioned in #101, this issue of MySQL version may be not fixed.

[willycotes]

in my case the problem was solved by changing the hostname "localhost" by the local ip "127.0.0.1"

[JamesWigley]

I solved mine when I realised I had a trailing space in my server address . It does seem that the positive result from the Test Connection can be misleading,

[Rohit-B-Patil]

Problem Solved just using some steps.

I also got the same issue and solved easily.

Delete the existing "MySQL connection" on which this error is occured in VSCode using right click "Delete Connection"

You will not loose any existing database or any table.

So create new "Add new connection" then select "MySQL connection" with same existing database on which this error is occuring. TEST it .Save connection and connect now. Thats it.You can see every databases and table again.

And always check on which database you are active in 'EDIT CONNECTION' while creating new database and table

Without DROPPING table it will not vanish from your database automatically in VScode. VSCODE has this type of bug that we can encounter.

[tarekahf]

I am facing the same problem when connecting to SQL server from VS Code. However, I can run SQL Queries.

I tried to follow all steps mentioned in this post to resolve the issue, but nothing worked. Please help.

Tarek

[tarekahf]

Hey, I'm not sure how MySQL got into this but the original issue was raised for MS SQL. Is this fix supposed to work for MS SQL too?

Yup

This action is not working at my end on SQL Server and I m getting a syntax error when trying to execute the provided command.

[tarekahf]

I have the same problem but on Windows and MS SQL Server. Any resolution found?

[xenago]

I can confirm this is still an issue when using the MS SQL server driver. It connects OK but no tables visible.

[gjsjohnmurray]

@xenago after connecting are you able to run this query:

SELECT * FROM INFORMATION_SCHEMA.TABLES

If so, does this list schemas and tables?

[xenago]

Yes, it does list them:

After doing some checking, I think the issue is related to the selected Database for the connection:

I am only able to see tables in that database which has been selected for that connection, not any others accessible to that db login, despite them all being visible in the ui.

For example, when connecting to 'master', I can't access anything:

But connecting directly to this other database, the schema becomes visible:

So maybe the ui should either hide these inaccessible databases (to make it clear that only a single database is usable per connection), or properly support accessing all of the user's visible databases. The latter is how I would expect it to work since that's how it works in SSMS, DBeaver, etc. However I don't currently see an option in the ui to switch the active database within the connection, so that option may be too complex.

[gjsjohnmurray]

@xenago so your issue is the same as #714

[Scriptize]

@xenago
I had the /GetChildrenForTreeItemRequest failed error for my azure cloud database; interestingly enough I changed the database to master like u did here and it's working perfectly fine haha so thanks

[JP-Ellis-KPMG]

Interestingly, I also have an MSSQL database on Azure, but I am unable to get the result from @xenago.

Specifically:

• If I connect to master database, I have the same result as him: I can see the databases, but the only subitem is "Schemas" and then there's nothing below that.
• If I connect directly to a database, I get the /GetChildrenForTreeItemRequest failed.

On other tools (such Azure Data Studio), the behaviour is:

• If I connect to master, I can see system databases along with all other databases
• If I connect to a specific database, I can only see items within that database.

The Microsoft SQL Server version is:

Microsoft SQL Azure (RTM) - 12.0.2000.8  	Oct 18 2022 13:24:45

If you need access to a Azure SQL Database for testing, I am happy to provision one, just email me and I can send through credentials.

[Scriptize]

@JP-Ellis-KPMG
If you want to see the tables and their info for a single database, so you don't have to go back and forth between ADS and VScode, I think there's an alternative that works well.

Again, the only downside is that it's one database at a time. (it may be a dealbreaker if you're working with a lot of them, haha)

There's an extension Microsoft has called SQL Server (mssql)
Here's what one database and its tables look like below. You can even go further to the columns, keys etc.:

I hope this helps!

[JP-Ellis-KPMG]

@Scriptize Thanks for the suggestion. I have quite enjoyed using vscode-sqltools, so while other extensions are alright for now, I still hope to see this bug fixed in this extension 👍

[zookeeper1320]

I face the same issue, when I drill down the Objects. I get back the database names but under the database when I click tables or views I get the error Request connection/GetChildrenForTreeItemRequest failed with message: Cannot read property 'ResultSet' of undefined
With the same connection I can run queries without any problem aginst an athena on AWS.

[crh-hireup]

Exactly the same issue as @zookeeper1320, can see full structure until I try to enter Tables or Views and then get Request connection/GetChildrenForTreeItemRequest failed with message: Cannot read property 'ResultSet' of undefined

@zookeeper1320 I was wondering if it is something to do with the SQLTools AWS Athena Driver instead - but not sure

[fzhem]

I have created a fork of the ms sql driver that potentially fixes this. I have tested it on AzureWorks sample database. The latest version 0.5.3 fixes this: https://marketplace.visualstudio.com/items?itemName=fzhem.sqltools-driver-mssql-commver

[fzhem]

Check out #1399 which potentially resolves this.