Skip to main content

SQL Server

Pre-requisites​

Check the supported versions​

DBmarlin supports versions of SQL Server from 2008 and above including SQL Server on Linux and cloud services such as Azure SQL and Amazon RDS SQL Server. Please check the list of supported platforms before continuing and contact us if you are not sure.

Create a monitoring user with the correct permissions​

Create a SQL authenticated monitoring user with the correct permissions​

Before adding a target MS SQL Server instance in the UI you should first create a dbmarlin user in the target database instance. This needs to be a SQL Server authenticated account (Windows Authentication not currently supported). Note you can choose to use a name other than dbmarlin if you prefer.

For SQL Server Self-hosted you can create a monitoring user like this:

CREATE LOGIN dbmarlin WITH PASSWORD = 'securepassword';
CREATE USER dbmarlin for login dbmarlin;
use master

EXEC sp_addsrvrolemember 'dbmarlin', 'sysadmin';  
GO

Using a Windows Authenticated domain account​

These guides assume that DBmarlin (or a DBmarlin Windows agent) is installed on a Windows server that is joined to the same Active Directory domain as the SQL Server instances. The DBmarlin Tomcat service runs as an Active Directory account, and logs onto the SQL Server Databases using that AD account. It is also possible to gather host metrics using the Windows AD account.

Actions performed on the Domain Controller​

Create a dbmarlin domain user account and grant "Log on as a service" domain wide using Group Policy.

Actions performed on the servers that are hosting SQL Server​

Add the dbmarlin domain user to each MS SQL Server you wish to monitor and add the dbadmin role for that user.

Actions performed on the DBmarlin server (or DBmarlin agent) installed on Windows​

Configure the “DBmarlin Tomcat” service on the DBmarlin server and any remote agents to Log on as the Domain service account that was created. Restart the “DBmarlin Tomcat” Service to make sure that the changes are picked up.

Actions performed in the DBmarlin UI​

When you add your SQL Server instance to DBmarlin in the Settings screen you should leave the Username and Password fields blank as these will be inherited from the domain account running the “DBmarlin Tomcat” service.

Then toggle Advanced Mode and under there you can append an extra parameter to the Connection Parameters integratedSecurity=true;. The final string should look like this:

cancelQueryTimeout=2;lockTimeout=2000;loginTimeout=2;queryTimeout=2;socketTimeout=2000;integratedSecurity=true;

For SQL Server running as a cloud service​

For SQL Server cloud services such as Azure SQL and Amazon RDS SQL Server you can create a monitoring user like this:

For cloud platforms such where granting sysadmin is not possible, then a dbmarlin user with the following permissions will work. Remember that the last step of creating a dbmarlin USER to map to your dbmarlin LOGIN needs to be repeated in every database you want to monitor.

USE master;
CREATE LOGIN dbmarlin WITH PASSWORD = 'securepassword';

CREATE SERVER ROLE dbmarlin_role;
ALTER SERVER ROLE dbmarlin_role ADD MEMBER dbmarlin;
ALTER SERVER ROLE processadmin ADD MEMBER dbmarlin;

GRANT CONNECT SQL TO dbmarlin_role;
GRANT VIEW ANY DATABASE TO dbmarlin_role;
GRANT VIEW ANY DEFINITION TO dbmarlin_role;
GRANT VIEW SERVER STATE to dbmarlin_role;

-- For each database you are monitoring create a USER and map to the LOGIN you created above
USE {database};
CREATE USER dbmarlin;
ALTER USER dbmarlin WITH login = dbmarlin;

Adding the Database Target​

This is done through the User Interface using the Add Database screen