SWDP package does not work with security database

[bartlomiejmucha]

Expected Behavior

When deploying Sitecore.PowerShell.Extensions-6.2.scwdp.zip and having a separate security database extracted from core database, the deployment should finish without error and deploy to correct databases.

scwdp package should accept connection string to core and security database.

Actual Behavior

When deploying package we get error that aspnet tables are not present in the database.

Error SQL72014: .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Line 2 Invalid object name 'aspnet_Applications'.

Steps to Reproduce the Problem

Sitecore 10.1.1 and SPE 6.2

Deploy SPE swdp package on top of vanilla installation. Vanilla installation is configured to use security database and the core database does not contain any aspnet tables. https://doc.sitecore.com/en/developers/92/platform-administration-and-architecture/walkthrough--moving-security-data-to-a-separate-database.html

[michaellwest]

Which server role are you could figuring the security database? Is this happening with standalone and/or content management?

[bartlomiejmucha]

With Standalone. I have not tested other roles yet, however I think it will be the same. Package contains core.dacpac file that adds roles to the aspnet tables, but the tables are not present in core as we moved them to security database.

[michaellwest]

Is the solution to the problem as simple as add the connection string to core and then remove after installation?

[michaellwest]

I've certainly not tested an installation where the security tables are in a separate database. Have you idenfitied what changes are made to the aspnet_* tables?

[bartlomiejmucha]

Inside the core.dacpac there is postdeploy.sql file and that file is inserting items to the core database, but it also inserts roles like sitecore\PowerShell Extensions Remoting and sitecore\PowerShellExtensionsAPI into the aspnet_Roles table (which does not exist).

Ideally, that part should be extracted into security.dacpac and the package should accept Security Connection String.

I guess the .swcdp package is generated using the Sitecore Azure Toolkit. Not sure if SAT supports a security database tbh.

[michaellwest]

Do you have any recommendations on "how" I could make this change? I have no problem creating this if it's reasonable to create.

[bartlomiejmucha]

I know how to add Security Connection Strings parameter and security.dacpac to the package. This can be done with SAT. I'm not sure how to extract that SQL script from core.dacpac to security.dacpac though. I will think about it.

[michaellwest]

@bartlomiejmucha Now that SPE releases a version using IAR, should we consider this no longer an issue?

[bartlomiejmucha]

@michaellwest there is a chance it will work now. Just need to set security connection string instead of core one. By quickly looking at postdeployment.sql script in core.dacpac there is only one place I'm not sure about. Inside the script there is base64_encode sql function dropped and recreated. I'm not sure if this function should go to core or security database.