2sxc fail on fresh Oqtane 4.0.0 because of restricted DB permissions

[nicpitsch]

I'm submitting a

[x] bug report => search github for a similar issue before submitting
[x] not sure

...about

[x] 2sxc in Oqtane
[x] Installation error

Current Behavior / expected Behavior

2sxc Content or App modules added to a page render following error:

System.NullReferenceException: Object reference not set to an instance of an object.
   at ToSic.Sxc.Oqt.App.Index.OnParametersSetAsync() in C:\Projects\2sxc\2sxc\Src\Oqtane\ToSic.Sxc.Oqt.Client\App\Index.razor.cs:line 64
   at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)
   at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()
   at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle, ComponentState owningComponentState)

Instructions to reproduce the Problem

My Windows Server environment is for production/deployment, not for development. It has other Oqtane installations - updated from 3.x to version 4.0.0 - where 2sxc is functional. The IIS is setup according to Installing Oqtane on IIS - now using .NET Core version 7.0.

Reproduce:

• Install latest Oqtane from scratch (don't update an existing).
• Install latest 2sxc using Oqtane Module Management.
• Add a 2sxc Content or App module to a page. In my environment the error above happens.

What I further did to try eliminate the error:

• Check instructions at [Install 2sxc in Oqtane](https://azing.org/2sxc/r/fOG3aByY too). Install Microsoft C++ runtimes.

My environment

• Operationg system Windows Server 2016, SQL Express
• 2sxc version(s): 16.02.00
• Oqtane: 4.0.0
• Hosting platform: IIS
• Browser: all
• Language: any/all | English

[tvatavuk]

Thank you for providing detailed information about the issue you're facing.

We have successfully set up and run clean install release Oqtane v4.0.0 with 2sxc v16.02.00 on a clean installation of Windows Server 2016, following standard configuration procedures without encountering any issues.

Given that we can't reproduce the error you're experiencing in our environments, it suggests that the issue might be specific to your configuration.

For more info on your issue you can explore 2sxc Insights on your oqtane installation (eg. https://your-website.oqtane.me/api/sxc/sys/insights/logs).

You can check for more info in oqtane Event Log.

Also you can enable builtin aspnetcore logging in web.config (https://azing.org/oqtane/r/gL2D-i_p) and search for more details on exception that happens in it.

I recommend going through the setup process once again, ensuring all steps are followed meticulously. Make sure all dependencies are correctly installed and up-to-date. If possible, you might want to try the installation on a different server or a local setup to rule out environment-specific issues.

[nicpitsch]

Thanks for the fast feedback. I found now the cause for this issue.

It's related to missing database permissions for my user in connection string. I don't want to set sa as User Id.

I created a login user oqtane_login and assigned to it the built in roles db_datareader, db_datawriter, db_ddladmin this way:

-- Create login:
USE [master]
GO
CREATE LOGIN [oqtane_login] WITH PASSWORD=N'[your password]', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
-- Assign login to database user..
USE [oqtane_test]
CREATE USER oqtane_login FOR LOGIN oqtane_login
ALTER USER oqtane_login WITH DEFAULT_SCHEMA=dbo
GO
-- Assign roles to login..
USE [pitsching_oqtane]
ALTER ROLE db_datareader ADD MEMBER oqtane_login
ALTER ROLE db_datawriter ADD MEMBER oqtane_login
ALTER ROLE db_ddladmin ADD MEMBER oqtane_login
GO

Having this user in the connection string, the issue above occurs.

Setting the SQL Server server admin sa as user in the connection string solves the issue.

It turns out, that it is necessary for 2sxc to to allow stored procedures execution permission too. As SQL Server seems not to have a built in role for that I followed https://dba.stackexchange.com/a/180434:

-- ..assign EXECUTE permissions (for stored procedures)
USE [oqtane_test]
CREATE ROLE dbo_execute
GRANT EXECUTE ON SCHEMA::dbo to dbo_execute
ALTER ROLE dbo_execute ADD MEMBER oqtane_login
GO

To be honest I found an earlier mention within my notes, which I couldn't remember anymore. Did I miss some instructions or notes concerning this requirement related to 2sxc in Oqtane?

[tvatavuk]

Thank you for your detailed troubleshooting and feedback.

Indeed, 2sxc uses SQL stored procedures and requires appropriate EXECUTE permission. In the current development and production scenarios, we tend to use a database user that is the database owner. This is because the same SQL connection is used for the initial 2sxc module installation as well later during end-user usage. Here is the command to assign the database owner role:

ALTER ROLE [db_owner] ADD MEMBER [oqtane_login]
GO

In high-security scenarios where the database user will require more permissions for Oqtane and custom module installation than later during end-user usage, there's no definitive guidance at the moment. This is primarily because Oqtane doesn't currently support such a scenario.

We really appreciate your efforts in digging into this, and your inputs will certainly help others facing similar issues.

[iJungleboy]

@tvatavuk Is there a way we can throw a better error to the user for this scenario?

Because it's really hard to figure out if people just get started.