Hi,

Thanks Rupali, you're right, I had assumed that the agent modules were for the manager. After I installed the application modules on the agent, the policy run worked.

Many Thanks,

Akah

May i ask what permissions you have assigned for the security checking of your SQL modules?  That is, what account are you using to connect to and enumerate the checks on the database(s)?

When i use an sql account (other than 'sa' which of course works) with the public role, it seems to work fine.  I am trying however to use a domain account with the same premissions in SQL.  It fails, unless the domain account is also a member of the local administrator's group.  Even when the builtin\Administrators has no log in rights to sql, this will still work.  It seems there are permissions required in two places - the windows server and the sql server on the same box.  when using an SQL account, the windows server setting are irrelevant.  but when using a domain account, they are not and i have yet to find the right combination of permissions to restrict the account without using the builtin\administrators group for the domain [service] account.

any assistance in this matter would be most appreciated.

Thank you
Sunny

I use the following steps and it works fine for me:
1) Let's assume the domain name is 'SomeDomain'.
2) Create a domain user by name 'esm_sql_service'. So now we have a user by name 'SomeDomain\esm_sql_service'
3) Add this user as a login onto the sql server that you want it to report on. I normally do this using 'Management Studio' or the 'Enterprise manager' since I can easily and visually verify using the UI that the domain user is actually reachable However you can use the following query: EXEC sp_grantlogin 'SomeDomain\esm_sql_service'
4) I grant the user all the required defined permissions. It would be a good idea to add this user to the sysadmin group otherwise some checks might fail to report.
5) Once this is done, what i generally do is log on to the member server (agent machine and not sql server) using the newly created domain user name ('SomeDomain\esm_sql_service' in our case), just to verify that we can log on properly to the agent machine using this newly created domain account. It would also be a good idea to verify that you can connect to your server using the sql management studio or sql enterprise manager.
6) If you can successfully connect to the server, and execute some queries, then this account when configured with ESM SQL server should also give you no problems.
7) Now run the mssqlsetup to configure this account. Make sure that you are providing the complete format. i.e. 'domain\user' whilst configuring.
8) Run  policy with one of the mssql modules in it. It would be a good idea to run the mssqlaccount module, since it requires minimum permissions, to see if the modules report fine or not.

Below I mention the permissions which are required for all versions of ms sql server (2K, 2K5, 2K8)
************************************************************************************************************
ALL select master..syslogins
ALL exec master..sp_helpsrvrolemember
ALL exec sp_helprole
ALL exec sp_helprolemember
ALL exec sp_helpuser
ALL exec master..xp_instance_regread
ALL select databasepropertyex
ALL exec master..xp_instance_regenumkeys
ALL select serverproperty
ALL select sysusers (for every databse)
ALL exec sp_helprotect
ALL exec master..sp_helpdb
ALL exec master..xp_loginconfig
ALL exec master..xp_regread
ALL exec master..xp_startmail
ALL exec master..xp_stopmail
ALL select @@servicename as 'ServiceName'
****************************************************

Following permissions are required explicitly on Sql Server 2000
***********************************************************************
select sysobjects (for every databse)
select information_schema.routines
select master..sysxlogins
select master..syscurconfigs
select master..sysdatabases
EXEC master.dbo.xp_sqlagent_proxy_account
select master..sysservers
select objectproperty
exec master..sp_configure
*******************************

Following permissions are required explicitly on Sql Server 2005 and Sql Server 2008
*********************************************************************************************
select master.sys.server_principals
select master.sys.server_permissions
select master.sys.databases
select master.sys.configurations
select master.sys.endpoints
exec msdb.dbo.sp_help_proxy
select sys.database_principals
select sys.objects (for every database)
select master.sys.databases
select syscomments
select sys.procedures
select master.sys.sql_logins
select master.sys.server_principals
exec master..sp_helpsrvrole
select serverproperty('productversion')
master.sys.server_permissions
master.sys.servers
master.sys.configurations
select sys.objects
select sys.sql_modules
select sys.schemas
select sys.objects
*********************