MSSQL - tables in a non "dbo" schema preventing onboarding

Help me!
1

Feature(s) impacted

I’m not able to add tables my project as they are not in the DBO schema in my MSSQL Database

Observed behavior

When setting up the project I get the following error

An error occurred, when deploying Agent: Forest Admin Cloud introspection error: The object ‘[xxxx]’ does not exist in database ‘yyyy’ or is invalid for this operation.

Expected behavior

It should detect all the tables in the non “DBO” schema"

Failure Logs

I used SQL Profiler and found the SQL statement returning the message.

exec sp_executesql @statement=N’EXEC sys.sp_helpindex @objname = N’‘[TABLENAME]’‘;’

I think the schema name may need to be in the TABLENAME space along with the table name.

Context

  • Project name: Company Editor
  • Team name: Operations
  • Environment name: Dev
  • Agent (forest package) name & version: Hosting
  • Database type: MSSQL 15.0.4236
  • Recent changes made on your end if any: None
2

I think I have worked out the fix.

The statement should be like this IF a schema is set in the connection details screen

exec sp_executesql @statement=N’EXEC sys.sp_helpindex @objname = N’‘[SCHEMA].[TABLE]’‘;’

INSTEAD OF

exec sp_executesql @statement=N’EXEC sys.sp_helpindex @objname = N’‘[TABLE]’‘;’

3 Likes
4

Hello @David_Martin,

Thank you for your very detailed report. I’ll try to reproduce the issue on my side and let you know.

Just to be sure, are you using the cloud version of Forest Admin, or are you hosting the agent’s code yourself?

2 Likes
5

First off - Great product!

Hosted with you guys.

I have a work around. If I set the DEFAULT schema to the schema that has my table it works fine.

3 Likes
6

Ok, thanks for sharing the tip. I’m glad to see that you found a workaround in the meantime. It’ll give me more time to investigate!

1 Like
7

Hello @David_Martin,

We released new versions of @forestadmin/datasource-sql and @forestadmin/agent fixing your issue about schemas. We also updated our cloud agents to work correctly with this use case.

I don’t know if you can test your original issue, but if it’s the case let me know if everything works correctly now.

1 Like
8

Hi there - I tried to add a new MSSQL (hosted with you guys) and it says I need to join a waiting list and that is “coming soon” - help :slight_smile:

Thanks,
Dave

9

Hello @David_Martin,

@GuillaumeGautreau just ping me on this topic. So we want to clarify what I said in the following thread:

  • Did it work for you in the past? Did you onboard (and complete it) with MSSQL and the “instant setup”?
  • What MSSQL provider did you use?
  • What security setting did you use in your MSSQL provider?

Kind regards,
Morgan

10

Yes we did on board ~ 6-7 times no issues. works well.
We use MSSQL Web Server Edition - hosted by AWS
SQL Login.

Hope this helps. We love the product and it has been great so far.

1 Like
11

Hey @David_Martin,

Thanks for your feedback. :pray:

We did encounter the issue with Azure MSSQL service and we thought that was a global issue with MSSQL but we will put it back with a disclaimer for Azure usages as it could be the only one not supporting our withlisting mechanism.

I will come back to you once the MSSQL flow has been added again. Again, sorry for this.

Kind regards,
Morgan

1 Like
12

Thank you. We love your service and so far during our testing in different scenario’s it’s been awesome.

1 Like
13

Hey @David_Martin,

Thank you for your supportive feedback, we try to improve our tool every day. :muscle:

The Cloud MSSQL onboarding should be back for you. So you will be able to try the fix from @GuillaumeGautreau.

Kind regards,
Morgan

1 Like
14

Awesome thanks very much!

1 Like