search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.58k stars 903 forks source link

"An exception occurred" error when expanding OE nodes #21644

Open Charles-Gagnon opened 1 year ago

Charles-Gagnon commented 1 year ago

Stable/RC1

  1. Connect to a server where you don't have permissions for a certain object, for example not having SELECT permissions on the "sys.sql_logins" object
  2. Try to expand a node that results in a query to the object you don't have permissions for (so for the above, try to expand the Security -> Logins node)
  3. Node fails to expand with generic message An exception occurred while executing a Transact-SQL statement or batch

This is a general issue for all OE node expansion errors. The issue here is that the user has no idea what went wrong - and only if they dig into the STS logs can they see what the actual problem is. We should allow them to see more detailed information - in the case of a SqlException the specific error message returned by the engine.

cheenamalhotra commented 1 year ago

Adding to that, a reproducible usecase that fails: Expanding AzureSQLServer/Security/Logins

23-05-12 15:57:49.1957642 pid:50148 tid:29 sqltools Verbose: 0 : eventTID:21980 SqlError.ctor | ERR | Info Number 229, Error State 5, Error Class 14, Error Message 'The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'.', Procedure '', Line Number 2
23-05-12 15:57:49.1959344 pid:50148 tid:29 sqltools Verbose: 0 : eventTID:21980 TdsParserStateObject.DecrementOpenResultCount | INFO | State Object Id 191, Processing Attention.
23-05-12 15:57:49.2000276 pid:50148 tid:29 sqltools Error: 0 : Failed getting smo objects. parent:sqlclient.database.windows.net/Security/Logins querier: Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SqlLoginQuerier error:An exception occurred while executing a Transact-SQL statement or batch. inner:The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'. stacktrace:   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences sp, String filterQuery, IEnumerable`1 extraFields)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.ClearAndInitialize(String filterQuery, IEnumerable`1 extraFields)
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SqlLoginQuerier.Query(SmoQueryContext context, String filter, Boolean refresh, IEnumerable`1 extraProperties) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoQueryModel.cs:line 96
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoChildFactoryBase.OnExpandPopulateNonFolders(IList`1 allChildren, TreeNode parent, Boolean refresh, String name, CancellationToken cancellationToken, IEnumerable`1 appliedFilters) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoChildFactoryBase.cs:line 189
23-05-12 15:57:49.2001832 pid:50148 tid:29 sqltools Error: 0 : Failed expanding oe children. parent:sqlclient.database.windows.net/Security/Logins error:An exception occurred while executing a Transact-SQL statement or batch. inner:The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'. stacktrace:   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences sp, String filterQuery, IEnumerable`1 extraFields)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.ClearAndInitialize(String filterQuery, IEnumerable`1 extraFields)
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SqlLoginQuerier.Query(SmoQueryContext context, String filter, Boolean refresh, IEnumerable`1 extraProperties) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoQueryModel.cs:line 96
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoChildFactoryBase.OnExpandPopulateNonFolders(IList`1 allChildren, TreeNode parent, Boolean refresh, String name, CancellationToken cancellationToken, IEnumerable`1 appliedFilters) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoChildFactoryBase.cs:line 189
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoChildFactoryBase.Expand(TreeNode parent, Boolean refresh, String name, Boolean includeSystemObjects, CancellationToken cancellationToken, IEnumerable`1 filters) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoChildFactoryBase.cs:line 56
23-05-12 15:57:49.2002649 pid:50148 tid:29 sqltools Error: 0 : Failed populating oe children. error:An exception occurred while executing a Transact-SQL statement or batch. inner:The SELECT permission was denied on the object 'sql_logins', database 'master', schema 'sys'. stacktrace:   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitChildLevel(Urn levelFilter, ScriptingPreferences sp, Boolean forScripting, IEnumerable`1 extraFields)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.InitializeChildCollection(Boolean refresh, ScriptingPreferences sp, String filterQuery, IEnumerable`1 extraFields)
   at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.ClearAndInitialize(String filterQuery, IEnumerable`1 extraFields)
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SqlLoginQuerier.Query(SmoQueryContext context, String filter, Boolean refresh, IEnumerable`1 extraProperties) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoQueryModel.cs:line 96
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoChildFactoryBase.OnExpandPopulateNonFolders(IList`1 allChildren, TreeNode parent, Boolean refresh, String name, CancellationToken cancellationToken, IEnumerable`1 appliedFilters) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoChildFactoryBase.cs:line 189
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.SmoModel.SmoChildFactoryBase.Expand(TreeNode parent, Boolean refresh, String name, Boolean includeSystemObjects, CancellationToken cancellationToken, IEnumerable`1 filters) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/SmoModel/SmoChildFactoryBase.cs:line 56
   at Microsoft.SqlTools.ServiceLayer.ObjectExplorer.Nodes.TreeNode.PopulateChildren(Boolean refresh, String name, CancellationToken cancellationToken, String accessToken, IEnumerable`1 filters) in /_/src/Microsoft.SqlTools.ServiceLayer/ObjectExplorer/Nodes/TreeNode.cs:line 391
23-05-12 15:57:49.2004981 pid:50148 tid:25 sqltools Verbose: 0 : Done with binding operation for sqlclient.database.windows.net/Security/Logins
23-05-12 15:57:49.2006834 pid:50148 tid:12 sqltools Verbose: 0 : Sending message of id[], of type[Event] and method[objectexplorer/expandCompleted]
erinstellato-ms commented 1 year ago

Adding a note that this still occurs in ADS 1.46 Insider and I would suspect it's extremely confusing for users. I guessed at what was happening because I tried other Azure SQL DB servers...and then tried running the T-SQL to create the AAD login, which then gave the appropriate error that I didn't have permissions.