I am trying to map all authentication info about users and roles from a database user source to a Microsft SQL Server database following this guide Database Authentication. I have configured it successfully using SQLite Database. However, when I tried to create a role or a user in a user source using Microsoft SQL Server Database, it triggered some errors. You can see them in the figures below.
Thank you for your time!
In addition, I am using SQL Authentication instead of Window Authentication to connect to the SQL Server.
The queries you defined for this profile may need to have the schema name prefixing the table names for SQL Server.
Thanks for your support.
I hope Ignition can manage all authentication info. Therefore, I chose Automatic Mode when I created a user source. I can query data from the same SQL DB in Designer.
The error occurred when I want to create some tables to that database.
@Kevin.Herron I came back to this topic after several days. Finally, I got the answer. I found that because the SQL Authentication account did not have permission to create databases and tables. Therefore, when I chose the automatic mode to create some user/role tables, it triggered the error as I mentioned above. Changing the permission of SQL Authen account helped me solve the problem.