<style>p { margin: 0 0 10px; }h1, h2, h3 { margin: 20px 0 10px; }h4, h5, h6 { margin: 10px 0 10px; }</style><p>Prevent abuse of the report builder by ensuring a database connection with read-only privileges is used for SQL reports.</p>

<style>p { margin: 0 0 10px; }h1, h2, h3 { margin: 20px 0 10px; }h4, h5, h6 { margin: 10px 0 10px; }</style><h2 id="create-a-sql-user-with-read-only-access-to-the-halo-database">Create a SQL user with read-only access to the Halo database</h2> <p>Create a new SQL user account in SQL Server Management Studio by going to Security &gt; Logins, right-click on Logins and select New Login.</p> <p>Use SQL server authentication as the authentication method and give your user a strong password (you may wish to disable password expiry). Copy this for later.</p> <p>On the User Mappings tab give your new user 'db_datareader' access to the Halo database.</p> <p>Save to create the user.</p> <p>Once created, navigate to databases -&gt; <em>halodatabase</em> and click properties. Under the permissions tab, find the user you just created, find the 'Execute' row and check the 'Grant' column. Your Read-Only uses needs to be able to execute functions in reporting.</p> <p>Verify the permissions on the user account by trying to log into SQL Server Management Studio as the user that you created, and verify they only have read access to the Halo database.</p> <h2 id="configure-halo">Configure Halo</h2> <p>There are two ways to configure this in Halo.</p> <h4 id="method-1-setting-in-config-reports">Method 1: Setting in Config &gt; Reports</h4> <p>This involves storing the read-only connection string in the database.</p> <p>Open Config &gt; Reporting &gt; General Settings. Check &quot;Enable read-only db user&quot;. Enter your database connection string for the read only connection in the &quot;Readonly ODBC&quot; field.</p> <p>If you're unsure what this is, open /api/appsettings.json on the Halo app server. The read only connection string will be the same as this except;</p> <ul> <li>User Id = the sql user name you created earlier</li> <li>Password = the sql users passwords</li> <li>Trusted_Connection = False</li> </ul> <p>To test this open the Security and Performance page and verify that &quot;Read-only connection for reports&quot; is now ticked.</p> <h4 id="method-2-appsettings">Method 2: appsettings</h4> <p>This involves storing the connection string on the Halo app server.</p> <p>On the Halo web server, open /api/appsettings.json.</p> <p>Underneath the DefaultConnection: line, add a second connection called &quot;ReadonlyConnection&quot; with the User Id and password for your read-only user. Ensure you add a comma to the end of the DefaultConnection line.</p> <p>To test this open the Security and Performance page and verify that &quot;Read-only connection for reports&quot; is now ticked.</p>