I am wanting to run some custom SQL to return a single value for a data check.
The SQL Server is being monitored via an Agent, with the agent initiating the communication. There is no direct connection.
The Custom SQL works as expected, I've run this on the server.
The Credentials are Windows Domain Credentials and work perfectly with the AppinsightSQL application Monitor.
I've created a new Application template and added the SQL Server User Experience Monitor, Selected the same Credentials used for the AppInsight.
For debugging the SQL Statement is 'Select 1'
No Instance is specified, its the default.
Database name is specified in the Catalog.
If i Check the 'Windows Authentication First' i get the following:
Cannot open database "<DATABASE_NAME>" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\SYSTEM'.
I believe this is the account the account the Agent is running with on the Server.
When i uncheck 'Windows Authentication First' i get the following:
Login failed for user '<DOMAIN>\<ACCOUNT>'
The account has the necessary access rights for the database.