The answer to your first query is that if the ESMDBA is granted db_datareader role instead of granting permissions on individual objects it is sufficient.

As for the scripts used for assigning individual permissions, the following script may be used.

GRANT SELECT ON [dbo].[sysdatabases] TO [domain1\user1]
GRANT SELECT ON [dbo].[sysusers] TO [domain1\user1]
GRANT SELECT ON [dbo].[syslogins] TO [domain1\user1]
GRANT SELECT ON [dbo].[sysobjects] TO [domain1\user1]
GRANT SELECT ON [dbo].[sysxlogins] TO [domain1\user1]
GRANT SELECT ON [dbo].[syscurconfigs] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_regread] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_instance_regread] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helpuser] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helprole] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helprolemember] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helpsrvrole] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helpsrvrolemember] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_loginconfig] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_startmail] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_stopmail] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_instance_regenumkeys] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_configure] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[xp_sqlagent_proxy_account] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helprotect] TO [domain1\user1]
GRANT EXECUTE ON [dbo].[sp_helpdb] TO [domain1\user1]

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
*********************

Hi Damodar, thank you very much for this process.  Just one last question, what Windows level permission does the SomeDomain\esm_sql_service user have on the agent machine?  Do you have it configured to just be a member of the local 'Users' group or does it have the log on privileges by virtue of just being a 'domain user'?

I am finding that no matter what permissions the user has on the local system, I can not successfully run a policy scan unless it is a member of builtin\administrators.  This can't be the way it has to be.  There may be some further hardening in place...

Thanks,
Sunny

Also try enabling logging for the mssql modules and see what the logs have to say.
The steps to enable logging will be available in the SU 35 release notes. [Download release notes from
http://www.symantec.com/avcenter/security/ESM/esmS... ]

Hi Damodar,

This has all been very helpful.  Most of the steps I had already run through already but i just double checked everything..

1. I can log in successfully
2. I can log in to Management Studio
3. I can execute the query successfully with no errors

4. the SQL account for security checking is also correctly set up:
E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>mssqlsetup -l

   *** Configuration records   ***
SQL Server : AJLCD-ESMSQL1\DEVESM
SQL Server login : LABCORP\svc.esmsqlagentdev

   *** Generic Credential Information ***

Generic Credential Information Not Available.

   ***   ***   ***   ***

5. When i execute the policy run it still gives me the message 'Connection with a server failed'.

6. I tried using the extended logging features as well, but it seems that in this example the modules don't really ever run - even though I can see them in the task manager while the policy scan kicks off, if only for a few seconds each.  Therefore, the logs are not generated.  It cannot connect to execute any queries to start off with.

7. if the account is in the local admins, then the policy scan does not have errors, and the mssql log files get generated.

Its very odd.  Its like i've done everything that we can think of correctly but it just doesn't work.  I am fairly convinced this environment has some other lock-downs in place which may be causing the interference.  The esmagent.log file does not provide any further information.  The log information i have at this point is inconclusive and i can not pinpoint the issue.

I did do some independant testing in a separate lab on my own, and everything worked fine as just a domain user...  This one remains a bit of a mystery for now.

Thanks for all of you help so far.  If you think of anything else, or any other way to debug do let me know.

One aspect that you have already pointed out is that it could be an environment issue which has gone wrong somewhere.
I know you might have tried many possible solutions so far. Am suggesting couple of other area which you could look into.
What esm's mssql modules really do underneath is use microsoft's osql/sqlcmd tool (which is avialable under the ms sql server installation's root folder) to fire queries and the results of which are analyzed  by the mssql module for checks reporting.
What you can try is as follows, which should confirm why the module is not working with domain user account:
1) Go to the system32 folder (e.g. C:\WINDOWS\system32), right click on cmd.exe, and run as the domain user. i.e. LABCORP\svc.esmsqlagentdev in your case.
2) If you have any logon errors whilst running as the domain user, then edit your Local security policy to grant the user 'Allow logon locally' right.
3) In the newly opened instance of command prompt, change your current directory to the latest installed version of ms sql server directory where you will be able to find the 'sqlcmd' utility. (For e.g. C:\Program Files\Microsoft SQL Server\90\Tools\binn) Let us call this as '$sqlprompt>'
4) From this prompt, run the following query to connect using windows authetication:
$sqlprompt>sqlcmd.exe -S AJLCD-ESMSQL1\DEVESM
5) You should have a successful connection. If this fails then you will need to resolve this else esm's mssql modules will not work.
6) If you have a successful connection, then just confirm that the currently logged on user for that session is indeed 'LABCORP\svc.esmsqlagentdev' by running the following query: sqlcmd -S AJLCD-ESMSQL1\DEVESM
sample command with expected output
##########################################################################
C:\Program Files\Microsoft SQL Server\90\Tools\binn>sqlcmd -S AJLCD-ESMSQL1\DEVESM
1> SELECT HOST_NAME() AS HostName, SUSER_NAME() LoggedInUser
2> go
(Results follow)
HostName                                                 LoggedInUser
-------------------------------------------           -----------------------------------------
AJLCD-ESMSQL1                                    LABCORP\svc.esmsqlagentdev
##########################################################################

All the best! :-)

So i logged in to the system as the service account domain user.  note it is a virtual machine, so i am logging in through terminal services, rdp, but the allow log on locally right has also been set.  as a 'non' domain admin user - the way we want it to be working....

- I log in to the machine successfully
- open a command prompt
- cd to the tools\binn directory
- run the sqlcmd -S servername\instancename
- receive a $sqlprompt
- run the query SELECT HOSTNAME...
- receive the output identical to your sample output
- quit

Then i kick off the policy run and although the other modules work fine (non SQL modules), I get a successful ESM policy run with no errors reported.  When I go to view the messages under any of the SQL modules I receive back one yellow message:

Title | Connection with a server failed |  The ESM Modules for SQL Server failed to connect with a MSSQL Server server. This can occur because the server is not up, the login account has been removed, or the password for the login account is changed. Symantec recommends that you restore the server, restore the login account or restore the password for the login account.
Level | Yellow
Updateable/Correctable | None
Name | AJLCD-ESMSQL1\DEVESM
Information | Connection failure: Error:

The message name is ESM_MSSQL_CONNECT_ERR and the description does not provide any more help to fixing the issue.

So in conclusion it is quite odd.  As the specified user i can do all of the things that the ESM SQL modules are supposed to be doing and yet it just does not work.  Would it have anything to do with the fact that the ESM Agent is running under LOCAL SYSTEM and then it tries to use the domain user account to connect...  some type of Impersonation error/issue? ...  hmmm

Thanks again, you have been most helpful in guiding me through the steps.  Especially since i am no DBA!

Cheers,
Sunny

Wow.. this seems to be a tough nut to crack. :-)
First lets answer you query - "Would it have anything to do with the fact that the ESM Agent is running under LOCAL SYSTEM and then it tries to use the domain user account to connect...  some type of Impersonation error/issue?"  The answer to this is NO.
Since when windows authentication is used, all sql queries are run through the command prompt created as a process in context of the specified domain user's credentials. If there is a problem here, then the module will not be able to create a command promt process, in case of which the module will give you a different error.

Somehow I get a feeling that this problem is specific to virtual machine setup.
Can you try these two things:
FIRST TASK
#################################################################################################################
1) On the agent machine go to esm's bin directory
For e.g. cd "C:\Program Files\Symantec\ESM\bin\w3s-ix86"
2) From there run mssqlsetup again to re-enter the server configuration details along with user name and password. Only this time instead of using the 'hostname\instancename' format as mssql serevr name, try providing the 'IP Address\instancename'.
3) Now try running the policy.
#################################################################################################################
SECOND TASK:
#################################################################################################################
1) On the agent machine go to esm's bin directory
For e.g. cd "C:\Program Files\Symantec\ESM\bin\w3s-ix86"
2) Try creating a module input file by running the modinput.exe utility
For e.g.
C:\Program Files\Symantec\ESM\bin\w3s-ix86>modinput.exe -t -p 5600 -m esmmanagername -U esmusername -P esmuserpassword -o esmmssql.mif esmmssqlpolicyname mssqlaccount
3) A file by name 'esmmssql.mif' should be created in the current location
4) Execute the mssqlaccount module with the mif file as input
For e.g. C:\Program Files\Symantec\ESM\bin\w3s-ix86>mssqlaccount esmmssql.mif
5) Does the above yield any results or does it return the same connection failure errors?
#################################################################################################################

Best Regards,
Damodar Shanke

Hi Damodar,

thanks for the new instructions...  here's what's happened:

when using the IP instead of hostname, it makes no difference.

For the second task,
i created the mif file as follows:
E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>modinput -t -p 5600 -m <ESM_Manager> -U ESM -P <password> -o esmmssql.mif _MSSQLTest mssqlaccount

then ran the accounts module:
E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>mssqlaccount esmmssql.mif
N       1000    ajlcd-esmsql1   2009/08/17 10:28:00
N       1001    Encryption Exception    <AxStringCode Code=863131369>%1. %2</AxStringCode>      2       Critical Error occured during encryption.       Module should be reconfigured

So this is very interesting, and something new at least.  If i run the command when the user is part of the administrators group, i get:

E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>mssqlaccount esmmssql.mif
N       1000    ajlcd-esmsql1   2009/08/17 10:36:16
N       220000  AJLCD-ESMSQL1\\DEVESM   <AxStringCode Code=124585312>Connection failure: Error: %1</AxStringCode>       1       CreateProcessAsUser() error for cmd: "E:\\Program Files\\Microsoft SQL Server\\90\\Tools\\Binn\\sqlcmd.exe" -S A
JLCD-ESMSQL1\\DEVESM -E -Q "select @@servicename as 'ServiceName'" -w 65535, err no: 1314. Cannot open osql for execution.

AND YET, the policy run is successful in this case...  If i try to just run the above command:

E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>sqlcmd -S AJLCD-ESMAPP1\DEVESM -E -Q "select @@servicename as 'ServiceName" -w 65535
HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

However, I checked the server properties and remote connections are allowed to this server\instance.

Also, as the domain user i want to perform SQL security checking with, if i run the mssqlsetup command i get the similar error as the first one:

E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>mssqlsetup -l
Critical Error occured during encryption.
Module should be reconfigured

It looks like perhaps the user does not have the correct permission level to run the modules or something.  I gave it full control over the ESM directory and still no.  there may be another location or temp space it needs access to perhaps that is being blocked?  I will continue to try and check on this issue...  if you can think of anything else in the mean time, do let me know.

thanks,
Sunny

The encryption errors occurs due to corruption to one of the mssql configuration files. Please delete the files listed below which should be available under '#esm/config/' folder and try running mssqlsetup, from the bin directory again(i.e. RECONFIGURE the mssql server records by running mssqlsetup utility).
Files to delete:
1) AESConfigMSSQLSERVER.dat
2) MSSQLServerModule.dat
3) RandMSSQLSERVER.dat

P.S. I hope you are logged in as administrator on the agent machine when you are running the modules with mif files. Do not log in as domain user.
The reason for this is as follows: When you run the policy via esm console/manager, the esmagent triggers the module run which is running as 'SYSTEM' account. Hence it is able to grant rights to domain users to actually creating the sqlcmd process. Since you cannot log in as SYSTEM, log in as administrator as it has the relevant rights. No doubt, the sql queries will be fired under the context of domain users.

I found a technote that talked about this as well.  It seems the files are locked and i needed to stop the service first.  however, i only get this 'encryption error' when i am logged in as the domain user.  like it does not have the privileges to run the command - which kind of reasserts your point above that this user cannot run a command as SYSTEM.

I did run it as the admin, and don't get the encryption error but the following:

E:\Program Files (x86)\Symantec\ESM\bin\w3s-ix64>mssqlaccount esmmssql.mif
N       1000    ajlcd-esmsql1   2009/08/17 10:36:16
N       220000  AJLCD-ESMSQL1\\DEVESM   <AxStringCode Code=124585312>Connection failure: Error: %1</AxStringCode>       1       CreateProcessAsUser() error for cmd: "E:\\Program Files\\Microsoft SQL Server\\90\\Tools\\Binn\\sqlcmd.exe" -S A
JLCD-ESMSQL1\\DEVESM -E -Q "select @@servicename as 'ServiceName'" -w 65535, err no: 1314. Cannot open osql for execution.

I don't know if this is what is expected or not.  I don't think i need to delete the files since there is no error when using the admin logon.  but i can try this as well.  i'm thinking it is just a false positive message for the error and its not corrupted files...  but i can give it a shot.

I don't think we need to spend too much more time on this.  I really appreciate all of your help.  We have discovered that in another environment, different domain, and in my isolated test lab - the documentation works fine with just 'allow log on locally'.  At this point, I believe the issue has something to do with security policies being pushed down through AD.  Oddly the development environment seems harder than production.  We may work to nail down what is causing the failure, but thats controlled by another group, so it could take some time.

this has certainly been an interesting challenge, but i believe that i've got the information necessary to make the appropriate recommendations and build a plan to move forward.  Again, thanks so much i've even learned a lot as I go.  now if i could only figure out how to write in sql...

Sunny