Microsoft SQL using integrated auth (kerberos) and official ODBC template issue

[ideschamps]

Hi,

We are running v5.02 of Zabbix server and Zabbix Proxy. All working good. We now want to monitor our SQL 2017 server which only has Integrated Authentication available and not SQL login/auth. I have followed all the instructions here https://git.zabbix.com/projects/ZBX/...odbc/README.md

I can get kerberos tickets and can login to our MS SQL server via sqlcmd with -E to use integrated auth. sqlcmd -E -S XXXX.

I have also setup the DSN in odbc.ini and have added it as a host macro to the template.

Every time when I run "Discovery" for the databases, I am getting the following error: Cannot connect to ODBC DSN: [SQL_ERROR]:[HY000][851968][[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]SSPI Provider: No Kerberos credentials available (default cache: FILE:/tmp/]|[HY000][851968][[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Cannot generate SSPI context]

I have tried adding and removing the username and password host macros, which I don't believe we need to enter as it's using integrated auth.

Anyone that has had similar issues or know how to get ir working?

thank you.

[ideschamps]

Ok so fixed this. Joined the proxy to the domain, logged in with the AD user that has got access to the MSSQL server. Started the zabbix proxy service as the AD user and working. No need to put in any username or password in the template, only the DSN configured in odbc.ini and included Trusted_Connection=Yes.

[Kirill_dz]

How to started zabbix proxy service as AD user, when zabbix proxy non supported Windows OS platform?

[Cristal]

Hi,

I'm facing the same problem.
Have you solved the problem? If yes, how?

My Balance Now

[BigSmooth]

Hello ideschamps ,
Any feedback on Cristal or Kirill_dz comments ?

[aholiveira]

Hello. Please see post below if this helps.

[aholiveira]

Hello,
I have got this working doing the steps described below.

• Create a regular Windows user (sqlmonitor in this example) in your domain for Zabbix to login to SQL Server
• In Zabbix server/proxy edit file /etc/odbc.ini and create an entry for your SQL Server similar to this

[SQLServer]
Driver=ODBC Driver 18 for SQL Server
Server=tcp:SQLSERVER\\INSTANCE,1433
Encryption=Yes
TrustServerCertificate=Yes
Trusted_Connection=Yes

Make sure that you have line

Trusted_Connection=Yes

(this is what enables Integrated Authentication).

Run the following command

sudo su -s /bin/bash - zabbix

As zabbix user run the following

kinit sqlmonitor

Enter the sqlmonitor Windows password

klist

Check that the output looks similar to this, meaning you have correctly been granted a Kerberos ticket.

Ticket cache: FILE:/tmp/krb5cc_107
Default principal: sqlmonitor@DOMAIN

Valid starting       Expires              Service principal
06/26/2024 12:05:12  06/26/2024 22:05:12  krbtgt/DOMAIN@DOMAIN
        renew until 06/27/2024 12:05:11

• In SQL Server run the following query, changing "DOMAIN" (your domain NetBios name), "sqlmonitor" (Windows user) and "SQL Monitoring" (SQL role) to appropriate values for your environment.

use [master]
if not exists (select name from sys.server_principals where name='DOMAIN\sqlmonitor' and type='U')
begin
CREATE LOGIN [DOMAIN\sqlmonitor] FROM WINDOWS WITH DEFAULT_DATABASE=[msdb], DEFAULT_LANGUAGE=[us_english]
end
if not exists (select name from sys.server_principals WHERE name='SQL Monitoring' AND type='R')
begin
CREATE SERVER ROLE [SQL Monitoring] AUTHORIZATION dbo;
end
ALTER SERVER ROLE [SQL Monitoring] ADD MEMBER [DOMAIN\sqlmonitor]
GRANT VIEW SERVER PERFORMANCE STATE TO [SQL Monitoring] AS [sa]
GRANT VIEW SERVER STATE TO [SQL Monitoring] AS [sa]
use [msdb]
if not exists (select 1 from sys.database_principals where name='SQL Monitoring' and Type = 'R')
begin
CREATE ROLE [SQL Monitoring] AUTHORIZATION dbo;
end
if not exists (select name from sys.database_principals where name='DOMAIN\sqlmonitor' and type='U')
begin
CREATE USER [DOMAIN\sqlmonitor] FOR LOGIN [DOMAIN\sqlmonitor] WITH DEFAULT_SCHEMA=[dbo]
end
ALTER ROLE [SQL Monitoring] ADD MEMBER [DOMAIN\sqlmonitor]
grant EXECUTE ON [dbo].[agent_datetime] TO [SQL Monitoring] AS [dbo]
grant SELECT ON [dbo].[sysjobschedules] TO [SQL Monitoring] AS [dbo]
grant SELECT ON [dbo].[sysjobs] TO [SQL Monitoring] AS [dbo]
grant SELECT ON [dbo].[sysjobhistory] TO [SQL Monitoring] AS [dbo]
grant SELECT ON [dbo].[sysjobactivity] TO [SQL Monitoring] AS [dbo]
grant SELECT ON [dbo].[sysjobservers] TO [SQL Monitoring] AS [dbo]

This will add the user "DOMAIN\sqlmonitor" to SQL Server, create role "SQL Monitor", add the user to that role and grant appropriate permissions to the role and user.

• In Zabbix, modify the host macros like this:
  • {$MSSQL.DSN} - set to the DSN name ([SQLServer]) that you created above in odbc.ini file
  • {$MSSQL.USER} - set to an empty string
  • {$MSSQL.PASSWORD} - set to an empty string

You should now be using Windows Kerberos Integrated authentication to connect to SQL Server.

Hope this helps somebody trying to set this up.
This is a much more secure setup, because credentials are not stored in Zabbix or the odbc.ini file and only needed once to create the Kerberos ticket.
Authentication failures may be due to the SPN (service principal name) of the service account that SQL Server is running under not being correctly set. Kerberos authentication requires correct SPNs to be set. Check for SPNs starting with "MSSQLSvc" either in the computer object or Managed Service Accounts. You can find information on how to correctly define the SPNs in Microsoft documentation.