Github user necouchman commented on a diff in the pull request:
https://github.com/apache/guacamole-manual/pull/66#discussion_r156170858
--- Diff: src/chapters/jdbc-auth.xml ---
@@ -299,6 +321,60 @@ Type "help" for help.
to run any database upgrade scripts.</para>
</important>
</section>
+ <section xml:id="jdbc-auth-sqlserver">
+ <title>SQL Server</title>
+ <para>If using SQL Server, the database and schema must be
created first. The example below assumes
+ that you are running SQL Server on Linux, using the
command-line tools to manage it.</para>
+ <informalexample>
+ <screen><prompt>$</prompt>
<userinput>/opt/mssql-tools/bin/sqlcmd -S localhost -U SA</userinput>
+<prompt>Password:</prompt>
<userinput><replaceable>password</replaceable></userinput>
+<prompt>1></prompt> <userinput>CREATE DATABASE
<replaceable>guacamole_db</replaceable>;</userinput>
+<prompt>2></prompt> <userinput>GO</userinput>
+<prompt>1></prompt> <userinput>CREATE LOGIN
<replaceable>guacamole_user</replaceable> WITH PASSWORD =
'<replaceable>S0me_Password</replaceable>';</userinput>
+<prompt>2></prompt> <userinput>GO</userinput>
+<prompt>1></prompt> <userinput>USE
<replaceable>guacamole_db</replaceable>;</userinput>
+<prompt>2></prompt> <userinput>GO</userinput>
+<prompt>1></prompt> <userinput>CREATE USER
<replaceable>guacamole_user</replaceable>;</userinput>
+<prompt>2></prompt> <userinput>GO</userinput>
+<prompt>1></prompt> <userinput>ALTER ROLE db_datawriter ADD MEMBER
<replaceable>guacamole_user</replaceable>;</userinput>
--- End diff --
According to Microsoft's documentation ([1]):
> db_datawriter | Members of the db_datawriter fixed database role can
add, delete, or change data in all user tables.
> db_datareader | Members of the db_datareader fixed database role can
read all data from all user tables.
Lacking expert SQL Server skills, but having tried a couple of things, it
does not look like there's a way to wildcard the GRANT in SQL Server the way
you can in MySQL or "ALL TABLES" as in Postgres. According a thread I found
([2]) you can grant permissions across the entire schema, so could do something
like:
`GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::[dbo] TO guacamole_user`
but it sounds like this is roughly the equivalent of adding to the
db_datareader and db_datawriter roles, except the roles grant those privileges
across the entire DB (all schemas) vs. a single schema. Let me know if you
have a preference on which route to go - I think adding to the roles is more
consistent with how MySQL and PostgreSQL examples are currently set up (grants
to all tables in the entire DB). Limiting to a particular schema is slightly
more secure; however, the way the documentation/examples read, it shouldn't
matter as its a separate DB with nothing else in it - a separate schema would
only apply in the case where the user explicitly loads it into a custom schema
(which would probably break the JDBC module, anyway).
[1] -
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
[2] -
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1489337c-56c9-4bb8-9875-3a75be7596be/grant-select-insert-update-delete-on-all-table?forum=transactsql
---
