The `acl:init` throws error creating schema in MicrosoftSQL Server

[rernesto]

Running acl:init against MSSQL Server throws this exception. I am using last pdo_sqlsrv and sqlsrv version available for PHP ^7.4, Microsoft SQl Server 19 Developer Edition and Symfony 5.4
Temporary solution I will try now is setup an SQLite connection just for the ACL.

{"message":"Error thrown while running command \"acl:init\". Message: \"An exception occurred while executing a query: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.\"","context":{"exception":{"class":"Doctrine\\DBAL\\Exception\\DriverException","message":"An exception occurred while executing a query: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.","code":1785,"file":"/var/www/isg/package-development/vendor/doctrine/dbal/src/Driver/API/SQLSrv/ExceptionConverter.php:67","previous":{"class":"Doctrine\\DBAL\\Driver\\PDO\\Exception","message":"SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.","code":1785,"file":"/var/www/isg/package-development/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:26","previous":{"class":"PDOException","message":"SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.","code":42000,"file":"/var/www/isg/package-development/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:34"}}},"command":"acl:init","message":"An exception occurred while executing a query: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."},"level":500,"level_name":"CRITICAL","channel":"console","datetime":"2022-01-20T10:38:44.865413-05:00","extra":{}}
10:38:44 CRITICAL  [console] Error thrown while running command "acl:init". Message: "An exception occurred while executing a query: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints." ["exception" => Doctrine\DBAL\Exception\DriverException { …},"command" => "acl:init","message" => "An exception occurred while executing a query: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Introducing FOREIGN KEY constraint 'FK_825DE299C671CEA1' on table 'acl_object_identity_ancestors' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints."]
{"message":"Command \"acl:init\" exited with code \"1785\"","context":{"command":"acl:init","code":1785},"level":100,"level_name":"DEBUG","channel":"console","datetime":"2022-01-20T10:38:44.877416-05:00","extra":{}}

[rernesto]

Ok, ACL is not part of security config anymore... I will try to use Voters instead. I guess support for ACL has been removed =(.

[derrabus]

ACL is not part of security config anymore...

@rernesto ACL has been split out of Symfony's main repository. Integrating the ACL library is not handled by Security Bundle anymore. Use https://github.com/symfony/acl-bundle/ instead.

Since none of us uses SQL Server, would you be able to work on a fix?

[rernesto]

ACL is not part of security config anymore...

@rernesto ACL has been split out of Symfony's main repository. Integrating the ACL library is not handled by Security Bundle anymore. Use https://github.com/symfony/acl-bundle/ instead.

Since none of us uses SQL Server, would you be able to work on a fix?

The documentation available in acl-bundle is not properly updated then (config section).
I can't work on a fix right now, already started working a solution using Voters because is a high priority project. If for some reason I end coming up with the schema solution for SQL Server (using default setting) along my project timeline I will be happy to fork and fix. Thank you for your answer.

[derrabus]

There's an old PR open that seems to address your issue. Can you test it? #42