RBAC with SQL server 2017 not work

[OkyTrilupito]

What steps will reproduce the problem?

this problem only in RBAC with DbManager, but in other CRUD its always working fine.
when i create role, assignment, assign route and etc im always get error.

What is the expected result?

please tell me step by step how to solve it.

What do you get instead?

Implicit conversion from data type char to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Additional info

Q	A
Yii version	2.0.13
PHP version	7.1
Operating system	SQL server 2017

[cebe]

could you please post the stack trace of the error? There is a nice little button for that on the page you screenshotted :)

[OkyTrilupito]

like this

PDOException: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Implicit conversion from data type char to varbinary(max) is not allowed. Use the CONVERT function to run this query. in D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\db\Command.php:994
Stack trace:
#0 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\db\Command.php(994): PDOStatement->execute()
#1 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\rbac\DbManager.php(283): yii\db\Command->execute()
#2 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\rbac\BaseManager.php(130): yii\rbac\DbManager->addItem(Object(yii\rbac\Permission))
#3 D:\XAMPP\htdocs\basic\vendor\mdmsoft\yii2-admin\models\AuthItem.php(173): yii\rbac\BaseManager->add(Object(yii\rbac\Permission))
#4 D:\XAMPP\htdocs\basic\vendor\mdmsoft\yii2-admin\components\ItemController.php(79): mdm\admin\models\AuthItem->save()
#5 [internal function]: mdm\admin\components\ItemController->actionCreate()
#6 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#7 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#8 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\Module.php(528): yii\base\Controller->runAction('create', Array)
#9 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction('auth/permission...', Array)
#10 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#11 D:\XAMPP\htdocs\basic\web\index.php(12): yii\base\Application->run()
#12 {main}

Next yii\db\Exception: SQLSTATE[42000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Implicit conversion from data type char to varbinary(max) is not allowed. Use the CONVERT function to run this query.
The SQL being executed was: INSERT INTO [auth_item] ([name], [type], [description], [rule_name], [data], [created_at], [updated_at]) VALUES ('book', 2, NULL, NULL, NULL, 1509740136, 1509740136) in D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\db\Schema.php:594
Stack trace:
#0 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\db\Command.php(1004): yii\db\Schema->convertException(Object(PDOException), 'INSERT INTO [au...')
#1 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\rbac\DbManager.php(283): yii\db\Command->execute()
#2 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\rbac\BaseManager.php(130): yii\rbac\DbManager->addItem(Object(yii\rbac\Permission))
#3 D:\XAMPP\htdocs\basic\vendor\mdmsoft\yii2-admin\models\AuthItem.php(173): yii\rbac\BaseManager->add(Object(yii\rbac\Permission))
#4 D:\XAMPP\htdocs\basic\vendor\mdmsoft\yii2-admin\components\ItemController.php(79): mdm\admin\models\AuthItem->save()
#5 [internal function]: mdm\admin\components\ItemController->actionCreate()
#6 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#7 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#8 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\Module.php(528): yii\base\Controller->runAction('create', Array)
#9 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\web\Application.php(103): yii\base\Module->runAction('auth/permission...', Array)
#10 D:\XAMPP\htdocs\basic\vendor\yiisoft\yii2\base\Application.php(386): yii\web\Application->handleRequest(Object(yii\web\Request))
#11 D:\XAMPP\htdocs\basic\web\index.php(12): yii\base\Application->run()
#12 {main}
Additional Information:
Array
(
    [0] => 42000
    [1] => 257
    [2] => [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Implicit conversion from data type char to varbinary(max) is not allowed. Use the CONVERT function to run this query.
)

[SilverFire]

Working on

[SilverFire]

Thank you for the report.
As I see, it was not broken by 2.0.13, it just never worked as expected and was not tested.
This issue will be fixed, but not as a patch to 2.0.13

[OkyTrilupito]

will this problem be fixed in the next version (2.0.14) ?

maybe for now I have to use PhpManager. Because the phpmanager rbac works well.

[sergeymakinen]

I plan to fix it in 2.0.14.

[OkyTrilupito]

thank you so much @SilverFire @cebe @sergeymakinen, I'll wait for the release of 2.0.14 :)

[njasm]

we're still in 2.0.12 and converted all columns from VARBINARY to VARCHAR (on DbSession also) to be able to work around this issue on SQL 2017
... looking forward for the fix..

[tsdogs]

I confirm that with all the versions of SQL server since 2008 the solution is to change varbinary to varchar.
As you are at it, it needs to be specified the NULL option as accepted.
SQL Server default database options set NOT NULL if nothing is specified, this can be changed on the database defaults, but many of us (at least I) use SQL Server as it needs to integrate with other existing applications and changing this could lead to unexpected results.