OS Authentication to Connect to SQL Instance

Similar to connecting to your SQL Server Instance, Windows Authentication (OS Authentication) is another possible way of accessing your databases. It’s default configuration is tied to the initial setup of dbWatch Control Center.

You can change this during the installation process by clicking on “Browse…”

Take note that this installation is tied to dbWatch Control Center Service running on Windows. By configuring this account, Control Center’s service will run under that account name and it makes it possible to connect to SQL Server using SQL Server Authentication login.

To edit the services’ account, you can right-click on the dbWatch Control Center service in the “Services” tool, selecting “Properties” and then navigating to the “Log On” tab.

To be able to use Windows Authentication login (which use credentials from Windows) the dbWatch Control Center Service must run under an account with sysadmin privileges on the SQL Server instances. Click “Browse” and specify the correct Windows account with the correct credentials. For this example, we want dbWatch Control Center Service running under a Windows account “SQLmonitor”.

We verify its privileges by opening Management Studio and checking the Windows Authentication. The “SQLmonitor” account is mapped in to a login on the SQL Server instance.

The “SQLmonitor” login needs the sysadmin server role to work properly.

You can check the connection pool by running the query below:

SELECT p.spid as "Session ID", 
rtrim(p.loginame) as Login, 
p.login_time as "Login Time", 
rtrim(p.hostname) as Host, 
d.name as "Database", 
p.cmd as Command, rtrim(p.program_name) as Program 
from master.dbo.sysprocesses p, master.dbo.sysdatabases d  
where p.dbid = d.dbid and rtrim(p.program_name) = 'dbWatch Control Center (monitoring)' order by p.spid

We can see a small connection pool (4 sessions) connected to the SQL Server.

You can also see all sessions from dbWatch Control Center Service (Server) in dbWatch Management GUI.

← Adding a SQLServer Instance / Adding SQLServer instances with kerberos →